My Binary Experience

My ODBC and Oracle Connect Experience.

Hi all,

I would like to share my experience with you people out there.

[Quoted] A couple of days ago i needed to update an Oracle table on a Unix system, with data I had in a local clarion database on a Windows NT system. This would be my first experience in trying ODBC-Oracle from CW 2.0

[Quoted] The fields in the clarion database was Name,Number and Minutia, where name is a string(20), number is a byte, and minutia is a string(512).

I installed SQL*Net Client,SQL*Plus and ODBC from the Oracle CD, then I used the ODBC manager to create a label? to point to my Oracle system.
I went to CW 2.0 data dictionary, and imported via ODBC the empty Oracle table that was created for me.
Then I imported the Clarion file layout. Now I created a test app that would use the process template to read from the one and update the other.

I ran it, and it worked fine ... but the Minutiafield was not updated properly. It got chopped off at various places. So I looked at the Oracle declaration, Minutia was mapped as Varchar2. I switched tracing on in ODBC, and saw the actual SQL statement used to update the table. I noticed that the SQL statement is wrong! The information in Minutia is binary, minutias is something we use to verify a fingerprint image for access control, and there can be NULLs in there. I checked my Clarion database, and saw that the minutia is saved correctly. Now i thought maybe an upgrade to a new ODBC version will help, so I upgraded, and upgraded, until I used ODBC 3, but I still had the same problem. PS1: I did try LONG, RAW, CHAR, ..., in place of VARCHAR2 in Oracle. PS2: By wrong up there i mean stuff was missing...

So I thought let us try Clarion's Native Oracle Driver, "Oracle Connect". I borrowed this, and tried it out, the same thing happened, because we are still talking to Oracle via SQL*Net, we are still sending SQL statements,and while we do that,we are sending (bad) strings, cause they contain Nulls in them.

SO! What do we need to do to send a binary blob to Oracle. Oracle comes with a OLE/Dynaset/OOP DLL, that contain stuff like AppendFieldChunk, where they talk about RAW data. But if I go that way, it would add 3/4 weeks to my development, because I would need to prototype _at_AppendFieldChunk$qpxcpxvus and all the other stuff to prep the database, and there is just not enough time for this.
PS:
I eventually found a "work-around"

             TmpSQL = ''
             Loop S# = 1 to Size(Enr:Minutia) -1
                  TmpSql = Clip(TmpSql) & |
                  'Chr(' & Val( Enr:Minutia[S#] ) & ') ||'
             .
             TmpSql = Clip(TmpSql) & |
                     'Chr(' & Val( Enr:Minutia[S#+1] )   & ')'
[Quoted]              TmpSql =  'INSERT INTO "WERNER"."TEMPVAR" ' & |
                        '("Number","Name","Minutia")   ' & |
                        'VALUES('                        & |
                        '''' & Enr:Pin_No       & ''' ,' & |
                        '''' & Enr:Name        & ''' ,'  & |
                        Clip(TmpSql)
             TmpSql = Clip(TmpSQL) & ')'
             TempVar {Prop:Sql} = Clip(TmpSql)
             If Error() Then Window{Prop:Status,1} = Error().

But this really stink!

I mean tomorrow I must maybe add a string(2048) with nulls in it?

Maybe we can make the "layer" understand what we would like to do via HextoRaw and RawtoHex? I'm still looking for documentation on this..

So i you got this far you are:
(1) laughing your head of about my stupidity
(2) crying your heart out about my sillyness
(3) bored, and should go home
(4) none of the above.

I would like to hear from ANYBODY out there with same experiences, and how they went about to fix it. Please remember that I'm very new to Oracle so please be gentle.

I'm vaguely starting to understand about the 6 or so layers that SQL*Net maintain, and I imagine that i need to go one layer down? But I must say I thought ODBC has been around long enough for somebody else to fix this sort of thing in some other way.

  _,--/|/|/|,_
 .___ _,_ |
:.\ /---\ /---\ Werner_at_Cgull.Baps.Com - Pegasus >-|-| 9 |-| 9 | Werner.Smit_at_Baps.Com - Outlook  \_ `---' `---' http://www.baps.com/werner   | __ '' |
  | | `----, '
   \ `-----' /
    `-.___,-' Received on Thu Jun 05 1997 - 00:00:00 CEST