ODBC error when trying to save message to database

[Rick Pritchett]

when i add the code to add the message to the database i get an error any ideas?

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

           odbc SQLPrepare $hstmt $insert SQL_NTS

           

#Bind varibles to columns

odbc SQLBindParameter $hstmt 1 SQL_PARAM_INPUT SQL_C_SLONG SQL_INTEGER 1000 0 mrn 1000 NULL

odbc SQLBindParameter $hstmt 2 SQL_PARAM_INPUT SQL_C_SLONG SQL_INTEGER 1000 0 fin 1000 NULL

odbc SQLBindParameter $hstmt 3 SQL_PARAM_INPUT SQL_C_CHAR SQL_CHAR 1000 0 lname 1000 NULL

odbc SQLBindParameter $hstmt 4 SQL_PARAM_INPUT SQL_C_CHAR SQL_CHAR 1000 0 fname 1000 NULL

odbc SQLBindParameter $hstmt 5 SQL_PARAM_INPUT SQL_C_SLONG SQL_INTEGER 1000 0 dob 1000 NULL

odbc SQLBindParameter $hstmt 6 SQL_PARAM_INPUT SQL_C_CHAR SQL_CHAR 1000 0 sex 1000 NULL

odbc SQLBindParameter $hstmt 7 SQL_PARAM_INPUT SQL_C_SLONG SQL_INTEGER 1000 0 ssn 1000 NULL

odbc SQLBindParameter $hstmt 8 SQL_PARAM_INPUT SQL_C_CHAR SQL_CHAR 1000 0 company 1000 NULL

odbc SQLBindParameter $hstmt 9 SQL_PARAM_INPUT SQL_C_CHAR SQL_CHAR 20000 0 adtmsg 20000 NULL

           #Insert values into columns

set insert_call [set mrn $ADT_mrn; set fin $ADT_fin; set lname $ADT_lname; set fname $ADT_fname; set dob $ADT_dob; set sex $ADT_sex; set ssn $ADT_ssn; set company $INS_LIST; set adtmsg $ADT_msg; odbc SQLExecute $hstmt]

         

          echo insert_call $insert_call

Topic

[David Barr]

What does the error message say?

[garry r fisher]

Hi Rickey,

At a guess even without seeing the error message I suspect you have spaces or some other character in a field that SQL doesn’t like. You will need to quote fields that contain these characters – For example company and ADT Message (NOTE – These might not be the offending fields – I’m just shownig an example):

set insert_call [set mrn $ADT_mrn; set fin $ADT_fin; set lname $ADT_lname; set fname $ADT_fname; set dob $ADT_dob; set sex $ADT_sex; set ssn $ADT_ssn; set company ‘$INS_LIST’; set adtmsg ‘$ADT_msg’; odbc SQLExecute $hstmt]

It also may be more logical to split your code:

set insert_call [set mrn $ADT_mrn; set fin $ADT_fin; set lname $ADT_lname; set fname $ADT_fname; set dob $ADT_dob; set sex $ADT_sex; set ssn $ADT_ssn; set company $INS_LIST; set adtmsg $ADT_msg]

echo $insert_call

set retCode [odbc SQLExecute $hstmt]

echo $retCode

Regards

Garry

[Ron Archambault]

Your insert syntax should be something like this.

set insert  

[Rick Pritchett]

ok i set the line :

odbc SQLBindParameter $hstmt 9 SQL_PARAM_INPUT SQL_C_CHAR SQL_CHAR 8000 0 adtmsg 8000 NULL

to 8000 and this corrected the issue.  is there any way to allow for more characters if needed?

[Ron Archambault]

Might be best to get the max length from your table by running the system function sp_columns

on your SQL server.

[Author]

Replies