C++

Forum

 
problem with SQLBindParameter

vivmen (120)
i am trying to insert some data in to MS SQL database but it fails. the problem here is size 500,if i remove 500 & put length of my buffer SQLBindParameter return -1

my table is like below

name varchar(50)
data varbinary(MAX)

how i can fix it

thanks in advance

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
void writetodb(SQLHSTMT hstmt ,const char *name,int nsize,char *buf,int bsize)
{
	SQLRETURN retcode;
	
	SQLCHAR *fname = new SQLCHAR[nsize];
	SQLCHAR *fdata = new SQLCHAR[bsize];

	memset(fname,0,nsize);
	memset(fdata,0,bsize);
	
	SQLINTEGER cbfname = SQL_NTS, cbfdata = SQL_NTS;
	
	retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR  , SQL_VARCHAR  , 500, 0, fname, 0, &cbfname);
	retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, 500, 0, fdata, 0, &cbfdata);

	retcode = SQLPrepareA(hstmt, (SQLCHAR*)"INSERT INTO files(fname, fdata) VALUES (?, ?)", SQL_NTS);

	memcpy(fname,name,nsize);
	memcpy(fdata,buf,bsize);

	retcode = SQLExecute(hstmt);
	
	delete fname;
	delete fdata;
}


freddie1 (1838)
Here are some SQLBindParameter() calls from a demo program I have that puts data in MS Access or SQL Server...

1
2
3
4
 
SQLBindParameter(hStmt,1,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER,0,0,&id,0,&iJnk);
SQLBindParameter(hStmt,2,SQL_PARAM_INPUT,SQL_C_DOUBLE,SQL_DOUBLE,0,0,&dbl,0,&iJnk);
SQLBindParameter(hStmt,3,SQL_PARAM_INPUT,SQL_C_TYPE_DATE,SQL_TYPE_TIMESTAMP,16,0,&ts,0,&iJnk);
SQLBindParameter(hStmt,4,SQL_PARAM_INPUT,SQL_C_TCHAR,SQL_CHAR,31,0,szString,32,&iNts);


