Fujitsu Cobol and MS SQL Server

[Zalek Bloom]

I am using Fujitsu Cobol with MS SQL Server. In order to improve
performance our DBA suggested to insert the following statement to our
SQL statements:
set nocount on

My question: how to embad this statemnt in SQL?

When I tried

EXEC SQL
set nocount on
END-EXEC.

I got a compile error saying: The SQL statemnt is invalid. Odbc-7600e
NOT EXECUTABLE STATEMENT

Any idea how to do it?

Thanks,

Zalek

[Richard]

Fujitsu will only process the SQL statement defined in their manual,
it won't process random (but valid) SQL statements that are not
described in the manual.

While it may not be an invalid statement in SQL, Fujitsu does not know
how to action it.

[Robert Wessel]

Presumably the local equivalent of dynamic SQL should be able to issue
any valid SQL statement, whether or not the preprocessor supports it.

[Pete Dashwood]

I'm too busy at the moment to actually try this, but Fujitsu does support
dynamic SQL very well (better than Micro Focus).

It should work with a string presented to EXECUTE IMMEDIATE, without
actually needing PREPARE. If it doesn't, then it would certainly work as a
stored procedure. Maybe the DBA should set up a suitable SP and let COBOL
call it.

I'd try something along these lines:

define a Host Variable as a string...

... 01 SQLSTMT pic x(256).

...
move "SET COUNT OFF" to SQLSTMT
EXEC SQL
EXECUTE IMMEDIATE :SQLSTMT
END-EXEC

Pretty sure that will do it.

Pete.

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

[Alistair Maclean]

nd I can not find any mention of the command in either of two thick
manuals on SQL.So.... can anyone actually confirm that this is a SQL
statement or is it a compiler directive or what?

[Pete Dashwood]

"SET NOCOUNT ON" is a T-SQL statement provided for MS SQL Server.

It is part of SQL Server's programming language, Transact-SQL.

This language is an extension by Microsoft of the original SQL developed by
IBM.

There are sites where they use T-SQL for all development (I managed one in
the Midlands of England) and it is a fully fledged programming language that
is complete in itself just like COBOL or VB.

There are some very powerful commands available.

Check "Transaction SQL" on Google.

When I first took over the site I mentioned, I was a bit shocked to find
that the "programmers" were SQL guys. But I soon found out that they were
very competent and had some amazing tools. I became persuaded when I saw how
quickly they produced stuff. They could do in 10 minutes, using graphic drag
and drop style tools, what would take three days to do in COBOL...

Although most ODBC drivers will not accept direct T-SQL commands it is
pretty easy to execute them as dynamic SQL, as I showed elsewhere in this
thread. (Always provided you are connected to MSSQL, of course... )

Probably worth mentioning that many systems connect directly to various
databases without using ODBC, by means of ADO, OLEDB, or named pipe
connections, so the limitations of ODBC do not apply to them. Nevertheless,
ODBC is definitely the most flexible approach, even if not the most
efficient. It is hard enough supporting Access, SQL Server, SQLite, MySQL,
PostgreSQL, Oracle and DB2 with the different syntax and idiosynchronisms
of each of these platforms, without having to build a separate product for
each of them. ODBC means you don't have to, but it is still non-trivial to
get absolutely equivalent functionality across all platforms.

Fujitsu COBOL requires ODBC for embedded SQL (ESQL) and you cannot use
resultsets from OLEDB or similar. (A SQL cursor is the way to do it in COBOL
but it is the least favoured way outside COBOL; resultsets are many times
more efficient.) Fujitsu PowerCOBOL offers an ADO control object or you can
instantiate ADO as an Automation server and use it via COM. It is better
than ESQL but not as good as LINQ. I've never tried experimenting with OLEDB
from COBOL but in theory it should be possible to use it just like ADO if
you instantiate an instance of it and manipulate its methods with OO COBOL.

The embedded procedural SQL which a large percentage of the world's COBOL
programs implement is, frankly, obsolete.

C# can use LINQ and Lambda expressions which are just a quantum leap above
ESQL. (I understand this will be made available to VB.Net in the near
future.)

The SQL which runs on modern platforms is a very long way away from the 8
basic commands which ran in the early 1980s...

(SELECT, INSERT, UPDATE, DELETE, DECLARE, OPEN, FETCH, CLOSE)

However, there are many COBOL programmers who believe that 8 commands are
all there is and definitely all you need... :-)

[Alistair Maclean]

On Dec 16, 12:37 pm, "Pete Dashwood"

<dashw...@removethis.enternet.co.nz> wrote:
>
> The SQL which runs on modern platforms is a very long way away from the 8
> basic commands which ran in the early 1980s...
>
> (SELECT, INSERT, UPDATE, DELETE, DECLARE, OPEN, FETCH, CLOSE)
>
> However, there are many COBOL programmers who believe that 8 commands are
> all there is and definitely all you need... :-)
>
> Pete.

Real programmers know that you also need DROP and CREATE.

Thanks to Pete and Pavel for their responses. I am a bit miffed that
neither of the teo thick books I used as references mentioned SET
NOCOUNT ON so that is a good fifty quid wasted.

Thanks guys.

[Pete Dashwood]

Alistair Maclean wrote:
> On Dec 16, 12:37 pm, "Pete Dashwood"
> <dashw...@removethis.enternet.co.nz> wrote:
>>
>> The SQL which runs on modern platforms is a very long way away from
>> the 8 basic commands which ran in the early 1980s...
>>
>> (SELECT, INSERT, UPDATE, DELETE, DECLARE, OPEN, FETCH, CLOSE)
>>
>> However, there are many COBOL programmers who believe that 8
>> commands are all there is and definitely all you need... :-)
>>
>> Pete.
>
> Real programmers know that you also need DROP and CREATE.

As you may be aware the blanket of "SQL" really falls onto two beds:

1. Data Definition Language (DDL) which also includes "Administrative"
statements such as GRANT and REVOKE as well as CREATE, DROP, and ALTER.

(Passing note: When you create tables with the PRIMA Toolset you can see the
generated DDL by pulling down the DDL directory from the Files, menu. The
Toolset generates the DDL then runs it automatically.)

2. Data Manipulation Language (DML) which includes the 8 statements noted
above, as well as a few others which may be vendor dependent.

Because most implementations of ODBC only allow DML statements, this subset
has become known loosely as "SQL". Probably it should be called ESQL because
Embedded SQL is almost invariably DML only.

>
> Thanks to Pete and Pavel for their responses. I am a bit miffed that
> neither of the teo thick books I used as references mentioned SET
> NOCOUNT ON so that is a good fifty quid wasted.

Glad to help.

You may be being a little unfair, Alistair. It is a SQL Server only
statement. If either of your books was SQL Server specific, I'm pretty sure
you'd find it.
>
> Thanks guys.