[#1000467] Can't use (b)lobs with Omnis Studio v4

View Trackers | Bugs | Download .csv | Monitor

2005-12-07 13:23
Submitted by:
Assigned to:
Ludek Finstrle (luf)
Can't use (b)lobs with Omnis Studio v4

Detailed description

My company uses PGSQL v8.1 as backend database and Raining Data's Omnis Studio v4.1 as developpement tool (see http://www.omnis.net/. There is a demo version).

LOB (blobs, clobs) can't be used with Postgresql and ODBC, either using lo or bytea. Queries fail or the app' crashes.

I tried different settings with psqlOBDC with no luck.

I've joined my log.

If you have questions or need more details : nte@mustinformatique.fr


Nathanael TERRIEN.


Date: 2006-12-14 13:19
Sender: Must Informatique

This bug can be closed.
Prior to Omnis Studio 4.2, Studio did'nt mapped correctly bytea.
Everything's working now (minus 2 problems on the Omnis side that will be corrected shortly I hope).
You have got to have :
- Onmis Studio 4.2
- a patched damodbc.dll (>=04/12/06) from Raining-Data
- psqlODBC driver v8.2.2+

To use blobs with PG, in Omnis, set those properties of the connexion to a high value (like 100 millions like $blobsize) :$lobchunksize and $lobthreshold.

To use PG's TEXT datatype in Studio, you have got to set "MaxLongVarCharSize" to a maximum of 65 millions (Omnis Studio can't fetch data from a table with a TEXT column if you set it above 65 millions).

To use Studio's VCS on PG via ODBC, you have got to set $lobchunksize and $lobthreshold to a high value (see above) in the "Advanced" tab of the VCS connexion window.

Nathanael TERRIEN.
Date: 2006-06-07 11:42
Sender: Must Informatique

Oops I didn't finish a sentence : I will send you the logs from both attempts.
Date: 2006-06-06 19:10
Sender: Ludek Finstrle

I take a look at mylog:
1) Could you ask Omnis why thay send query: select @@version
It's MS-SQL syntax which isn't supported by PgSQL (I can wrote some hack patch which will be without more support - so it isn't solution).
2) Could you try the latest dll at http://www.geocities.jp/inocchichichi/psqlodbc/index.html ?
The problem with columns information (in 08.02) may be fixed there (in CVS too).

If I'll be faster I test it here. But it helps me when you try it and post here the result.
I'm going to look at it tomorrow or during weekend.


Date: 2006-06-06 10:23
Sender: Ludek Finstrle

I receive your files and I attach them here.


Date: 2006-06-06 07:45
Sender: Must Informatique

Hi. I've remailed the files.
And I received an e-mail from the Beta Program at Omnis Studio about the problem with PgSQL.
Here it is :

