how to allow variable number of argumets in mysql insert query

Help help help please

Iâ€=99m using DBD::mysql, where Insert query looks like:

____________________________________________________________ __=20
my $str=3D â€=9C?,?,?,,,â€=9D;=20
$q=3D$dbh->prepare("INSERT INTO $table VALUES($str)")=20
or die "Couldn't prepare statement: " . $dbh->errstr;=20

$q->execute($args1[0],$args1[1],$args1[2],â€=A6)=20
or die "Couldn't execute statement: " . $q->errstr;=20
____________________________________________________________ ____=20

THE PROBLEM:=20
If I want to change the number of arguments sent to execute(), I must chang=
e
the source code.=20
I would like to be able to change it on run-time with something like:=20


____________________________________________________________ __=20
$q->execute(f(@args1))=20
or die "Couldn't execute statement: " . $q->errstr;=20
____________________________________________________________ _=20

where f() formats the input to the function, and @args1 is allowed to have =
a
variable size.=20
This may add invaluable flexibility to my program, but how to do it ????.


thanks for your advices
A

--=20
View this message in context: http://www.nabble.com/how-to-allow-variable-n=
umber-of-argumets-in-mysql-insert-query-tp14399793p14399793. html
Sent from the MySQL - Perl mailing list archive at Nabble.com.


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: how to allow variable number of argumets in mysql insert query

Hi. This way will let u take advantage of prepare_cached and be totally
flexible. $dataref is a reference to a hash whose keys are the column
names and whose values are the field values to insert. $fieldref is an
array reference containing the column names i.e. hash keys.

sub insert {
my ($fieldref, $dataref) = @_ or return undef;
$handle = $dbh->prepare_cached( 'INSERT INTO `table` (`' .
join("`, `", @{$fieldref}) .
'`) VALUES (' .
join(", ", ("?") x scalar @{$fieldref}) .
')' );
$handle->execute(map {$dataref->{$_}} @{$fieldref})
or (print $dbh->errstr and return undef);
}

fungazid wrote:
>
> Help help help please
>
> I’m using DBD::mysql, where Insert query looks like:
>
> ____________________________________________________________ __
> my $str= “?,?,?,,,”;
> $q=$dbh->prepare("INSERT INTO $table VALUES($str)")
> or die "Couldn't prepare statement: " . $dbh->errstr;
>
> $q->execute($args1[0],$args1[1],$args1[2],…)
> or die "Couldn't execute statement: " . $q->errstr;
> ____________________________________________________________ ____
>
> THE PROBLEM:
> If I want to change the number of arguments sent to execute(), I must change
> the source code.
> I would like to be able to change it on run-time with something like:


--
Chris Wagner
CBTS
GE Aircraft Engines
Chris.Wagner@ae.ge.com

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: how to allow variable number of argumets in mysql insert query

Thanks Chris,

Using prepare_cached() is a good idea,=20
Does your function calls execute() with an array ? (I think mysql will
complain)
I found a simple that works perfectly for me, which is to use eval() !

$str=3D'$args[1],$args[2],$args[3],$args[4],$args[5],';
my $str2=3D'$q->execute('.$str.') or die "Couldn\'t execute statement: " .
$q->errstr';=20
eval $str2 or die "error!!!\n";=20




Wagner, Chris (GEAE, CBTS) wrote:
>=20
> Hi. This way will let u take advantage of prepare_cached and be totally
> flexible. $dataref is a reference to a hash whose keys are the column
> names and whose values are the field values to insert. $fieldref is an
> array reference containing the column names i.e. hash keys.
>=20
> sub insert {
> my ($fieldref, $dataref) =3D @_ or return undef;
> $handle =3D $dbh->prepare_cached( 'INSERT INTO `table` (`' .=20
> =09join("`, `", @{$fieldref}) .=20
> =09'`) VALUES (' .=20
> =09join(", ", ("?") x scalar @{$fieldref}) .=20
> =09')' );
> $handle->execute(map {$dataref->{$_}} @{$fieldref})=20
> =09or (print $dbh->errstr and return undef);
> }
>=20
> fungazid wrote:
>>=20
>> Help help help please
>>=20
>> I’m using DBD::mysql, where Insert query looks like:
>>=20
>> ____________________________________________________________ __
>> my $str=3D “?,?,?,,,”;
>> $q=3D$dbh->prepare("INSERT INTO $table VALUES($str)")
>> or die "Couldn't prepare statement: " . $dbh->errstr;
>>=20
>> $q->execute($args1[0],$args1[1],$args1[2],… )
>> or die "Couldn't execute statement: " . $q->errstr;
>> ____________________________________________________________ ____
>>=20
>> THE PROBLEM:
>> If I want to change the number of arguments sent to execute(), I must
>> change
>> the source code.
>> I would like to be able to change it on run-time with something like:
>=20
>=20
> --=20
> Chris Wagner
> CBTS
> GE Aircraft Engines
> Chris.Wagner@ae.ge.com
>=20
> --=20
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=3Dlists@nabble.com
>=20
>=20
>=20

