Problem with placeholder

------_=_NextPart_001_01C5BA2C.0EDF79C2
Content-Type: text/plain

The following is an attempt to use a placeholder in an "in" clause...

my $placeholder = q{'} . join(q{','}, @accounts) . q{'};
$\ = "\n"; #set Output record separator
print " Given Group Id: $std_group";
print " Group Title is: $title";
print "Groups Accounts: $placeholder";
my $sth_budget = $dbh->prepare(q{select to_char(sum(budget_amount),
'$999,999,999.99') from t_customer_budget
where fiscal_year = to_date(?, 'dd-Mon-yyyy') and account_num in
(?)}
or &MyDieBatch('prepare failed', 80, __LINE__);
my $budget;
$sth_budget->bind_columns(\$budget)
or &MyDieBatch('bind failed', 80, __LINE__);
$sth_budget->execute($FY, $placeholder);
$sth_budget->fetch;

I have also tried:
my $placeholder = join(q{','}, @accounts);

thinking it would put the quotation marks at the beginning and end of the
"string" ...

Both of my attempts failed...

I can do a subquery in some instants but it is not appropriate in all
case...

Please suggest an appropriate method for the above. Thank you in advance for
any help.

John W Moon
Systems Project Analyst

Enterprise Information Technology Services (EITS)
Department of Management Services
4030 Esplanade Way, Suite 260G
Tallahassee, Fl 32399-0950
Office: (850)922-7511
Email: John.Moon@MyFlorida.com
Please note: Florida has a very broad public records law. Most written
communications to or from state officials regarding state business are
public records available to the public and media upon request. Your e-mail
communications may therefore be subject to public disclosure.



------_=_NextPart_001_01C5BA2C.0EDF79C2--

RE: Problem with placeholder

Moon, John [mailto:john.moon@myflorida.com] wrote:
>
> The following is an attempt to use a placeholder in an "in" clause...
>
> my $placeholder = q{'} . join(q{','}, @accounts) . q{'};
> $\ = "\n"; #set Output record separator
> print " Given Group Id: $std_group";
> print " Group Title is: $title";
> print "Groups Accounts: $placeholder";
> my $sth_budget = $dbh->prepare(q{select to_char(sum(budget_amount),
> '$999,999,999.99') from t_customer_budget
> where fiscal_year = to_date(?, 'dd-Mon-yyyy') and account_num in
> (?)}
> or &MyDieBatch('prepare failed', 80, __LINE__);
> my $budget;
> $sth_budget->bind_columns(\$budget)
> or &MyDieBatch('bind failed', 80, __LINE__);
> $sth_budget->execute($FY, $placeholder);
> $sth_budget->fetch;
>
> I have also tried:
> my $placeholder = join(q{','}, @accounts);
>
> thinking it would put the quotation marks at the beginning and end of the
> "string" ...

A placeholder takes the place of a *single* value. If you want to bind 7
values, you have to use 7 placeholders.

Here's one way to do it:

my $sth = $dbh->prepare(<<"EndOfSQL");
SELECT TO_CHAR(SUM(budget_amount), '$999,999,999.99')
FROM t_customer_budget
WHERE fiscal_year = TO_DATE(?, 'dd-Mon-yyyy')
AND account_num IN ( @{[ join ',', ('?') x @accounts ]} )
EndOfSQL

$sth->execute($FY, @accounts);


That uses the repetition operator (x) to create as many placeholders as
there are entries in the array.

Of course, you will need to prepare a new statement every time the number of
entries changes.


HTH,
Ronald

Re: Problem with placeholder

------=_Part_21209_9608463.1126815024981
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

>Of course, you will need to prepare a new statement every time the number=
=20
of
>entries changes.

unless you "cache" your statement handles that is:

unless (defined($sth[@accounts])){
$sth[@accounts] =3D $dbh->prepare(<<"EndOfSQL");
SELECT TO_CHAR(SUM(budget_amount), '$999,999,999.99')
FROM t_customer_budget
WHERE fiscal_year =3D TO_DATE(?, 'dd-Mon-yyyy')
AND account_num IN ( @{[ join ',', ('?') x @accounts ]} )
EndOfSQL
}

$sth[@accounts]->execute($FY, @accounts);





On 9/15/05, Ronald J Kimball wrote:
>=20
>=20
> Moon, John [mailto:john.moon@myflorida.com] wrote:
> >
> > The following is an attempt to use a placeholder in an "in" clause...
> >
> > my $placeholder =3D q{'} . join(q{','}, @accounts) . q{'};
> > $\ =3D "\n"; #set Output record separator
> > print " Given Group Id: $std_group";
> > print " Group Title is: $title";
> > print "Groups Accounts: $placeholder";
> > my $sth_budget =3D $dbh->prepare(q{select to_char(sum(budget_amount),
> > '$999,999,999.99') from t_customer_budget
> > where fiscal_year =3D to_date(?, 'dd-Mon-yyyy') and account_num in
> > (?)}
> > or &MyDieBatch('prepare failed', 80, __LINE__);
> > my $budget;
> > $sth_budget->bind_columns(\$budget)
> > or &MyDieBatch('bind failed', 80, __LINE__);
> > $sth_budget->execute($FY, $placeholder);
> > $sth_budget->fetch;
> >
> > I have also tried:
> > my $placeholder =3D join(q{','}, @accounts);
> >
> > thinking it would put the quotation marks at the beginning and end of=
=20
> the
> > "string" ...
>=20
> A placeholder takes the place of a *single* value. If you want to bind 7
> values, you have to use 7 placeholders.
>=20
> Here's one way to do it:
>=20
> my $sth =3D $dbh->prepare(<<"EndOfSQL");
> SELECT TO_CHAR(SUM(budget_amount), '$999,999,999.99')
> FROM t_customer_budget
> WHERE fiscal_year =3D TO_DATE(?, 'dd-Mon-yyyy')
> AND account_num IN ( @{[ join ',', ('?') x @accounts ]} )
> EndOfSQL
>=20
> $sth->execute($FY, @accounts);
>=20
>=20
> That uses the repetition operator (x) to create as many placeholders as
> there are entries in the array.
>=20
> Of course, you will need to prepare a new statement every time the number=
=20
> of
> entries changes.
>=20
>=20
> HTH,
> Ronald
>=20
>=20
>

------=_Part_21209_9608463.1126815024981--

RE: Problem with placeholder

You are using one "?" for many values. That won't work. You have two choices.

Insert as many "?" placeholders in the IN clause as you have @account entries.

Or,

Insert data without the "?" placeholder. In other words:
.... prepare(q{... account_num in (} . $placeholder . q{)...});
And remove $placeholder from the execute stmt.

There are pros and cons to each. For more info, consult this archived posting and associated postings in the thread...

http://www.mail-archive.com/dbi-users@perl.org/msg22789.html

-----Original Message-----
From: Moon, John [mailto:john.moon@myflorida.com]
Sent: Thursday, September 15, 2005 12:31 PM
To: 'dbi-users@perl.org'
Subject: Problem with placeholder

The following is an attempt to use a placeholder in an "in" clause...

my $placeholder = q{'} . join(q{','}, @accounts) . q{'};
$\ = "\n"; #set Output record separator
print " Given Group Id: $std_group";
print " Group Title is: $title";
print "Groups Accounts: $placeholder";
my $sth_budget = $dbh->prepare(q{select to_char(sum(budget_amount),
'$999,999,999.99') from t_customer_budget
where fiscal_year = to_date(?, 'dd-Mon-yyyy') and account_num in
(?)}
or &MyDieBatch('prepare failed', 80, __LINE__);
my $budget;
$sth_budget->bind_columns(\$budget)
or &MyDieBatch('bind failed', 80, __LINE__);
$sth_budget->execute($FY, $placeholder);
$sth_budget->fetch;

I have also tried:
my $placeholder = join(q{','}, @accounts);

thinking it would put the quotation marks at the beginning and end of the
"string" ...

Both of my attempts failed...

I can do a subquery in some instants but it is not appropriate in all
case...

Please suggest an appropriate method for the above. Thank you in advance for
any help.

John W Moon
Systems Project Analyst

Enterprise Information Technology Services (EITS)
Department of Management Services
4030 Esplanade Way, Suite 260G
Tallahassee, Fl 32399-0950
Office: (850)922-7511
Email: John.Moon@MyFlorida.com
Please note: Florida has a very broad public records law. Most written
communications to or from state officials regarding state business are
public records available to the public and media upon request. Your e-mail
communications may therefore be subject to public disclosure.

Re: Problem with placeholder

I don't think it's possible, at least with most (any?) database drivers,
to use a placeholder where the database expects a list (check out the
DBI man page, and look under "Placeholders and Bind Values" for a nice
discussion of that). One way to get around this would be to create a
temporary table to store your list in, and do a sub-select or join on
that table. You could also always re-prepare your statement and just
interpolate the list in perl, or have a separate placeholder for each
possible "in" value. There are probably lots of other ways to do it,
and which is best probably depends on how often the statement is used
after being prepared, whether you always have the same number of values
in the list, and how often the data you'd put in the list would change
significantly.

Jim Bosch


Moon, John wrote:

>The following is an attempt to use a placeholder in an "in" clause...
>
>my $placeholder = q{'} . join(q{','}, @accounts) . q{'};
>$\ = "\n"; #set Output record separator
>print " Given Group Id: $std_group";
>print " Group Title is: $title";
>print "Groups Accounts: $placeholder";
>my $sth_budget = $dbh->prepare(q{select to_char(sum(budget_amount),
>'$999,999,999.99') from t_customer_budget
> where fiscal_year = to_date(?, 'dd-Mon-yyyy') and account_num in
>(?)}
> or &MyDieBatch('prepare failed', 80, __LINE__);
>my $budget;
>$sth_budget->bind_columns(\$budget)
> or &MyDieBatch('bind failed', 80, __LINE__);
>$sth_budget->execute($FY, $placeholder);
>$sth_budget->fetch;
>
>I have also tried:
>my $placeholder = join(q{','}, @accounts);
>
>thinking it would put the quotation marks at the beginning and end of the
>"string" ...
>
>Both of my attempts failed...
>
>I can do a subquery in some instants but it is not appropriate in all
>case...
>
>Please suggest an appropriate method for the above. Thank you in advance for
>any help.
>
>John W Moon
>Systems Project Analyst
>
>Enterprise Information Technology Services (EITS)
>Department of Management Services
>4030 Esplanade Way, Suite 260G
>Tallahassee, Fl 32399-0950
>Office: (850)922-7511
>Email: John.Moon@MyFlorida.com
>Please note: Florida has a very broad public records law. Most written
>communications to or from state officials regarding state business are
>public records available to the public and media upon request. Your e-mail
>communications may therefore be subject to public disclosure.
>
>
>
>
>