Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > executing an sql statement in perl

Reply
Thread Tools

executing an sql statement in perl

 
 
MMWJones@googlemail.com
Guest
Posts: n/a
 
      05-21-2007
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,

 
Reply With Quote
 
 
 
 
Tad McClellan
Guest
Posts: n/a
 
      05-21-2007
http://www.velocityreviews.com/forums/(E-Mail Removed) <(E-Mail Removed)> wrote:
> 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.


--
Tad McClellan SGML consulting
(E-Mail Removed) Perl programming
Fort Worth, Texas
 
Reply With Quote
 
 
 
 
Paul Lalli
Guest
Posts: n/a
 
      05-21-2007
On May 21, 6:11 am, "(E-Mail Removed)"
<(E-Mail Removed)> wrote:
> 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

 
Reply With Quote
 
Thomas J.
Guest
Posts: n/a
 
      05-21-2007
On 21 Mai, 12:11, "(E-Mail Removed)" <(E-Mail Removed)>
wrote:
> 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

 
Reply With Quote
 
Mumia W.
Guest
Posts: n/a
 
      05-21-2007
On 05/21/2007 05:11 AM, (E-Mail Removed) wrote:
> 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.

 
Reply With Quote
 
xhoster@gmail.com
Guest
Posts: n/a
 
      05-21-2007
"(E-Mail Removed)" <(E-Mail Removed)> wrote:
> 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

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB
 
Reply With Quote
 
Xicheng Jia
Guest
Posts: n/a
 
      05-22-2007
On May 21, 11:23 pm, Dennis Roesler <(E-Mail Removed)> wrote:
> Paul Lalli wrote:
> > On May 21, 6:11 am, "(E-Mail Removed)"
> > <(E-Mail Removed)> wrote:
> >> 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

>
> 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


 
Reply With Quote
 
Paul Lalli
Guest
Posts: n/a
 
      05-22-2007
On May 21, 11:23 pm, Dennis Roesler <(E-Mail Removed)> wrote:
> Paul Lalli wrote:
> > On May 21, 6:11 am, "(E-Mail Removed)"
> > <(E-Mail Removed)> wrote:
> >> 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

>
> 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

 
Reply With Quote
 
Paul Lalli
Guest
Posts: n/a
 
      05-22-2007
On May 21, 11:23 pm, Dennis Roesler <(E-Mail Removed)> wrote:
> Paul Lalli wrote:
> > On May 21, 6:11 am, "(E-Mail Removed)"
> > <(E-Mail Removed)> wrote:
> >> 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

>
> 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

 
Reply With Quote
 
xhoster@gmail.com
Guest
Posts: n/a
 
      05-22-2007
Dennis Roesler <(E-Mail Removed)> wrote:

>
> 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

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL ERROR while executing the code java.sql.SQLException: ORA-01008: not all variables bound mahesh Java 3 04-06-2010 10:59 AM
getting error while executing .sql file from sql task of ant naveenduttvyas Java 0 11-26-2008 09:23 AM
Issue in executing more than one perl script from Single perl script nilesh.sonawane@gmail.com Perl Misc 3 11-01-2007 06:19 PM
DBI SQL column datatype not jiving with SQL statement requirement dna Perl 1 01-18-2004 04:15 PM
Re: SQL statement working in SQL Server but not in .aspx.cs page William \(Bill\) Vaughn ASP .Net 0 08-21-2003 10:41 PM



Advertisments