נתקעתי בבעיה קטנה של המרת קוד ישן שעובד עם freetds + unixodbc בשביל להכניס מידע לבסיס נתונים של MS SQL.
כזכור הסוג IMAGE יורד בהקדם אז צריך לעשות המרה של הקוד שמטפל בזה לאחרים.
אז הנה דוגמה לשימוש ב כfreetds ע"ג odbc בשביל לשלוח מידע שיכול לאחסן קבצים.
כזכור הסוג 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); }
אין תגובות:
הוסף רשומת תגובה