Re: SQL Syntax error in DBI::Informix

In article <396CDD85.8D0C4384@conxsysinc.com>, paul@conxsysinc.com <paul@conxsysinc.com> wrote: >My script connects with the Informix DB server, but it doesn't like my >INSERT >statement (below). This code below is supposed to store a new user into >a database. > >print "DEBUG: OK, so we are adding $userd with password > $passd and server $host at port $port\\n"; >my $sth = $dbh->prepare(" > INSERT INTO $table > VALUES ($userd, $passd, $host, $port) > ") > or die "Can't prepare SQL statement to INSERT data\\n"; > >The error message it gives is: >DBD::Informix::db prepare failed: SQL: -201: A syntax error has >occurred. at >./adduser line 53. The variables $userd, $passd, $host and $port are all interpolated into that string unchanged. If you haven't arranged for appropriate quote characters in the variables (and I doubt you have), the resulting SQL string you are preparing is something like INSERT INTO foo VALUES (fred, somepass, somehost, 1234)hence the syntax error. You should have something like $dbh->prepare(sprintf("INSERT INTO $table VALUES (%s, %s, %s, %d)", $dbh->quote($userd), $dbh->quote($passd), $dbh->quote($host), $port)); which will produce INSERT INTO foo VALUES ('fred', 'somepass', 'somehost', 1234) Use $dbh->quote($foo) and not just '$foo' because the quote method will handle strings containing quote characters properly (and any other db specific quoting rules). This is all standard DBI stuff and not Informix-specific. --Malcolm -- Malcolm Beattie <mbeattie@sable.ox.ac.uk> Oxford University Computing Services "I permitted that as a demonstration of futility" --Grey Roger