Click here to Skip to main content
15,846,571 members
Please Sign up or sign in to vote.
2.00/5 (2 votes)
See more:
Hi all

i want to use c++ to insert binary data in to mssql actually i am reading file in to char buffer & then i want to insert this file into table

the table have two fields

name as varchar(50)
data as varbinary(MAX)

can some one provide me any link for doing this , i tried google but i got c# code rather than c++

thanks in advance

[EDIT]
well i do not want C# i am trying c++

i try SQLPutData for inserting data but not working

C++
#include <iostream>
#include <windows.h>
#include <tchar.h>
#include <string>
#include <sqlext.h>
#include <string.h>
#include <fstream>

using namespace std;

void geterror(SQLHSTMT hstmt)
{
	  SQLSMALLINT     HandleType;
	  SQLHANDLE       Handle;
	  SQLSMALLINT     RecNumber;
	  SQLCHAR         SQLState[500]="INSERT INTO files VALUES('Paul Borm', ?)";
	  SQLINTEGER      NativeErrorPtr;
	  SQLCHAR         MessageText[SQL_MAX_MESSAGE_LENGTH] = "";
	  SQLSMALLINT     BufferLength=0;
	  SQLSMALLINT     TextLengthPtr;

	  SQLSTATE state;
	  SQLRETURN retcode;
	//  SQLRETURN retcode = SQLGetDiagRecA(SQL_HANDLE_STMT,hstmt,1,(SQLCHAR*)state,&NativeErrorPtr,MessageText,BufferLength,&TextLengthPtr);

	   int i = 1;
		while (( retcode = SQLGetDiagRecA(SQL_HANDLE_STMT, hstmt, i, SQLState, &NativeErrorPtr,MessageText, sizeof(MessageText), &TextLengthPtr)) != SQL_NO_DATA) 
		{
			cout<<SQLState<<endl;
			i++;
		}
}


#define TEXTSIZE  12000
#define MAXBUFLEN 256

SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc1 = SQL_NULL_HDBC;     
SQLHSTMT hstmt1 = SQL_NULL_HSTMT;

void Cleanup()
{
   if (hstmt1 != SQL_NULL_HSTMT)
      SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);

   if (hdbc1 != SQL_NULL_HDBC) {
      SQLDisconnect(hdbc1);
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
   }

   if (henv != SQL_NULL_HENV)
      SQLFreeHandle(SQL_HANDLE_ENV, henv);
}