The "ERROR : length for type varchar can not exceed 10485760" problem is a bug with the PGSQL ODBC driver. The PGSQL driver reports BYTEA, TEXT and VARCHAR precisions incorrectly (fault reference ST/*B/039).

We have added patches in the ODBC DAM release candidate to work around these issues (I will send this to you separately via email).

"The PGSQL ODBC driver also has problems fetching binary data (e.g. lists) and we have obseved similar crashes in doing so. (Your Microsoft error report should show that the crash is occuring within the ODBC driver).
Date: 2006-06-04 17:33
Sender: Ludek Finstrle

I get no message from you. I have problems with e-mails on Friday. Please could you repost it once more?


Date: 2006-06-02 12:40
Sender: Must Informatique

I couldn' find how to add more files here so I send them all to Ludek Finstrle.

Date: 2006-06-02 12:40
Sender: Ludek Finstrle

Feel free to send files to me directly via e-mail if you're unable to attach it here. I hope I have some time furing this weekend.


Date: 2006-06-02 12:32
Sender: Must Informatique

I have a few more tests and still get the same errors.
I'm uploading 3 more files :
One "mylog" (mylog_3608.log) while trying to visualize a table structure within Omnis studio (with the integrated SQL Browser).
Another "mylog" (mylog_3052.log) while Omnis is trying to create the tables for a VCS database (with blobs).
And finaly, a pdf file (20060602_OmnisStudio4_PGSQL_ODBC_BUG.pdf) wich is an "How to", to help you reproduce those simple tests with demo version.
It implies no programming at all, just using the integrated IDE tools of Omnis Studio.
Hope this helps.

PS : I've also contact Omnis Studio's editor, Raining Data. I'm in their beta program for the next version (4.1.5) and they have taken in account the problem with postgresql. But this no luck yet :(

Thanks for your help.
Date: 2006-06-01 18:48
Sender: Ludek Finstrle

Oh, I mean mylog output from 08.02.0002 which is unicode driver.


Date: 2006-06-01 18:47
Sender: Ludek Finstrle

Could you send us the mylog output or better the minimalistic example which leads to error?
I have downloded Omnis studio but I see no help from you.

I'm looking forward

Date: 2006-05-10 07:13
Sender: Must Informatique

BLOBs still don't work correctly with version 08.02.0002.
Worse, through an ODBC connexion, Omnis Studio can't see anymore the tables and their structure, though it was working with previous version and querries seem to be still functionning.
But we use to use the Unicode driver of the previous version and everything is coded with UTF8 on the DB server. Is is related ?
Date: 2006-04-28 09:12
Sender: Dave Page

Please test version 08.02.0002 (available from http://pgfoundry.org/frs/?group_id=1000125) and report whether or not the problem still exists.
Date: 2006-01-18 10:34
Sender: Dave Page

Luf has been dealing with this off-tracker. Bug re-opened.
Date: 2006-01-18 10:18
Sender: Dave Page

No response from reporter - bug deleted.
Date: 2005-12-07 15:15
Sender: Ludek Finstrle

It seems to me that it's coming cutted to ODBC driver.
There is no source code of OStudio so I don't know why it is doing it this way. Maybe psqlODBC answer bad information before. But I don't know where and I don't know what OStudio expects.

How do you created lo type? Could you try newer ODBC driver? Could you post minimalistic example (or steps leads to reproduce it)? I don't want to learn OStudio.

I don't think that OStudio sent exactly the same query ;-)
Date: 2005-12-07 14:55
Sender: Must Informatique

Sorry for the double post before.

Yes, I've read the logs.
You're right, of course, this isn't a correct SQL command and that's the real problem :)

The SQL commande seems to be "cut" and I don't why or by whom (Omnis Studio or the psqlODBC driver) ... but you should know ;).
ANd it's cut just before a blob datatype.
FYI, the exact same command sent by OStudio to MySQL 4.x through ODBC works perfectly and create a "mediumblob" field.

Those logs aside (wich result of an automated VCS Database creation monitored by OSTudio), I've never been able to use lo/bytea blobs with psqlODBC and Omnis Studio : it crash or read/write faulty data.

I have, of course, sent a bug report to Raining Data too but I thought I should inform the great psqlODBC team also ;)

Thanks for looking at this issue, regards,

Date: 2005-12-07 14:04
Sender: Ludek Finstrle

I upload logs and delete second report ...
Date: 2005-12-07 14:03
Sender: Ludek Finstrle

I see no problem with lo or bytea:
CREATE TABLE VCS_ComponentData (vcs_ID int4 NOT NULL,vcs_ChunkID int4 NULL,vcs_ChunkSize int4 NULL,vcs_ChunkChecksum int4 NULL,vcs_Data

This isn't SQL command. Do you read what driver report?

STATEMENT ERROR: errnum=7, errmsg='Error while executing the query'
CONN ERROR: errnum=108, errmsg='ERROR: syntax error at end of input au caractĨre 137'

Please don't create new bug report. Either log in before you report bug or send additional information to pgsql-odbc mailing list.


Field Old Value Date By
ResolutionPostponed2006-06-06 10:26luf
File Added113: mylog_3608.log2006-06-06 10:26luf
File Added112: mylog_3052.log2006-06-06 10:25luf
File Added111: 20060602_OmnisStudio4_PGSQL_ODBC_BUG.pdf2006-06-06 10:23luf
status_idError - Not Found2006-01-18 10:34dpage
status_idOpen2006-01-18 10:18dpage
close_date2006-01-18 10:182006-01-18 10:18dpage
File Added62: psqlODBC_OmnisStudio_logs.zip2005-12-07 14:04luf
assigned_tonone2005-12-07 14:03luf
ResolutionNone2005-12-07 14:03luf
Powered By FusionForge