IDS Forum

RE: Logging SQL statements - SQLCMD anybody?

Posted By: Sebastian, Norma J.
Date: Wednesday, 15 September 2004, at 3:55 p.m.

In Response To: Re: Logging SQL statements - SQLCMD anybody? (Jonathan Leffler )

And if you are on IDS 9.4:

New feature....
Ability to monitor queries dynamically using the onmode -Y Command


-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Jonathan Le....
Sent: Wednesday, September 15, 2004 1:25 PM
To: ids@iiug.org
Subject: Re: Logging SQL statements - SQLCMD anybody? [3430]

"LLOYD SERRAO" <lloyd_s@rediffmail.com> wrote on 09/15/2004 03:20:17 AM:
> I have a script which calls another file which contains a list of
> insert statements to be loaded into the database. The file contents
> are
-
>
> dbaccess test /export/home/user1/emp 2> emp.log; dbaccess test
> /export/home/user1/dept 2> dept.log; dbaccess test
> /export/home/user1/leave 2> leave.log; dbaccess test
> /export/home/user1/sal 2> sal.log;
>
> The output is written to respective log files, which tells me 1 row
> inserted for each sql statement. If there is an error in a file and
> if there are 100 records it becomes difficult to track where the error

> occured. Is there a way i can log the actual insert clause into a file

> & if there is an error in that statement i can find out.

One more option which no-one else mentioned (probably because it doesn't
use DB-Access) is to obtain SQLCMD from the IIUG web site.

sqlcmd -d test -cx -f /export/home/user1/emp 2>emp.log

The -d specifies the database; the -c says continue on error (the
default is to abort - which rolls back transactions if they're open, of
course); the -x says trace statements; the -f (not strictly necessary)
says the following name is a file. SQLCMD is disciplined about where it
writes information - error messages and trace output all go to stderr.
One of the reasons I wrote SQLCMD - or, more accurately, its progenitor,
RDSQL - was because ISQL (in the days before DB-Access) was not
disciplined about its output formatting and where it sent its output.
SQLCMD is designed for use in shell scripts -- it does the things I need
it to do. Note that sqlcmd

(You only need the '-f' option explicitly if the filename contains a
space. SQLCMD uses a simple heuristic; if the argument isn't an option
argument and it contains one or more spaces, clearly, it is an SQL
comment; no spaces, clearly it is a file name. Use -e 'expression' and
-f 'filename' to be explicit. The only single word commands in SQL are
'commit' and 'rollback' - the 'work' is optional - and in SQLCMD the
'dbnames' command, which is deprecated in favour of 'info databases'
anyway.)

(Be aware: SQLCMD 72.06 has a buglet in internal.c if you don't have a
C99 aware C compiler, or you run such a compiler in strict C90 mode.
There's a KLUDGE() macro ahead of a variable declaration, and the KLUDGE
macro expands to some executable code, so you get an error about
variable declarations must come before executable code. Either delete
the KLUDGE line, or move it to just after the variable declaration. I'm
hoping to release an upgraded and fixed version of SQLCMD soon, but I've
got one bug in current versions of CSDK that I still have to work around
-- obscure to the point of confusion, but since I'm trying to support
the DB-Access style backslash-space notation meaning a zero-length
non-null VARCHAR or NVARCHAR or LVARCHAR, and the code I've got doesn't
work properly on NVARCHAR, it may be a week or two yet. There's a
conference thingy absorbing quite a lot of effort at the moment.)

Sorry about the self-advertisement...

--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon
Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"





-----------------------------------------
============================================================ The
information contained in this message may be privileged and confidential
and protected from disclosure. If the reader of this message is not the
intended recipient, or an employee or agent responsible for delivering
this message to the intended recipient, you are hereby notified that any
reproduction, dissemination or distribution of this communication is
strictly prohibited. If you have received this communication in error,
please notify us immediately by replying to the message and deleting it
from your computer. Thank you. Tellabs
============================================================

Messages In This Thread

• Re: Logging SQL statements - SQLCMD anybody?
  Jonathan Leffler -- Wednesday, 15 September 2004, at 2:25 p.m.
  • RE: Logging SQL statements - SQLCMD anybody?
    Sebastian, Norma J. -- Wednesday, 15 September 2004, at 3:55 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.