Hi all,

I am having troule with the SQL insert statement inside of a loop.

In my main Flowchart I have a SQL SELECT Loop as follows:

∙ SQL SELECT
∙ Compare (EOF/Record Count)
∙ Fetch
∙ SQL INSERT ( Call specific Information into a Calls table)
∙ SQL END( for this insert W/the Same Handle as the INSERT)
∙ Counter Var decrement
∙ LOOP Back to Compare

If I take out the INSERT and END, the loop performs as expected. If I put them back in it errors out and I get the follwing message in Simulator:


"Feb 14 12:55:47.30: Function: DisplayError
Feb 14 12:55:47.30: About to call SQLGetDiagRec...
Done
Feb 14 12:55:47.30: Error message:
Feb 14 12:55:47.30: SQL Error State: HY000
Native Error Code: 0
ODBC Error: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
Feb 14 12:55:47.30: About to call SQLGetDiagRec...
Done
Feb 14 12:55:47.30: About to call SQLFreeHandle...
Done
Feb 14 12:55:47.32: Function: DisplayError
Feb 14 12:55:47.32: About to call SQLGetDiagRec...
Done
Feb 14 12:55:47.32: About to delete szFullQry...
Done
Feb 14 12:55:47.32: FAILURE
Feb 14 12:55:47.32: [11] Go to Label
Feb 14 12:55:47.32: Label: 'APP_ERROR'
Feb 14 12:55:47.33: [25] NoOp
Feb 14 12:55:47.33: Label: APP_ERROR
Feb 14 12:55:47.33: [26] Run Flowchart
Feb 14 12:55:47.33: App: 'APP_ERROR'
Feb 14 12:55:47.33: ==>APP_ERROR"

Are Nested SQL scripts not allowed?

thanks for any help.

Ben

You can do nested SQL Statements in TeleFlow. The error you see is returned by SQL Server.

However, one of the more recent releases of SQL Server addressed this issue. I don't remember which version this comes into play(2005, I think), but here is what you must do to take advantage of the fix:

1) You must use the "SQL Native Client" driver for your ODBC data source.

2) In your DB Connect in TeleFlow, you must add "MARS_Connection=yes" to your connect string. So, it might look something like this:
DSN=my_data_source;uid=my_user_id;pwd=my_pass;MARS_Connection=yes

Chris,
I hate to reopen old wounds , but i have gone round and round with this project and now my client is starting to put some pressure on me. Would you be so kind as to explain what I am doing wrong?

You will notice that there are several Flowcharts that have Orphan Labels/GoTo Labels. I had to bypass these to get the project to run as expected. However, i still need the inner SQL statements to run properly. thanks in advance,

Ben

So, the only issue is the nested SQL loops, correct? (sorry, I wasn't sure if you were indicating another problem with the mention of orphaned labels)

In my previous post in this thread, I explain what you need to do to address this issue.

I see in your "DB Connect" step that you haven't used the required "MARS_Connection=yes" as part of your connect string. This also requires the use of SQL Native Client for your ODBC entry. This is the only solution, and is only possible in more recent versions of SQL Server.

You can probably just add the "MARS_Connection=yes" between your password and language lines. Eg.
Pwd=password;
MARS_Connection=yes;
Language=etc....

Sorry. Did that. Doesn't work.

I changed driver from {SQL Server} to {SQL Native Client}. All that changed was the error msg a lillte. It no longer says hstmt. Now reads:

"Connection is busy with results for another command."

which, to me, means the same as the one above. It's not that I ignored your suggestions; it's that I tried them without success.


This script WAS working. Now it only works sporadically. Very confused. I did download a much newer version of designer though. And that seems to be helping somewhat.

Please let me know if you have any more suggestions.

ben

Sorry, I assumed since I didn't see that in there, that the suggestion hadn't been applied.

Note that no matter what, you will need to go back to SQL Native Client and MARS_Connection=yes to get things working. The "standard" SQL Server driver won't work.

On looking at your application again:

You have a number of SQL Handles that are non-numeric. I'm not sure precisely what this will do, but it is likely related to the problem. All SQL Handles should be numeric, and numbered from 1-9. The numbers tie the steps together. (so, SQL Statement with handle 1 is what the SQL Fetch with handle 1 gets a row for) I suspect that by using a non-numeric handle, TeleFlow is defaulting them all to a valid number, they same number at that. This could be why you saw little in the way of a change.

Be careful that you don't, for example, use a new SQL Statement step with the same numbered handle as another that you might still wish to run a SQL Fetch for.

Not related, but something I noticed: I see that you don't have Global Event steps set up to indicate what labels TeleFlow should go in the event of a Fatal error or Hang up. These events will still be handled by TeleFlow, but will not necessarily do what you want or expect them to. (I see you have 2 labels, APP_ERROR and APP_HANGUP, that you obviously intend for this purpose, but your application as written will never use them.)

Correction: I don't suspect that it is defaulting, it IS defaulting. If you set a SQL Statement handle to "TEST", and it contains a SELECT statement that will return rows, then have a SQL Fetch step with a handle of "SOME_OTHER_HANDLE", it will fetch as though the two are the same.

Changing your handles to all be numeric, and being careful about not re-using numbers for SELECT statements you want to Fetch from repeatedly, should solve the problem.

Ok. I got everything working.

Here are the steps i took:

∙ Upgraded my TeleFlow Designer from 2003 to 2007
∙ Moved my DB from SQL 2000 to SQL 2005
∙ Added suggested/required portions of the connection string


So basically I upgraded our TeleFlow Infrastructure and that has made all the difference.

Thanks for all of your help.

Ben

That's great, thanks for letting us know!

Since I gather from your solution description you didn't change the named handles to numeric handles, I just thought I would caution you that this might be a problem down the road. Because string handles are all converted to the same handle number, it is possible that a handle you want to remain open will be closed when you use a different named handle to perform an update.

In any case, I was happy to help.