RE: using ? in "in" Take Two

Sorry, about the previous email. When you say system wide name, I think you mean database wide. That would be true. How about you create a sequence, Use this sequence number in the external table name. create the external table Take action upon the data drop the external table If you are worried about stray external tables, just create a database scheduler task that will cleanup up all external tables which are older than 1 day. The creation time is already stored with the creation of the external table. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 10/11/2010 04:44:14 PM: > [image removed] > > RE: RE: using ? in "in" [21629] > > Wyza, Jonathon > > to: > > ids > > 10/11/2010 04:44 PM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > That might work, except it appears that external tables are not capable of > being temporary and must have unique system wide names. Since this > process has > the capability of being run more than once at a time then that could present > problems. Beyond that I'd have to make sure it was dropped after the process > finished. Obviously i could use a begin_work statement (and thus if the > program exited then it would rollback), but then it would log all the work, > which i really don't need it to do. > > Jonathon Wyza > CX & CBORD System Administrator > CX Programmer/Analyst > Administrative Computing > Bethel College > (574)-257-3381 > AIM: Iamwyza > jonathon.wyza@bethelcollege.edu > ============================== > SLES 11x64 & IDS 11.50.FC6 > > “Don’t document the problem, fix it.” > – Atli Björgvin Oddsson > > ________________________________________ > From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of John > Miller iii > [miller3@us.ibm.com] > Sent: Monday, October 11, 2010 6:52 PM > To: ids@iiug.org > Subject: RE: RE: using ? in "in" [21628] > > Have you considered external tables? This does require the load/unload= > > file to be > accessible from the database server. > > John F. Miller III > STSM, Embedability Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 10/11/2010 01:54:26 PM: > > > [image removed] > > > > RE: RE: using ? in "in" [21624] > > > > Wyza, Jonathon > > > > to: > > > > ids > > > > 10/11/2010 01:54 PM > > > > Sent by: > > > > ids-bounces@iiug.org > > > > Please respond to ids > > > > True, but it seems faster in perl to do this: > > > > INSERT INTO temp_table(id) VALUES (list of values that goes to charac=> ter > > limit); #run until we run out of values > > > > Than to do this > > > > While(list of values) > > INSERT INTO temp_table(id) VALUES(value)> > > > I'd pay good money for the load from file to not be a isql/dbaccess > > only thing > > and to work in DBI. > > > > Jonathon Wyza > > CX & CBORD System Administrator > > CX Programmer/Analyst > > Administrative Computing > > Bethel College > > (574)-257-3381 > > AIM: Iamwyza > > jonathon.wyza@bethelcollege.edu > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D > > SLES 11x64 & IDS 11.50.FC6 > > > > "Don't document the problem, fix it." > > - Atli Bj=F6rgvin Oddsson > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of= > Art > > > Kagel > > Sent: Monday, October 11, 2010 4:14 PM > > To: ids@iiug.org > > Subject: Re: RE: using ? in "in" [21621] > > > > 64K actually. But putting the list of values into temp table also sol= > ves > the > > max statement length problem. > > > > Art > > On Oct 11, 2010 3:26 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu> > wrote: > > > Nah, the problem is the limit in the size of the string. I was hopi= > ng > > > to > > get > > > around the 32k character limit. > > > > > > Jonathon Wyza > > > CX & CBORD System Administrator > > > CX Programmer/Analyst > > > Administrative Computing > > > Bethel College > > > (574)-257-3381 > > > AIM: Iamwyza > > > jonathon.wyza@bethelcollege.edu > > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D > > > SLES 11x64 & IDS 11.50.FC6 > > > > > > "Don't document the problem, fix it." > > > - Atli Bj=F6rgvin Oddsson > > > > > > -----Original Message----- > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf = > Of > > > Walt > > > > > Hultgren > > > Sent: Monday, October 11, 2010 3:07 PM > > > To: ids@iiug.org > > > Subject: Re: using ? in "in" [21617] > > > > > > If you know the number of values, either by program logic or by > > > collecting > > and > > > scanning user input, you could build the query string with the > > > appropriate > > > > > number of question marks before it's prepared. > > > > > > Walt. > > > > > > On Oct 11, 2010, at 2:54 PM, Art Kagel wrote: > > > > > >> You can but you need one for each item in the list. You can't just= > > > >> use one ? for the whole list. For a variable length list use a tem= > p > table. > > >> > > >> Art > > >> On Oct 11, 2010 2:34 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu= > > > > >> wrote: > > >> > > >> --00163630ee9fdea2c004925be309 > > >> > > >> > > >> > > > > > > > > > > > >

