ODBC problem

[Jason Melton]

I have spent all day banging my head on this on.

Attached is a tcl proc which deletes an entry (well should do) from a table held in Oracle.  

The output from it when it runs is

query delete from account_expires where user_id =’xxxx’

SQlConnect SQL_SUCCESS

SQLALLOCHANDLE : SQL_SUCCESS

SQLExecDirect: SQL_ERROR

SQL_SUCCESS

Previous debug that I had added had the SQLExecDirect error as being NO_DATA_FOUND

Connecting to the database using sqlplus on the same box with the same ID/password I can run the command and the row deletes fine.

Regards,

Jason

Topic

[Robert Milfajt]

I wonder if you need quotes around the deleteQuerySql argument…  

Old

Code:

     set err [odbc SQLExecDirect $hstmt $deleteQuerySql SQL_NTS]

New

Code:

     set err [odbc SQLExecDirect $hstmt “$deleteQuerySql” SQL_NTS]

Hope this works,

Robert Milfajt
Northwestern Medicine
Chicago, IL

[Jason Melton]

finally tracked it down to a typo in the odbc ini file.

Jason

[Jim Kosloskey]

Jason,

I have just started doing ODBC with Cloverleaf(R) and I have decided not to use the odbc.ini file for every DB.

Instead I use a Connection String wherein I reference a DB Type’s (let’s say Oracle) provided dsn in the odbc.ini file.

Then I enter in the connection string those entries that differ from the distributed dsn.

This gives me a stable odbc.ini file and the flexibility of connect time definition of the connection,  etc. characteristics.

I use a lookup table to contain the connection string entries as well as other arguments I need for my proc but the arguments could just as wellbe in the NetConfig.

Using the connection string method does not preclude the use of the odbc.ini file so if it is necessary to have specific definitions in the odbc.ini that can be done. This seems to give me the most flexibility.

My goal is to have a reusable ODBC proc that is argument driven.

To that end we are attempting to only deal with Stored Procedures. That way the ‘business rules’ are separated from Cloverleaf(R) and exist with the receiving system and the Cloverleaf(R) proc can be ignorant of the specifics of the business or data management functions.

Also, ideally the Stored Procedure with which we will interact actually inserts in a transaction table. Later another mechanism of the receiving system’s choice picks up the transaction and processes it.

This way Cloverleaf(R) delivers a message and upon successful completion of a very simplistic Stored Procedure, can move on to the next message.

The receiving system then has a natural ‘queue and audit trail’ of received messages. They can archive, analyze and otherwise manage those received messages as they see fit. Moreover, an opportunity arises to completely trace and debug the message flow.

Anyway, that is the plan. Many of the above pieces are in place or are being actively tested.

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

[Sergey Sevastyanov]

Jason

I know it’s not what you were asking about but I noticed that the default branch of switch in your deleteUser proc:

…

default {

     error “Unknown mode ‘$mode’ in $module”

  }

  run {

…

…

will never work. In order to work it must be the last branch of the switch.

[Sergey Sevastyanov]

Jim,

I wonder if you figured out how to get return parameters from a stored procedure? I was trying to save HL7 messages in MS SQL and I used TclODBC (because it’s free and we don’t have license for odbc). It worked fine but I had to switch to a different approach for two reasons:

1. I didn’t want to program business logic in cloverleaf

2. I didn’t figure out how to get back parameters from a stored procedure

So currently I send messages via Tcp to a Windows machine that runs a VB.NET program. That program actually calls stored procedures and gets results from them.

I don’t really like VB for that particular task and would rather switch back to using TclODBC from Cloverleaf if I could figure out how to get returned values.

Thanks

[Jim Kosloskey]

Sergey,

I am no expert, but my research thus far has led me to the conclusion, SQL Server returns a recordset from a Stored Procedure even if the Stored Procedure does not intentionally produce a recordset AND (according to what I have read via Microsoft) you need to exhaust the recordset before the OUTPU parameters vis the Stored Procedure can be referenced.

I have not yet gotten to the point where I can get access to an OUTPUT parameter from an SQL Server Stored Procedure.

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

[Sergey Sevastyanov]

Jim,

That’s interesting. I don’t have problems accessing OUTPUT parameters in VB, I just access a variable and get me value (but maybe there is stuff going on in code generated by VB)

Thanks

[Jim Kosloskey]

Sergey,

In you VB call do you use an SQL call to the Stored Procedure or do you use something like ADO?

That might have something to do with it.

But as I said, I have not been successful thus far getting the OUTPUT parameters from an SQL Server Stored Procedure using the Cloverleaf(R) ODBC Drivers.

I think it can be done, I just have not learnded the secret as of yet.

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

[Sergey Sevastyanov]

Jim,

I don’t use ADO. I define an existing stored procedure in a dataset. From what I can see in the code VB generates it’s a function that creates an SqlCommand object, passes parameters, then executes nonquery and retrieves parameters defined as OUTPUT in stored procedure.

Of course I don’t know what’s happening on dll level.

[Author]

Replies