Re: Double quotes in select statement throw an error
On Tue, 18 Jan 2005 16:10:52 -0500, Moosmann, James <[EMAIL PROTECTED]> wrote: > The following line produces and error in DBI excuting with either a do or a > prepare, execute > > my $statement = "SELECT \"Rows inserted\" + @rows "); # ODBC error > my $statement2 = "SELECT 'Rows inserted' + @rows "); # works That conforms to the SQL standard. According to the standard, the version with double quotes is attempting to use a delimited identifier - a column name called "Rows inserted" (where the double quotes are not part of the column name, but the blank is, and the name is case sensitive). You should, in theory, get a column not found error - syntax error is a substitute, perhaps? Were you using an Informix database, you would be fine - it treats double quotes and single quotes interchangeably -- unless you specify via an environment variable that you want to use delimited identifiers, in which case, you have to follow the standard. -- Jonathan Leffler <[EMAIL PROTECTED]> #include Guardian of DBD::Informix - v2003.04 - http://dbi.perl.org "I don't suffer from insanity - I enjoy every minute of it."
RE: Double quotes in select statement throw an error
> From: "Moosmann, James" <[EMAIL PROTECTED]>
> Date: 2005/01/19 Wed AM 08:24:12 CST
> Does anyone know how to check/set this attribute using DBI so I don't have
> to instruct a user on changing his DSN? ( I don't see this option mentioned
> in DBD::ODBC )
Might I suggest using DSN-less connections, so that users don't have to worry
about DSNs at all?
$ConnStr= 'dbi:ODBC:driver={SQL
Server};Server='.$Server'.;database='.$database.';';
$dbh = DBI->connect( $ConnStr, $user, $pwd, {RaiseError => 1, AutoCommit => 1}
);
> Thanks.
> James
HTH,
amonotod
--
`\|||/ amonotod@| sun|perl|windows
(@@) charter.net | sysadmin|dba
ooO_(_)_Ooo
_|_|_|_|_|_|_|_|
RE: Double quotes in select statement throw an error
> > Thank you everyone who responded and to Tim and Jeff: > > The reason the server generates an error is because there are > 2 flags which can be set when you create the DSN: > > Use ANSI quoted identifiers > Use ANSI null, padding and warning > > And they were both selected. > > De-selecting "Use ANSI null, padding and warning" fixed the issue. > > This may only be an issue on Win32. > > Does anyone know how to check/set this attribute using DBI so > I don't have to instruct a user on changing his DSN? ( I > don't see this option mentioned in DBD::ODBC ) Sorry, there isn't a way to do this via DBD::ODBC. What you might want to do, if your scripts require a specific setting, is to generate a query (or set of queries) after the connect to validate that the settings you need are set, if not, die with an error message indicating what needs to be done. That way, you won't be half-way through a transaction and have a query fail. Regards, Jeff
RE: Double quotes in select statement throw an error
Thank you everyone who responded and to Tim and Jeff: The reason the server generates an error is because there are 2 flags which can be set when you create the DSN: Use ANSI quoted identifiers Use ANSI null, padding and warning And they were both selected. De-selecting "Use ANSI null, padding and warning" fixed the issue. This may only be an issue on Win32. Does anyone know how to check/set this attribute using DBI so I don't have to instruct a user on changing his DSN? ( I don't see this option mentioned in DBD::ODBC ) Thanks. James
RE: Double quotes in select statement throw an error
> Lee,
> The select statement is very valid and so is:
>
> SELECT 'Hello World!' as 'My first SQL Statement'
> -or-
> SELECT answer = 2+3
>
> Really, try it.
>
> Anyway... I was sent the correct answer and was very
> surprised in that the syntax is indeed valid on some servers,
> however the ANSI standard is single quotes for all string
> literals and that these other drivers/servers handled string
> literals with either single or double quotes on a somewhat
> random basis...bizzare. Perl DBI thinks any double quoted
> string must be a column or table name ( [ and ] are
> preferred, but double quotes are still acceptable). The
> problem is that these other apps let the coder use that
> non-standard syntax and it works. I guess we will have to
> retrain the folks who generated this stuff.
Actually, it's not Perl/DBI, it's the ODBC driver. DBD::ODBC just passes the
query
(quotes and all) to the ODBC driver, which is complaining.
Regards,
Jeff
>
> Thanks anyway. Question answered.
>
> James
>
>
> -Original Message-
> From: JupiterHost.Net [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, January 18, 2005 6:49 PM
> To: dbi-users@perl.org
> Subject: Re: Double quotes in select statement throw an error
>
>
>
>
> Moosmann, James wrote:
> > Nope, same results, Here is a simple example:
> >
> > Is the syntax invalid?
> >
> > use DBI;
> > my $dbh = DBI->connect('dbi:ODBC:somedb','','');
> > my $qs = $dbh->quote( "SELECT \"Rows returned: \" ");
>
> Why are you quoting the entire query as a string?
>
> > $dbh->do($qs);
>
> Use a valid query:
>
> $dbh->do("SELECT \"valid column\" FROM \"valid table\"") ...
>
> > print $dbh->errstr;
> >
> > ---RESULT---
> > DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server
> Driver][SQL
> > Server]Invalid column name 'Rows returned: '. (SQL-42S22)
>
> err, at the risk of sounding pedantic or sarcastic, it would appear
> there is no column named "Rows returned: ", so I'd say yes,
> the syntax
> is invalid...
>
> I'm not a Microsoft user (thank the Lord!!!) but even Bill
> Gates should
> be able to understand that error :)
>
> Lee.M
>
Re: Double quotes in select statement throw an error
On Tue, Jan 18, 2005 at 11:22:58PM -0500, Moosmann, James wrote: > Perl DBI thinks any double quoted string must be a column > or table name ( [ and ] are preferred, but double quotes are still > acceptable). Not true. Neither the DBI nor the driver (generally) parse the SQL. The DBI gives it to the driver, the driver gives it to the database server, and the database server sends back a description of the columns etc. Tim.
Re: Double quotes in select statement throw an error
Moosmann, James wrote: Lee, Hello, The select statement is very valid and so is: SELECT 'Hello World!' as 'My first SQL Statement' -or- SELECT answer = 2+3 Really, try it. ok, but if you $dbh->quote() it, it becomes something like: 'SELECT \'Hello World!\' as \'My first SQL Statement\'' literally, but as far as validity, if the statement in question was valid it would return something instead of an error... Anyway... I was sent the correct answer and was very surprised in that the syntax is indeed valid on some servers, however the ANSI standard is single I imagine it may be but what does: Invalid column name 'Rows returned: ' mean then? quotes for all string literals and that these other drivers/servers handled string literals with either single or double quotes on a somewhat random basis...bizzare. Perl DBI thinks any double quoted string must be a column or table name ( [ and ] are preferred, but double quotes are still really? where did you see that in the docs? I can: SELECT foo FROM bar WHERE ID="1"; without 1 having to be a column or table name.. acceptable). The problem is that these other apps let the coder use that non-standard syntax and it works. I guess we will have to retrain the folks who generated this stuff. Yes, Microsoft users are the lamest ;p
RE: Double quotes in select statement throw an error
Lee,
The select statement is very valid and so is:
SELECT 'Hello World!' as 'My first SQL Statement'
-or-
SELECT answer = 2+3
Really, try it.
Anyway... I was sent the correct answer and was very surprised in that the
syntax is indeed valid on some servers, however the ANSI standard is single
quotes for all string literals and that these other drivers/servers handled
string literals with either single or double quotes on a somewhat random
basis...bizzare. Perl DBI thinks any double quoted string must be a column
or table name ( [ and ] are preferred, but double quotes are still
acceptable). The problem is that these other apps let the coder use that
non-standard syntax and it works. I guess we will have to retrain the folks
who generated this stuff.
Thanks anyway. Question answered.
James
-Original Message-
From: JupiterHost.Net [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 18, 2005 6:49 PM
To: dbi-users@perl.org
Subject: Re: Double quotes in select statement throw an error
Moosmann, James wrote:
> Nope, same results, Here is a simple example:
>
> Is the syntax invalid?
>
> use DBI;
> my $dbh = DBI->connect('dbi:ODBC:somedb','','');
> my $qs = $dbh->quote( "SELECT \"Rows returned: \" ");
Why are you quoting the entire query as a string?
> $dbh->do($qs);
Use a valid query:
$dbh->do("SELECT \"valid column\" FROM \"valid table\"") ...
> print $dbh->errstr;
>
> ---RESULT---
> DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Invalid column name 'Rows returned: '. (SQL-42S22)
err, at the risk of sounding pedantic or sarcastic, it would appear
there is no column named "Rows returned: ", so I'd say yes, the syntax
is invalid...
I'm not a Microsoft user (thank the Lord!!!) but even Bill Gates should
be able to understand that error :)
Lee.M
Re: Double quotes in select statement throw an error
Moosmann, James wrote:
Nope, same results, Here is a simple example:
Is the syntax invalid?
use DBI;
my $dbh = DBI->connect('dbi:ODBC:somedb','','');
my $qs = $dbh->quote( "SELECT \"Rows returned: \" ");
Why are you quoting the entire query as a string?
$dbh->do($qs);
Use a valid query:
$dbh->do("SELECT \"valid column\" FROM \"valid table\"") ...
print $dbh->errstr;
---RESULT---
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL
Server]Invalid column name 'Rows returned: '. (SQL-42S22)
err, at the risk of sounding pedantic or sarcastic, it would appear
there is no column named "Rows returned: ", so I'd say yes, the syntax
is invalid...
I'm not a Microsoft user (thank the Lord!!!) but even Bill Gates should
be able to understand that error :)
Lee.M
RE: Double quotes in select statement throw an error
quote() is normally used like this (for instance):
my $stmt = "UPDATE mytable SET name=" . $dbh->quote("fred") . "WHERE age=3";
That is, the quote() function *puts* the quotes in. Try
$ man DBI
for more information.
The other part: I'm rusty on Oracle, but the query
SELECT 'Rows returned:'
doesn't sound like it would be valid syntax in any flavour of SQL. Maybe
you need to review SQL basics first.
-Will
-Original Message-
From: Moosmann, James [mailto:[EMAIL PROTECTED]
Sent: Tuesday 18 January 2005 18:35
To: 'David Goodman'
Cc: dbi-users@perl.org
Subject: RE: Double quotes in select statement throw an error
Nope, same results, Here is a simple example:
Is the syntax invalid?
use DBI;
my $dbh = DBI->connect('dbi:ODBC:somedb','','');
my $qs = $dbh->quote( "SELECT \"Rows returned: \" "); $dbh->do($qs); print
$dbh->errstr;
- - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - -
This e-mail and any attachments may contain information which is confidential,
proprietary, privileged or otherwise protected by law. The information is
solely intended for the named addressee (or a person responsible for delivering
it to the addressee). If you are not the intended recipient of this message,
you are not authorized to read, print, retain, copy or disseminate this message
or any part of it. If you have received this e-mail in error, please notify the
sender immediately by return e-mail and delete it from your computer.
RE: Double quotes in select statement throw an error
David Goodman [mailto:[EMAIL PROTECTED] wrote:
>
> Use DBI's quote:
>
> my $statement = dbh->quote("SELECT \"Rows inserted\" +
> @rows");
This is not at all how quote() is meant to be used. quote() is for quoting
a single value for interpolation, not an entire SQL statement.
For example:
my $date = $dbh->quote('2005-01-18');
my $sql = "SELECT * FROM mytable WHERE date_applied = $date";
But using placeholders is generally better than using quote() anyway.
Ronald
RE: Double quotes in select statement throw an error
Nope, same results, Here is a simple example:
Is the syntax invalid?
use DBI;
my $dbh = DBI->connect('dbi:ODBC:somedb','','');
my $qs = $dbh->quote( "SELECT \"Rows returned: \" ");
$dbh->do($qs);
print $dbh->errstr;
---RESULT---
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL
Server]Invalid column name 'Rows returned: '. (SQL-42S22)
-Original Message-
From: David Goodman [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 18, 2005 6:03 PM
To: dbi-users@perl.org
Subject: Re: Double quotes in select statement throw an error
Use DBI's quote:
my $statement = dbh->quote("SELECT \"Rows inserted\" +
@rows");
regards,
David
--- "Moosmann, James"
<[EMAIL PROTECTED]> wrote:
> The following line produces and error in DBI
> excuting with either a do or a
> prepare, execute
>
> my $statement = "SELECT \"Rows inserted\" + @rows
> "); # ODBC error
> my $statement2 = "SELECT 'Rows inserted' + @rows ");
> # works
>
> $dbh->do( $statement );
>
> or
>
> $sth->prepare( $statement );
> $sth->execute();
>
> DBI: 1.46
> DBD-ODBC: 1.11
>
> Active State Perl 5.8.3 multi-thread
> Win2K
>
> The error:
>
> DBD::ODBC::db do failed: [Microsoft][ODBC SQL Server
> Driver][SQL
> Server]Invalid column name 'Rows inserted: '.
> (SQL-42S22)
> [Microsoft][ODBC SQL Server Driver][SQL
> Server]Invalid column name 'Rows
> inserted: '. (SQL-42S22)(DBD: Execute immediate
> failed err=-1) at
> c:/Cw/Dev/Library/Executables/sptool line 215,
> <$fileHandle> line 211.:
>
>
> MSSQL isql and Embarqadero do not complain about the
> double quotes.
>
> This is in a stored procedure, the text of which we
> are trying to run.
> Other tools do not complain about the double quotes,
> only DBI.
>
> Any ideas? Is this a bug? Or, is it simply
> incorrect for the user to be
> using double quotes (ie, not ANSI ? )
>
> Thanks,
>
> James
>
>
Re: Double quotes in select statement throw an error
Use DBI's quote:
my $statement = dbh->quote("SELECT \"Rows inserted\" +
@rows");
regards,
David
--- "Moosmann, James"
<[EMAIL PROTECTED]> wrote:
> The following line produces and error in DBI
> excuting with either a do or a
> prepare, execute
>
> my $statement = "SELECT \"Rows inserted\" + @rows
> "); # ODBC error
> my $statement2 = "SELECT 'Rows inserted' + @rows ");
> # works
>
> $dbh->do( $statement );
>
> or
>
> $sth->prepare( $statement );
> $sth->execute();
>
> DBI: 1.46
> DBD-ODBC: 1.11
>
> Active State Perl 5.8.3 multi-thread
> Win2K
>
> The error:
>
> DBD::ODBC::db do failed: [Microsoft][ODBC SQL Server
> Driver][SQL
> Server]Invalid column name 'Rows inserted: '.
> (SQL-42S22)
> [Microsoft][ODBC SQL Server Driver][SQL
> Server]Invalid column name 'Rows
> inserted: '. (SQL-42S22)(DBD: Execute immediate
> failed err=-1) at
> c:/Cw/Dev/Library/Executables/sptool line 215,
> <$fileHandle> line 211.:
>
>
> MSSQL isql and Embarqadero do not complain about the
> double quotes.
>
> This is in a stored procedure, the text of which we
> are trying to run.
> Other tools do not complain about the double quotes,
> only DBI.
>
> Any ideas? Is this a bug? Or, is it simply
> incorrect for the user to be
> using double quotes (ie, not ANSI ? )
>
> Thanks,
>
> James
>
>
Double quotes in select statement throw an error
The following line produces and error in DBI excuting with either a do or a prepare, execute my $statement = "SELECT \"Rows inserted\" + @rows "); # ODBC error my $statement2 = "SELECT 'Rows inserted' + @rows "); # works $dbh->do( $statement ); or $sth->prepare( $statement ); $sth->execute(); DBI: 1.46 DBD-ODBC: 1.11 Active State Perl 5.8.3 multi-thread Win2K The error: DBD::ODBC::db do failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Rows inserted: '. (SQL-42S22) [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Rows inserted: '. (SQL-42S22)(DBD: Execute immediate failed err=-1) at c:/Cw/Dev/Library/Executables/sptool line 215, <$fileHandle> line 211.: MSSQL isql and Embarqadero do not complain about the double quotes. This is in a stored procedure, the text of which we are trying to run. Other tools do not complain about the double quotes, only DBI. Any ideas? Is this a bug? Or, is it simply incorrect for the user to be using double quotes (ie, not ANSI ? ) Thanks, James
