Re: Fujitsu cobol and SQLServer



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


.

Relevant Pages

  • 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: Cursors
    ... Assuming you refer to WHILE loops in SQL Server vs. a cursor in SQL Server, i.e., all back-end programming: ... As for ANSI SQL compliance, SQL Server added to the DECLARE CURSOR support for the ANSI SQL 92 standard in ... So, yes, cursors are in ANSI SQL. ...
    (microsoft.public.sqlserver.server)
  • Re: Fujitsu cobol and SQLServer
    ... "Steve Rainbird" wrote in ... 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: JDBC and ResultSet problem
    ... > FROM CUSTOMERS ORDER BY CUSTOMER_REVENUE DESC ... cursors, where all the rows are cached _in memory_ by the driver. ... behavior you need (see the current implementation of jTDS). ... One more thing you could take into consideration is that SQL Server ...
    (comp.lang.java.databases)
  • Re: Cursor for loops
    ... Their is a big difference between the way that Oracle and SQL Server ... This is the root reason that Oracle doesn't discourage ... cursors at all and that they are generally discouraged on SQL Server. ...
    (microsoft.public.sqlserver.programming)