[Gambas-user] escape a picture
Hi, How can I escape a picture in order to insert it into a BYTEA postgresql data field? -- Cocaine: using tomorrow's energy today. -- Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july ___ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user
Re: [Gambas-user] escape a picture
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
--
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
___
Gambas-user mailing list
Gambas-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/gambas-user
Re: [Gambas-user] escape a picture PS
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
--
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
___
Gambas-user mailing list
Gambas-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/gambas-user
Re: [Gambas-user] escape a picture
richard terry a écrit :
No Richard, I can't do that for:
* I already have a connection, and I wanna keep connection number as low
as possible,
* I do things as they should be done, so not any direct user permission
on any DB schema/table/index/sequence/etc; So I can't insert anything
directly,
* I already have a big trigger on this table (ON INSERT) and I don't
wanna bloat it (and this wouldn't solve the PB.)
So I *really* need to send an escaped string to my procedure.
Regards
JY
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.
--
A watched clock never boils.
--
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
___
Gambas-user mailing list
Gambas-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/gambas-user
Re: [Gambas-user] escape a picture PS
richard terry a écrit : ... 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; ^^^ You like to live *very* dangerously... 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) This is not acceptable, nor usable! 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. I've got quite a same DB. Once again, this is not acceptable and a large flaw in design, not to mention the lack of security. 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. You can do that if your procedure gain the SU permissions, which, once again, is not acceptable (at all.) 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. I'm gonna ask the Pg ML; if there's no possibility I'll leave gambas and return to Python. Hope this all helps. Unfortunately not :( Regards JY -- X-rated movies are all alike ... the only thing they leave to the imagination is the plot. -- Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july ___ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user
Re: [Gambas-user] escape a picture PS
richard terry a écrit : ... Note also with gambas. I've raised this with the list/devel/benoit and had no replies Ducking is never a good answer... -- The proof of the pudding is in the eating. -- Miguel de Cervantes -- Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july ___ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user
Re: [Gambas-user] escape a picture
Am Dienstag, den 24.11.2009, 19:30 +0100 schrieb Jean-Yves F. Barbier: Hi, How can I escape a picture in order to insert it into a BYTEA postgresql data field? Salut, no idea whats a BYTEA, I looked in the I-net, and found something in german. http://www.ms-office-forum.net/forum/sitemap/index.php?t-243720.html - encode the binary file using base64 and store as TEXT - is using ADODB.Stream (are there something like that in gambas ) Amicalement -- Charlie - growing old is mandatory, growing up is discretionary. - vieillir est obligatoire, Grandir est discrétionnaire. - Altwerden ist obligatorisch, aufgewachsen ist Ermessenssache. -- Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july ___ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user
Re: [Gambas-user] escape a picture
Hi, How can I escape a picture in order to insert it into a BYTEA postgresql data field? Normally, if the postgresql field datatype is BYTEA, the gb.db component will see it as a blob. Let's suppose the field name is Picture. You will do: MyResult!Picture = File.Load(/path/to/picture/file) Is it what you need? -- Benoît Minisini -- Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july ___ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user
Re: [Gambas-user] escape a picture
Benoît Minisini a écrit : Hi, How can I escape a picture in order to insert it into a BYTEA postgresql data field? Normally, if the postgresql field datatype is BYTEA, the gb.db component will see it as a blob. Let's suppose the field name is Picture. You will do: MyResult!Picture = File.Load(/path/to/picture/file) Is it what you need? NO, I need the opposite: sqlQry = SELECT MyTable_ins(MyPicture); where MyTable_ins(TEXT) is a stored PLPGSQL procedure that do an indirect insert into myschema.mytable -- Every journalist has a novel in him, which is an excellent place for it. -- Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july ___ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user
Re: [Gambas-user] escape a picture
Charlie Reinl a écrit : ... http://www.ms-office-forum.net/forum/sitemap/index.php?t-243720.html - encode the binary file using base64 and store as TEXT - is using ADODB.Stream (are there something like that in gambas ) Thanks Charlie -- Q: What does it say on the bottom of Coke cans in North Dakota? A: Open other end. -- Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july ___ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user
Re: [Gambas-user] escape a picture
Benoît Minisini a écrit : Hi, How can I escape a picture in order to insert it into a BYTEA postgresql data field? Normally, if the postgresql field datatype is BYTEA, the gb.db component will see it as a blob. Let's suppose the field name is Picture. You will do: MyResult!Picture = File.Load(/path/to/picture/file) Is it what you need? NO, I need the opposite: sqlQry = SELECT MyTable_ins(MyPicture); where MyTable_ins(TEXT) is a stored PLPGSQL procedure that do an indirect insert into myschema.mytable OK. So MyPicture must be a string including quoted binary data? Alas there is no public access to the internal driver function that quotes binary data for a blob. Do you want me to add it in Gambas 3? Something like DB.QuoteBlob()? -- Benoît Minisini -- Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july ___ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user
Re: [Gambas-user] escape a picture
Benoît Minisini a écrit : Benoît Minisini a écrit : Hi, How can I escape a picture in order to insert it into a BYTEA postgresql data field? Normally, if the postgresql field datatype is BYTEA, the gb.db component will see it as a blob. Let's suppose the field name is Picture. You will do: MyResult!Picture = File.Load(/path/to/picture/file) Is it what you need? NO, I need the opposite: sqlQry = SELECT MyTable_ins(MyPicture); where MyTable_ins(TEXT) is a stored PLPGSQL procedure that do an indirect insert into myschema.mytable OK. So MyPicture must be a string including quoted binary data? Alas there is no public access to the internal driver function that quotes binary data for a blob. Do you want me to add it in Gambas 3? Something like DB.QuoteBlob()? What would also be nice would be direct conversions (ie: from String to Picture and reverse) -- Assume a virtue, if you have it not. -William Shakespeare -- Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july ___ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user
