[Gambas-user] escape a picture PS

[richard terry]

On Wednesday 25 November 2009 07:46:31 you wrote:
> On Wednesday 25 November 2009 05:30:52 Jean-Yves F. Barbier wrote:
> > Hi,
> >
> > How can I escape a picture in order to insert it into a BYTEA
> > postgresql data field?
> 
> Having mucked around with this interminably looking at different ways, the
> solution is in the picture database sample file, but basically do something
> like this and you won't need to manually escape anything. You would use a
> different connection method like in the picture db not my function
> (modDbconnect), I've commented this for  you
> 
> Public Function Image_Save(ImagePath As String, Optional fk_Image As
>  Integer = 0) As Integer
>   Dim newPicture As result
>   Dim $Result As Result
>   Dim img As Image
>   Dim tempfile As String
>   Dim conn As Connection
>  'get the connection to the backend
>  conn = modDBConnect.Get_Connection()
> 'connect to the table
>   newPicture = conn.Create("all_images")
> 'save the picture file to a tempory file
>   img = Image.Load(ImagePath)
>   tempFile = Temp() & ".png"
>   img.Save(tempFile)
> 'save to the database
>   If Not fk_image Then
>     newPicture["image"] = File.Load(tempFile)
>     newPicture.Update()
> 'don't worry about this I just needed the pk, perhaps there is an easier
>  way $Result = modDBConnect.exec_query("Select currval('all_images_pk_seq')
>  as pk_image")
>   End If
>   Return $Result!pk_image
> 
> End
> 
> Think that's ok, notify me if dosn't work, but follow the picture databas
>  in samples line by line.
> 
> Regards
> 
> Richard
> 

Don't forget to commit your transaction


sample table:

CREATE TABLE all_images
(
  pk serial NOT NULL,
  image bytea,
  deleted boolean,
  CONSTRAINT all_images_pkey PRIMARY KEY (pk)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE all_images OWNER TO easygp;
GRANT ALL ON TABLE all_images TO easygp;
GRANT ALL ON TABLE all_images TO staff;


Note also with gambas. I've raised this with the list/devel/benoit and had no 
replies but after much angst it seems that you can't address schema.table, ie
you cannot do this:

 newPicture = conn.Create("myschema.all_images")

I got tripped up for many many hours trying to figure out why these damn 
things wouldn't save, even when I just put a table called "images" into 
public, until I realised that throughout my db (of 350 odd tables scattered 
over 28 schemas, I have several "image" tables in different schemas so gambas 
was baulking without an error message when it got to the reference to "image" 
as  table. (hence the temporary name I've provided you "all_images" as I've 
not yet removed all the other tables due to my extensive use of views which 
I'll have to laboriously correct!

So in the end I've decided single table for all bytea data which I keep in 
public and (touch wood) seems to work quickly and easily. apparently you can 
store up to 1GIG in a bytea field but I suspect putting it in there could be 
real slow. 

You can use client side lo_creat,  and lo_import  functionsto directly write 
to blobs (different to bytea) in postgres as user (not the server side 
functions) but though easy to do on the postgres command line, I've not 
figured out how to do it in sql, and no-one on the postgresql novice list 
seemed to  know either. 

I spent probably 10 hours trawling the net to find a solution and though 
millions of references to it the 'smart ****$$%%^& ers' all sound really 
knowledgable by quoting the postgresl docs which in the end to idiots like 
myself mean nothing, but no-one seems to be able to offer a practicel sql 
solution. If you find out let me know.

Hope this all helps.

Regards

Richard