odbc SQLExecute repeatedly

Hi all,
I want to setup odbc statement, bind it to pointers to variables, and then execute it repeatedly when values change. I tried everything, SQLExecute always fails with "Sql state:=22003 value larger than specified precision allowed for this column"

Here's the table:
CREATE TABLE dbo.table1(i_int1 int NOT NULL,
      i_int2 int NOT NULL,
      f_float1 float NOT NULL,
      f_float2 float NOT NULL,
      c_char varchar(20) NOT NULL)


Here's the text for SQLPrepare:
"INSERT INTO dbo.table1 (i_int1,i_int2,f_float1,f_float2,c_char) VALUES (?,?,?,?,?)"

The struct I use to hold the values:
typedef struct struct1
{
      BYTE b1;        
      unsigned short us1;
      float f1;
      double d1;                      
      char c1[16];            
} struct1;

Then I bind:
long bytSize = sizeof(SQLCHAR);
long ushortSize = sizeof(SQLUSMALLINT);
long dblSize = sizeof(SQLDOUBLE);
long sSize = 21;
long lenParmBYTE = sizeof(SQLCHAR);
long lenParmUS = sizeof(SQLUSMALLINT);
long lenParmDbl = sizeof(SQLDOUBLE);
long lenParmS = 0;
rc = SQLBindParameter(m_hStmt,1,SQL_PARAM_INPUT, SQL_C_CHAR,
                            SQL_CHAR, bytSize, 0, &st.b1, bytSize, &lenParmBYTE);
rc = SQLBindParameter(m_hStmt,1,SQL_PARAM_INPUT, SQL_C_SHORT,
                            SQL_SMALLINT, ushortSize, 0, &st.us1, ushortSize, &lenParmUS);
rc = SQLBindParameter(m_hStmt,1,SQL_PARAM_INPUT, SQL_C_DOUBLE,
                            SQL_DOUBLE, dblSize, 16, &st.f1, dblSize, &lenParmDbl);
rc = SQLBindParameter(m_hStmt,1,SQL_PARAM_INPUT, SQL_C_DOUBLE,
                            SQL_DOUBLE, dblSize, 16, &st.d1, dblSize, &lenParmDbl);
rc = SQLBindParameter(m_hStmt,1,SQL_PARAM_INPUT, SQL_C_CHAR,
                            SQL_VARCHAR, sSize, 0, st.c1, sizeof(st.c1), &lenParmS);          

The very first time I assign values and execute, I get an error. Values are all in range, I even get the error if I assign all zeroes.

Any help is appreciated.



darrgyasAsked:
Who is Participating?
 
itsmeandnobodyelseCommented:
Check the table of ODBC data types:

   http://msdn.microsoft.com/en-us/library/ms714556(v=VS.85).aspx

Try

rc = SQLBindParameter(m_hStmt,1,SQL_PARAM_INPUT, SQL_C_CHAR,
                            SQL_CHAR, sizeof(BYTE), 1, &st.b1, sizeof(BYTE), NULL);
rc = SQLBindParameter(m_hStmt,1,SQL_PARAM_INPUT, SQL_C_SHORT,
                            SQL_SMALLINT, sizeof(short), 1, &st.us1, sizeof(short), NULL);
rc = SQLBindParameter(m_hStmt,1,SQL_PARAM_INPUT, SQL_C_FLOAT,
                            SQL_FLOAT, sizeof(float), 5, &st.f1, sizeof(float), NULL);
rc = SQLBindParameter(m_hStmt,1,SQL_PARAM_INPUT, SQL_C_DOUBLE,
                            SQL_DOUBLE, sizeof(double), 8, &st.d1, sizeof(double), NULL);
rc = SQLBindParameter(m_hStmt,1,SQL_PARAM_INPUT, SQL_C_CHAR,
                            SQL_VARCHAR, sizeof(st.c1), 0, &st.c1, sizeof(st.c1), &lenParmS);          




0
 
momi_sabagCommented:
the second parameter of SQLBindParameter is the parameter number,
you always use 1 (probably copy paste error), try this

rc = SQLBindParameter(m_hStmt,1,SQL_PARAM_INPUT, SQL_C_CHAR,
                            SQL_CHAR, bytSize, 0, &st.b1, bytSize, &lenParmBYTE);
rc = SQLBindParameter(m_hStmt,2,SQL_PARAM_INPUT, SQL_C_SHORT,
                            SQL_SMALLINT, ushortSize, 0, &st.us1, ushortSize, &lenParmUS);
