TD-24918: [TD721] Error with BLOBs (or any other large datatype) on all database brands (SqlBase, Oracle etc)

[Dave Rabelink]

I changed the title of this topic to be more inclusive of other database brands.
So it is not only for Oracle, but for any database (SqlBase, Sql Server etc).



Ticket # 4311798
TD 7.2.1 throws an error when inserting LONG RAW or BLOB data using Oracle.

This error is given:

Code: Select all

ORA-01461: can bind a LONG value only for insert into a LONG column 

The same code does work ok in TD 7.2.0

Strangely, we see a difference in behavior when running in IDE where the code is part of the main source.
When running from EXE or the SQL is executed from a dynalib (apd) there is no error and the data is saved into database. When running our application in IDE, no error is given.

I tried to create a full testcase to reproduce it.
The testcase shows the error in all cases. So running from IDE, from EXE or when code is in dynalib.
I could not get the sample running fine from EXE or APD.
But at least, the sample shows the issue.

The attached testcase is self-contained. So it holds all needed files and will create/drop the needed table/columns
automatically, so long as the given db user has the rights.

Testcase

OracleLongInsertSelect.zip

1) Use the source OracleLongInsertSelect.apt
2) Change the database,user and password in the Init function of the main form
3) Change the sql.ini to be used for the oracle database
4) Press the upper button to get the Oracle client/server version (when needed)
5) Press "Insert DB" to insert a bitmap into the database

See that on TD 7.2.0 no error is given and insert is ok.
See that on TD 7.2.1 the mentioned error is given

To test other combinations, use the "select DB" button to fetch the previously inserted image from database and display on screen.
Use the radio buttons LONG RAW and BLOB to use these types accordingly.

The lower part of the form does text inserts.

This situation was tested on Oracle client 12.2.0.1.0 and server Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Screenshot_Error.png

[peter4711]

Hi Dave,

found the following in the 7.2.1 release notes:
Binding data type for long data will be cleared when executing SqlPrepare. Because of this change SqlPrepareAndExecute, executing SqlPrepare and SqlExecute internally, does work with SqlSetLongBindDatatype anymore. If application uses Long String variable as bind variable for binary data, application should split SqlPrepareAndExecute with SqlPrepare and SqlExecute and calls SqlSetLongBindDatatype between them.

So I think we have to rewrite some code...
HTH.
Peter

[Dave Rabelink]

found the following in the 7.2.1 release notes:
...

Wow. I have read it on release, but immediately forgot it

And yeah, you are right, the sample works again when you split SqlPrepare and SqlExecute.
Thanks for reminding me !

Well, now we need to find all locations where we use the single function implementation.

[Dave Rabelink]

Ok. So the splitting of prepare and the execute is a change by design.
But this still does not explain why we did not detect the issue when running the applications from executable.

We have a fully automated testsuite on most of the application features which is running on TD 7.2.1 for a while now.
All BLOB tests are ok. We insert/load many smaller and large blobs all the time. Images, scans, Word documents, huge texts etc etc.
All of them are inserted and loaded without errors and the data is fine.

The testcase provided in this forum thread is unable to reproduce the difference. Seems something in our project sources which prevents the issue to occur when running the applications from exe.

I will report this back to OpenText. Indicating that the splitting of SqlPrepareAndExecute solves the issues, but that they have to investigate why it could be that it is not detectable at runtime on our side.

[Dave Rabelink]

I changed the title of this topic to be more inclusive of other database brands.
So it is not only for Oracle, but for any database (SqlBase, Sql Server etc).

Meaning everyone has to scan their sources for usage of large SQL datatypes and check for SqlPrepareAndExecute occurences.
You need to change it to the implementation mentioned in the release notes.

Best is to scan all sources using a text search tool for SqlSetLongBindDatatype.
And manually inspect the code and potentially change it to the needed structure.

My scan reveals a lot of occurences. And each change needs to be tested. The 7.2.1 change gives us a lot of extra work to do.

[Dave Rabelink]

Issue is solved in TD 7.3

[a_sivababu]

Hi Dave,
So, should TD 7.3 works fine without "SqlPrepare and SqlExecute and calls SqlSetLongBindDatatype between them"? I was thinking that SqlSetLongBindDatatype in win32 and .NET is not used anymore.

found the following in the 7.2.1 release notes:
Binding data type for long data will be cleared when executing SqlPrepare. Because of this change SqlPrepareAndExecute, executing SqlPrepare and SqlExecute internally, does work with SqlSetLongBindDatatype anymore. If application uses Long String variable as bind variable for binary data, application should split SqlPrepareAndExecute with SqlPrepare and SqlExecute and calls SqlSetLongBindDatatype between them.

Thanks
Siva A

[Dave Rabelink]

Yes, the issue that 7.2.1 introduced

application should split SqlPrepareAndExecute with SqlPrepare and SqlExecute and calls SqlSetLongBindDatatype between them.

which breaks functionality which worked previously (back to TD 1.5) is now not needed in TD 7.3.

But you still need SqlSetLongBindDatatype. To my knowledge this was never changed that you need this to be able to handle blobs.

[a_sivababu]

Thanks Dave.
Understood. The SqlSetLongBindDataType() is still required if we use Long String variable in TD and image in DB column. However this bind function is not required if we use Binary variable in TD. Not sure i am right

Thanks
Siva A

[Dave Rabelink]

Thanks Dave.
Understood. The SqlSetLongBindDataType() is still required if we use Long String variable in TD and image in DB column. However this bind function is not required if we use Binary variable in TD. Not sure i am right

Based on this knowledgebase article:

https://knowledge.opentext.com/knowledg ... KB14634327

you are right. The TD binary datatype does not need SqlSetLongBindDataType.

Summary

In Gupta Team Developer, using SqlSetLongBindDatatype, an error "ORA-01401 : insert value too large for column" occurred while fetching/retrieving the data from database.
This issue occurs in (but may not be limited to): Gupta Team Developer (TD) 7.1.4

Resolution

The solution is to remove the function SqlSetLongBindDatatype while fetching the blob. It is not required to call SqlSetLongBindDatatype to fetch/select binary data because TD gets DB column data type, BLOB, and handle it as binary automatically. From the TD help description SqlSetLongBindDatatype can be called before executing the SQL statement that writes or updates the column data. When writing LONG STRING data to IMAGE columns, the bind variable that contains the data must be bound to the appropriate data type by calling SqlSetLongBindDatatype.

[a_sivababu]

Thanks Dave for sharing.

Regards,
Siva A

[Dave Rabelink]

Issue also solved in TD 7.2.2