Postgresql & GetDatabaseBlob

[Droopy]

Hello,

I successfully add a binary picture of 34554 bytes into the database with the code below :

Code: Select all

;/ PureBasic 4.51

;- Store binary data

;/ Create DatabaseUsePostgreSQLDatabase()
If OpenDatabase(0, "host=127.0.0.1 port=5432", "postgres", "root")
  Debug "Open : Succès"
Else
  Debug "Open : Fail"
  End
EndIf

If DatabaseUpdate(0,"CREATE TABLE Pictures ( Blob1_data BYTEA) ; ")=0
  Debug DatabaseError()
EndIf

;/ Read the picture and put in a buffer (34554 bytes)
Picture.s="d:\Picture1.bmp"
PictureSize=FileSize(Picture)
Debug PictureSize
*buffer=AllocateMemory(PictureSize)
ReadFile(0,Picture)
ReadData(0,*buffer,PictureSize)
CloseFile(0)

;/ Add binary data to database
SetDatabaseBlob(0,0,*buffer,PictureSize)
If DatabaseUpdate(0, "INSERT INTO Pictures VALUES ($1);")=0
  Debug DatabaseError()
EndIf
FreeMemory(*buffer)

;/ Read Binary data to buffer
DatabaseQuery(0,"SELECT * FROM Pictures ;")
NextDatabaseRow(0)
PictureSize=DatabaseColumnSize(0,0) ;/ Get size of the blob
Debug PictureSize
*buffer=AllocateMemory(PictureSize)
GetDatabaseBlob(0,0,*buffer,PictureSize)

;/ Write buffer to file
Picture2.s="d:\Picture2.bmp"
CreateFile(0,Picture2)
WriteData(0,*buffer,PictureSize)
CloseFile(0)
FreeMemory(*buffer)

FinishDatabaseQuery(0)
CloseDatabase(0)

But when i extract the picture from the database the size is 69110 bytes ??

Thanks

[Droopy]

I tried inserting ZZZZ (4 bytes) and i Get x5a5a5a5a (10 bytes) when i read the database.
5a (hex) = 90 (dec) = Z (ascii)

Postgre encode binary data as hex (start with x, end with 0), buf GetDatabaseBlob doens't decode this.
is it a Purebasic bug ?

[Droopy]

This code could decode the buffer

Code: Select all

Structure OneByte 
  a.b 
EndStructure 

Procedure Hex2Dec(HexNumber.s) 
  *t.OneByte = @HexNumber 
  Result.l = 0 
  While *t\a <> 0 
    If *t\a >= '0' And *t\a <= '9' 
      Result = (Result << 4) + (*t\a - 48) 
    ElseIf *t\a >= 'A' And *t\a <= 'F' 
      Result = (Result << 4) + (*t\a - 55) 
    ElseIf *t\a >= 'a' And *t\a <= 'f' 
      Result = (Result << 4) + (*t\a - 87) 
    Else 
      Result = (Result << 4) + (*t\a - 55) 
    EndIf 
    *t + 1 
  Wend 
  ProcedureReturn Result 
EndProcedure 

Procedure DecodePostgreBlob(BufferAdress,BuffLenght) ; Return the size of the buffer
  
  For n=0 To (BuffLenght-2)/2
    c.s=Chr(PeekB(BufferAdress+n*2+1))+Chr(PeekB(BufferAdress+n*2+2))
    PokeB(BufferAdress+n,Hex2Dec(c))
  Next
  
  ProcedureReturn (BuffLenght-2)/2
  
EndProcedure

;/ Decode the buffer
PictureSize=DecodePostgreBlob(*buffer,PictureSize)

[HeX0R]

I stumbled across this behaviour also.

The problem is, that since postgresql 9.0 there also exists another method of saving/restoring blobs:
Hex (no, not me )

You can simply add

Code: Select all

bytea_output    'escape'

to your postgresql.config, restart your service
and it will work as usual.
(Data will not get corrupted, this is only the output-method)

Only problem i see is: how could i determine, how this flag is set at the users pc?
(Since PB does the escaping for me [which is not that bad generally] i don't see the difference anymore)
So for me i decided to go away from using blobs with postgresql and use a base64-text-field-combo instead.