How to avoid encoded blob parameters

Forum related to PostgreSQL

Moderators: gto, cipto_kh, EgonHugeist, olehs

tarnschaf
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 18.06.2012, 23:07
Location: Germany

How to avoid encoded blob parameters

Postby tarnschaf » 27.09.2012, 22:39

I think (hope) that I have a rather simple question for ZEOS experts: We defined a table for uploading files to a Postgres 8.3 database, currently still with Zeos 6.6.6 stable, protocol "postgresql-8".

Definition of the table looks like this:

Code: Select all

CREATE TABLE files
(
  "ID" serial NOT NULL,
  "Customer" integer,
  "Filename" character varying(60),
  "Data" bytea,
  CONSTRAINT files_pkey PRIMARY KEY ("ID" )
)


For uploading files, I tried two possibilities:
1. Create ZQuery with am INSERT SQL and named parameters. Load the blob parameter with:

Code: Select all

qryInsert.ParamByName('data').LoadFromFile(filename, ftBlob);
qryInsert.ExecSQL;


2. Create a IZPreparedStatement with an INSERT SQL command, position-based parameters (?). Load the blob parameter into a stream and then:

Code: Select all

PreparedStatement.SetBinaryStream(3, FileStream);


Both approaches work fine - however they encode the binary data into a string like this:

Code: Select all

'\\\\377\\\\330\\\\377\\\\340\\\\000\\\\020JFIF....


This would not be critical if our 10MB images wouldn't be increased about four times when encoded. I searched the forums and found that others manage to send unencoded data - but how?

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1701
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 28.09.2012, 01:11

tarnschaf,

Do you use OID Blobs or bytea fields?

You need Zeos7 to get what you want.

Oid-Blobs can be sended in chunks(TZDataSet.Properties.Values['chunk_size'] := xxx) Also can you set TZQuery/TZTable.Options := [coPreferePrepared] and you can access real C-API lightning fast Prepared Statements which can send binary data as is...

No way with 6.6.6 except you use OID blobs. (I hope...)

For both versions: To get OID support add 'oidasblob=true' to the TZConnection.Properties.

Hope this will help you.
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

tarnschaf
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 18.06.2012, 23:07
Location: Germany

Postby tarnschaf » 28.09.2012, 20:22

Hi EgonHugeist,

I somehow aniticipated that YOU would reply. Thanks a lot!

The app is using bytea fields and I will have to read into OID blobs. It appeared to me that with the activated options all blob transfers are behaving differently which I didn't want.

Will get back to you as soon as I figured it out! The fast C-Api sounds very promising...

Tarnschaf

tarnschaf
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 18.06.2012, 23:07
Location: Germany

Postby tarnschaf » 30.09.2012, 21:11

Hi again,

I am experimenting with Zeos 7 to achieve better performance uploading those files. I checked out the trunk/ version from Sourceforge and installed it. Luckily, my application (on first sight) still works as before.

Changing the upload method back to use a TZQuery again and setting the option to soPreferdBla seems to solve the problem - the file is now uploaded as binary which is indeed a lot faster!

However, after upgrading to the new Zeos version I get an EInvalidPointer exception each time I call "inherited Destroy" in the main data view TFrame. The result is that the frame is not destroyed which causes a lot of other exceptions. The frame contains no Zeos components, but a lot of data aware stuff, DataSources that are linked to Zeos in a data module..

Is it neccesary to do some cleanup before destroying data-boudnd components? Is checking out trunk a bad idea?

Thanks for your help,
tarnschaf

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1701
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 01.10.2012, 06:16

tarnschaf,

You got Exceptions now? Which revision do you use? Older then 1840?

Can you explain me waht are you doing?
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

tarnschaf
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 18.06.2012, 23:07
Location: Germany

Postby tarnschaf » 01.10.2012, 12:39

Hi,

I checked out the latest revision of the trunk. The exception occurs when Free/Destroying a TFrame component. It did not occur with ZEOS 6.6. However, I am not able to reproduce it in a smaller application. The exception is NOT occuring when I remove an (empty) groupbox from the frame.
Thus, it looks to me like a memory corruption that happens on another place (earlier). I will further try to build a small application that reproduces this behaviour and then get back to you.

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1701
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 01.10.2012, 14:36

tarnschaf,

I will further try to build a small application that reproduces this behaviour and then get back to you.


Well that's a deal! I must admit i've no clue what could be going wrong....
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image


Return to “PostgreSQL”

Who is online

Users browsing this forum: No registered users and 1 guest