Home » Developer & Programmer » Precompilers, OCI & OCCI » Binding arrays with OCI
Binding arrays with OCI [message #163408] Thu, 16 March 2006 11:13 Go to next message
Herode
Messages: 12
Registered: February 2006
Location: Isère (France, 38)
Junior Member
Gentlemen,

I'm trying to bind a simple array of integers before an INSERT
statement. I guess something in the documentation escaped my
attention, because I'm unable to make the bind. For exemple, on the
following code, I get an ORA-01485 error (traduced from french : the
length of bind at compile time is not the same than at execution time).
 
[...] 
  int linkedInts[] = { 33,34,35 }; 
  ub2 eltSizes[3]; 
  eltSizes[ 0 ] = eltSizes[ 1 ] = eltSizes[ 2 ] = sizeof( int ); 
  ub4 count = 3; 
  //sb2 indicator[ 2 ]; 
  OCIBind* pBindh; 
  char* name = ":col1"; 
  st = ::OCIBindByName( m_pStmt, &pBindh, m_pErr, 
                        reinterpret_cast< text* >( name ), strlen( name 
), 
                        &linkedInts, sizeof( int ), SQLT_INT, 
                        NULL, eltSizes, NULL, 
                        0, &count, OCI_DEFAULT ); 
  if ( st != OCI_SUCCESS && st != OCI_SUCCESS_WITH_INFO ) { 
    CString ret = GetErrorText( m_pErr ); 
    cout << ret.GetBuffer( 0 ) << endl; 
    return false; 
  } 
[...] 

I'm stuck. Any help will be appreciated !
Re: Binding arrays with OCI [message #163536 is a reply to message #163408] Fri, 17 March 2006 02:53 Go to previous messageGo to next message
Herode
Messages: 12
Registered: February 2006
Location: Isère (France, 38)
Junior Member
Ok, I've got it. The answer was not in the arguments of the OCIBindByName function, but (not so obviously) in the arguments of the OCIStmtExecute. More precisely in the iters argument, which you can set to the array length value.
Re: Binding arrays with OCI [message #163638 is a reply to message #163408] Fri, 17 March 2006 10:39 Go to previous message
Herode
Messages: 12
Registered: February 2006
Location: Isère (France, 38)
Junior Member
One more question on OCI, and the last one I hope before i'm done with this task...

I'm now trying to bind arrays. It works with intrgers and doubles & so on. But I can't find what to do with strings. The column for the insert is VARCHAR2[75]. A code such as :
  char* linkedStrings[ 3 ];
  linkedStrings[ 0 ] = new char[ 2 ];
  strcpy( linkedStrings[ 0 ], "A" );
  linkedStrings[ 1 ] = new char[ 2 ];
  strcpy( linkedStrings[ 1 ], "B" );
  linkedStrings[ 2 ] = new char[ 2 ];
  strcpy( linkedStrings[ 2 ], "C" );

  ub2 eltSizes3[ 3 ];
  eltSizes3[ 0 ] = strlen( linkedStrings[ 0 ] ) + 1;
  eltSizes3[ 1 ] = strlen( linkedStrings[ 1 ] ) + 1;
  eltSizes3[ 2 ] = strlen( linkedStrings[ 2 ] ) + 1;

  OCIBind* pBindh3;
  char* name3 = ":col5";
  st = ::OCIBindByName( m_pStmt, &pBindh3, m_pErr, 
                        reinterpret_cast< text* >( name3 ), strlen( name3 ), 
                        &linkedStrings, 2, SQLT_VCS, 
                        NULL, (ub2*) eltSizes3, NULL, 
                        0, NULL, OCI_DEFAULT );
  if ( st != OCI_SUCCESS && st != OCI_SUCCESS_WITH_INFO ) {
    CString ret = GetErrorText( m_pErr );
    cout << ret.GetBuffer( 0 ) << endl;
    return false;
  }

  st = ::OCIStmtExecute( m_pService, m_pStmt, m_pErr, iters, 0, NULL, NULL, OCI_DEFAULT );
  if ( st != OCI_SUCCESS && st != OCI_SUCCESS_WITH_INFO ) {
    CString ret = GetErrorText( m_pErr );
    cout << ret.GetBuffer( 0 ) << endl;
    return false;
  }


throws an ORA-01459 error : invalid length for a variable length string

If I use the SQLT_STR instead, I have the ORA-01480 : undefined value @ not found at the end of the string (here : @ stands for an unreadable character on the output console... : 0x00 maybe ? )

If I use SQLT_CHR (removing the +1 for strlen or leaving it...), the 3 inserts are done, but the inserted values are unreadable characters.

What should I do ????????
Surprised(

Previous Topic: Assigning text in an OCI String
Next Topic: How can I pass objects to stored procedure from C++ code by using OCCI
Goto Forum:
  


Current Time: Thu Mar 28 13:28:42 CDT 2024