rc = SQLBindParameter(m_hStmt,3,SQL_PARAM_INPUT, SQL_C_DOUBLE,
                            SQL_DOUBLE, dblSize, 16, &st.f1, dblSize, &lenParmDbl);
rc = SQLBindParameter(m_hStmt,4,SQL_PARAM_INPUT, SQL_C_DOUBLE,
                            SQL_DOUBLE, dblSize, 16, &st.d1, dblSize, &lenParmDbl);
rc = SQLBindParameter(m_hStmt,5,SQL_PARAM_INPUT, SQL_C_CHAR,
                            SQL_VARCHAR, sSize, 0, st.c1, sizeof(st.c1), &lenParmS);          
0
 
darrgyasAuthor Commented:
I need to do the same for DB2, Oracle and SQLServer
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
darrgyasAuthor Commented:
>> the second parameter of SQLBindParameter is the parameter number,
sorry, my mistake. It's correct in the actual code, I just had to simplify it and made mistakes in the process.
0
 
momi_sabagCommented:
how did you decide how to map the variables types to the database data types?
0
 
darrgyasAuthor Commented:
>> how did you decide how to map the variables types to the database data types?
According to documentation.
The truth is, I tried many mappings, what you see here is the 20th try.
0
 
darrgyasAuthor Commented:
I lost track of what I did and did not try. Let me try this.
OK, different error now:
Sql state:=22001can bind a LONG value only for insert into a LONG column" (1)
0
 
itsmeandnobodyelseCommented:
>>>> 22001, can bind a LONG value only for insert into a LONG column

A LONG is a BLOB (binary large object) which hardly can be caused by the SQLBindParameter statements from above. Do you have more SQLBindParameter calls? One where a BLOB column (MEMO)is involved?

>>>> what you see here is the 20th try.

IMO, that makes little sense. There are so many input parameters. You can't expect them to work if you don't set them methodically and according the docs.

BTW, if using Visual C++ compiler you could find good code samples for SQLBindParameter in the MFC source.

0
 
darrgyasAuthor Commented:
>>>> There are so many input parameters. You can't expect them to work if you don't set them methodically and according the docs.

You're absolutely right. But when you've done that and it still doesn't work, you start looking elsewhere.
In short, I tried almost everything I could find in the examples on the web. There are quite a few,
very different. and I'm still looking.
By the book doesn't work, I'm trying to figure out why.

No, there's no LONG values
0
 
itsmeandnobodyelseCommented:
>>>> No, there's no LONG values

Ok. Please post the statement where you get the error message

    22001, can bind a LONG value only for insert into a LONG column

Is it SQLPrepare ? If so, please post all SQLBindParameter calls previous to that SQLPrepare and tell me what column type that column has in the Oracle table.
0
 
darrgyasAuthor Commented:
I only get errors on SQLExecute.
I've figured out this particular error, it's coming from this binding:

rc = SQLBindParameter(m_hStmt,1,SQL_PARAM_INPUT, SQL_C_CHAR,
                            SQL_CHAR, sizeof(BYTE), 1, &st.b1, sizeof(BYTE), NULL);

As you can see above, the first column is defined in the table as int, and as BYTE in the struct (can't change the struct in any way). When I changed the binding to
SQL_C_LONG, SQL_INTEGER, sizeof(BYTE)...
the error disappeared.
I am now doing what I was trying to avoid - I created the test table, and test field by field.
I'll let you know how it ends, but please share any thoughts.

0
 
itsmeandnobodyelseCommented:
What column type is the field you try to bind?

0
 
darrgyasAuthor Commented:
If you look at the definition of the table and struct at the top, you'll see what data types I have in the struct (can't change those) and what I decided to match it to in the table.
0
 
itsmeandnobodyelseCommented:
>>>> what data types I have in the struct (can't change those)

No, not the struct. I need the column type in Oracle. Do 'describe TABLE' in sqlplus or look at the table definition in TOAD, SQLDeveloper or other GUI tool.
0
 
darrgyasAuthor Commented:
Above is the table definition for SQLServer. I use NUMBER in Oracle.
I presented data here in a very simplified form, it's too time consuming to do this again for Oracle.
0
 
darrgyasAuthor Commented:
Using a struct to hold values is much more restrictive than using "stand-alone" variables.
The sizes and types have to be precise.
Using SQL_C_UTINYINT for BYTE solved the problem.
Thank you for your help
0
 
itsmeandnobodyelseCommented:
>>>> Above is the table definition for SQLServer

Ok. It is INT type what cannot be mapped to a BYTE with SQL_C_CHAR. You would need to either change the struct member type to 'int' or 'unsigned int' or modifiy the column type to CHAR(1).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.