DBI::Informix, ClientSDK and Informix SE

Hi all, I've been battling for a while trying to get Perl DBI::Informix 0.95 to install and test correctly. The environment is: IBM RS/6000 43P with AIX 4.3.2 Informix SE 5.01 ClientSDK 2.3 ClientSDK appeared to install reasonably happily (installed as root). During the first stage of DBI::Informix installation (making the Makefile) however, while it correctly identifies the Perl, DBD and ESQL/C versions, and compiles the test program, it cannot make a connection to the stores database which I connected. While I must admit I was dubious about SE acceptiong connections at all, all the docs I've seen seem to assert that this configuration should work, albeit with some tweaking. Now, I'm really not a database guru AT ALL(!), never mine knowledgeable about the idiosyncracies of Informix in particular. SO, what am I missing? I believe that I've set INFORMIXDIR and PATH correctly. I wasn't sure about INFORMIXSERVER... Tried giving it a pathname to the ifxserver prog (which failed, obviously). It was happier when I set it to 'localhost' but still wouldn't connect to the database. Any ideas? (And sorry if I've been stupid and missed something blatantly obvious -- we have to start somewhere though :)) Hal

In comp.lang.perl.misc Halford Dace <hdace@bluedot.co.za> wrote: > Hi all, > > I've been battling for a while trying to get Perl DBI::Informix 0.95 to > install and test correctly. > > The environment is: > > IBM RS/6000 43P with AIX 4.3.2 > Informix SE 5.01 > ClientSDK 2.3 > > ClientSDK appeared to install reasonably happily (installed as root). > > During the first stage of DBI::Informix installation (making the > Makefile) however, while it correctly identifies the Perl, DBD and > ESQL/C versions, and compiles the test program, it cannot make a > connection to the stores database which I connected. > > While I must admit I was dubious about SE acceptiong connections at all, > all the docs I've seen seem to assert that this configuration should > work, albeit with some tweaking. > > Now, I'm really not a database guru AT ALL(!), never mine knowledgeable > about the idiosyncracies of Informix in particular. SO, what am > I missing? > > I believe that I've set INFORMIXDIR and PATH correctly. I wasn't sure > about INFORMIXSERVER... Tried giving it a pathname to the ifxserver > prog (which failed, obviously). It was happier when I set it to > 'localhost' but still wouldn't connect to the database. > This is really a question about the configuration of the Informix database and really should be only asked in comp.databases.informix . As well as INFORMIXDIR, you *must* set INFORMIXSERVER and (for SE) DBPATH. DBPATH should contain the path of the directory where your stores database resides (i.e. where the stores.dbs directory is ) - for example you might have : DBPATH=/usr/databases # stores database in /usr/databases/stores.dbs export DBPATH DBPATH can have a colon separated list of directories if you multiple databases in various locations. INFORMIXSERVER points to a record in the file $INFORMXDIR/etc/sqlhosts that relates to the database server you want to use - you might from one machine for instance want to access a local SE database or an IDS server on another host - setting INFORMIXDIR appropriately will allow you to control the default behaviour. Your $INFORMXDIR/etc/sqlhosts should have an entry similar to : local seipcpip gellyfish sqlexec Of course you will replace 'gellyfish' with the name of your own host - so INFORMIXSERVER will be set to 'local' here (and exported). The second column here defines the method that the client and dtabase engine will communicate with - you might have to look at the release notes for your platform which can be found in $INFORMIXDIR/release somewhere to find what you should use if the above doesnt work ... Have Fun /j\\ -- Jonathan Stowe <jns@gellyfish.com> <http://www.gellyfish.com>; ** Uri Guttman - Have You CPANed Backward.pm Yet ? **

Halford Dace wrote: > I've been battling for a while trying to get Perl DBI::Informix 0.95 to > install and test correctly. I hope you've tried reading the documentation during the course of your battling. Not that it specifically addresses this question, but because it does tell you where to ask questions, and the forums chosen are not the ones it suggests you should use. > The environment is: > > IBM RS/6000 43P with AIX 4.3.2 > Informix SE 5.01 > ClientSDK 2.3 That is a weird combination. Are you sure you mean 5.01 and not 5.10? It is still an unusual combination if you mean 5.10, but not completely outrageously so, but 5.01 should have been upgraded years ago. 5.01 is not considered Y2K compliant, nor is it supported any more; 5.10 is both Y2K-compliant and supported. > ClientSDK appeared to install reasonably happily (installed as root). OK. And have you written any programs in ESQL/C which connect to your database? No? Then you need to get a program using CSDK to talk to your database -- that's what the testing is doing. It does it to head problems off at the pass; this is a basic Informix connectivity question (so comp.databases.informix is not wholly inappropriate as a news group). Normally, I'd suggest getting DB-Access to talk to the database is a good first step. However, the DB-Access you have is built with ESQL/C 5.01 or 5.10, and not with CSDK, so it won't help this time. > During the first stage of DBI::Informix installation (making the > Makefile) however, while it correctly identifies the Perl, DBD and > ESQL/C versions, and compiles the test program, it cannot make a > connection to the stores database which I connected. > > While I must admit I was dubious about SE acceptiong connections at all, > all the docs I've seen seem to assert that this configuration should > work, albeit with some tweaking. > > Now, I'm really not a database guru AT ALL(!), never mine knowledgeable > about the idiosyncracies of Informix in particular. SO, what am > I missing? > > I believe that I've set INFORMIXDIR and PATH correctly. I wasn't sure > about INFORMIXSERVER... Tried giving it a pathname to the ifxserver > prog (which failed, obviously). It was happier when I set it to > 'localhost' but still wouldn't connect to the database. > > Any ideas? > > (And sorry if I've been stupid and missed something blatantly obvious -- > we have to start somewhere though :)) Jonathan Stowe's answer contains a lot of good information. This is a highly non-trivial setup -- not one I'd recommend to novices. I assume you installed both CSDK and SE into the same INFORMIXDIR? If not, you're making life harder for yourself. And I'm assuming that you don't have I-Net, so network connections are out of the question? Given the assumptions, then you need to use the seipcpip connectivity method in the sqlhosts file, as Jonathan S suggested. The fourth column in a line specifying seipcpip connections specifies the name of an executable in $INFORMIXDIR/lib, and sqlexec is the name of the SE program. I have some slight concerns that this will not work without I-Net and sesoctcp or setlitcp connections. You should probably compile the esqlbasic.ec program that comes with DBD::Informix and use that to fiddle with the connectivity. Until you get that to connect cleanly to the database, forget about Perl. I'd recommend upgrading your database to something more recent, or downgrading your ESQL/C to the same version as SE. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN #include <disclaimer.h>

According to the perldoc DBI page: ...Placeholders can only represent single scalar values, so this statement, for example, won't work as expected for more than one value: "select name, age from people where name in (?)" # wrong Given that, and the fact that preparing statements provides a nice performance boost, what's the best solution? I could prepare multiple statements, like these: "select name, age from people where name in (?)" "select name, age from people where name in (?,?)" "select name, age from people where name in (?,?,?)" But some of these statements can have 50-200 parameters, and this seems like a but of a hack. Any advice would be appreciated. Sent via Deja.com http://www.deja.com/ Before you buy.

rboucher@my-deja.com wrote: > According to the perldoc DBI page: > > ...Placeholders can only represent single scalar > values, so this statement, for example, won't work as > expected for more than one value: > > "select name, age from people where name in (?)" # wrong > > Given that, and the fact that preparing statements > provides a nice performance boost, what's the best > solution? > > I could prepare multiple statements, like these: > > "select name, age from people where name in (?)" > "select name, age from people where name in (?,?)" > "select name, age from people where name in (?,?,?)" > > But some of these statements can have 50-200 parameters, > and this seems like a but of a hack. > > Any advice would be appreciated. It depends on your context, but one way of doing it is with a sub-select: SELECT name, age FROM people WHERE name IN (SELECT * FROM some_temp_table) You then populate the temp table with the current list of values. At some point in the proceedings, using this becomes faster than a long IN list -- I seem to remember the break-even point being in the 50-200 items range in 5.00 OnLine (yes, ancient, but that was the latest version when I was doing the benchmarking -- I had to wait for 5.01 to get the triggers that I really needed). Basically, if you want to have variable lists of items, you either have to use multiple statements or something like the sub-select. Of course, you could simply have variations with, say, 10, 20, 40, 80, 160, 320 question marks, and simply repeat some of the values when you have only 30 distinct values to search for. Have fun. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include Guardian of DBD::Informix v0.95 -- http://www.perl.com/CPAN "Windows is NOT a virus: a virus is small and efficient."