SQLBindParameter

This is the forum for miscellaneous technical/programming questions.

Moderator: 2ffat

SQLBindParameter

Postby theLizard » Wed Jun 08, 2016 7:36 pm

My problem HY000 unbound parameters in query, not sure where the problem lies.

HY000 is described as General Error with no specific SQLSTATE

I think it maybe to do with ParameterValuePtr ( Fields[c]->Value.c_str(), ) but not sure how to go about correcting it if it is the problem, more than likely there may / will be other problems in my code I am not aware of, have searched for possible answers but none yet.

FYI, info for the fields vector is obtained from the table using SQLColumnsW(.....)

ParameterValuePtr is the value stored applicable database field, would I be right in thinking that this is the problem area or am I off the beaten track :?

First go at Parameter Binding...

Any insight would be good.

Cheers

Code: Select all
   if(Fields.size() > 0)
      sql = "INSERT INTO " + Fields[0]->TableName + "(";
   else
      return(success); //false

   for(; c < Fields.size(); c++)
      {
      sql += Fields[c]->Field;
      p += "?";

      if(c < Fields.size()-1)
         {
         sql += ",";
         p += ",";
         }
      }
   sql += ")VALUES(" + p + ")";

   c = 1;
   for(; c < Fields.size(); c++)
      {
      q.retcode = SQLBindParameter(q.hstmt,
          c,                                 //ParameterNumber
          SQL_PARAM_INPUT,                   //InputOutputType
          Fields[c]->DataType,               //ValueType
          Fields[c]->SqlDataType,            //ParameterType
          Fields[c]->ColumnSize,             //ColumnSize
          Fields[c]->DecimalDigits,          //DecimalDigits
          Fields[c]->Value.c_str(),          //ParameterValuePtr
          Fields[c]->Value.Length(),         //BufferLength
          &len);                             //StrLen_or_IndPtr

      }
   q.retcode = SQLPrepare(q.hstmt, sql.c_str(), SQL_NTS);
   SQLNumParams(q.hstmt, &NumParams);
   q.retcode = SQLExecute(q.hstmt);

   if (!MYSQLSUCCESS(q.retcode))
      {
      error_out(SQL_HANDLE_STMT, q.hstmt);
      FreeSqlHandles(&q);
      }

theLizard
BCBJ Master
BCBJ Master
 
Posts: 447
Joined: Wed Mar 18, 2009 2:14 pm

Re: SQLBindParameter

Postby rlebeau » Thu Jun 09, 2016 3:22 pm

Your loops do not look right, especially some of your indexing. Try something more like this instead:

Code: Select all
if (Fields.size() == 0)
   return(success); //false

sql = "INSERT INTO " + Fields[0]->TableName + "(" + Fields[0]->Field;
p = "?";

for(c = 1; c < Fields.size(); ++c)
   {
   sql += ("," + Fields[c]->Field);
   p += ",?";
   }

sql += (") VALUES(" + p + ")");

// assuming q.retcode is already SQL_SUCCESS, otherwise set it explicitly...
//q.retcode = SQL_SUCCESS;

for(c = 0; c < Fields.size(); ++c)
   {
   q.retcode = SQLBindParameter(q.hstmt,
      c+1,                               //ParameterNumber
      SQL_PARAM_INPUT,                   //InputOutputType
      Fields[c]->DataType,               //ValueType
      Fields[c]->SqlDataType,            //ParameterType
      Fields[c]->ColumnSize,             //ColumnSize
      Fields[c]->DecimalDigits,          //DecimalDigits
      Fields[c]->Value.c_str(),          //ParameterValuePtr
      Fields[c]->Length(),               //BufferLength
      &len);                             //StrLen_or_IndPtr
   if (!MYSQLSUCCESS(q.retcode))
      break;
}

if (MYSQLSUCCESS(q.retcode))
   {
   q.retcode = SQLPrepare(q.hstmt, sql.c_str(), SQL_NTS);
   if (MYSQLSUCCESS(q.retcode))
      {
      SQLNumParams(q.hstmt, &NumParams);
      q.retcode = SQLExecute(q.hstmt);
      }
   }

if (!MYSQLSUCCESS(q.retcode))
   {
   error_out(SQL_HANDLE_STMT, q.hstmt);
   FreeSqlHandles(&q);
   }

...


