Saving text data into Oracle 10g

General discussion forum about all databases other than SqlBase.
sadowsky

Saving text data into Oracle 10g

Post by sadowsky » 16 May 2011, 21:39

Hi Jeff,

good idea; let's start a new threat on the problem of writing/reading text into oracle 10g.
Here is my specific problem:

If we write a compressed text (e.g. " 1010101") into the database, and decompress it after reading it from the database, then the result is correct if we read the data from SQLBase. However, the same procedure with Oracle gives us a wrong result.

We have written a test program to detect the error. This program can create a table and write and read data into this table (see attachment).

If we write the data and fetch the data immediately from Oracle with "Call SqlSetLongBindDatatype( 1, DT_LONGBINARY )", then we get the error 1062. If we close TD6.0 and make the read directly, than there is no error but the data are wrong.

I hope that you can reconstruct the problem.

Thanks, and best regards
Volker
You do not have the required permissions to view the files attached to this post.

Jeff Luther

Re: Saving text data into Oracle 10g

Post by Jeff Luther » 16 May 2011, 22:11

I'd be happy to look at this but I'm a bit confused: column type for the create is LONG VARCHAR, but that's not an Oracle column type. That is SB, of course, and you mentioned it works with SB. I'm interested in a test case that fails for you.

Couple things...
* What's the Oracle column type you are using for the CREATE? It must be something Oracle uses to store binary data, like BLOB:
http://www.orafaq.com/wiki/BLOB

* Maybe you are aware of this, but that compress string is a binary value now. A 'string' in TD v5.2/v6 is a Unicode 2-byte value. Once you call compress lstr is not a 'string' any longer, it's a buffer of binary bytes. Looks like you know that because you are calling SqlSetLongBindDatatype(), but it being binary will impact the Oracle column type you can use. Historically, LONG VARCHAR in SB was for either text or binary data. You calling the column 'text' though prompts me to mention this.

* One other thing I see: SqlSetLongBindDatatype() is in the wrong place for the fetch. It needs to come after prepExe and before the Fetch:

Code: Select all

If (SqlPrepare(hSql, strSqlStatement ))
   Call SqlExecute( hSql)
   Call SqlSetLongBindDatatype( 1,  DT_LONGBINARY ) -- called after PrepExe and before FetchNext
   Call SqlFetchNext( hSql,nPFetch  )
   Call SalStrUncompress( lstr )
   Set dflstrOut = lstr

sadowsky

Re: Saving text data into Oracle 10g

Post by sadowsky » 17 May 2011, 10:56

LONG is an Oracle data type for storing character data of variable length up to 2 Gigabytes in length (from wiki). We use this type for a long time and historically the type BLOB does not exist in Oracle.

We have make a test with the data type BLOB and than we can write/read the data correct. Therfore we will chance the data type.

Thanks
Volker

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests