odbc question

[Ryan Spires]

Have a question in regards to odbc and tcl.

I am new to the concept so be gentle 🙂

I am attempting to put data into a table.

I have been able to successfully read from the table, so I know connectivity is good, etc.

#I prepare the statement…

set insert “INSERT INTO tblFunction VALUES (?, ?, ?, ?, ?, ?, ?)”

odbc SQLPrepare $hstmt1 $insert SQL_NTS

#bind the parameters.

odbc SQLBindParameter $hstmt1 0 SQL_PARAM_INPUT SQL_C_CHAR SQL_CHAR 15 0 tblPtNo 15 NULL

odbc SQLBindParameter $hstmt1 1 SQL_PARAM_INPUT SQL_C_CHAR SQL_CHAR 8 0 tblMsgDt 8 NULL

odbc SQLBindParameter $hstmt1 2 SQL_PARAM_INPUT SQL_C_CHAR SQL_CHAR 8 0 tblMsgTm 8 NULL

odbc SQLBindParameter $hstmt1 3 SQL_PARAM_INPUT SQL_C_CHAR SQL_CHAR 15 0 tblMsgConId 15 NULL

odbc SQLBindParameter $hstmt1 4 SQL_PARAM_INPUT SQL_C_CHAR SQL_CHAR 15 0 tblOperatorId 15 NULL

odbc SQLBindParameter $hstmt1 5 SQL_PARAM_INPUT SQL_C_CHAR SQL_CHAR 8 0 tblUpdateDt 8 NULL

odbc SQLBindParameter $hstmt1 6 SQL_PARAM_INPUT SQL_C_CHAR SQL_CHAR 8 0 tblUpdateTm 8 NULL

#set the values

set tblPtNo $msgPtNo

set tblMsgDt $msgDate

set tblMsgTm $msgTime

set tblMsgConId $msgConId

set tblOperatorId $msgOperatorId

set tblUpdateDt $msgUpdateDt

set tblUpdateTm $msgUpdateTm

#and execute the statement

set rVal [odbc SQLExecute $hstmt1]

echo $rVal

#and go grab my error

if { [cequal $rVal SQL_ERROR] } {

   odbc SQLError $henv $hdbc $hstmt1 state local errorMsg 200 sz

   echo “ERROR: unable to update table: State = ‘$state’, Msg = ‘$errorMsg'”

}

The error is the following:

state=07002

Msg= COUNT field incorrect

I am hopeful that someone will look at this, shake their head, and provide a simple explanation to the err of my ways 🙂

Thanks,

Topic

[Anonymous]

Sounds like a data type problem of some kind.

You should dump out your select and paste it into SQL window and run it without using the engine and check the data types.

[garry r fisher]

Hi,

Not sure whether it is the problem or not but change the column values in your Binds to start from 1 rather than 0.

Regards

Garry

[Ryan Spires]

made some modifications based on the suggestions… still suffering from the same error.  I am including the mods as well as my data I am attempting to insert into the table

odbc SQLBindParameter $hstmt1 1 SQL_PARAM_INPUT SQL_C_CHAR SQL_CHAR 15 0 tblPtNo 15 NULL

odbc SQLBindParameter $hstmt1 2 SQL_PARAM_INPUT SQL_C_TYPE_DATE SQL_TYPE_DATE 10 0 tblMsgDt 8 NULL

odbc SQLBindParameter $hstmt1 3 SQL_PARAM_INPUT SQL_C_CHAR SQL_CHAR 8 0 tblMsgTm 8 NULL

odbc SQLBindParameter $hstmt1 4 SQL_PARAM_INPUT SQL_C_CHAR SQL_CHAR 15 0 tblMsgConId 15 NULL

odbc SQLBindParameter $hstmt1 5 SQL_PARAM_INPUT SQL_C_CHAR SQL_CHAR 15 0 tblOperatorId 15 NULL

odbc SQLBindParameter $hstmt1 6 SQL_PARAM_INPUT SQL_C_TYPE_DATE SQL_TYPE_DATE 10 0 tblUpdateDt 8 NULL

odbc SQLBindParameter $hstmt1 7 SQL_PARAM_INPUT SQL_C_CHAR SQL_CHAR 8 0 tblUpdateTm 8 NULL

data:

W1111111

05/03/2007

15:07

CHPFQP03

RYAN

05/23/2007

15:58:53

dump of table:

Column_name,Type,Computed,Length,Prec,Scale,Nullable,TrimTrailingBlanks,FixedLenNullInSource

PtNo,varchar,no,15,     ,     ,no,no,no

MsgDt,datetime,no,8,     ,     ,yes,(n/a),(n/a)

MsgTm,char,no,8,     ,     ,yes,no,yes

MsgType,varchar,no,15,     ,     ,yes,no,no

MsgConID,varchar,no,15,     ,     ,yes,no,no

OperatorID,varchar,no,15,     ,     ,yes,no,no

UpdateDt,datetime,no,8,     ,     ,no,(n/a),(n/a)

UpdateTm,char,no,8,     ,     ,no,no,no

[Anonymous]

Echo your select out. Cut and paste into a SQL window running against your database. This will show you any data type errors and confirm that the syntax is correct.

The odbc returned errors are almost never of any use.  If it runs in one place it will run in the other.

[garry r fisher]

Hi,

Have you tried hard coding your INSERT within your script? This is the way I test mine and it quite often shows up problems with quotes, numbers, dates etc.

INSERT into tblFunction VALUES (W1111111,05/03/2007,15:07,CHPFQP03,RYAN,05/23/2007,15:58:52)

The other thing I do is a tip from Quovadx and always oversize my values in the BIND eg

odbc SQLBindParameter $hstmt1 1 SQL_PARAM_INPUT SQL_C_CHAR SQL_CHAR 1000 0 tblPtNo 1000 NULL

Hope this helps.

Garry

[Ryan Spires]

Garry,

Thanks for the suggestion…  I am new to doing this odbc stuff, so for some reason I had it in my mind that I needed to prepare the sql statement.   Your suggestion worked…  I was missing a data element so my data was not getting placed into the proper columns.  Thanks for everyone’s help!   I am 1 out of 19 tables closer of getting to the end.

Thanks,

[Steve Robertson]

Ryan,

I don’t see right off what the problem is.

However, may I suggest using SQLExecDirect instead of SQLPrepare/SQLExecute? It’s a whole bunch easier. We have a large daily volume per day and have no performance issues with ODBC.

Here is some sample code where I insert into a table some info about unknown HL7 message types:

set sqltext “insert into hl7.ib_unknown_msg_types (record_created_date, facility, filter_table_file, message_type, raw_message_type, message_timestamp, message_control_id, message_disposition) values (SYSDATE, ‘$msg_facility’, ‘$filter_table’, ‘$msg_type’, ‘$msgtype’, ‘$msg_datetime’, ‘$msg_controlid’, ‘$unk_disp’)”

set sqlreturn [odbc SQLExecDirect $hstmt $sqltext SQL_NTS]

Then I can check $sqlreturn for success or failure.

Also, to go along with Ryan’s thought, be very sure that the actual values for all fields of data you’re attempting to insert are no larger than defined in the database.

Steve Robertson

TeamHealth, Inc.

[garry r fisher]

Hi Ryan,

Glad to hear that you got your problem sorted.

Regards

Garry

[Author]

Replies