Re: Fujitsu cobol and SQLServer



"Pete Dashwood" <dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:6lfcggFbvb1fU1@xxxxxxxxxxxxxxxxxxxxx


"Steve Rainbird" <steve.nospam.rainbird@xxxxxxxxxxxxxxxxx> wrote in message news:6leijjFc1p9sU1@xxxxxxxxxxxxxxxxxxxxx


"Pete Dashwood" <dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:6ledroFbu0u7U1@xxxxxxxxxxxxxxxxxxxxx


"Steve Rainbird" <steve.nospam.rainbird@xxxxxxxxxxxxxxxxx> wrote in message news:6le8dmFbtr82U1@xxxxxxxxxxxxxxxxxxxxx
I have a Fujitsu Cobol program connected to an sql server database via odbc.

I have one cursor already open, when I try and open another cursor I get the
following error.

[Microsoft][SQL Native Client]Connection is busy with results for another
command

I think from googling that I need to tell the odbc driver to use server side cursors but have
no idea about how to do this.

Any help gratefully received

Regards
--
Steve
Server side cursors with SQL Server are not a good idea and are best avoided. As far as I know, there is no way in COBOL you can specify them anyway.

Set up two connections to the database and switch between them. (See also: "The solution" under "RDB limitation" thread in this newsgroup.)

EXEC SQL
CONNECT 'DBServer' AS 'conn1' *> assumed userid and password are in your ODBC.inf file
END-EXEC
EXEC SQL
CONNECT 'DBServer' AS 'conn2' *> assumed userid and password are in your ODBC.inf file
END-EXEC

Fujitsu assumes last connection until/unless you use SET CONNECTION.

Declare and open each of your 2 cursors under a separate connection.

You cannot set more than one connection to DEFAULT.

Pete.
--
"I used to write COBOL...now I can do anything."



Pete,

With microfocus cobol I never had this problem (true it is 4 or 5 yesrs since I used Mf and SQL server I tend to use Oracle).

Steve, I don't have enough information to know exactly WHY you're getting this problem, but there are some known problems with SQL Server that can cause it to emit this message. Most of these date back to the time when it only supported one thread per activity.You should ensure that you have all updates applied so you can be sure you are using the latest ODBC drivers and an updated current version of SQL Server.

You could try compiling with MicroFocus (if you still have it) and see if the result is the same, but it's a fair amount of work for very little gain. It would establish whether the problem is with the low level API calls produced by Fujitsu or whether it is with the actual SQL server you are using, but if it turns out to be the Fujitsu code, there's very little you can do.

Given that I don't have enough to properly diagnose this problem, I suggested a course of action that will get around it. (I call this "scrubbing round the problem" because it isn't a true "cleanup"). I find myself having to do a fair bit of this with Fujitsu COBOL, but I am using version 6 so this may have been fixed in 7 and 8.

Out of interest why are server side cursors not a good idea?

Mainly for the reason that Robert posted, but also because using them has a deleterious effect on overall throughput through the server; they consume server resources and that slows everything down.

I agree pretty much with what Robert said about using cursors and have arrived at this conclusion independently, through my own experience. However, if you are dealing with legacy COBOL, cursors are the accepted way of doing things for sequential processing, so I understand you may have no option to fetch into a table or multi-row

While the suggested solution may seem "clumsy" to you at first glance, in practice, it works very well. The switch between connections is based on in-memory addresses and is very fast; not like you had to re-establish a DB connection every time you switch. The solution I posted in the "RDB limitations" thread is getting between 90 and 100 rows per second and is switching in and out of the two connections constantly. It is a robust solution as far as I can tell and I've tried many variations on it without it crashing or failing.

The other thing about this solution is that even if the database you are using (say you move to MySQL or something other than SQL Server) supports only single threading, this solution will still work. However, it could never be as efficient as NOT using a cursor at all.

Processing result sets is infinitely preferable because the DB connection time is a fraction of what it has to be to support cursors. (Once you have the result set, you can disconnect) This means the connection pool is freer and overall throughput is improved. Not an important consideration if it is just a few machines on a LAN using the server, but it becomes significant as the number of connected machines increases.

Hope this helps,

Pete.
--
"I used to write COBOL...now I can do anything."



Thanks Pete for your time.

--
Steve

.

Relevant Pages

  • Re: Fujitsu cobol and SQLServer
    ... I think from googling that I need to tell the odbc driver to use server ... Server side cursors with SQL Server are not a good idea and are best ... Fujitsu assumes last connection until/unless you use SET CONNECTION. ...
    (comp.lang.cobol)
  • Re: SQL Server connectivity
    ... I feel like it must be something with the location of the cursors. ... table connection is probably using CLIENT side cursors. ... required by ODBC framework). ... > The problem is that the SQL server was moved to another office and a VPN ...
    (microsoft.public.data.ado)
  • Re: Fujitsu cobol and SQLServer
    ... Server side cursors with SQL Server are not a good idea and are best avoided. ... Fujitsu assumes last connection until/unless you use SET CONNECTION. ... With microfocus cobol I never had this problem (true it is 4 or 5 yesrs since I used Mf and SQL server I tend to use Oracle). ...
    (comp.lang.cobol)
  • Re: Error "SQL Server does not allow remote connections"
    ... The application could not connect to the sql server db. ... network is blocking the connection. ... SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 ... integratedSecurity, SqlConnection owningObject) +737554 ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Connection from remote computer to network SQL Server
    ... There is no firewall on the W2K machine acting as the SQL server. ... I tried making the SQL machine a "trusted" on the router. ... connection works. ... To find the IP address of your computer inside the network, ...
    (microsoft.public.access.adp.sqlserver)