TCL database query error

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).

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

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”.

???

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

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

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?