ODBC – Error – [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error

 

Background

Continuing to review ODBC API calls via C.

Ran into a simple error.

Error Message – #1 – COUNT field incorrect or syntax error

Error Image

vertica.preparedStatements.01.20200107.0833AM

Error Text


State  :- 07002
native :- 0
text   :- [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error

Code

Code Snippet


    // Bind CustNames
     custNameDataLen = SQL_NTS;

    SQLBindParameter
    (
          hdlStmt
        , 2
        , SQL_PARAM_INPUT
        , SQL_C_CHAR
        , SQL_VARCHAR
        , 50
        , 0
        , (SQLPOINTER)custName
        ,  custNameDataLen
        , &custNameLen
   );

TroubleShooting

Issue

The issue is the custNameDataLen variable.

Documentation

The CustNameDataLen variable map to the BufferLength variable.

Microsoft Documentation

Links

  1. SQLBindParameter Function
    • Microsoft
      • SQL Server 2019
        Link

Outline

Text

BufferLength
[Input/Output] Length of the ParameterValuePtr buffer in bytes.
For more information, see “BufferLength Argument” in “Comments.”

Image

documentation.microsoft.bufferLength

Expanded

  1. For character and binary C data, the BufferLength argument specifies the length of the *ParameterValuePtr buffer (if it is a single element) or the length of an element in the *ParameterValuePtr array (if the value in the SQL_ATTR_PARAMSET_SIZE statement attribute is greater than 1).
  2. This argument sets the SQL_DESC_OCTET_LENGTH record field of the APD. If the application specifies multiple values, BufferLength is used to determine the location of values in the *ParameterValuePtr array, both on input and on output. For input/output and output parameters, it is used to determine whether to truncate character and binary C data on output:
  3. For character C data, if the number of bytes available to return is greater than or equal to BufferLength, the data in *ParameterValuePtr is truncated to BufferLength less the length of a null-termination character and is null-terminated by the driver.
  4. For binary C data, if the number of bytes available to return is greater than BufferLength, the data in *ParameterValuePtr is truncated to BufferLength bytes.
  5. For all other types of C data, the BufferLength argument is ignored. The length of the *ParameterValuePtr buffer (if it is a single element) or the length of an element in the *ParameterValuePtr array (if the application calls SQLSetStmtAttr with an Attribute argument of SQL_ATTR_PARAMSET_SIZE to specify multiple values for each parameter) is assumed to be the length of the C data type.
  6. For streamed output or streamed input/output parameters, the BufferLength argument is ignored because the buffer length is specified in SQLGetData.

Summary

  1. For Character Data
    • sizeof() multiplied by length
    • CHAR
      • sizeof(char) multiplied by max-string-length
    • WCHAR ( unicode)
      • sizeof(wchar) multiplied by max-string-length

Code – Revision

Outline

  1. Buffer Length
    • size of
      • size of SQLCHAR or SQLWCHAR
    • Column Length

Code Snippet #1


    define CUST_NAME_LEN 50

    // Bind CustNames
    // custNameDataLen = SQL_NTS;
    //custNameDataLen = sizeof(SQLCHAR) * strlen(custName);
    custNameDataLen = sizeof(SQLCHAR) * CUST_NAME_LEN;

    SQLBindParameter
    (
          hdlStmt
        , 2
        , SQL_PARAM_INPUT
        , SQL_C_CHAR
        , SQL_VARCHAR
        , 50
        , 0
        , (SQLPOINTER)custName
        ,  custNameDataLen
        , &custNameLen
   );

Code Snippet #2 – Use Zero (0)


    define CUST_NAME_LEN 50

    // Bind CustNames
    // custNameDataLen = SQL_NTS;
    //custNameDataLen = sizeof(SQLCHAR) * strlen(custName);
    //custNameDataLen = sizeof(SQLCHAR) * CUST_NAME_LEN;
    int iZero=0

    SQLBindParameter
    (
          hdlStmt
        , 2
        , SQL_PARAM_INPUT
        , SQL_C_CHAR
        , SQL_VARCHAR
        , 50
        , 0
        , (SQLPOINTER)custName
        //, custNameDataLen
        , iZero
        , &custNameLen
   );

Error Message – #2 – State – 22001 – Error Text – [Microsoft][ODBC SQL Server Driver]String data, right truncation

Error Image

vertica.preparedStatements.StringDataRight truncation.01.20200107.1043AM

Error Text


State :- 22001 

native :- 0 

text :- [Microsoft][ODBC SQL Server Driver]String data, right truncation

Code Review

Code – Revised


     // Bind phoneNums
	phoneNumLen = sizeof(SQLCHAR) * CUST_NAME_LEN;
	//phoneNumLen = sizeof(SQLCHAR) * PHONE_NUM_LEN;
	printf
	(
		  "\tBind phoneNum to prepared statement ( sizeof) %d  - Length %d - BufferLength %d ....\n"
		, sizeof(SQLCHAR)
		, PHONE_NUM_LEN
		, phoneNumLen
	);

    SQLBindParameter
	(
		hdlStmt
		, 3
		, SQL_PARAM_INPUT
		, SQL_C_CHAR
		, SQL_CHAR
		//, 15
		, PHONE_NUM_LEN
		, 0
		, (SQLPOINTER)phoneNum
		, phoneNumLen
		, &phoneNumLen
	);  

Code – revised


     // Bind phoneNums
     //phoneNumLen = sizeof(SQLCHAR) * CUST_NAME_LEN;
     phoneNumLen = sizeof(SQLCHAR) * PHONE_NUM_LEN;

     printf
     (
	  "\tBind phoneNum to prepared statement ( sizeof) %d  - Length %d - BufferLength %d ....\n"
		, sizeof(SQLCHAR)
		, PHONE_NUM_LEN
		, phoneNumLen
     );

     SQLBindParameter
	(
		hdlStmt
		, 3
		, SQL_PARAM_INPUT
		, SQL_C_CHAR
		, SQL_CHAR
		//, 15
		, PHONE_NUM_LEN
		, 0
		, (SQLPOINTER)phoneNum
		, phoneNumLen
		//&phoneNumLen
		, &phoneNumDataLen
	);  

Listening

Stay listening to ….

Dave East – GodFather 4

Video

Dave East – “Godfather 4” Feat. Nas (Official Lyric Video)
Link

Lyric

Source :-  Link

My dead grandad came to my cousin through a psychic
I don’t really like it, pay prices for afterlife advisements
Kinda double jumble, prefer to take my chances

Like a tree falling’ in the middle of the forest
Nobody heard your ____ drop, it’s really garbage

No diss to them, man who got it in
This is now, that was then
Different style, different Benz

 

It’s clear there’s a difference
You resortin’ to, uh, distortin’ the truth


They not thorough

How you my brother? You not (…..)

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s