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