That being said, note that the BufferLength parameter of SQLBindParameter() is expressed in bytes, not characters, so if your Value member is not an AnsiString then you must multiply its Length() by the byte size of its actual Char type.
Last edited by rlebeau on Fri Jun 10, 2016 10:28 am, edited 1 time in total.
Remy Lebeau (TeamB)
Lebeau Software
User avatar
rlebeau
BCBJ Author
BCBJ Author
 
Posts: 1457
Joined: Wed Jun 01, 2005 3:21 am
Location: California, USA

Re: SQLBindParameter

Postby theLizard » Thu Jun 09, 2016 6:20 pm

Thanks Remy, Almost there...

My updated code, for a quick fix, I have a value var of AnsiString and assign it the value from a UnicodeString, this works fine for string values.

Integer values are a problem, have not tested other types as yet.

Code: Select all
//at this point q.retcode is SQL_SUCCESS,  could not get here otherwise..
//   if (!AllocHandles(&q))
//      return(success);  //handles not allocated, return.

   p = "";
   if(Fields.size() > 0)
      sql = "INSERT INTO " + Fields[0]->TableName + "(";
   else
      return(success);

   for(c= 1; c < Fields.size(); c++)
      {
      sql += Fields[c]->Field;
      p += "?";

      value = Fields[c]->Value,
      q.retcode = SQLBindParameter(q.hstmt,
          c,                                       //ParameterNumber
          SQL_PARAM_INPUT,                         //InputOutputType
          SQL_C_DEFAULT,                                     //ValueType
//             ParameterType(Fields[c]->SqlDataType), //ParameterType
          SQL_CHAR,            //ParameterType
          Fields[c]->ColumnSize,                   //ColumnSize
          Fields[c]->DecimalDigits,                //DecimalDigits
          value.c_str(),                                  //ParameterValuePtr
          value.Length(),                                  //BufferLength
          &len);                                   //StrLen_or_IndPtr

      if(c < Fields.size()-1)
         {
         sql += ",";
         p += ",";
         }
      }
   sql += ")VALUES(" + p + ")";

   q.retcode = SQLPrepare(q.hstmt, sql.c_str(), SQL_NTS);
   SQLNumParams(q.hstmt, &NumParams);

   q.retcode = SQLExecute(q.hstmt);

   if (!MYSQLSUCCESS(q.retcode))
      {
      error_out(SQL_HANDLE_STMT, q.hstmt);
      FreeSqlHandles(&q);
      }
   else
      success = true;


This ParameterType(Fields[c]->SqlDataType), //ParameterType

seems to be a problem, see switch code

When DataType is 4 (SQL_INTEGER), the value added to the database record is not the value of the field, i.e if the value is 1, the recorded value is 7630441

in my previous code, parameter type was SQL_CHAR, in this case the value of (1) was added as 0 to the database, so question is, is the ParameterValue for int's giving me my problems..

Cheers

Code: Select all
SQLSMALLINT __fastcall .... ParameterType(int type)
   {

   switch(type)
      {
      case 0:
         return(SQL_UNKNOWN_TYPE);
         break;
      case 1:
         return(SQL_CHAR);
         break;
      case 2:
         return(SQL_NUMERIC);
         break;
      case 3:
         return(SQL_DECIMAL);
         break;
      case 4:
         return(SQL_INTEGER);
         break;
      case 5:
         return(SQL_SMALLINT);
         break;
      case 6:
         return(SQL_FLOAT);
         break;
      case 7:
         return(SQL_REAL);
         break;
      case 8:
         return(SQL_DOUBLE);
         break;
      case 9:
         return(SQL_DATETIME);
         break;
      case 10:
         return(SQL_TIME);
         break;
      case 11:
         return(SQL_TIMESTAMP);
         break;
      case 12:
         return(SQL_VARCHAR);
         break;
      case 91:
         return(SQL_TYPE_DATE);
         break;
      case 92:
         return(SQL_TYPE_TIME);
         break;
      case 93:
         return(SQL_TYPE_TIMESTAMP);
         break;
      case -1:
         return(SQL_LONGVARCHAR);
         break;
      case -2:
         return(SQL_BINARY);
         break;
      case -3:
         return(SQL_VARBINARY);
         break;
      case -4:
         return(SQL_LONGVARBINARY);
         break;
      case -5:
         return(SQL_BIGINT);
         break;
      case -6:
         return(SQL_TINYINT);
         break;
      case -7:
         return(SQL_BIT);
         break;
      case -8:
         return(SQL_WCHAR);
         break;
      case -9:
         return(SQL_WVARCHAR);
         break;
      case -10:
         return(SQL_WLONGVARCHAR);
         break;
      case -11:
         return(SQL_GUID);
         break;
      case -150:
         return(SQL_SS_VARIANT);
         break;
      case -151:
         return(SQL_SS_UDT);
         break;
      case -152:
         return(SQL_SS_XML);
         break;
      case -153:
         return(SQL_SS_TABLE);
         break;
      case -154:
         return(SQL_SS_TIME2);
         break;
      case -155:
         return(SQL_SS_TIMESTAMPOFFSET);
         break;
      default:
         return(SQL_UNKNOWN_TYPE);
         break;
      }
   }
