TCL database query error

[Mike Campbell]

CL 6.0 – AIX.

I have a tclproc called from within an XLATE to connect to our HIM database [Cerner] and return a value.  

I’m connecting okay to the database, but when I execute the query I’m getting:

SQL_SUCCESS HY010 0 [DataDirect][ODBC lib] Function sequence error

The query is:

   set demoQry {SELECT p.person_id FROM person p

        plan p

        WHERE p.name_last_key = ‘$last_name’

        AND p.name_first_key = ‘$first_name’ and p.active_ind = 1}

If I use the Cerner tool the same query works and returns a single row.  Nothing on the WEB seems to provide an answer.  Any ideas??

Topic

[Jim Kosloskey]

Have you turned on OBC Trace to see if there is any clarification?

Have you tried the Progress Software Knowledge base for that error?

I don’t have any more particulars because we would use a Stored Procedure rather than coding the SQL ourselves so we don’t have to contend with the vagaries of SQL via ODBC and various platforms.

Also be aware the SQL usage via ODBC is different (even if just slightly) than directly to a DB. Thus a certain SQL command sequence inside a DB Tool may not behave the same using ODBC (or JDBC for that matter).

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Peter Heggie]

Maybe the problem has to do with the way the ODBC implementation handles the table aliases. Your query uses the same table alias twice, for different tables – i.e. alias p is used for person as well as for plan:

{SELECT p.person_id

 FROM person p plan p

 WHERE p.name_last_key = ‘$last_name’ AND p.name_first_key = ‘$first_name’ and p.active_ind = 1}

Maybe the above is a typo.

if column ‘active_ind’ belongs to table ‘plan’ then you could use this:

{SELECT p.person_id

 FROM person p plan n

 WHERE p.name_last_key = ‘$last_name’ AND p.name_first_key = ‘$first_name’ and n.active_ind = 1}

Pete

Peter Heggie
PeterHeggie@crouse.org

[Terry Kellum]

I notice in your query above that you don’t have a space between Person P and Plan P.  After line ending parsing it will look like “person pplan p”.

???

[Peter Heggie]

Also, what column in table ‘person’ relates to what column in table ‘plan’?

Peter Heggie
PeterHeggie@crouse.org

[Mike Campbell]

Removed the plan and tried it again. Same results.

Added an echo of the actual query set-up:

[tcl :out :INFO/0:

[Peter Heggie]

Do you have other queries, using the same technical approach, that work?

Peter Heggie
PeterHeggie@crouse.org

[Mike Campbell]

Yes I have several that use the same overall process, and work just fine.

I’m using the same open, connect, fetch, etc. commands with just a difference in the actual query and table.

   set mrnQry {SELECT person_alias_id FROM PERSON_ALIAS WHERE ALIAS_POOL_CD = 33451

                     AND ACTIVE_IND = 1 AND ALIAS = ‘$mrn’}

[Peter Heggie]

I found two main causes of this error: 1) the variables bound to the SQL Parameters may not have been set correctly, 2) the statement handle was not used correctly when the SQLExec was called.

1) variables – for example, if last_name, first_name or dob were not set before the call. This does not seem likely for your example because you are not using an SQLPrepare or SQLBindParameter.

2) possibly the statement handle is being used by another process. Perhaps all of the data available had not been fetched before invoking the command again? Are you looping through a result set? Or do you have another odbc thread in the same process using the same credentials? Or something is happening to the handle variable?

Peter Heggie
PeterHeggie@crouse.org

[Mike Campbell]

Interesting theory.  I’ll check the handle situation but this should be the only query being executed at the time.

[Author]

Replies