int main()
{
   RETCODE retcode;

   // SQLBindParameter variables.
   SQLLEN cbTextSize, lbytes;

   // SQLParamData variable.
   PTR pParmID;

   // SQLPutData variables.
  

   string s = "C:\\z\\Worker_at_carbon_black_plant2.jpg";

			   ifstream fin;
			   fin.open(s.c_str(),ios::binary|ios::ate);

			   size_t sz = fin.tellg();
			   fin.seekg(0,ios::beg);

			   char *ptr = new char[sz];

			   fin.read(ptr,sz);

			   fin.close();

   SDWORD cbBatch = sz - 1;

   // 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");
      Cleanup();
      return(9);
   }

   // 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");
      Cleanup();
      return(9);    
   }

   // Allocate ODBC connection handle and connect.
   retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
   if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS))
   {
      printf("SQLAllocHandle(hdbc1) Failed\n\n");
      Cleanup();
      return(9);
   }

   // Sample uses Integrated Security, create SQL Server DSN using Windows NT authentication. 
   
   
  // retcode = SQLConnectA(hdbc1, (UCHAR*)"Test", SQL_NTS, (UCHAR*)"",SQL_NTS, (UCHAR*)"", SQL_NTS);
   SQLCHAR OutConnStr[255];
   SQLSMALLINT OutConnStrLen;

   retcode = SQLDriverConnectA( // SQL_NULL_HDBC
               hdbc1, 
               NULL, 
               (SQLCHAR*)"DRIVER=SQL Server;SERVER=WW2001453\\SQLEXPRESS;DATABASE=vivdb;Trusted_Connection=Yes;", 
               _countof("DRIVER=SQL Server;SERVER=WW2001453\\SQLEXPRESS;DATABASE=vivdb;Trusted_Connection=Yes;"),
               OutConnStr,
               255, 
               &OutConnStrLen,
               SQL_DRIVER_NOPROMPT );

  
   
   if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) 
   {
      printf("SQLConnect() Failed\n\n");
      Cleanup();
      return(9);
   }

   // Allocate statement handle.
   retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);
   if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) 
   {
      printf("SQLAllocHandle(hstmt1) Failed\n\n");
      Cleanup();
      return(9);
   }

   // Set parameters based on total data to send.
   //lbytes = (SDWORD)TEXTSIZE;

   lbytes = sz;
   // Bind the parameter marker.
   retcode = SQLBindParameter (hstmt1,           // hstmt
                               1,                // ipar
			      SQL_PARAM_INPUT,  // fParamType
			     SQL_C_BINARY,       // fCType
			     SQL_VARBINARY,  // FSqlType
                               0 ,           // cbColDef
                               0,                // ibScale
                               (VOID *)1,        // rgbValue
                               0,                // cbValueMax
                               &cbTextSize);     // pcbValue

    geterror(hstmt1);
   if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
      printf("SQLBindParameter Failed\n\n");
      Cleanup();
      return(9);
   }

   // Execute the command.
   retcode = SQLExecDirectA(hstmt1, (UCHAR*)"INSERT INTO files VALUES('Paul Borm', ?)", SQL_NTS);
   
   if ( (retcode != SQL_SUCCESS) && (retcode != SQL_NEED_DATA) && (retcode != SQL_SUCCESS_WITH_INFO) )
   {
      printf("SQLExecDirect Failed\n\n");
      Cleanup();
      return(9);
   }

   // Check to see if NEED_DATA; if yes, use SQLPutData.
   
   retcode = SQLParamData(hstmt1, &pParmID);
   
   if (retcode == SQL_NEED_DATA) 
   {
      while (lbytes > 5000)
	  {
         retcode = SQLPutData(hstmt1, ptr, 5000);

		 geterror(hstmt1);

         lbytes -= 5000;
		 ptr += 5000;
      }
      // Put final batch.
      retcode = SQLPutData(hstmt1, ptr, lbytes); 
   }

   geterror(hstmt1);
   if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
   {
      printf("SQLParamData Failed\n\n");
      Cleanup();
      return(9);
   }

   // Make final SQLParamData call.
   retcode = SQLParamData(hstmt1, &pParmID);
   if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) 
   {
      printf("Final SQLParamData Failed\n\n");
      Cleanup();
      return(9);
   }

   // Clean up.
   SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
   SQLDisconnect(hdbc1);
   SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
 
  SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
<pre>
[/EDIT]
Posted
Updated 12-Sep-11 23:11pm
v2
Comments
Richard MacCutchan 13-Sep-11 4:12am    
What do you mean by: i try SQLPutData for inserting data but not working?

Check the results of your command and find out what error code(s) gets returned. Also check that your input values are correct.

Hello Vivek,

try this link it may be useful to you

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection(v=vs.71).aspx[^]


thanks
sanjeev
 
Share this answer
 
finally i solve the problem thanks to all
 
Share this answer
 
Comments
Iain Clarke, Warrior Programmer 13-Sep-11 7:49am    
If one of the solutions here helped you, you should "Accept" the solution so others will know it was a good one.

And then people like me will know that your question has been answered.

If they did not help, and you found out the answer yourself, then you should add a comment to your original question.

Glad CP helped you,

Iain
scrol till last in the above given page
 
Share this answer
 
Comments
Richard MacCutchan 28-Jun-11 9:34am    
Why not just use the "Improve solution" link in your original post?
 
Share this answer
 
Comments
RedDk 19-Sep-11 14:13pm    
Yeah,

So IS this question answered?

Can't seem to determine whether it is; nothning here seems to indicate whether the above "page", scrolled to end or not, actually works. It doesn't for me on VS2010 ...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900