[Gambas-user] PictureDatabase with PostgreSQL

[richard terry]

On Wed, 15 Jul 2009 04:07:28 am Pino Zollo wrote:
> Hi,
>
> I am trying to use the example "PictureDatabase" with PostgreSQL but I get
> the following error:
>
> "Invalid byte sequence for encoding UTF-8"
>
> when executes the instruction:
>
>  newPicture.Update()
>
> in PUBLIC SUB Add(ImagePath AS String)
>
> The creation of the database structure seems correct:
> -----------
>   Tabella "public.pictures"
>    Colonna   |  Tipo  |                     Modificatori
> ------------------+--------+-----------------------------------------------
>-------- id               | bigint | not null default
> nextval('pictures_id_seq'::regclass)
>  thumb        | bytea  |
>  image         | bytea  |
>  description | text   |
> Indici:
>     "pictures_pkey" PRIMARY KEY, btree (id)
> -----------
>
> I have tried also to create manually the database with the option -E UTF-8,
> but no change. (By the way how can this done in GAMBAS ? as
> databaseConnection.Databases.Add(DBName)  does not permit other parameters)
>
> I do not understand if the problem is in PostgreSQL or in GAMBAS....
>
> It seems that that picture is treated as a text string instead of a blob.
>
> Thanks for any idea.  (PostgreSQL 8.3, GAMBAS 2.14, Debian 5.0.1, QT)
>
> Pino

Pino,

I have the solution for this problem as I struggled with it for ages, but my 
dad just broke his hip (87yrs) and has been urgently tranferred to hospital, 
so I can't think to much about it now, so mail me again if this dosn't help.

You'll need this sort of stuff:

The first routine someone gave me, the second just shows how to take a 
temporary image and insert it into postgres. the code is pretty old and not 
too clever as it was my first attempt and once I got it working I didn't 
change it. I use it to save diagrams, photographs, Xrays etc into my medical 
program.

CREATE TABLE clin_consult.images
(
  pk serial NOT NULL,
  image bytea,
  CONSTRAINT images_pkey PRIMARY KEY (pk)
)

Private Function COct(num As Integer) As String
  Dim octnumber As New Integer[]
  Dim octstring As String

  octnumber.Add(Int(num / 64))
  octnumber.Add(Int((num Mod 64) / 8))
  octnumber.Add((num Mod 64) Mod 8)
  octstring = octnumber[0] & octnumber[1] & octnumber[2]
  Return octstring
End

Public Function Image_Save(ImagePath As String) As Integer
   
   '------------------------------------------------
   'Saves an image file to clin_consult.images table
   'return its key
   '------------------------------------------------
  Dim hFile As Stream
  Dim buffer As Byte
  Dim picText As String
    
  Dim img As Image
  Dim newPicture As Result
  Dim pictureData As String
  Dim scale As Float
  Dim sql As String
  Open ImagePath For Input As #hFile
    While Not Eof(hFile)
      Read #hFile, buffer, Lof(hFile)
      If buffer < 32 Or buffer > 126 Then
        picText = picText & "\\\\" & Coct(buffer)
        
      Else
        Select buffer
          Case 92    ' mask  "|" char
            picText = picText & "\\\\134"
          Case 39    ' mask "'" char
            picText = picText & "\\'"
          Default
            picText = picText & Chr$(buffer)
         '   Print "was a default", Chr$(buffer)
        End Select
      End If
    Wend
  Close hFile
   '-----------------------------------
   'Start transaction, insert the piccie
   '------------------------------------
    sql = "insert into clin_consult.images(image)"
    sql &= "values(E'" & picText & "')"
    $Result = modDBConnect.exec_query(sql)
    sql = "SELECT currval( 'clin_consult.images_pk_seq') as pk"
    $Result = modDBConnect.exec_query(sql)
     Return $Result!pk
End


I think postgres now can auto-return fields so part of this code can be 
eliminated, so if you wanted the pk back:

$Result=  modDBConnect.exec_query("insert into clin_consult.images(image)  
returning pk)

ModDobConnect.exect_query is our own internal postgres handling module you'll 
have to use your own.

Hope this helps.

Regards

Richard