Insert command to a SQL database (Microsoft SQL) using TCL

[Tom Gilbert]

I am trying to do an insert to a SQL table from TCL.  I have done a fetch and search.

Please Help!!!…🙂

Topic

[Tom Gilbert]

I am getting connected ok and I can do a fetch with no problem.

I am trying to insert data to the SQL table trying to use the following:

set err [odbc SQLExecute $hstmt “INSERT INTO $table (id,prm_msgid,pcis_orderno,insertdatetime) values(,$msh_10,,)” SQL_NTS]

Please help Need assistance on why it is not working… ❓

[Steve Pringle]

here’s a not for production example:

       odbc SQLAllocEnv henv

       odbc SQLSetEnvAttr $henv SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3 0

       odbc SQLAllocConnect $henv hdbc

   

       set connectResponse [odbc SQLConnect $hdbc $dsn SQL_NTS $uid

           SQL_NTS $pwd SQL_NTS]

   

       if { [cequal $connectResponse “SQL_ERROR”] } {

           echo ERROR: Unable to connect to !rn

           error “ERROR: Unable to connect to !”

       }

   

       if { $ssn == “”} {

           msgset $mh $msg

           # need to insert number PID.4 into lwPendingAccts table

           # from tpsCheckAcctOtherSys

           odbc SQLAllocStmt $hdbc hstmt5

   

           set insert “insert into lwPendingAccts (Dnumber, MsgSource)

               VALUES (‘$IdNum’,’$MsgSource’)”

   

           set rval [odbc SQLExecDirect $hstmt5 $insert SQL_NTS]

           if {$rval == “SQL_ERROR” } {

               echo “insert = $insert”

               echo “error executing update table”

               catch {odbc SQLFreeHandle SQL_HANDLE_STMT $hstmt5}

               catch {odbc SQLDisconnect $hdbc                  }

               catch {odbc SQLFreeConnect $hdbc                 }

               catch {odbc SQLFreeEnv $henv                     }

               return “{ERROR $mh}”

           }

   

           catch {odbc SQLFreeHandle SQL_HANDLE_STMT $hstmt5}

           catch {odbc SQLDisconnect $hdbc                  }

           catch {odbc SQLFreeConnect $hdbc                 }

           catch {odbc SQLFreeEnv $henv                     }

           return “{CONTINUE $mh}”

       }

[Tom Gilbert]

I am getting the following error:

MESSAGE 1

Tcl callout error

erroCode: NONE

errorInfo:

wrong # args: odbc SQLExecute hstmt

      while executiong

“odbc SQLExecute $hstmt “INSERT INTO $table (id,prm_msgid,pcis_orderno,insertdatetime) values(,$msh_10,,)” SQL_NTS”

      (procedure “vmctps_storemsgid_prm_kddh_orm” line 83)

      invoked from within

“vmctps_storemsgid_prm_kddh_orm

[Tom Gilbert]

Hello Steve,  Thank you…  I don’t get errors any more…  The problem is that the data is not inserting into the table.  I put some echo’s in there and can see a the echo’s before and after the ‘set insert….’ but did not actually insert…

Any thoughts…

[Steve Pringle]

Your values list has fewer arguments than your insert columns.  They need to match.

“odbc SQLExecute $hstmt “INSERT INTO $table (id,prm_msgid,pcis_orderno,insertdatetime) values(,$msh_10,,)”

Try something like this:

“odbc SQLExecute $hstmt “INSERT INTO $table (id,prm_msgid,pcis_orderno,insertdatetime) values($id,$msh_10,$ordno,$datetime)”

[Tom Gilbert]

Now I am getting an SQL Error when executing the odbc SQLExescDirect:

set insert “INSERT INTO PRM_PCIS_OrderNo_xRef (prm_msgid,pcis_orderno) VALUES(”,’$msh_10′,”,”)”

set err [odbc SQLExecDirect $hstmt $insert SQL_NTS]

echo err $err

$erro is equaling ‘SQL_ERROR’

[Mike Willeson]

Tom,

I think Steve is pointing you in the right direction.  I have never done this with TCL before, but thinking about it from a database perspective, in your original values you were leaving the insert value for the prm_msgid value blank because you have an auto-incrementing primary key field there.

Now that you have added a value of ” to your insert statement, you are technically trying to insert a string in a numeric field.

It seems like you need to pre-determine an ID value you can use in your insert.  I am from the oracle world, and we had sequences we could use.  In SQL Server it appears there may be an “IDENTITY” you can use, but I don’t know how it works.

In psuedo code:

newID = select from IDENTITY to get numeric value

set insert “INSERT INTO PRM_PCIS_OrderNo_xRef (prm_msgid,pcis_orderno) VALUES(newID,’$msh_10′,”,”)”

Try a test and do like Steve suggested, have a variable for every insert parameter and make sure the variable is set to a real value of the proper data type.

[Jim Kosloskey]

Tom,

Try executing a sqlGetDiagRec command after the error to get clarification of what caused the SQL-ERROR to be thrown.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Tom Gilbert]

set insert “INSERT INTO PRM_PCIS_OrderNo_xRef (id,prm_msgid,pcis_orderno,insertdatetime) VALUES(newID,’$msh_10′,”,”)”

set err [odbc SQLExecDirect $hstmt $insert SQL_NTS]

What would be the best way to configure SQLGetDiagRec.  I keep getting wrong args… 😕

Thank you

[David Barr]

Code:

odbc SQLGetDiagRec SQL_HANDLE_STMT $hstmt 1 SqlState NativeError MessageText 511 TextLength
echo ” ”

[Charlie Bursell]

Doing ODBC is a lot like doing FTP.  If it doesn’t work from the client it won’t work in ODBC.  Don’t you have a client that you can test this on?

Is the id column numeric?  If so newID will not work.  If not numeric the it needs to be ‘newID’

A hint that will save you *MANY* hours.

If using a variable that could possible contain an apostrephe (‘), e.g. O’Conner, the apostrephes must be doubled or it will give you fits.  I always do something like:

set var [string map {‘ ”} $var]

Just in case

[Tom Gilbert]

Thank you so much for helping…

I finally got the error: 2400 Invaled Cursor State

I used the following command to get the error:

set err2 [odbc SQLGetDiagRec SQL_HANDLE_STMT $hstmt 1 SqlState NativeError MessageText 511 TextLength]

echo err2 $err2

echo sqlstate $SqlState

echo nativeerror $NativeError

echo messagetext $MessageText

The SqlState gave me the error code…

Then I found the information and this is what I did to insert data:

set insert “INSERT INTO PRM_PCIS_OrderNo_xRef (prm_msgid,pcis_orderno) VALUES(‘$msh_10’,”)”

set err1 [odbc SQLCloseCursor $hstmt]

set err [odbc SQLExecDirect $hstmt $insert SQL_NTS]

echo err $err

echo err1 $err1

When I added the CloseCursor it added the data to the sql table correcly…

Hope this helps someone… 🙂

[Author]

Replies