executing an sql statement in perl

[MMWJones]

If this is the wong board to post this question can someone guide me
to the correct board...

I have an sql statement that is in single quotes


or $dbh->do ('update test set (mudid, date_of_last_entrym
number_of_entries) = (select mudid, max(thetime) as
date_of_last_entry, count(*) as number_of_entries from log@test l,
user@test u
where l.user = u.user
and mudid = '$a'
and thetime > sysdate - 90
group by mudid)')

where $a is defined as the id.

however i don't think it likes the 2 sets of single quotes. I have to
use single quotes as otherwise the @ symbols aren't read correctly.

The below sql statement works fine directly in my sql program (TOAD)
and this is why i think it is a perl problem:

select id, max(thetime) as date_of_last_entry, count(*) as
number_of_entries from log@test l, user@test u
where l.user = u.user
and id = 'MATT'
and thetime > sysdate - 90
group by id


any ideas?

Thanks,

 

[Tad McClellan]

If this is the wong board to post this question can someone guide me
to the correct board...

I have an sql statement that is in single quotes


or $dbh->do ('update test set (mudid, date_of_last_entrym
number_of_entries) = (select mudid, max(thetime) as
date_of_last_entry, count(*) as number_of_entries from log@test l,
user@test u
where l.user = u.user
and mudid = '$a'
and thetime > sysdate - 90
group by mudid)')

where $a is defined as the id.

however i don't think it likes the 2 sets of single quotes. I have to
use single quotes as otherwise the @ symbols aren't read correctly.

You have to use double quotes or the $a won't be interpolated correctly.

any ideas?

Use double quotes, and backslash the at-signs.

 

[Paul Lalli]

If this is the wong board to post this question can someone guide me
to the correct board...

I have an sql statement that is in single quotes

or $dbh->do ('update test set (mudid, date_of_last_entrym
number_of_entries) = (select mudid, max(thetime) as
date_of_last_entry, count(*) as number_of_entries from log@test l,
user@test u
where l.user = u.user
and mudid = '$a'
and thetime > sysdate - 90
group by mudid)')

where $a is defined as the id.

however i don't think it likes the 2 sets of single quotes. I have to
use single quotes as otherwise the @ symbols aren't read correctly.

The below sql statement works fine directly in my sql program (TOAD)
and this is why i think it is a perl problem:

select id, max(thetime) as date_of_last_entry, count(*) as
number_of_entries from log@test l, user@test u
where l.user = u.user
and id = 'MATT'
and thetime > sysdate - 90
group by id

any ideas?

Because you need interpolation to occur in this string, you NEED to
use double quotes to surround the string. To prevent Perl from
thinking the @ characters start an array, simply put a backslash in
front of them.

$dbh->do ("update test set (mudid, date_of_last_entrym
number_of_entries) = (select mudid, max(thetime) as
date_of_last_entry, count(*) as number_of_entries from log\@test l,
user\@test u
where l.user = u.user
and mudid = '$a'
and thetime > sysdate - 90
group by mudid)");


Alternatively, do not put the variable within the SQL directly. Use
placeholders instead.

$dbh->do ('update test set (mudid, date_of_last_entrym
number_of_entries) = (select mudid, max(thetime) as
date_of_last_entry, count(*) as number_of_entries from log@test l,
user@test u
where l.user = u.user
and mudid = ?
and thetime > sysdate - 90
group by mudid)', {}, $a);

See also:
perldoc DBI
perldoc perlsyn
perldoc perldata

Hope this helps,
Paul Lalli

 

[Thomas J.]

If this is the wong board to post this question can someone guide me
to the correct board...

I have an sql statement that is in single quotes

or $dbh->do ('update test set (mudid, date_of_last_entrym
number_of_entries) = (select mudid, max(thetime) as
date_of_last_entry, count(*) as number_of_entries from log@test l,
user@test u
where l.user = u.user
and mudid = '$a'
and thetime > sysdate - 90
group by mudid)')

where $a is defined as the id.

If you would "print" your sql-statement you will probably see the
mistake.

Try sting-concat via "." ...
eg. 'bla'.$a.'bla'

hth, Thomas

 

[Mumia W.]

If this is the wong board to post this question can someone guide me
to the correct board...

I have an sql statement that is in single quotes


or $dbh->do ('update test set (mudid, date_of_last_entrym
number_of_entries) = (select mudid, max(thetime) as
date_of_last_entry, count(*) as number_of_entries from log@test l,
user@test u
where l.user = u.user
and mudid = '$a'
and thetime > sysdate - 90
group by mudid)')

where $a is defined as the id.

however i don't think it likes the 2 sets of single quotes. I have to
use single quotes as otherwise the @ symbols aren't read correctly.
[...]

Use double-quotes and put backslashes before the @ symbols to prevent
them (and the characters that follow) from being interpreted as array names.

 

[xhoster]

If this is the wong board to post this question can someone guide me
to the correct board...

I have an sql statement that is in single quotes

Use place holders. That is, use a ? in the SQL string, and then pass in
the value of $a separately.

$dbh->do('whatever where mudid=?', undef, $a);

Xho

 

[Xicheng Jia]

I don't think place holders will work with $dbh->do because that does a
prepare and execute in one go.

why not, I use placeholder with $dbh->do(...) on my website, and it
works pretty well so far.

Regards,
Xicheng

 

[Paul Lalli]

I don't think place holders will work with $dbh->do because that does a
prepare and execute in one go.

http://search.cpan.org/~timb/DBI-1.56/DBI.pm#do

What the hell? Did you bother reading this URL? Why would you post
it if you didn't read it?

Yes, placeholders work. The syntax is:
$rows = $dbh->do($statement, \%attr, @bind_values) or die ...
which that URL that *you* posted tells us is "logically similar" to:
sub do {
my($dbh, $statement, $attr, @bind_values) = @_;
my $sth = $dbh->prepare($statement, $attr) or return undef;
$sth->execute(@bind_values) or return undef;
my $rows = $sth->rows;
($rows == 0) ? "0E0" : $rows; # always return true if no error
}

Placeholders work just fine.

Paul Lalli

 

[Paul Lalli]

I don't think place holders will work with $dbh->do because that does a
prepare and execute in one go.

http://search.cpan.org/~timb/DBI-1.56/DBI.pm#do

Did you bother reading this URL? Yes, placeholders work. The syntax
is:
$rows = $dbh->do($statement, \%attr, @bind_values) or die ...
which that URL that *you* posted tells us is "logically similar" to:
sub do {
my($dbh, $statement, $attr, @bind_values) = @_;
my $sth = $dbh->prepare($statement, $attr) or return undef;
$sth->execute(@bind_values) or return undef;
my $rows = $sth->rows;
($rows == 0) ? "0E0" : $rows; # always return true if no error
}

Placeholders work just fine.

Paul Lalli

 

[xhoster]

I don't think place holders will work with $dbh->do because that does a
prepare and execute in one go.

http://search.cpan.org/~timb/DBI-1.56/DBI.pm#do

Look at the third line of the initial examples in the link you just posted:

$rows = $dbh->do($statement, \%attr, @bind_values) or die ...


(As a hint, if you have no \%attr to pass, just use undef instead.)

Xho

 

[Dr.Ruud]

Dennis Roesler schreef:

I don't think place holders will work with $dbh->do because
that does a prepare and execute in one go.

Why prepare without placeholders?

(Your one go has at least two steps.)

 

[Dr.Ruud]

(e-mail address removed) schreef:

(As a hint, if you have no \%attr to pass, just use undef instead.)

And some people prefer to pass {}.