SqlSetLongBindDatatype function and Long raw column

Discussions specific to Team Developer 5.1.

SqlSetLongBindDatatype function and Long raw column

Postby danicristea7511 » Wed Dec 12, 2007 8:07 am

Hello all,

I am using Team developer 5.1 and I tried with the old code from Team developer 4.1 to load some data from LONG RAW column (ORACLE database ), but it doesn't work anymore. I get a null long string.

My code is as following:
!!CB!! 70
Set lstr_MPPlan = ''
Set sSqlString = 'SELECT ' || GetIniItemName( sBin ) ||
' FROM ' || sDatabase || GetIniTblName( sTable ) ||
' WHERE ' || GetIniItemName( 'MEASURINGPROJECTID' ) || ' = :sProjectId ' ||
' AND ' || GetIniItemName( 'VERSIONNR' ) || ' = :sVersionNr ' ||
' AND ' || GetIniItemName( 'VERSIONTYPE' ) || ' = :sVersionType ' ||
' INTO :lstr_MPPlan '
! adapt oracle's longbuffer
Call SqlGetParameterAll( hSqlExists, 5112, nLongBufferSize, sDBParam, TRUE )
Call SqlSetParameterAll( hSqlExists, 5112, 2000000, sDBParam, TRUE )
! there is a binary to be loaded:
Call SqlSetLongBindDatatype(1, 23) ! 4=4. BindVar, 23=Binary (22=Text)
Call SqlPrepareAndExecute( hSqlExists, sSqlString )
Call SqlFetchNext( hSqlExists, nExists )
! reset oracle's longbuffer
Call SqlSetParameterAll( hSqlExists, 5112, nLongBufferSize, sDBParam, TRUE )
Call SqlSetLongBindDatatype(1, 22) ! 4=4. BindVar, 23=Binary (22=Text)

where lstr_MPPlan is long string variable.

If anybody has any idea will be very appreciate.

Thank you in advance!

Best regards,
Daniela Cristea
danicristea7511
 
Posts: 18
Joined: Mon Dec 10, 2007 8:59 am

Re: SqlSetLongBindDatatype function and Long raw column

Postby jmgemperle » Fri Dec 14, 2007 10:04 am

Hi,

Strange, this works for me with the release of TD 5.1 you have. Please note that I used a UNICODE ORACLE INSTANCE (AL32UTF8) and my NLS_LANG client charset is also AL32UTF8 (not sure if that plays a role). I can insert fetch a binary blob (JPG file of 40Kb) using both LONG RAW or BLOB data types and this both using native router and OLEDB...Also note that LONG RAW is deprecated from ORACLE AFAIK, still this should work.

Not sure why this does not work for you, also note that I use SqlPrepare followed by SqlSetLongBindDatatype and SqlExecute, as far as I know you should use it this way both for the insert and the fetch (the long position in SqlSetLongBindDatatype is the position of the LONG bind in the statment note the column position in the table) . Can you try the attached sample, just change the credential in the APP and let me know if it works for you. Still if that worked with TD 4.2 it should with TD 5.1 so I'm curious to know what you find out.

Cheers

Jean-Marc


Bellow this is what I do :
=====
On SAM_Click
! !
Set SqlDatabase='UNIORCL'
Set SqlUser='support'
Set SqlPassword='support'
If cbOLEDB
Set SqlDatabase=''
Set SqlUDL='Provider=OraOLEDB.Oracle.1;Password=support;Persist Security Info=True;User ID=support;Data Source=UNIORCL'
Call SqlConnect(hSqlORA)
If NOT cbLong
Call SqlSetParameterAll( hSqlORA, DBP_ORAUSELOB, 1, '', TRUE )
Call SqlPrepareAndExecute( hSqlORA, "drop table blobtest" )
If cbLong
Call SqlPrepareAndExecute( hSqlORA, "create table blobtest(ORACLE LONG RAW)" )
Else
Call SqlPrepareAndExecute( hSqlORA, "create table blobtest(ORACLE BLOB)" )
Call InsertBlob('ORACLE', hSqlORA )
Call FetchBlob('ORACLE', hSqlORA )
If cbLong
Call SqlSetParameterAll( hSqlORA, DBP_ORAUSELOB, 0, '', TRUE )
Call SqlDisconnect( hSqlORA )
! !
====

