Re: Logging SQL statements - SQLCMD anybody?

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

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 ============================================================