יום שני, נובמבר 28, 2011

Inserting LONGVARBINARY into MS SQL Server

נתקעתי בבעיה קטנה של המרת קוד ישן שעובד עם freetds + unixodbc בשביל להכניס מידע לבסיס נתונים של MS SQL.
כזכור הסוג IMAGE יורד בהקדם אז צריך לעשות המרה של הקוד שמטפל בזה לאחרים.
אז הנה דוגמה לשימוש ב כfreetds ע"ג odbc בשביל לשלוח מידע שיכול לאחסן קבצים.


/*

    Copyright 2011  Boris Shtrasman boris sh 1983 at gmail dot com

    Permission to use, copy, modify, and distribute this software
    and its documentation for any purpose and without fee is hereby
    granted, provided that the above copyright notice appear in all
    copies and that both that the copyright notice and this
    permission notice and warranty disclaimer appear in supporting
    documentation, and that the name of the author not be used in
    advertising or publicity pertaining to distribution of the
    software without specific, written prior permission.

    The author disclaim all warranties with regard to this
    software, including all implied warranties of merchantability
    and fitness.  In no event shall the author be liable for any
    special, indirect or consequential damages or any damages
    whatsoever resulting from loss of use, data or profits, whether
    in an action of contract, negligence or other tortious action,
    arising out of or in connection with the use or performance of
    this software.

*/
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>

#define TABLE_NAME "binary_v3"

static void extract_error(
    char *fn,
    SQLHANDLE handle,
    SQLSMALLINT type);



int main()
{
   #undef UNICODE /* for the Win ppl */
   SQLHENV henv;
   SQLHDBC dbc;
   SQLHSTMT stmt;

   RETCODE retcode;

   SQLLEN cbTextSize, lbytes;
   PTR pParmID;
   char binary_data[8024]="";

   memset(binary_data,0,sizeof(binary_data));
   binary_data[1] = 5;

   //lets define some strage number for batch  
   SDWORD batch_size = sizeof(binary_data) - 1024;

   // Allocate the ODBC environment and save handle.
   retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
   if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) 
   {
      printf("SQLAllocHandle(Env) Failed\n\n");
      goto cleanup;
   }

   // Notify ODBC that this is an ODBC 3.0 app.
   retcode = SQLSetEnvAttr(henv, 
        SQL_ATTR_ODBC_VERSION,
           (SQLPOINTER) SQL_OV_ODBC3,
                SQL_IS_INTEGER);

   if ( 
       (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) 
   {
      printf("SQLSetEnvAttr(ODBC version) Failed\n\n");
      goto cleanup;
   }

   // Allocate ODBC connection handle and connect.
   retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &dbc);
   if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS))
   {
      printf("SQLAllocHandle(dbc) Failed\n\n");
      goto cleanup;
   }
     
   SQLCHAR odbc_dsn_string_dump[255];
   SQLSMALLINT odbc_dsn_string_dumpLen;

   retcode = SQLDriverConnect(
           dbc,
           NULL,
           (SQLCHAR *)"Driver=FreeTds;"
                "SERVER=192.168.1.2\\SQLEXPRESS;UID=sa;"
                "PWD=pwd;DATABASE=db",
           SQL_NTS,
           odbc_dsn_string_dump,
           sizeof(odbc_dsn_string_dump),
           &odbc_dsn_string_dumpLen,
           SQL_DRIVER_COMPLETE);
   
   if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) 
   {
      extract_error("Describe param",dbc, SQL_HANDLE_DBC);  
       
      goto cleanup;
   }

   // Allocate statement handle.
   retcode = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
   if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) 
   {
      extract_error("Allocation ... ",stmt, SQL_HANDLE_STMT);  
       
      goto cleanup;
   }

   //tell the driver to work only in sync mode !
   SQLSetStmtOption(dbc, SQL_ASYNC_ENABLE, SQL_ASYNC_ENABLE_OFF);

   if (SQL_SUCCESS !=  SQLExecDirect(stmt,  (SQLCHAR *)
            "IF EXISTS "
            "(SELECT * FROM sysobjects WHERE id = "
            "object_id(N'[dbo].[" TABLE_NAME "]') "
            "AND OBJECTPROPERTY(id, N'IsUserTable') = 1) "
            "DROP TABLE [dbo].[" TABLE_NAME "]; " 
            "CREATE TABLE [dbo].[" TABLE_NAME "] "
            "( name varchar(256), content varbinary(max) NOT NULL ); ",    
            SQL_NTS))
   {
      extract_error("drop + create table ... ",stmt, SQL_HANDLE_STMT);  
       
      goto cleanup;
 
   }
   
   lbytes = (SDWORD) sizeof(binary_data);
   //we are setting the length to be calculated on run time.
   cbTextSize = SQL_LEN_DATA_AT_EXEC(lbytes);

   // Bind the parameter marker.
   
   SQLSMALLINT    binded_column_type,
                  persicion_in_binded_column,
                  is_binded_type_nullable;
   
   SQLUINTEGER   binded_column_size_from_driver = 0;
   
   retcode = SQLPrepare( stmt,
                         (SQLCHAR*)"INSERT INTO " 
                                    TABLE_NAME 
                        " (name, content) VALUES ('file', ?)",SQL_NTS);