Function: InsertBlob
Description:
Returns
Parameters
String: sCol
Sql Handle: hSql
Static Variables
Local variables
Actions
Call SqlSetParameterAll( hSql, DBP_LONGBUFFER, 50000, '', TRUE )
Call SqlPrepare( hSql, "insert into blobtest (" || sCol || ") values (:lsBlob)" )
Call SqlSetLongBindDatatype( 1, 23 )
Call SqlExecute(hSql)
Call SqlCommit(hSql)
=====

Function: FetchBlob
Description:
Returns
Parameters
String: sCol
Sql Handle: hSql
Static Variables
Local variables
Number: nInd
Actions
Set lsBlob=''
Call SalPicClear( pic2 )
Call SalMessageBox( "Ready to Fecth " || sCol || " BLOB", "Info", 0 )
Call SqlPrepare(hSql,"select " || sCol || " from blobtest into :lsBlob")
Call SqlSetLongBindDatatype( 1, 23 )
Call SqlExecute(hSql)
Call SqlFetchNext( hSql, nInd )
Call SqlCommit(hSql)
Call SalPicSetImage( pic2, lsBlob, PIC_ImageTypeJPEG )
Call SalUpdateWindow( pic2 )
You do not have the required permissions to view the files attached to this post.
User avatar
jmgemperle
 
Posts: 886
Joined: Thu Feb 15, 2007 1:57 am
Location: Leiden The Netherlands

Re: SqlSetLongBindDatatype function and Long raw column

Postby danicristea7511 » Fri Dec 14, 2007 11:03 am

Hi,

And thank you very much vor your reply.

after an all day, finally I find out that not with SqlSetLongBindDatatype() function was the problem. The function work fine.

To put the data (loaded from a LONG RAW column) in a word document, I had to open the file in binary mode as following:
SalFileOpen( hFile, sZipFileName, OF_Create | OF_ReadWrite | OF_Binary ) .

I only did
SalFileOpen( hFile, sZipFileName, OF_Create | OF_ReadWrite ).............

Soryy that I disturb you!
Daniela Cristea
danicristea7511
 
Posts: 18
Joined: Mon Dec 10, 2007 8:59 am

Re: SqlSetLongBindDatatype function and Long raw column

Postby thanhtien19 » Thu Jan 06, 2011 1:36 am

i had problem same you ,but return data from long raw column only code hex example [[[[[[[|||||||

but i don't know to fix it how

if you had fixed it ok ,can you share with me .thanks
thanhtien19
 
Posts: 8
Joined: Mon Nov 22, 2010 7:55 am

Re: SqlSetLongBindDatatype function and Long raw column

Postby Jeff Luther » Fri Jan 07, 2011 3:42 pm

Couple things:

I see JM's code from 2007, but a recent TD forum thread here brought to light when to call Call SqlSetLongBindDatatype():
INSERT/UPDATE:
SqlPrepare()
SqlSetLongBindDatatype()
SqlExecute()

BUT, for SELECT:
SqlPrepare()
SqlExecute()
SqlSetLongBindDatatype()
SqlFetchNext()

Note how SqlSetLongBindDatatype() is between Prep & Exe in DML (INS/UPD), but after Prep/Exe and before Fetch in SELECT.

You wrote:
return data from long raw column only code hex example

It sounds like the DB data is binary format, but you haven't called SqlSetLongBindDatatype( ..., 23 ) correctly with "23" = Long Binary.

Now, it the DB data is character data... that's a different issue. The call for text/character data is SqlSetLongBindDatatype( ..., 22 ) -- that's a "22" -- though that's the default value.
Jeff Luther
 


Return to Team Developer 5.1

Who is online

Users browsing this forum: No registered users and 1 guest

cron