Actually, here is the whole procedure...

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
UINT blnInsert(SQL& sql, TCHAR** ptrLines, unsigned int& iLine, HWND hWnd, int iCountExistingRecords, LPCRITICAL_SECTION cs)
{
 TCHAR* szStr[]={_T("My Birthday"),_T("Walk On Moon?"),_T("Some String"),_T("April Fools Day")};
 TCHAR* szDate[]={_T("11/15/1952"),_T("6/30/1969"),_T("1/1/2006"),_T("4/1/2006")};
 double dblNum[]={3.14159,1.23456,15.1234,0.54321};
 TCHAR  szQuery[100],szString[32],szBuffer[128];      //Let me give you a hint about something.  If you decide
 SQLINTEGER iNts=SQL_NTS;                             //to use raw ODBC as your database access methodology, the
 UINT i,id,iRet=FALSE;                                //hard part is SQLBindParameter() for inserting prepared
 TIMESTAMP_STRUCT ts;                                 //SQL statements, and SQLBindCol() for selecting data.  These
 SQLINTEGER iJnk;                                     //will inevitably take you some time to learn.  I chose an
 SQLHSTMT hStmt;                                      //integer, a double, a data, and a char string so as to get
 double dbl;                                          //you started on the most common data types.

 #if defined(MY_DEBUG)
     _ftprintf(fp,_T("Entering blnInsert()\n"));
     _ftprintf(fp,_T("  iCountExistingRecords = %u\n"),iCountExistingRecords);
 #endif
 if(SQLAllocHandle(SQL_HANDLE_STMT,sql.hConn,&hStmt)==SQL_SUCCESS)
 {
    _tcscpy((TCHAR*)szQuery,_T("INSERT INTO Table1(Id, Float_Point, Date_Field, Text_Field) VALUES(?,?,?,?);"));
    if(blnLineFailed(hWnd,sql,ptrLines,iLine,szQuery))
       return FALSE;
    iLine++;
    if(blnLineFailed(hWnd,sql,ptrLines,iLine,_T("                                                         SQLExecute(hStmt)")))
       return FALSE;
    if(blnLineFailed(hWnd,sql,ptrLines,iLine,_T("iId      Double           Date           String           0=SQL_SUCCESS")))
       return FALSE;
    if(blnLineFailed(hWnd,sql,ptrLines,iLine,_T("========================================================================")))
       return FALSE;
    if(SQLPrepare(hStmt,(SQLTCHAR*)szQuery,SQL_NTS)==SQL_SUCCESS)
    {
       SQLBindParameter(hStmt,1,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER,0,0,&id,0,&iJnk);
       SQLBindParameter(hStmt,2,SQL_PARAM_INPUT,SQL_C_DOUBLE,SQL_DOUBLE,0,0,&dbl,0,&iJnk);
       SQLBindParameter(hStmt,3,SQL_PARAM_INPUT,SQL_C_TYPE_DATE,SQL_TYPE_TIMESTAMP,16,0,&ts,0,&iJnk);
       SQLBindParameter(hStmt,4,SQL_PARAM_INPUT,SQL_C_TCHAR,SQL_CHAR,31,0,szString,32,&iNts);
       #if defined(MY_DEBUG)
           _ftprintf(fp,_T("\n                                                         SQLExecute(hStmt)\n"));
           _ftprintf(fp,_T("  iId      Double           Date           String           0=SQL_SUCCESS  \n"));
           _ftprintf(fp,_T("  ========================================================================\n"));
       #endif
       for(i=0;i<4;i++)
       {
           EnterCriticalSection(cs);
           id=i+iCountExistingRecords, dbl=dblNum[i];
           ts=ParseDate(szDate[i],_T("mdy"),_T("/"));
           _tcscpy(szString,szStr[i]);
           if(SQLExecute(hStmt)==SQL_SUCCESS)
           {
              memset(szBuffer,0,128);
              _stprintf(szBuffer,_T("%-6u%8.2f           %-12.10s  %-20s%6u"),id,dbl,szDate[i],szString,SQL_SUCCESS);
              if(blnLineFailed(hWnd,sql,ptrLines,iLine,szBuffer))
                 return FALSE;
              #if defined(MY_DEBUG)
                  _ftprintf(fp,_T("  %u\t%f\t%s\t%s\t\t%u\n"),id,dbl,szDate[i],szString,SQL_SUCCESS);
              #endif
           }
           else
           {
              SQLGetDiagRec(SQL_HANDLE_STMT,hStmt,1,sql.szErrCode,&sql.iNativeErrPtr,sql.szErrMsg,512,&sql.iTextLenPtr);
              #if defined(MY_DEBUG)
                  _ftprintf(fp,_T("  sql.dr.szErrCode = %s\n"),sql.szErrCode);
                  _ftprintf(fp,_T("  sql.dr.szErrMsg  = %s\n"),sql.szErrMsg);
              #endif
              SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
              return FALSE;
           }
           LeaveCriticalSection(cs);
       }
       iRet=TRUE;
       #if defined(MY_DEBUG)
           _ftprintf(fp,_T("\n"));
       #endif
    }
    SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
 }
 #if defined(MY_DEBUG)
     _ftprintf(fp,_T("Leaving blnInsert()\n\n"));
 #endif

 return iRet;
}


Do you need to be using SQL_VARCHAR or SQL_VARBINARY in the 5th parameters of the call? I don't believe I've ever used that. It looks like you are trying to insert character string data, and for that I've always done as in my example code above. Maybe try that. In other words, when I want to put character string data into the db, I bind a TCHAR buffer szString, which in my case above I have sized to 32 characters. My code then copies to that buffer whatever I want to go in the db, and you can see I'm passing in 31 for one of the parameters and 32 for another. Also, I'd never have figured out ODBC if it wasn't for SQLGetDiagRec(). That function usually returns quite good explanations of why an error is occurring. Its setup is a bit daunting, however.
Last edited on
vivmen (120)
Well thanks for reply.

actually i want to insert a file into database, so i don't know its size it can be anything.

&hence i can not use any fix value all need to decide run time.

my table has it as varbinary(MAX) .

well i did it in .NET approx year ago it works there.

but now i need to do it in c++.
vivmen (120)
well problem is solved

call SQLDescribeParam before binding parameter
Topic archived. No new replies allowed.