--=20
View this message in context: http://www.nabble.com/how-to-allow-variable-n=
umber-of-argumets-in-mysql-insert-query-tp14399793p14416766. html
Sent from the MySQL - Perl mailing list archive at Nabble.com.


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: how to allow variable number of argumets in mysql insert query

Heh, that way may work, but it's horrid. :P My way does call execute
with a list value and MySQL nor Perl doesn't complain. I forgot to
mention that $fieldref should be a sorted list of the keys.
prepare_cached() needs the same order every time or it thinks it's a new
call.

fungazid wrote:
>
> Thanks Chris,
>
> Using prepare_cached() is a good idea,
> Does your function calls execute() with an array ? (I think mysql will
> complain)
> I found a simple that works perfectly for me, which is to use eval() !
>
> $str='$args[1],$args[2],$args[3],$args[4],$args[5],';
> my $str2='$q->execute('.$str.') or die "Couldn\'t execute statement: " .
> $q->errstr';
> eval $str2 or die "error!!!\n";


--
Chris Wagner
CBTS
GE Aircraft Engines
Chris.Wagner@ae.ge.com

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

RE: how to allow variable number of argumets in mysql insert query

> -----Original Message-----
> From: fungazid [mailto:fungazid@yahoo.com]
> Sent: Tuesday, December 18, 2007 9:57 AM
> To: perl@lists.mysql.com
> Subject: how to allow variable number of argumets in mysql insert query
>
> Help help help please
>
> I'm using DBD::mysql, where Insert query looks like:

Modules exist for this; SQL::Abstract is one.


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: how to allow variable number of argumets in mysql insert query

Chris,
you are a genius ! nothing less=20
Your function works as magic (even though the eval method is more simple to
implement)

I created a hash, hash keys, and called it with these lines:

my @outputFields1=3Dsort @outputFields;
$insert->insert(\@outputFields1,\%outputArgs);
=20
(just had to remove those â€=9C`â€=9D from your code, they bring c=
omplains )=20

But - Is there a way to know that prepare() doesnâ€=99t work more than =
once for
each table type during the run ?




Wagner, Chris (GEAE, CBTS) wrote:
>=20
>=20
> Hi. This way will let u take advantage of prepare_cached and be totally
> flexible. $dataref is a reference to a hash whose keys are the column
> names and whose values are the field values to insert. $fieldref is an
> array reference containing the column names i.e. hash keys.
>=20
> sub insert {
> my ($fieldref, $dataref) =3D @_ or return undef;
> $handle =3D $dbh->prepare_cached( 'INSERT INTO `table` (`' .=20
> =09join("`, `", @{$fieldref}) .=20
> =09'`) VALUES (' .=20
> =09join(", ", ("?") x scalar @{$fieldref}) .=20
> =09')' );
> $handle->execute(map {$dataref->{$_}} @{$fieldref})=20
> =09or (print $dbh->errstr and return undef);
> }
>=20
>=20

--=20
View this message in context: http://www.nabble.com/how-to-allow-variable-n=
umber-of-argumets-in-mysql-insert-query-tp14399793p14423828. html
Sent from the MySQL - Perl mailing list archive at Nabble.com.


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: how to allow variable number of argumets in mysql insert query

Glad to help. Prepare_cached should reuse the prepared statement for as
long as the database handle is alive. It will resuse the prepared
statement as long as the SQL query passed to it is identical.

fungazid wrote:
> But - Is there a way to know that prepare() doesn’t work more than once for
> each table type during the run ?


--
Chris Wagner
CBTS
GE Aircraft Engines
Chris.Wagner@ae.ge.com

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org