Error with the odbc driver and the large objects

["Manuel Fernández Campos" <mfc1981@xxxxxxxxx>]

Hi,
the problem is that i can insert, or I think that it is insert (it didn't show any error), well. But when  I try to read the large object in the db, it didn't read nothing. The last var parameter of SQLbindCol, StrLen_or_IndPtr, ever return 0.

I don't know where the error is. Any Idea to fix this?

Thanks a lot for your time

The code: 

int PgsqlImageDao::insertImage

(Connection* conn, const Image& image,
                    unsigned char* previewData, unsigned int previewSize,
                    Date photoDate, Date insDate,
                    const string& info) throw(DataAccessException) {

    char ODBC_ident[IDENT_SIZE];
    char ODBC_info[INFO_SIZE];
    unsigned char* imageData = NULL;
    long ODBC_result;                               // Result of function calls.
    SQLHDBC ODBC_conHnd;                            // Handle for a connection
    SQLHSTMT ODBC_stmHnd;                           // Handle for a statement
    SQLINTEGER ODBC_id;
    SQLINTEGER ODBC_size;
    SQLINTEGER ODBC_previewSize;
    SQLINTEGER ODBC_strlen = 0;  
    SQLINTEGER ODBC_dataPar;
    SQLINTEGER ODBC_previewPar;
    SQLPOINTER ODBC_token;
    SQLSMALLINT ODBC_shortCols[IMAGE_SHORT_BINDS];
    SQL_DATE_STRUCT ODBC_photoDate;
    SQL_DATE_STRUCT ODBC_insDate;

    ODBC_conHnd = conn->getHnd();    
    ODBC_result = SQLAllocHandle(SQL_HANDLE_STMT, ODBC_conHnd, &ODBC_stmHnd);

    if ((ODBC_result != SQL_SUCCESS) && (ODBC_result != SQL_SUCCESS_WITH_INFO)) {
       delete conn;
       throw SysDataAccessException(ODBC_result, "Cannot allocate statement handle");
    }   

    /* Get new image ID. */
    SQLBindCol(ODBC_stmHnd, 1, SQL_C_ULONG, &ODBC_id, 1, &ODBC_strlen);
    ODBC_result = SQLExecDirect(ODBC_stmHnd, (SQLCHAR*) GET_ID_QUERY, SQL_NTS);

    if ((ODBC_result != SQL_SUCCESS) && (ODBC_result != SQL_SUCCESS_WITH_INFO)) {

      SQLFreeHandle(SQL_HANDLE_STMT, ODBC_stmHnd);
      delete conn;
      throw SysDataAccessException(ODBC_result,
              "Cannot execute statement GET_ID_QUERY");
    }

    ODBC_result = SQLFetch(ODBC_stmHnd);  

    if (ODBC_result == SQL_NO_DATA) {
       SQLFreeHandle(SQL_HANDLE_STMT, ODBC_stmHnd);
       delete conn;
       throw UserDataAccessException(UserDataAccessException::DATA_NOT_FOUND,
               "Cannot get a new image ID (GET_ID_QUERY)");
    }

    ODBC_result = SQLFreeStmt(ODBC_stmHnd, SQL_CLOSE);  
    ODBC_result = SQLFreeStmt(ODBC_stmHnd, SQL_UNBIND);  
    ODBC_result = SQLFreeStmt(ODBC_stmHnd, SQL_RESET_PARAMS);  

    /* Set query parameters values with the image properties. */
   
    imageData = (unsigned char *) malloc (image.SizeOfImage());
    imageData =(unsigned char *)memcpy(imageData, image.ImageB(), image.SizeOfImage());
    
    ODBC_shortCols[0] = image.getType();
    ODBC_shortCols[1] = image.getNCol();
    ODBC_shortCols[2] = image.getNLin();
    ODBC_size = image.SizeOfImage();
    ODBC_previewSize = previewSize;
    ODBC_photoDate.year = photoDate.getYear();
    ODBC_photoDate.month = photoDate.getMonth();
    ODBC_photoDate.day = photoDate.getDay();
    ODBC_insDate.year = insDate.getYear();
    ODBC_insDate.month = insDate.getMonth();
    ODBC_insDate.day = insDate.getDay();
    {
      const string& ident = image.getIdent();
      ident.copy(ODBC_ident, IDENT_LENGTH);
      int i = (ident.length() < IDENT_LENGTH)? ident.length(): IDENT_LENGTH;
      ODBC_ident[i] = '\0';
      ODBC_ident[IDENT_LENGTH] = '\0';
      
      info.copy(ODBC_info, INFO_LENGTH);
      i = (info.length() < INFO_LENGTH)? info.length(): INFO_LENGTH;
      ODBC_info[i] = '\0';
      ODBC_info[INFO_LENGTH] = '\0';
    }
    ODBC_strlen = SQL_NTS;
    
    SQLPrepare(ODBC_stmHnd, (SQLCHAR*) INSERT_IMAGE_QUERY, SQL_NTS);    
    SQLBindParameter(ODBC_stmHnd, 1, SQL_PARAM_INPUT, SQL_C_SLONG,  SQL_INTEGER, 0, 0, &ODBC_id, 0, NULL);
    SQLBindParameter(ODBC_stmHnd, 2, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_SMALLINT, 0, 0, &ODBC_shortCols[0], 0, NULL);
    SQLBindParameter(ODBC_stmHnd, 3, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_SMALLINT, 0, 0, &ODBC_shortCols[1], 0, NULL);
    SQLBindParameter(ODBC_stmHnd, 4, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_SMALLINT, 0, 0, &ODBC_shortCols[2], 0, NULL);
    SQLBindParameter(ODBC_stmHnd, 5, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &ODBC_size, 0, NULL);    
    SQLBindParameter(ODBC_stmHnd, 6, SQL_PARAM_INPUT, SQL_C_TYPE_DATE, SQL_DATE, 0, 0, &ODBC_photoDate, 0, NULL);
    SQLBindParameter(ODBC_stmHnd, 7, SQL_PARAM_INPUT, SQL_C_TYPE_DATE, SQL_DATE, 0, 0, &ODBC_insDate, 0, NULL);
    SQLBindParameter(ODBC_stmHnd, 8, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, IDENT_LENGTH, 0, &ODBC_ident, 0, &ODBC_strlen);    
    SQLBindParameter(ODBC_stmHnd, 9, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, INFO_LENGTH, 0, &ODBC_info, 0, &ODBC_strlen);    
    SQLBindParameter(ODBC_stmHnd, 10, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARBINARY, 0, 0, (SQLPOINTER) DATA_TOKEN, 0, &ODBC_dataPar);
    SQLBindParameter(ODBC_stmHnd, 11, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARBINARY, 0, 0, (SQLPOINTER) PREVIEW_TOKEN, 0, &ODBC_previewPar);
    ODBC_previewPar = SQL_LEN_DATA_AT_EXEC(0);    
    ODBC_dataPar = SQL_LEN_DATA_AT_EXEC(0);    
    ODBC_result = SQLExecute(ODBC_stmHnd);  

    /* Send DATA_AT_EXEC. */
    while (ODBC_result == SQL_NEED_DATA) {
      ODBC_result = SQLParamData(ODBC_stmHnd, &ODBC_token);
      if (ODBC_result == SQL_NEED_DATA) {
          long putResult;

          switch ((int) ODBC_token) {
              
              case DATA_TOKEN:                    
                 putResult = SQLPutData(ODBC_stmHnd, imageData, ODBC_size);
                    if ((putResult != SQL_SUCCESS) && (putResult != SQL_SUCCESS_WITH_INFO)) {
                      SQLFreeHandle(SQL_HANDLE_STMT, ODBC_stmHnd);
                      delete conn;
                      throw SysDataAccessException(ODBC_result,
                              "Error(s) sending DATA_AT_EXEC (DATA_TOKEN) in INSERT_IMAGE_QUERY");
                    }
                    break;
              
              case PREVIEW_TOKEN:
                 putResult = SQLPutData(ODBC_stmHnd, previewData, ODBC_previewSize);
                    if ((putResult != SQL_SUCCESS) && (putResult != SQL_SUCCESS_WITH_INFO)) {
                      SQLFreeHandle(SQL_HANDLE_STMT, ODBC_stmHnd);
                      delete conn;
                      throw SysDataAccessException(ODBC_result,
                              "Error(s) sending DATA_AT_EXEC (PREVIEW_TOKEN) in INSERT_IMAGE_QUERY");
                    }
                    break;
              
              default :
                    SQLFreeHandle(SQL_HANDLE_STMT, ODBC_stmHnd);
                    delete conn;
                    throw SysDataAccessException(0,
                              "Error(s) sending DATA_AT_EXEC (unrecognized token) in INSERT_IMAGE_QUERY");
          }
      }
    }

    if ((ODBC_result != SQL_SUCCESS) && (ODBC_result != SQL_SUCCESS_WITH_INFO)) {

      SQLFreeHandle(SQL_HANDLE_STMT, ODBC_stmHnd);
      delete conn;
      throw SysDataAccessException(ODBC_result,
              "Cannot execute statement INSERT_IMAGE_QUERY");
    }

    SQLFreeHandle(SQL_HANDLE_STMT, ODBC_stmHnd);
    delete conn;
    return ODBC_id;
}



Image* PgsqlImageDao::getImage(Connection* conn, int id)
        throw(DataAccessException) {

    char buffer[BUFFER_SIZE];
    char ODBC_ident[IDENT_SIZE];
    char* imageData = NULL;
    int imageSize = 0;
    long ODBC_result;                               // Result of function calls.
    SQLHDBC ODBC_conHnd;                            // Handle for a connection
    SQLHSTMT ODBC_stmHnd;                           // Handle for a statement
    SQLINTEGER ODBC_id;
    SQLINTEGER ODBC_bytesRead[IMAGE_SHORT_BINDS];   // Bytes read in each successful fetch
    SQLINTEGER ODBC_identRead;
    SQLINTEGER ODBC_bufferRead;
    SQLINTEGER ODBC_size;
    SQLINTEGER ODBC_sizeRead;
    SQLSMALLINT ODBC_shortCols[IMAGE_SHORT_BINDS];
    Image* im;
   
    ODBC_id = id;
    ODBC_conHnd = conn->getHnd();   
    ODBC_result = SQLAllocHandle(SQL_HANDLE_STMT, ODBC_conHnd, &ODBC_stmHnd);
   
    if ((ODBC_result != SQL_SUCCESS) && (ODBC_result != SQL_SUCCESS_WITH_INFO)) {

       delete conn;
       throw SysDataAccessException(ODBC_result, "Cannot allocate statement handle");
    }
    SQLPrepare(ODBC_stmHnd, (SQLCHAR*) GET_IMAGE_QUERY, SQL_NTS);   
    SQLBindParameter(ODBC_stmHnd, 1, SQL_PARAM_INPUT, SQL_C_SLONG,  SQL_INTEGER, 0, 0, &ODBC_id, 0, NULL);
    SQLBindCol(ODBC_stmHnd, 1, SQL_C_SSHORT, &(ODBC_shortCols[0]), 1, &(ODBC_bytesRead[0]));
    SQLBindCol(ODBC_stmHnd, 2, SQL_C_SSHORT, &(ODBC_shortCols[1]), 1, &(ODBC_bytesRead[1]));
    SQLBindCol(ODBC_stmHnd, 3, SQL_C_SSHORT, &(ODBC_shortCols[2]), 1, &(ODBC_bytesRead[2]));
    SQLBindCol(ODBC_stmHnd, 4, SQL_C_SLONG, &ODBC_size, 1, &ODBC_sizeRead);
    SQLBindCol(ODBC_stmHnd, 5, SQL_C_CHAR, &ODBC_ident, IDENT_SIZE, &ODBC_identRead);   
    SQLBindCol(ODBC_stmHnd, 6, SQL_C_BINARY, (SQLPOINTER) &buffer, BUFFER_SIZE, &ODBC_bufferRead);
   
    ODBC_result = SQLExecute(ODBC_stmHnd);  

    if ((ODBC_result != SQL_SUCCESS) && (ODBC_result != SQL_SUCCESS_WITH_INFO)) {

      SQLFreeHandle(SQL_HANDLE_STMT, ODBC_stmHnd);
      delete conn;
      throw SysDataAccessException(ODBC_result,
              "Cannot execute statement GET_IMAGE_QUERY");
    }

    ODBC_result = SQLFetch(ODBC_stmHnd); 

    if (ODBC_result == SQL_NO_DATA) {
      SQLFreeHandle(SQL_HANDLE_STMT, ODBC_stmHnd);
      delete conn;
      throw UserDataAccessException(UserDataAccessException::DATA_NOT_FOUND,
               "Cannot find requested image (GET_IMAGE_QUERY)");
    }

    imageData = new char[ODBC_size];
   
    do {
         imageData = (char*) memcpy(imageData, buffer, ODBC_bufferRead);
         imageSize +=  ODBC_bufferRead;        
         ODBC_result = SQLFetch(ODBC_stmHnd); 
    } while ((ODBC_result != SQL_NO_DATA) && (imageSize < ODBC_size));

   
    im = new Image(ODBC_ident, (byte*) imageData, ODBC_shortCols[0],
           ODBC_shortCols[1], ODBC_shortCols[2]);

    delete[] imageData;
    SQLFreeHandle(SQL_HANDLE_STMT, ODBC_stmHnd);
    delete conn;

    return im;
}