Bonehead dbaccess question
User on Solaris 8 / IDS 10.00.UC8 generated ALTER TABLE ... DROP CONSTRAINT statements from sysconstraints using dbaccess's OUTPUT TO, but lines wrapped at about 64 characters, breaking the generated SQL. An IBM developer confirmed this is a known page-width limitation of dbaccess/ISQL. The recommended workaround was to use UNLOAD TO instead of OUTPUT, optionally with DELIMITER set (e.g. a space, or ';' via DBDELIMITER so each line ends with a semicolon). Marco Greco also suggested his SQSL tool as an alternative.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Solaris 8
Informix 10.00.UC8
I am running the following sql statement:
output to '/tmp/drop_fk.out' without headings
select "alter table " || tabname || " " || "drop constraint " ||
trim(constrname) || ";"
from sysconstraints A, systables B
where A.tabid = B.tabid
and A.constrtype = "R"
and B.tabname = "cnty_cov_lv_tp_pri"
The query runs fine and all - but my output file seems to be limited to 64
characters - which causes odd breaks - like:
alter table cnty_cov_lv_tp_pri drop constraint fk_cnty_cov_lv_tp_p
ri_1;
Which causes an annoying syntax error... before setting off on an awk/sed
mission - is there a way around this? I've tried to use the concat' to create
a break - i.e.
alter table cnty_cov_lv_tp_pridrop constraint fk_cnty_cov_lv_tp_pri_1;
But I cannot get the syntax to do it...
Help appreciated -
MM
Hi Mike,
This is something in our radar as we got this reported quite recently.
This is a limitation in dbaccess (and ISQL) for now!
Thanks !
Regards,
Srini
"MIKE MAGIE"
<jmmagie@yahoo.co
m> To
Sent by: ids@iiug.org
ids-bounces@iiug. cc
org
Subject
Bonehead dbaccess question [15253]
19/03/2009 23:21
Please respond to
ids@iiug.org
Solaris 8
Informix 10.00.UC8
I am running the following sql statement:
output to '/tmp/drop_fk.out' without headings
select "alter table " || tabname || " " || "drop constraint " ||
trim(constrname) || ";"
from sysconstraints A, systables B
where A.tabid = B.tabid
and A.constrtype = "R"
and B.tabname = "cnty_cov_lv_tp_pri"
The query runs fine and all - but my output file seems to be limited to 64
characters - which causes odd breaks - like:
alter table cnty_cov_lv_tp_pri drop constraint fk_cnty_cov_lv_tp_p
ri_1;
Which causes an annoying syntax error... before setting off on an awk/sed
mission - is there a way around this? I've tried to use the concat' to
create
a break - i.e.
alter table cnty_cov_lv_tp_pridrop constraint fk_cnty_cov_lv_tp_pri_1;
But I cannot get the syntax to do it...
Help appreciated -
MM
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
One thing I do is use the unload command. First I set the environment variable
DBDELIMITER=";" and export it before going into dbaccess. Then in your case I
would do :
unload to '/tmp/drop_fk.out'select "alter table " || tabname || " " || "drop constraint " ||
trim(constrname)
from sysconstraints A, systables B
where A.tabid = B.tabid
and A.constrtype = "R"
and B.tabname = "cnty_cov_lv_tp_pri"
This will create your file and put a ";" at the end of each line.
Anthony Judish
Software Design Architect
Lextron Inc.
ph 970.378.2056
fax 970.346.2356
ajudish@lextron-inc.com
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MIKE
MAGIE
Sent: Thursday, March 19, 2009 11:52 AM
To: ids@iiug.org
Subject: Bonehead dbaccess question [15253]
Solaris 8
Informix 10.00.UC8
I am running the following sql statement:
output to '/tmp/drop_fk.out' without headings
select "alter table " || tabname || " " || "drop constraint " ||
trim(constrname) || ";"
from sysconstraints A, systables B
where A.tabid = B.tabid
and A.constrtype = "R"
and B.tabname = "cnty_cov_lv_tp_pri"
The query runs fine and all - but my output file seems to be limited to 64
characters - which causes odd breaks - like:
alter table cnty_cov_lv_tp_pri drop constraint fk_cnty_cov_lv_tp_p
ri_1;
Which causes an annoying syntax error... before setting off on an awk/sed
mission - is there a way around this? I've tried to use the concat' to create
a break - i.e.
alter table cnty_cov_lv_tp_pridrop constraint fk_cnty_cov_lv_tp_pri_1;
But I cannot get the syntax to do it...
Help appreciated -
MM
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Yes, OUTPUT is limited in its page width. Instead use UNLOAD:
UNLOAD to '/tmp/drop_fk.out' DELIMITER ' 'select "alter table " || tabname || " " || "drop constraint " ||
trim(constrname) || ";"
from sysconstraints A, systables B
where A.tabid = B.tabid
and A.constrtype = "R"
and B.tabname = "cnty_cov_lv_tp_pri";
Art
On Thu, Mar 19, 2009 at 1:51 PM, MIKE MAGIE <jmmagie@yahoo.com> wrote:
> Solaris 8
> Informix 10.00.UC8
>
> I am running the following sql statement:
>
> output to '/tmp/drop_fk.out' without headings
> select "alter table " || tabname || " " || "drop constraint " ||
> trim(constrname) || ";"
> from sysconstraints A, systables B
> where A.tabid = B.tabid
> and A.constrtype = "R"
> and B.tabname = "cnty_cov_lv_tp_pri"
>
> The query runs fine and all - but my output file seems to be limited to 64
> characters - which causes odd breaks - like:
>
> alter table cnty_cov_lv_tp_pri drop constraint fk_cnty_cov_lv_tp_p>
> ri_1;
>
> Which causes an annoying syntax error... before setting off on an awk/sed
> mission - is there a way around this? I've tried to use the concat' to
> create
> a break - i.e.
>
> alter table cnty_cov_lv_tp_pri> drop constraint fk_cnty_cov_lv_tp_pri_1;
>
> But I cannot get the syntax to do it...
>
> Help appreciated -
>
> MM
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
--0016364eef7014557304657e7b24
MIKE MAGIE wrote:
> Solaris 8
> Informix 10.00.UC8
>
> I am running the following sql statement:
>
> output to '/tmp/drop_fk.out' without headings
> select "alter table " || tabname || " " || "drop constraint " ||
> trim(constrname) || ";"
> from sysconstraints A, systables B
> where A.tabid = B.tabid
> and A.constrtype = "R"
> and B.tabname = "cnty_cov_lv_tp_pri"
>
> The query runs fine and all - but my output file seems to be limited to 64
> characters - which causes odd breaks - like:
>
> alter table cnty_cov_lv_tp_pri drop constraint fk_cnty_cov_lv_tp_p>
> ri_1;
>
> Which causes an annoying syntax error... before setting off on an awk/sed
> mission - is there a way around this? I've tried to use the concat' to create
> a break - i.e.
>
> alter table cnty_cov_lv_tp_pri> drop constraint fk_cnty_cov_lv_tp_pri_1;
>
> But I cannot get the syntax to do it...
>
> Help appreciated -
>
> MM
>
SQSL will do that nicely for you...
--
Ciao,
Marco
______________________________________________________________________________
Marco Greco /UK /IBM Standard disclaimers apply!
Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm
I use this type of statement often, except, I use a delimiter of ';'. E.G
Taking arts example below
UNLOAD to '/tmp/drop_fk.out' DELIMITER ';'select "alter table " || tabname || " " || "drop constraint " ||
trim(constrname)
from sysconstraints A, systables B
where A.tabid = B.tabid
and A.constrtype = "R"
and B.tabname = "cnty_cov_lv_tp_pri";
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
Kagel
Sent: 19 March 2009 10:17 PM
To: ids@iiug.org
Subject: Re: Bonehead dbaccess question [15257]
Yes, OUTPUT is limited in its page width. Instead use UNLOAD:
UNLOAD to '/tmp/drop_fk.out' DELIMITER ' 'select "alter table " || tabname || " " || "drop constraint " ||
trim(constrname) || ";"
from sysconstraints A, systables B
where A.tabid = B.tabid
and A.constrtype = "R"
and B.tabname = "cnty_cov_lv_tp_pri";
Art
On Thu, Mar 19, 2009 at 1:51 PM, MIKE MAGIE <jmmagie@yahoo.com> wrote:
> Solaris 8
> Informix 10.00.UC8
>
> I am running the following sql statement:
>
> output to '/tmp/drop_fk.out' without headings select "alter table " ||
> tabname || " " || "drop constraint " ||
> trim(constrname) || ";"
> from sysconstraints A, systables B
> where A.tabid = B.tabid
> and A.constrtype = "R"
> and B.tabname = "cnty_cov_lv_tp_pri"
>
> The query runs fine and all - but my output file seems to be limited
> to 64 characters - which causes odd breaks - like:
>
> alter table cnty_cov_lv_tp_pri drop constraint fk_cnty_cov_lv_tp_p>
> ri_1;
>
> Which causes an annoying syntax error... before setting off on an
> awk/sed mission - is there a way around this? I've tried to use the
> concat' to create a break - i.e.
>
> alter table cnty_cov_lv_tp_pri> drop constraint fk_cnty_cov_lv_tp_pri_1;
>
> But I cannot get the syntax to do it...
>
> Help appreciated -
>
> MM
>
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do those
opinions reflect those of other individuals affiliated with any entity with
which I am affiliated nor those of the entities themselves.
--0016364eef7014557304657e7b24
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
==================
Please read our Email Disclaimer :
http://www.thefuelgroup.com/disclaimer.html