Re: Q: Oracle 7.3 ODBC problems (BLOBs)

From: mark tomlinson <marktoml_at_gdi.net>
Date: Tue, 21 Apr 1998 12:22:53 GMT
Message-ID: <353c8f0d.324362618@newshost.us.oracle.com>

On Mon, 20 Apr 1998 11:41:28 GMT, marktoml_at_gdi.net (mark tomlinson) wrote:

>Sure, bind a variable and do the insert/update/whatever. You simply
>can't use a SQL literal when doing this.

Then Bogdan Stepien <stepien_at_tpg.pl> wrote:

>
>Thank you for your reply, but unfortunately I am quite new to PL/SQL.
>Could you tell me more about "binding to variable"? What do you mean
>by bind a variable? Recall, that I have trouble with accessing LONG
>variable values (longer than 2000) through the ODBC interface.
>
>Thx for any info.
>BS

So what does this have to do with PL/SQL?Are you trying to use ODBC or PL/SQL? Here is an example using the ODBC API from C.

/*** This sample requires a binary file named INPUT.EXE to be placed
** in the C:\TEMP directory and it will create an OUTPUT.EXE from
** a fetch of the inserted rawdata.**
** You must create the following table in user SCOTT's schema:
** CREATE TABLE testlraw (c1 NUMBER, c2 LONG RAW);*/#include

<windows.h>
#include <stdio.h>#include <io.h>#include <string.h>#include <sql.h> // Error handling#define RCODE if (retcode != SQL_SUCCESS) { goto errhndl; }
void main(){ // Generic variables HENV henv; HDBC hdbc;
  HSTMT hstmt; RETCODE retcode; PTR pToken;   unsigned long i; // Data Specifc variables

  long int      nData;         // Number Column
  char          rData[16000];  // Long Raw Column  FILE*
fileptr;
  unsigned long filelen;  SDWORD        pcbValue;  SDWORD

pcbValue2;
  // Establish a connection to Oracle retcode = SQLAllocEnv(&henv); RCODE
  retcode = SQLAllocConnect(henv, &hdbc); RCODE   retcode = SQLSetConnectOption( hdbc, SQL_AUTOCOMMIT, 0 ); RCODE   printf("\n%s","Successful SQLSetConnectOption");   retcode = SQLConnect ( hdbc, (unsigned char*)"ORA32", SQL_NTS,

                (unsigned char*)"SCOTT", SQL_NTS, (unsigned char*)"TIGER", SQL_NTS ); RCODE
  if (retcode == SQL_SUCCESS_WITH_INFO) { goto errhndl; } else   { printf("\n%s","Successful SQLConnect"); }   // Prepare and execute the insert statement

  retcode = SQLAllocStmt(hdbc, &hstmt); RCODE
  fileptr = fopen("C:\\TEMP\\INPUT","rb");
  filelen = _filelength(_fileno(fileptr));

  printf("\nFile is %i bytes long",filelen); nData = 10;   retcode = SQLSetParam( hstmt, 1, SQL_C_LONG, SQL_INTEGER, 0, 0, &nData, &pcbValue2 ); RCODE
  printf("\n%s","Successful SQLSetParam"); pcbValue = SQL_DATA_AT_EXEC;
  retcode = SQLSetParam( hstmt, 2, SQL_C_BINARY, SQL_LONGVARBINARY, filelen, 0, NULL,

            &pcbValue ); RCODE printf("\n%s","Successful SQLSetParam");
  retcode = SQLExecDirect(hstmt,

            (unsigned char*)"INSERT INTO SCOTT.TESTLRAW VALUES (?, ?)", SQL_NTS);
  printf("\n%s","Successful SQLExecute");   // Note the SQLParamData delimits the piecewise insert   retcode = SQLParamData( hstmt, &pToken ); i=0; do {     fread(rData, 1, 16000, fileptr); SQLPutData( hstmt, &rData, SQL_NTS );
        i+=16000; } while (i < filelen); fclose(fileptr);   retcode = SQLParamData( hstmt, &pToken );   printf("\n%s","Successful SQLPutData"); SQLTransact(henv, hstmt, SQL_COMMIT);
    SQLFreeStmt(hstmt, SQL_DROP); // Prepare and execute the data fetch
  retcode = SQLAllocStmt(hdbc, &hstmt); RCODE   retcode = SQLPrepare ( hstmt, (unsigned char*)"SELECT C2 FROM SCOTT.TESTLRAW WHERE C1 = ?",
                        SQL_NTS ); RCODE printf("\n%s","Successful SQLPrepare");
  retcode = SQLSetParam( hstmt, 1, SQL_C_LONG, SQL_INTEGER, 15, 0, &nData, &pcbValue2 ); RCODE
  printf("\n%s","Successful SQLSetParam"); retcode = SQLExecute(hstmt); RCODE
  printf("\n%s","Successful SQLExecute"); retcode = SQLFetch(hstmt); RCODE
  printf("\n%s","Successful SQLFetch"); fileptr = fopen("OUTPUT.EXE","wb");
  i=0; do { SQLGetData( hstmt, 1, SQL_C_BINARY, &rData, 16000, &pcbValue );

    if ( i < filelen )      if (filelen >= 16000)
        fwrite(rData, 1, 16000, fileptr);      else
        fwrite(rData, 1, filelen, fileptr);	else
        fwrite(rData, 1, (filelen - i), fileptr);	i+=16000;  }

  while ( i < filelen); fclose(fileptr);   printf("\n%s","Successful SQLGetData"); SQLFreeStmt(hstmt, SQL_CLOSE);
  SQLDisconnect(hdbc); SQLFreeConnect(hdbc); SQLFreeEnv(henv); exit(0);
errhndl: UCHAR sqlstate[6]; UCHAR
error_msg[SQL_MAX_MESSAGE_LENGTH];
  char str[200];
  SQLError ( henv, hdbc, hstmt, sqlstate, NULL, error_msg, SQL_MAX_MESSAGE_LENGTH-1, NULL );

  strcpy(str,"ODBC Message:\n");  strcat(str,(char*)sqlstate);
  strcat(str,"\n");  strcat(str,(char*)error_msg);
  printf("\n%s",str,"Fatal Error");  exit(1);}

Received on Tue Apr 21 1998 - 07:22:53 CDT