theLizard
BCBJ Master
BCBJ Master
 
Posts: 447
Joined: Wed Mar 18, 2009 2:14 pm

Re: SQLBindParameter

Postby rlebeau » Fri Jun 10, 2016 10:49 am

theLizard wrote:My updated code, for a quick fix, I have a value var of AnsiString and assign it the value from a UnicodeString, this works fine for string values.


You are still indexing your fields wrong. Your first loop is completely ignoring the fieldname and value of Fields[0]. Your Fields container is 0-based. The ParameterNumber of SQLBindParameter() is 1-based.

Did you even try the looping code I gave you in my last reply?

theLizard wrote:Integer values are a problem
have not tested other types as yet.


For integer fields, you need to pass a pointer to an 'int' variable to the ParameterValuePtr parameter of SQLBindParameter(). Your fields have an AnsiString member instead, so you are passing a char* pointer to every field regardless of its true field type. You need to use the correct data type that matches the field type.

Worse, you are now hard-coding the field type of every field to SQL_CHAR, whereas before you were at least using your DataType and SqlDataType members instead. Why the change?

theLizard wrote:This ParameterType(Fields[c]->SqlDataType), //ParameterType

seems to be a problem, see switch code


The problem is that you are not paying attention to what ParameterType() actually returns so you can pass the correct type of data to SQLBindParameter().

theLizard wrote:When DataType is 4 (SQL_INTEGER), the value added to the database record is not the value of the field, i.e if the value is 1, the recorded value is 7630441


You are not passing an int* to SQLBindParameter(), you are passing a char* instead. So the DB will dereference the char* and interpret the first 4 'char' values as an 'int' value. The numeric value 7630441 as bytes is 0x69 0x6E 0x74 0x00, which is the same bytes as the character string "int".

If your Fields[index]->Value is an AnsiString, you will have to convert it to an 'int' first, eg:

Code: Select all
int value = Fields[index]->Value.ToInt();
...
q.retcode = SQLBindParameter(q.hstmt,
          c,                                       //ParameterNumber
          SQL_PARAM_INPUT,                         //InputOutputType
          SQL_INTEGER,                             //ParameterType
          Fields[c]->ColumnSize,                   //ColumnSize
          Fields[c]->DecimalDigits,                //DecimalDigits
          &value,                                  //ParameterValuePtr
          0,                                       //BufferLength
          &len);                                   //StrLen_or_IndPtr


You might consider changing your Value member to a Variant instead of an AnsiString.
Last edited by rlebeau on Sun Jun 12, 2016 2:27 am, edited 1 time in total.
Remy Lebeau (TeamB)
Lebeau Software
User avatar
rlebeau
BCBJ Author
BCBJ Author
 
Posts: 1457
Joined: Wed Jun 01, 2005 3:21 am
Location: California, USA

Re: SQLBindParameter

Postby theLizard » Fri Jun 10, 2016 3:05 pm

rlebeau wrote:You are still indexing your fields wrong. Your first loop is completely ignoring the fieldname and value of Fields[0]. Your Fields container is 0-based. The ParameterNumber of SQLBindParameter() is 1-based.


Yes I am aware of that, Fields[0] (id) is the identity field in the database and it is auto increment so does not need to be included in the binding therefore fields[1] is parameter 1 that is why I have stated c = 1.

rlebeau wrote:Did you even try the looping code I gave you in my last reply?


Yes, Fields[c]->DataType, is the data type read from the table definition and being aware that I need to parse the correct type to SQLBindParameter(), I request the type via ParameterType(Fields[c]->SqlDataType), which, stepping through the code, returns, if Fields[c]->DataType = 4, SQL_INTEGER for -9 the return type is SQL_WVARCHAR so, if ValueType is as declared an SQLSMALLINT, at this point from my understanding it is getting the correct ValueType.

rlebeau wrote:You are not passing an int* to SQLBindParameter(), you are passing a char* instead. So the DB will dereference the char* and interpret the first 4 'char' values as an 'int' value. The numeric value 7630441 as bytes is 0x69 0x6E 0x74 0x00, which is the same bytes as the character string "int".

