Re: Retrieving the CURRENT default database name
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_database A simple "select database();" returns the current active database. Does that not do what you want? michael On Mon, 22 Oct 2012, Meir Guttman wrote: Date: Mon, 22 Oct 2012 19:19:24 +0200 From: Meir Guttman To: dbi-users@perl.org Subject: Retrieving the CURRENT default database name Dear mavens, My application interacts with a MySQL DBMS. There I am using subroutines and packages that set the various attributes passed to the DBI "connect" method. Also, I later switch databases based on a user's input by executing a "USE 'DB_name'" SQL statement. What I am after is a way to debug my application and/or log a particular run to verify what was actually selected as the "default database name" at any given juncture. A bug there might be devastating Unfortunately, a real SQL maven says (http://forums.mysql.com/read.php?10,571841,571848) that no SQL statement exists to retrieve the currently set database. I searched the web to find a way to do so, without success. Is there a DBI (or DBD?) way to do so? Regards Meir
Retrieving the CURRENT default database name
Dear mavens, My application interacts with a MySQL DBMS. There I am using subroutines and packages that set the various attributes passed to the DBI "connect" method. Also, I later switch databases based on a user's input by executing a "USE 'DB_name'" SQL statement. What I am after is a way to debug my application and/or log a particular run to verify what was actually selected as the "default database name" at any given juncture. A bug there might be devastating Unfortunately, a real SQL maven says (http://forums.mysql.com/read.php?10,571841,571848) that no SQL statement exists to retrieve the currently set database. I searched the web to find a way to do so, without success. Is there a DBI (or DBD?) way to do so? Regards Meir
Re: Prepared statements- variable number of placeholders
On 2012-10-22 15:16:47 +0800, Tim Bowden wrote:
> Is it possible, and if so, how can I set the number of placeholders in a
> prepared statement at runtime?
>
> IE, given something like:
>
> my $sth = $dbh->prepare("INSERT INTO $table ($fields) VALUES (?,?,?));
>
> which is fine if I know how many fields I'm inserting into, but what if
> I don't know till runtime how many fields? How can I put the (?,?,?)
> into a variable and have it recognised as placeholders?
You could do something like
my $placeholders = '(?,?,?)';
my $sth = $dbh->prepare("INSERT INTO $table ($fields) VALUES $placeholders");
but I guess you thought of that and your question is really
How can I get $placeholders from $fields?
If I have a variable number of fields I usually have them in array, not
a scalar, so I can just count the members:
$fields = join(',', @fields);
$placeholders = '(' . join(',', map('?', @fields)) . ')';
my $sth = $dbh->prepare("INSERT INTO $table ($fields) VALUES $placeholders");
(and then you can get rid of $fields and $placeholders and do it all in
a single line)
hp
--
_ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR | eingeprägt stehen: "Ein ewig Rätsel will ich
| | | h...@wsr.ac.at | bleiben, mir und andern."
__/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd
signature.asc
Description: Digital signature
Re: Prepared statements- variable number of placeholders- SOLVED
Thanks Henri. Much appreciated.
On Mon, 2012-10-22 at 09:21 +0200, Henri Asseily wrote:
> The $sth is a mutable string… Which means you can change it at runtime!
>
> Here's an example of a modified $sth at runtime:
>
> my $up_sql = defined($updated_at) ? 'updated_at < ? AND ' : undef;
> my $sql = "SELECT * FROM mytable WHERE $up_sql type_id = ? AND
> serial_number IN (
> SELECT serial_number FROM anothertable WHERE device_id = ?)";
> my $sth = $dbh->prepare($sql);
> my $i = 1;
> if (defined($up_sql)) {
> $sth->bind_param($i, $updated_at, SQL_INTEGER); $i++;
> }
> $sth->bind_param($i, $pass_type_id, SQL_VARCHAR); $i++;
> $sth->bind_param($i, $device_id, SQL_VARCHAR); $i++;
> $sth->execute;
>
>
>
> On Oct 22, 2012, at 9:16 AM, Tim Bowden wrote:
>
> > Is it possible, and if so, how can I set the number of placeholders in a
> > prepared statement at runtime?
> >
> > IE, given something like:
> >
> > my $sth = $dbh->prepare("INSERT INTO $table ($fields) VALUES (?,?,?));
> >
> > which is fine if I know how many fields I'm inserting into, but what if
> > I don't know till runtime how many fields? How can I put the (?,?,?)
> > into a variable and have it recognised as placeholders?
> >
> > Thanks,
> > Tim Bowden
> >
>
Re: Prepared statements- variable number of placeholders
The $sth is a mutable string… Which means you can change it at runtime!
Here's an example of a modified $sth at runtime:
my $up_sql = defined($updated_at) ? 'updated_at < ? AND ' : undef;
my $sql = "SELECT * FROM mytable WHERE $up_sql type_id = ? AND serial_number
IN (
SELECT serial_number FROM anothertable WHERE device_id = ?)";
my $sth = $dbh->prepare($sql);
my $i = 1;
if (defined($up_sql)) {
$sth->bind_param($i, $updated_at, SQL_INTEGER); $i++;
}
$sth->bind_param($i, $pass_type_id, SQL_VARCHAR); $i++;
$sth->bind_param($i, $device_id, SQL_VARCHAR); $i++;
$sth->execute;
On Oct 22, 2012, at 9:16 AM, Tim Bowden wrote:
> Is it possible, and if so, how can I set the number of placeholders in a
> prepared statement at runtime?
>
> IE, given something like:
>
> my $sth = $dbh->prepare("INSERT INTO $table ($fields) VALUES (?,?,?));
>
> which is fine if I know how many fields I'm inserting into, but what if
> I don't know till runtime how many fields? How can I put the (?,?,?)
> into a variable and have it recognised as placeholders?
>
> Thanks,
> Tim Bowden
>
Prepared statements- variable number of placeholders
Is it possible, and if so, how can I set the number of placeholders in a
prepared statement at runtime?
IE, given something like:
my $sth = $dbh->prepare("INSERT INTO $table ($fields) VALUES (?,?,?));
which is fine if I know how many fields I'm inserting into, but what if
I don't know till runtime how many fields? How can I put the (?,?,?)
into a variable and have it recognised as placeholders?
Thanks,
Tim Bowden