#if 0

//for now freetds does not support describe param 
// but on the second it does I must use describe
//      param inorder to get the correct sizes and column types
   retcode = SQLDescribeParam(stmt,
         1,
         &binded_column_type,
         &binded_column_size_from_driver,
         &persicion_in_binded_column,
         &is_binded_type_nullable);

   printf ("Desc info (%d) %d %d %d %d\n",retcode , binded_column_type , 
binded_column_size_from_driver, persicion_in_binded_column, is_binded_type_nullable);

   if (binded_column_type != SQL_LONGVARBINARY) 
   {
        printf("This is strange got %d instead of %d\n" , 
binded_column_type, SQL_LONGVARBINARY);
   }

   if (SQL_SUCCESS != retcode ) 
   {
     extract_error("Describe param",stmt, SQL_HANDLE_STMT);  
         goto cleanup;
   }
#else
    binded_column_size_from_driver = 0;//Don't ask me why freetds\
 fails to work with the 2G size 
#endif

   retcode = SQLBindParameter(stmt, 
                              1, 
                              SQL_PARAM_INPUT,
                              SQL_C_BINARY,
                              SQL_LONGVARBINARY,
                              binded_column_size_from_driver
                                        /*2147483647 vs 32K*/,
                              0,
                              (VOID *) 1/* any value other then null*/,
                              0,
                              &cbTextSize);
    
   if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
      extract_error("Bind param param",stmt, SQL_HANDLE_STMT);  
      goto cleanup;
   }

   // Execute the command.
   retcode = SQLExecDirectA(
                stmt, 
                (SQLCHAR *)"INSERT INTO " 
                            TABLE_NAME 
                           " (name, content) VALUES ('file', ?)", SQL_NTS);
   
   if ( 
        (retcode != SQL_SUCCESS) 
           &&
        (retcode != SQL_NEED_DATA)
           && 
        (retcode != SQL_SUCCESS_WITH_INFO) )
   {
      extract_error("Insert",stmt, SQL_HANDLE_STMT);  
      goto cleanup;
   }

   // We set up the data len to be computed on executing therefor we must \
         actually send the data.
   // prior to calling put data you must first call to SQLParamData .. \
         why, only the odbc masters know 
   retcode = SQLParamData(stmt, &pParmID);
   printf("SQLParam Data = %d \n",retcode);

   if (SQL_NEED_DATA != retcode)
   {
      extract_error("grrr ... ",stmt , SQL_HANDLE_STMT);
      goto cleanup;
   }

   char * pt_data_be_inserted_into_db = binary_data;
   //we defined that data len will be calculated on executing \
        this is the reason why we got this value
  
   if (retcode == SQL_NEED_DATA) 
   {
      
      while (lbytes > batch_size)
      {

         retcode = SQLPutData(stmt,
                   (SQLCHAR*) pt_data_be_inserted_into_db, batch_size);
         lbytes -= batch_size;
         pt_data_be_inserted_into_db += batch_size;
      }
      // Put final batch.
      retcode = SQLPutData(stmt, pt_data_be_inserted_into_db, lbytes); 
      printf("last put data with %d bytes and exited with %d\n", lbytes,retcode);
   
      if ( 
           (retcode != SQL_SUCCESS)
              && 
           (retcode != SQL_SUCCESS_WITH_INFO) )
      {
         extract_error("Put data for stmt",stmt, SQL_HANDLE_STMT);  
         goto cleanup;
      }
     
   }
  
   // send the data to the db.
   retcode = SQLParamData(stmt, &pParmID);
   if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) 
   {
      extract_error("Final Param data",stmt, SQL_HANDLE_STMT);  
      goto cleanup;
   }
   
cleanup:
   // Clean up.
   SQLFreeHandle(SQL_HANDLE_STMT, stmt);
   SQLDisconnect(dbc);
   SQLFreeHandle(SQL_HANDLE_DBC, dbc);
   SQLFreeHandle(SQL_HANDLE_ENV, henv);
   return 0;
}

void extract_error(
    char *fn,
    SQLHANDLE handle,
    SQLSMALLINT type)
{
    SQLINTEGER     i = 0;
    SQLINTEGER     native;
    SQLCHAR     state[ 7 ];
    SQLCHAR     text[256];
    SQLSMALLINT     len;
    SQLRETURN     ret;

    fprintf(stderr,
            "\n"
            "The driver reported the following diagnostics whilst running "
            "%s\n\n",
            fn);

    perror("Last os error :");
    do
    {
        ret = SQLGetDiagRec(type, handle, ++i, state, &native, text,
                            sizeof(text), &len );
        if (SQL_SUCCEEDED(ret))
            printf("%s:%ld:%ld:%s\n", state, i, native, text);
    }
    while( ret != SQL_NO_DATA );
    fprintf(stderr, "found %d elements of information\n",(i>0)?i - 1:0);
}

אין תגובות:

הוסף רשומת תגובה