If your Fields[index]->Value is an AnsiString, you will have to convert it to an 'int' first, eg:


This is what I was not sure about, I thought that this was the problem but not sure how to go about correcting it but think I have the answer to that now, will try it today and see how I go.

Cheers
theLizard
BCBJ Master
BCBJ Master
 
Posts: 447
Joined: Wed Mar 18, 2009 2:14 pm

Re: SQLBindParameter

Postby theLizard » Fri Jun 10, 2016 6:00 pm

theLizard wrote:This is what I was not sure about, I thought that this was the problem but not sure how to go about correcting it but think I have the answer to that now, will try it today and see how I go.


Now I am completely lost, following your example, I tried a number of things using a switch statement to ensure that binding was done based on the data type, INT, VARCHAR declaring an int var and an AnsiString var also tried with a Variant wityh the following results (also tried other variations of ValueType, ParameterType and ParameterValuePtr)

Code: Select all
int iValue = StrToInt(p.Value); also tried p.Value.ToInt();
Watch: - > DataType: 4 (0x00000004)
q.retcode = SQLBindParameter(q.hstmt, c, SQL_PARAM_INPUT, SQL_INTEGER, SQL_C_SSHORT, p.ColumnSize,0, &iValue, 0, &len);

AnsiString value = p.Value.c_str();
Watch: - > DataType: -9 (0xFFFFFFF7)
q.retcode = SQLBindParameter(q.hstmt, c,SQL_PARAM_INPUT,SQL_C_DEFAULT, SQL_WVARCHAR, p.ColumnSize, p.DecimalDigits, value.c_str(), value.Length(), 0); //result in database

Statement :-  INSERT INTO projectList(f1,f2,f3)VALUES(?,?,?)
SQLNumParams(q.hstmt, &NumParams); --> NumParams = 3
q.retcode = 0

id   Fields[1]->Value = 2   Fields[2]->Value = "hello"   Fields[3]->Value = "fred"
   Parameter 1      Parameter 2         Parameter 3

=========================================================================================
245      0         ")"            ")"
=========================================================================================
Watch values   2 (0x00000002)      :01FCC4DC "hello"      :01FCC4DC "fred"
=========================================================================================

q.retcode = SQLBindParameter(q.hstmt, c,SQL_PARAM_INPUT,p.DataType, SQL_WVARCHAR,p.ColumnSize, p.DecimalDigits, value.c_str(), value.Length(), 0); //program type out of range
q.retcode = SQLBindParameter(q.hstmt, c,SQL_PARAM_INPUT,SQL_WVARCHAR, SQL_WVARCHAR,p.ColumnSize, p.DecimalDigits, value.c_str(), value.Length(), 0); //program type out of range
q.retcode = SQLBindParameter(q.hstmt, c,SQL_PARAM_INPUT,SQL_WVARCHAR, SQL_C_WCHAR,p.ColumnSize, p.DecimalDigits, value.c_str(), value.Length(), 0); //program type out of range
q.retcode = SQLBindParameter(q.hstmt, c,SQL_PARAM_INPUT,SQL_WVARCHAR, SQL_C_WCHAR,p.ColumnSize, p.DecimalDigits, value.c_str(), value.Length(), &len); //program type out of range


   switch(p.DataType)
      {
      case 4:
      case 5:
         iValue = StrToInt(p.Value); also tried p.Value.ToInt();

         q.retcode = SQLBindParameter(q.hstmt, c, SQL_PARAM_INPUT, SQL_INTEGER, SQL_C_SSHORT, p.ColumnSize,0, &iValue, 0, 0);

         if (!MYSQLSUCCESS(q.retcode))
            error_out(SQL_HANDLE_STMT, q.hstmt);
      case -9:
         value="";
         value = p.Value.c_str();
         q.retcode = SQLBindParameter(q.hstmt, c,SQL_PARAM_INPUT,SQL_C_DEFAULT, SQL_WVARCHAR, p.ColumnSize, p.DecimalDigits, value.c_str(), value.Length(), 0);

         if (!MYSQLSUCCESS(q.retcode))
            error_out(SQL_HANDLE_STMT, q.hstmt);


SQLite3 Table Def

CREATE TABLE [table] (
  [id] INTEGER PRIMARY KEY,
  [f1] INT,
  [f2] VARCHAR,    (WideString)
  [f3] VARCHAR);   (WideString)

Table Design :-

Name   Declared Type   Type   Size   Precision   Not Null   Not Null On Conflict   Default Value   Collate   Position   Old Position
id   INTEGER      INTEGER   0   0      False            0   0
f1   INT      INT   0   0      False            1   1
f2   VARCHAR      VARCHAR   0   0      False            2   2
f3   VARCHAR      VARCHAR   0   0      False            3   3



:?

Where to now!!!

Cheers
theLizard
BCBJ Master
BCBJ Master
 
Posts: 447
Joined: Wed Mar 18, 2009 2:14 pm

Re: SQLBindParameter

Postby theLizard » Fri Jun 10, 2016 8:47 pm

Update:

Thinking maybe that SQLite was causing the problem, I duplicated the table in SQL Server 2016 but got the same result, database shows field values as 5 68690696 ) ) which is similar to that in the SQLite DB.