Now you're just being intentionally obtuse! Art Art S. Kagel Advanced DataTools (www.advancedatatools.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, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Oct 11, 2010 at 8:16 PM, John Miller iii <miller3@us.ibm.com> wrote: > > Sorry, about the previous email. > > When you say system wide name, I think you mean database > wide. That would be true. > > How about you create a sequence, > > Use this sequence number in the external table name. > > create the external table > Take action upon the data > drop the external table > > If you are worried about stray external tables, just create a database > scheduler task that will cleanup up all external tables which are older > than 1 day. The creation time is already stored with the creation of the > external table. > > > John F. Miller III > STSM, Embedability Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 10/11/2010 04:44:14 PM: > > > [image removed] > > > > RE: RE: using ? in "in" [21629] > > > > Wyza, Jonathon > > > > to: > > > > ids > > > > 10/11/2010 04:44 PM > > > > Sent by: > > > > ids-bounces@iiug.org > > > > Please respond to ids > > > > That might work, except it appears that external tables are not capable > of > > being temporary and must have unique system wide names. Since this > > process has > > the capability of being run more than once at a time then that could > present > > problems. Beyond that I'd have to make sure it was dropped after the > process > > finished. Obviously i could use a begin_work statement (and thus if the > > program exited then it would rollback), but then it would log all the > work, > > which i really don't need it to do. > > > > Jonathon Wyza > > CX & CBORD System Administrator > > CX Programmer/Analyst > > Administrative Computing > > Bethel College > > (574)-257-3381 > > AIM: Iamwyza > > jonathon.wyza@bethelcollege.edu > > ============================== > > SLES 11x64 & IDS 11.50.FC6 > > > > “Don’t document the problem, fix it.” > > – Atli Björgvin Oddsson > > > > ________________________________________ > > From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of John > > Miller iii > > [miller3@us.ibm.com] > > Sent: Monday, October 11, 2010 6:52 PM > > To: ids@iiug.org > > Subject: RE: RE: using ? in "in" [21628] > > > > Have you considered external tables? This does require the load/unload= > > > > file to be > > accessible from the database server. > > > > John F. Miller III > > STSM, Embedability Architect > > miller3@us.ibm.com > > 503-578-5645 > > IBM Informix Dynamic Server (IDS) > > > > ids-bounces@iiug.org wrote on 10/11/2010 01:54:26 PM: > > > > > [image removed] > > > > > > RE: RE: using ? in "in" [21624] > > > > > > Wyza, Jonathon > > > > > > to: > > > > > > ids > > > > > > 10/11/2010 01:54 PM > > > > > > Sent by: > > > > > > ids-bounces@iiug.org > > > > > > Please respond to ids > > > > > > True, but it seems faster in perl to do this: > > > > > > INSERT INTO temp_table(id) VALUES (list of values that goes to charac=> > ter > > > limit); #run until we run out of values > > > > > > Than to do this > > > > > > While(list of values) > > > INSERT INTO temp_table(id) VALUES(value)> > > > > > I'd pay good money for the load from file to not be a isql/dbaccess > > > only thing > > > and to work in DBI. > > > > > > Jonathon Wyza > > > CX & CBORD System Administrator > > > CX Programmer/Analyst > > > Administrative Computing > > > Bethel College > > > (574)-257-3381 > > > AIM: Iamwyza > > > jonathon.wyza@bethelcollege.edu > > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > > =3D=3D=3D=3D=3D=3D=3D > > > SLES 11x64 & IDS 11.50.FC6 > > > > > > "Don't document the problem, fix it." > > > - Atli Bj=F6rgvin Oddsson > > > > > > -----Original Message----- > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of= > > Art > > > > > Kagel > > > Sent: Monday, October 11, 2010 4:14 PM > > > To: ids@iiug.org > > > Subject: Re: RE: using ? in "in" [21621] > > > > > > 64K actually. But putting the list of values into temp table also sol= > > ves > > the > > > max statement length problem. > > > > > > Art > > > On Oct 11, 2010 3:26 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu> > > wrote: > > > > Nah, the problem is the limit in the size of the string. I was hopi= > > ng > > > > to > > > get > > > > around the 32k character limit. > > > > > > > > Jonathon Wyza > > > > CX & CBORD System Administrator > > > > CX Programmer/Analyst > > > > Administrative Computing > > > > Bethel College > > > > (574)-257-3381 > > > > AIM: Iamwyza > > > > jonathon.wyza@bethelcollege.edu > > > > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > > =3D=3D=3D=3D=3D=3D=3D > > > > SLES 11x64 & IDS 11.50.FC6 > > > > > > > > "Don't document the problem, fix it." > > > > - Atli Bj=F6rgvin Oddsson > > > > >@@NL