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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C++

From novice to tech pro — start learning today.