Interestingly when I changed

q.retcode = SQLBindParameter(q.hstmt, c, SQL_PARAM_INPUT, p->SqlDataType, SQL_INTEGER, p->ColumnSize,0, &iValue, 0, 0);

to

q.retcode = SQLBindParameter(q.hstmt, c, SQL_PARAM_INPUT, SQL_INTEGER, SQL_INTEGER, p->ColumnSize,0, &iValue, 0, 0);

the database fields show 6 (int) 68887304 (varchar) Hello Fred (varchar) )

Hello Fred is what I entered for field 2 so it is correct but nothing changed in

q.retcode = SQLBindParameter(q.hstmt, c,SQL_PARAM_INPUT,SQL_C_DEFAULT, SQL_WVARCHAR, p->ColumnSize, p->DecimalDigits, value.c_str(), value.Length(), 0); //result in database

But changing to

q.retcode = SQLBindParameter(q.hstmt, c,SQL_PARAM_INPUT,p->DataType, SQL_WVARCHAR, p->ColumnSize, p->DecimalDigits, value.c_str(), value.Length(), 0); //result in database

produces this error [Microsoft][ODBC Driver Manager] Program type out of range

????
theLizard
BCBJ Master
BCBJ Master
 
Posts: 447
Joined: Wed Mar 18, 2009 2:14 pm

Re: SQLBindParameter

Postby theLizard » Sat Jun 11, 2016 6:53 pm

Forrest for the Trees!!!

Problem solved, was concentrating too much on SQLBindParameter( ... ) and ignoring the fact that ParameterValuePtr was [Deferred Input] as the doc said. :oops: I read it more than once too.

Anyway, getting the correct results now, however, there is always a better way so any suggestion would be welcomed.

This is the code that works...

Code: Select all
   if (!AllocHandles(&q))
      return(success);

   int size = Fields.size();
   p = "";
   if(size > 0)
      sql = "INSERT INTO " + Fields[0]->TableName + "(";
   else
      return(success);


   for(c= 1; c < size; ++c)
      {
      sql += Fields[c]->Field;
      p += "?";

      param = new TLSQLParameters();
      param->order = c;
      ParameterType(q, Fields[c], *param, c);
      parameters.push_back(param);

      if(c < size-1)
         {
         sql += ",";
         p += ",";
         }
      }
   sql += ")VALUES(" + p + ")";

   q.retcode = SQLPrepare(q.hstmt, sql.c_str(), SQL_NTS);
   SQLNumParams(q.hstmt, &NumParams);
   AnsiString v;

   //much more work to cater for all types of data.
   for(c = 0; c < parameters.size(); ++c)
      {
      switch(Fields[parameters[c]->order]->DataType)
         {
         case 12:
            //for strings
            v = Fields[parameters[c]->order]->Value.c_str();
            strcpy(parameters[c]->szFieldValue, v.c_str());
            parameters[c]->sValue = Fields[parameters[c]->order]->ColumnSize;
            parameters[c]->cbValue = SQL_NTS;
            break;
         case 4:
            //for integers
            v = Fields[parameters[c]->order]->Value.c_str();
            parameters[c]->sValue = StrToInt(v);
            break;
         }
      }

   q.retcode = SQLExecute(q.hstmt);

   if (!MYSQLSUCCESS(q.retcode))
      {
      error_out(SQL_HANDLE_STMT, q.hstmt);
      FreeSqlHandles(&q);
      }
   else
      success = true;

theLizard
BCBJ Master
BCBJ Master
 
Posts: 447
Joined: Wed Mar 18, 2009 2:14 pm


Return to Technical

Who is online

Users browsing this forum: Google [Bot] and 12 guests