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

Report SqlBase bugs and possible workarounds.
Dave Rabelink
Founder/Site Admin
Founder/Site Admin
Netherlands
Posts: 3218
Joined: 24 Feb 2017, 09:12
Location: Gouda, The Netherlands

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

Post by Dave Rabelink » 19 Nov 2019, 10:49

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
You do not have the required permissions to view the files attached to this post.
Regards,
Dave Rabelink

Image
Articles and information on Team Developer Tips & Tricks Wiki
Download samples, documents and resources from TD Sample Vault
Videos on TDWiki YouTube Channel

peter4711
Germany
Posts: 24
Joined: 22 Jun 2017, 12:30
Location: Munich

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

Post by peter4711 » 19 Nov 2019, 15:41

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
Founder/Site Admin
Founder/Site Admin
Netherlands
Posts: 3218
Joined: 24 Feb 2017, 09:12
Location: Gouda, The Netherlands

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

Post by Dave Rabelink » 19 Nov 2019, 15:53

peter4711 wrote:
19 Nov 2019, 15:41
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.
Regards,
Dave Rabelink

Image
Articles and information on Team Developer Tips & Tricks Wiki
Download samples, documents and resources from TD Sample Vault
Videos on TDWiki YouTube Channel

Dave Rabelink
Founder/Site Admin
Founder/Site Admin
Netherlands
Posts: 3218
Joined: 24 Feb 2017, 09:12
Location: Gouda, The Netherlands

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

Post by Dave Rabelink » 20 Nov 2019, 06:46

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.
Regards,
Dave Rabelink

Image
Articles and information on Team Developer Tips & Tricks Wiki
Download samples, documents and resources from TD Sample Vault
Videos on TDWiki YouTube Channel

Dave Rabelink
Founder/Site Admin
Founder/Site Admin
Netherlands
Posts: 3218
Joined: 24 Feb 2017, 09:12
Location: Gouda, The Netherlands

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

Post by Dave Rabelink » 20 Nov 2019, 07:40

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.
Regards,
Dave Rabelink

Image
Articles and information on Team Developer Tips & Tricks Wiki
Download samples, documents and resources from TD Sample Vault
Videos on TDWiki YouTube Channel

Dave Rabelink
Founder/Site Admin
Founder/Site Admin
Netherlands
Posts: 3218
Joined: 24 Feb 2017, 09:12
Location: Gouda, The Netherlands

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

Post by Dave Rabelink » 10 Feb 2020, 10:14

Issue is solved in TD 7.3
Regards,
Dave Rabelink

Image
Articles and information on Team Developer Tips & Tricks Wiki
Download samples, documents and resources from TD Sample Vault
Videos on TDWiki YouTube Channel

a_sivababu
India
Posts: 501
Joined: 10 Mar 2017, 08:46
Location: Chennai,India

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

Post by a_sivababu » 10 Feb 2020, 10:54

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
Founder/Site Admin
Founder/Site Admin
Netherlands
Posts: 3218
Joined: 24 Feb 2017, 09:12
Location: Gouda, The Netherlands

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

Post by Dave Rabelink » 10 Feb 2020, 11:24

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.
Regards,
Dave Rabelink

Image
Articles and information on Team Developer Tips & Tricks Wiki
Download samples, documents and resources from TD Sample Vault
Videos on TDWiki YouTube Channel

a_sivababu
India
Posts: 501
Joined: 10 Mar 2017, 08:46
Location: Chennai,India

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

Post by a_sivababu » 10 Feb 2020, 12:16

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
Founder/Site Admin
Founder/Site Admin
Netherlands
Posts: 3218
Joined: 24 Feb 2017, 09:12
Location: Gouda, The Netherlands

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

Post by Dave Rabelink » 10 Feb 2020, 12:22

a_sivababu wrote:
10 Feb 2020, 12:16
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.
Regards,
Dave Rabelink

Image
Articles and information on Team Developer Tips & Tricks Wiki
Download samples, documents and resources from TD Sample Vault
Videos on TDWiki YouTube Channel

a_sivababu
India
Posts: 501
Joined: 10 Mar 2017, 08:46
Location: Chennai,India

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

Post by a_sivababu » 10 Feb 2020, 15:06

Thanks Dave for sharing.

Regards,
Siva A

Dave Rabelink
Founder/Site Admin
Founder/Site Admin
Netherlands
Posts: 3218
Joined: 24 Feb 2017, 09:12
Location: Gouda, The Netherlands

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

Post by Dave Rabelink » 22 Apr 2020, 06:10

Issue also solved in TD 7.2.2
Regards,
Dave Rabelink

Image
Articles and information on Team Developer Tips & Tricks Wiki
Download samples, documents and resources from TD Sample Vault
Videos on TDWiki YouTube Channel

Return to “Bug Reports”

Who is online

Users browsing this forum: [Ccbot] and 0 guests