[ psqlodbc-Bugs-1000467 ] Can't use (b)lobs with Omnis Studio v4

Bugs item #1000467, was opened at 07/12/2005 13:23
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1000467&group_id=1000125

Category: None
Group: None
Status: Open
Resolution: Accepted
Priority: 3
Submitted By: Nobody (None)
Assigned to: Ludek Finstrle (luf)
Summary: Can't use (b)lobs with Omnis Studio v4

Initial Comment:
Hi.

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

Thanks.

Nathanael TERRIEN.


----------------------------------------------------------------------

Comment By: Must Informatique (mustinfodev)
Date: 07/06/2006 11:41

Message:
Latest news from the Omnis Studio 4/psqlODBC front :

I tried the latest dll you mentionned, both with
- Studio 4.1.1  :  table structure can be seen, data can be inserted but vcs can't build tables.
- Studio 4.1.5beta2 (with modified odbc handling for pgSQL at my demand) : table structure can be seen, data can be
inserted,vcs manage to build tables but inserting vcs (blob) data fails or even crash studio. 
I will send the

In a recent e-mail, the omnis support tech team asks me to transmit this to you :
[quote]
It turns out that the SQL Browser library (i.e. code written in Omnis) is executing 'select @@version' purely to
determinewhether the connection is to MS-SQLServer. If the statement does not fail then a flag is set internally. 

Regarding the issues with VARCHAR, TEXT and BYTEA precisions, Postgres may be interested in how to reproduce the
problem.After logging on (with SQLConnect), there are a few calls to SQLGetInfo followed by the allocation of a
temporarystatement handle which is used fetch a list of ODBC data types supported by the driver: 
SQLAllocStmt
SQLGetTypeInfo(..., SQL_ALL_TYPES)
SQLFetch
SQLGetData(..., SQL_TYPE_NAME)
SQLGetData(..., SQL_DATA_TYPE)
SQLGetData(..., SQL_PRECISION)
SQLGetData(..., SQL_CREATE_PARAMS)
SQLGetData(..., SQL_UNSIGNED_ATTRIBUTES)

In the case of VARCHAR ((SQL_VARCHAR), the precision returned is 254
In the case of TEXT (SQL_LONGVARCHAR), the precision returned is 8190
In the case of BYTEA (SQL_VARBINARY), the precision returned is 254

Currently the ODBC DAM detects these types and changes the precisions to 0xA00000 (10485760) for VARCHAR, 0x7FFFFFFF
(~unlimited)for TEXT and 0x40000000 (1GB) for BYTEA.   

If Posgres manage to resolve these issues, it should not affect the behaviour of the DAM and means that we can activate
theworkaround conditionally upon the version number of the driver- allowing the DAM to continue working with the old
driver.

If Postgres have any other questions on reproducing the problem or with the ODBC DAM in general, they are welcome to
contactus directly via support@omnis.net.  
[unquote]

It would be great if you can deal about this directly with them as I always fear to miss or forget something.

If I sum up the situation :
- Studio 4.1.1+psqlODBC 8.01.x : works except (b)lob (thus vcs) management
- Studio 4.1.1+psqlODBC 8.02.x : sometimes crashes, sometimes works except (b)lob (thus vcs) management, table
structurereading and perhaps other problems I did'nt detect 
- Studio 4.1.5beta2+special ODBCDAM+psqlODBC 8.01.x : seems to work perfectly but with special tweaking
- Studio 4.1.5beta2(+special ODBCDAM or not)+psqlODBC 8.02.x : sometimes crashes, sometimes works except (b)lob (thus
vcs)management, table structure reading and perhaps other problems I did'nt detect 

PS : and many thanks for all the effort you put in resolving this problem :)

----------------------------------------------------------------------

Comment By: Ludek Finstrle (luf)
Date: 06/06/2006 19:10

Message:
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 -
soit 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.

Regards,

Luf

----------------------------------------------------------------------

Comment By: Ludek Finstrle (luf)
Date: 06/06/2006 10:23

Message:
I receive your files and I attach them here.

Thanks,

Luf

----------------------------------------------------------------------

Comment By: Must Informatique (mustinfodev)
Date: 06/06/2006 07:45

Message:
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 :

[quote]
The "ERROR : length for type varchar can not exceed 10485760" problem is a bug with the PGSQL ODBC driver. The PGSQL
driverreports 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
viaemail). 

"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). 
"
[unquote]

----------------------------------------------------------------------

Comment By: Ludek Finstrle (luf)
Date: 04/06/2006 17:33

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

Thanks,

Luf

----------------------------------------------------------------------

Comment By: Must Informatique (mustinfodev)
Date: 02/06/2006 12:40

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




----------------------------------------------------------------------

Comment By: Ludek Finstrle (luf)
Date: 02/06/2006 12:40

Message:
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
thisweekend. 

Thanks,

Luf

----------------------------------------------------------------------

Comment By: Must Informatique (mustinfodev)
Date: 02/06/2006 12:32

Message:
Hi.
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
simpletests 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
theyhave taken in account the problem with postgresql. But this no luck yet :( 

Thanks for your help.


----------------------------------------------------------------------

Comment By: Ludek Finstrle (luf)
Date: 01/06/2006 18:48

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

Regards,

Luf

----------------------------------------------------------------------

Comment By: Ludek Finstrle (luf)
Date: 01/06/2006 18:47

Message:
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

Luf

----------------------------------------------------------------------

Comment By: Must Informatique (mustinfodev)
Date: 10/05/2006 07:13

Message:
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
withprevious 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? 

----------------------------------------------------------------------

Comment By: Dave Page  (dpage)
Date: 28/04/2006 09:12

Message:
Please test version 08.02.0002 (available from http://pgfoundry.org/frs/?group_id=1000125) and report whether or not
theproblem still exists. 

----------------------------------------------------------------------

Comment By: Dave Page  (dpage)
Date: 18/01/2006 10:34

Message:
Luf has been dealing with this off-tracker. Bug re-opened.

----------------------------------------------------------------------

Comment By: Dave Page  (dpage)
Date: 18/01/2006 10:18

Message:
No response from reporter - bug deleted.

----------------------------------------------------------------------

Comment By: Ludek Finstrle (luf)
Date: 07/12/2005 15:15

Message:
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
reproduceit)? I don't want to learn OStudio. 

I don't think that OStudio sent exactly the same query ;-)

----------------------------------------------------------------------

Comment By: Must Informatique (mustinfodev)
Date: 07/12/2005 14:55

Message:
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/byteablobs 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,
Nathanael.



----------------------------------------------------------------------

Comment By: Ludek Finstrle (luf)
Date: 07/12/2005 14:04

Message:
I upload logs and delete second report ...

----------------------------------------------------------------------

Comment By: Ludek Finstrle (luf)
Date: 07/12/2005 14:03

Message:
I see no problem with lo or bytea:
[SQLExecute]
CREATE TABLE VCS_ComponentData (vcs_ID int4 NOT NULL,vcs_ChunkID int4 NULL,vcs_ChunkSize int4 NULL,vcs_ChunkChecksum
int4NULL,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
mailinglist. 

----------------------------------------------------------------------

You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1000467&group_id=1000125