Front page | perl.dbi.users |
Postings from February 2001
RE: A puzzle.....
Thread Next
From:
Warden, Ronald MCF:EX
Date:
February 7, 2001 09:03
Subject:
RE: A puzzle.....
Message ID:
DD5711D5247CD4118B280008C791400601B9B971@sharp.itsd.gov.bc.ca
Lars,
You have to use a do statement, you can't use a prepare statement. From the
perl manual
Prepare and execute a single statement. Returns the number of
rows affected (-1 if not known or not available) or undef on error.
This method is typically most useful for non-select statements which
either cannot be prepared in advance (due to a limitation of the
driver) or which do not need to be executed repeatedly. It should
not be used for select statements because it does not return a statement
handle so you can't fetch any data.
Example:
my $rows_deleted = $dbh->do(q{delete from table where status = ?},
undef, 'DONE') || die $dbh->errstr;
Using placeholders and @bind_values with the do method can be useful
because it avoids the need to correctly quote any variables in the
$statement.
In your case just build the a string that contains the create table command
and
concatenate the ID to the end. Then just do the string, checking for
success.
Hope this helps.
Cheers
-----Original Message-----
From: Lars Eskildsen [mailto:lae@ccieurope.com]
Sent: Wednesday, February 07, 2001 6:15 AM
To: 'DBI Users'
Subject: A puzzle....
Hi'
I am trying to construct an DBI SQL statement like:
$my_stmt = $my_dbcon->prepare("CREATE TABLE BLABLABLA AS (SELECT * FROM
ANOTHER_TABLE WHERE ID = ?)");
:
<Calculate $ID>
:
$my_stmt->execute($ID);
When i do this i get the following DBD error:
DBD::Oracle::st execute failed: ORA-01036: illegal variable name/number (DBD
ERR
OR: OCIBindByName) at .....
If i calculate $ID in advance and then perform the following:
$my_stmt = $my_dbcon->prepare("CREATE TABLE BLABLABLA AS (SELECT * FROM
ANOTHER_TABLE WHERE ID = '$ID')");
$my_stmt->execute;
it works just fine!
Is placeholders not allowed in DDL statements or what?
--------
Im using: PERL 5.6, DBI vers. 1.14, DBD::Oracle driver vers. 1.06
on: SUN Solaris 5.6, Oracle RDBMS 8.1.7/8.1.6
---------
-- Lars S. Eskildsen (M.Sc.) - Software Developer
-- Stibo Directory Solutions - ADVICE
-- e-mail: MAILTO:lae@stibo-ds.com
-- www : http://www.stibo-ds.com
-- Address : Soeren Nymarksvej 21
-- Postal Code : DK-8270 Hoejbjerg
-- Country : Denmark
-- Phone (Main) : (+45) 87 33 44 55
-- "" (Direct) : (+45) 87 33 44 21
-- Fax : (+45) 87 33 44 99
Thread Next