Calling a PostgreSQL function via DBI

Hello out there,
I just try to call a PostgreSQL-function via the DBI.
The function is very simple:
CREATE OR REPLACE FUNCTION neues_suchprofil ( NUMERIC, TEXT ) RETURNS void
AS $$
BEGIN
INSERT INTO suchprofile ( kid, spname ) VALUES ( $1, $2 );
END
$$ LANGUAGE plpgsql;

It is as you can see made in plpgsql. If I call this in psql it works:
SELECT neues_suchprofil ( 1::numeric , 'test'::text );
neues_suchprofil
------------------

(1 Zeile)

test2=#

So when I call this via DBI, I made it this way:

.....
$result = $dbh->prepare ( "SELECT neues_suchprofil ( $cookieValue::numeric,
'$suchprofil_name::text' ) " ) or die "Vorbereitung nicht durchfuehrbar!\n";
$result->execute() or die "Abfrage nicht ausfuehrbar -suchprofil!
$DBI::errstr\n";
$result->finish();
......

I got this error:
[Wed Feb 22 14:25:54 2006] suchprofil_uebernehmen.cgi: Abfrage nicht
ausfuehrbar -suchprofil! FEHLER: Fehler»Syntaxfehler« bei »,« at character
27

which means: suchprofil_uebernehmen.cgi: query not executable. Error
syntaxerror at >,< at char 27.

How do I have to cover this function to make a succesfull request?

Gruss Christian


--
Telefonieren Sie schon oder sparen Sie noch?
NEU: GMX Phone_Flat http://www.gmx.net/de/go/telefonie

Re: Calling a PostgreSQL function via DBI

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> $result = $dbh->prepare ( "SELECT neues_suchprofil ( $cookieValue::numeric,
> '$suchprofil_name::text' ) " ) or die "Vorbereitung nicht durchfuehrbar!\n";

You are mixing up your syntaxes: double-colons in Perl are not the same as double
colons in Postgres. Perl is treating $cookieValue::numeric as the variable
"numeric" in the package "cookieValue". What you need to do is either:

SELECT neues_suchprofil ( ${cookieValue}::numeric

or use placeholders:

SELECT neues_suchprofil ( ?::numeric

You might also want to make a single variable to pass to prepare, as it can
help in debugging:

my $COM = "SELECT neues_suchprofil ( $cookieValue::numeric, '$suchprofil_name::text' )";
$result = $dbh->prepare ($COM)
or die "Vorbereitung nicht durchfuehrbar! ($COM)\n";

Not only does it make the code easier to read, but your error message would have
shown you that those variables were not getting set as you thought they were.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200602220909
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFD/HFEvJuQZxSWSsgRAkG7AKDoqzOxjg6OSpcDp/x9DYUwMVCNMgCf RwsD
flkczHWCUXi5If3sWW8Ee3I=
=LIPT
-----END PGP SIGNATURE-----

Re: Calling a PostgreSQL function via DBI

Thank you Greg,
I allready solf it. First I changed the code this way:
my $arg1 = $cookieValue . "::numeric";
my $arg2 = $suchprofil_name . "::text";

$result = $dbh->prepare ( "SELECT neues_suchprofil ( $arg1, '$arg2' ) " ) or
die "Vorbereitung nicht durchfuehrbar!\n";
$result->execute() or die "Abfrage nicht ausfuehrbar -suchprofil!
$DBI::errstr\n";
$result->finish();

And then I grand some priviliges on the user, which access the DBI. Now it
works :-)

Gruss Christian

--
DSL-Aktion wegen großer Nachfrage bis 28.2.2006 verlängert:
GMX DSL-Flatrate 1 Jahr kostenlos* http://www.gmx.net/de/go/dsl

Re: Calling a PostgreSQL function via DBI

> Thank you Greg,
> I allready solf it. First I changed the code this way:
> my $arg1 = $cookieValue . "::numeric";
> my $arg2 = $suchprofil_name . "::text";
>
> $result = $dbh->prepare ( "SELECT neues_suchprofil ( $arg1, '$arg2' ) " )
> or
> die "Vorbereitung nicht durchfuehrbar!\n";
> $result->execute() or die "Abfrage nicht ausfuehrbar -suchprofil!
> $DBI::errstr\n";
> $result->finish();
>

And here comes an update:

$arg1 = $cookieValue . "::numeric";

$result = $dbh->prepare ( "SELECT neues_suchprofil ( $arg1,
'$suchprofil_name' ) " ) or die "Vorbereitung nicht durchfuehrbar!\n";
otherwise I put the name concated with the '::text' into the database, and
this is not what I want ;-)

Thank you.

Gruss Christian

--
Telefonieren Sie schon oder sparen Sie noch?
NEU: GMX Phone_Flat http://www.gmx.net/de/go/telefonie

Re: Calling a PostgreSQL function via DBI

You should not interpolate variables into the SQL statement, especially
not when you work in a CGI environment (or similar). You are begging for
big trouble. Learn what SQL injection means, and avoid it like hell.

Use:
use $dbh->prepare('SELECT neues_suchprofil (?::numeric,?::text)'); #
single quotes -> no interpolation, placeholders instead of interpolation
$dbh->execute($cookieValue,$suchprofil_name); # replacing placeholders
in the database.

Alexander

Christian Stalp wrote:

>Thank you Greg,
>I allready solf it. First I changed the code this way:
>my $arg1 = $cookieValue . "::numeric";
>my $arg2 = $suchprofil_name . "::text";
>
>$result = $dbh->prepare ( "SELECT neues_suchprofil ( $arg1, '$arg2' ) " ) or
>die "Vorbereitung nicht durchfuehrbar!\n";
>$result->execute() or die "Abfrage nicht ausfuehrbar -suchprofil!
>$DBI::errstr\n";
>$result->finish();
>
>And then I grand some priviliges on the user, which access the DBI. Now it
>works :-)
>
>Gruss Christian
>
>
>


--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/

Re: Calling a PostgreSQL function via DBI

Back to the problem with my plpgsql-procedures. I have another problem with
another procedure, with even more aguments:

$arg1 = $cookieValue . "::numeric";
$arg2 = $ueberschrift . "::text";
$arg3 = $beschreibung . "::text";
$arg4 = $system_zeit . "::timestamp";
$arg5 = "2001-11-11 11:11:11" . "::timestamp";
$arg6 = $startpreis . "::numeric";
$arg7 = $startpreis . "::numeric";
$arg8 = $kategorie_nummer . "::numeric";


$result = $dbh->prepare ( "SELECT neue_auktion ( $arg1, '$arg2', '$arg3',
'$arg4', '$arg5', $arg6, $arg7, $arg8 ) " ) or die "Vorbereitung nicht
durchfuehrbar!\n";
$result->execute() or die "Abfrage nicht ausfuehrbar -suchprofil!
$DBI::errstr\n";
$result->finish();

I know, shouldn't do it with interpolate variables but how ever it should
work. The error-dump I get with this function is:

"Fehler »Syntaxfehler« bei »::« at character 123" which means:
Syntaxerror at >>::<< at character 123

And how I can avoid interpolate variables? Is it simmular to this:
$dbh->do( qq{INSERT INTO kunden ( kid, nachname, vorname, strasse, plz,
email, wohnort, bankid, kontonr) VALUES(?,?,?,?,?,?,?,?,?)},
undef,
$user_nummer,
$nachname,
$vorname,
$strasse,
$plz,
$email,
$wohnort,
$bankid,
$kontonummer
) or die "Kann nicht eintragen kunden\n";

Thank you.

Gruss Christian

--
DSL-Aktion wegen großer Nachfrage bis 28.2.2006 verlängert:
GMX DSL-Flatrate 1 Jahr kostenlos* http://www.gmx.net/de/go/dsl

Re: Calling a PostgreSQL function via DBI

--------------060303060003090207080405
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Christian Stalp wrote:

>Back to the problem with my plpgsql-procedures. I have another problem with
>another procedure, with even more aguments:
>
>$arg1 = $cookieValue . "::numeric";
>$arg2 = $ueberschrift . "::text";
>$arg3 = $beschreibung . "::text";
>$arg4 = $system_zeit . "::timestamp";
>$arg5 = "2001-11-11 11:11:11" . "::timestamp";
>$arg6 = $startpreis . "::numeric";
>$arg7 = $startpreis . "::numeric";
>$arg8 = $kategorie_nummer . "::numeric";
>
>
>$result = $dbh->prepare ( "SELECT neue_auktion ( $arg1, '$arg2', '$arg3',
>'$arg4', '$arg5', $arg6, $arg7, $arg8 ) " ) or die "Vorbereitung nicht
>durchfuehrbar!\n";
>$result->execute() or die "Abfrage nicht ausfuehrbar -suchprofil!
>$DBI::errstr\n";
>$result->finish();
>
>I know, shouldn't do it with interpolate variables but how ever it should
>work. The error-dump I get with this function is:
>
>"Fehler »Syntaxfehler« bei »::« at character 123" which means:
>Syntaxerror at >>::<< at character 123
>
>And how I can avoid interpolate variables? Is it simmular to this:
>$dbh->do( qq{INSERT INTO kunden ( kid, nachname, vorname, strasse, plz,
>email, wohnort, bankid, kontonr) VALUES(?,?,?,?,?,?,?,?,?)},
> undef,
> $user_nummer,
> $nachname,
> $vorname,
> $strasse,
> $plz,
> $email,
> $wohnort,
> $bankid,
> $kontonummer
> ) or die "Kann nicht eintragen kunden\n";
>
>Thank you.
>
>Gruss Christian
>
>
>
Christian,
As an example, I'd do the following:

$arg1 = "$cookieValue" . "::numeric";
....

$result = $dbh->prepare ( "insert into kunden ( kid, nachname, ..., ) values ( ?, ?, ... )" );

$sqls->bind_param(1, $user_nummer);
$sqls->bind_param(2, $nachname);
....

$sqls->execute();
$sqls->finish();

That will take care of your inserting issues. the "bind_param()" function takes care of the variables and puts them in a 'proper' form.


As for your select statement with the $arg1, ..., $argN, the bind_param() function will work on those arguments too, I'd just suggest to use the process of double quoting your string concatenation assignments upfront:

$arg1 = "$someVariable" . "::someText";

Hope this helps.






--------------060303060003090207080405--

RE: Calling a PostgreSQL function via DBI

Louis, I'm sure the construct you give below has no effect in Perl.
"$someVariable" is identical to $someVariable.

Perhaps what you meant was
$arg1 =3D $dbh->quote( $someVariable ) . "::someText";

-Will


-----Original Message-----
From: Louis Gonzales [mailto:louis.gonzales@linuxlouis.net]=20
Sent: Thursday 23 February 2006 10:28
To: Christian Stalp
Cc: dbi-users@perl.org; alexander@foken.de
Subject: Re: Calling a PostgreSQL function via DBI


As for your select statement with the $arg1, ..., $argN, the
bind_param() function will work on those arguments too, I'd just suggest
to use the process of double quoting your string concatenation
assignments upfront:

$arg1 =3D "$someVariable" . "::someText";


- - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - - =20
This e-mail and any attachments may contain information which is confidenti=
al, proprietary, privileged or otherwise protected by law. The information =
is solely intended for the named addressee (or a person responsible for del=
ivering it to the addressee). If you are not the intended recipient of this=
message, you are not authorized to read, print, retain, copy or disseminat=
e this message or any part of it. If you have received this e-mail in error=
, please notify the sender immediately by return e-mail and delete it from =
your computer.

Re: Calling a PostgreSQL function via DBI

--------------050608030002090102050600
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Rutherdale, Will wrote:

>Louis, I'm sure the construct you give below has no effect in Perl.
>"$someVariable" is identical to $someVariable.
>
>Perhaps what you meant was
>$arg1 = $dbh->quote( $someVariable ) . "::someText";
>
>-Will
>
>
>-----Original Message-----
>From: Louis Gonzales [mailto:louis.gonzales@linuxlouis.net]
>Sent: Thursday 23 February 2006 10:28
>To: Christian Stalp
>Cc: dbi-users@perl.org; alexander@foken.de
>Subject: Re: Calling a PostgreSQL function via DBI
>
>
>As for your select statement with the $arg1, ..., $argN, the
>bind_param() function will work on those arguments too, I'd just suggest
>to use the process of double quoting your string concatenation
>assignments upfront:
>
>$arg1 = "$someVariable" . "::someText";
>
>
> - - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - -
>This e-mail and any attachments may contain information which is confidential, proprietary, privileged or otherwise protected by law. The information is solely intended for the named addressee (or a person responsible for delivering it to the addressee). If you are not the intended recipient of this message, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this e-mail in error, please notify the sender immediately by return e-mail and delete it from your computer.
>
>
>
Will,
No, I meant exactly what I wrote. The quoting of the "$someVariable" in
string concatenation is a preference when using the binary "."
operator. The "" around the "$someVariable", strictly speaking do the
obvious interpolation. I don't think it's necessary to go down the
multitude of ways to write the string concatenation in PERL, which is
not the topic of this thread.

As for your suggestion of $dbh->quote(...) . "..."
if that works, GREAT, if you look further down the message, you'll see
that I address the 'properness' of the variables with bind_param() which
works beautifully with runtime parameter binding, to be used with the
"?"'s in the SQL statements.

In short, did you have a full example or suggestion for Christian? Or
did you want to discuss the multitude of ways to do the same thing?

--------------050608030002090102050600--

Re: Calling a PostgreSQL function via DBI

> Christian,
> As an example, I'd do the following:
>
> $arg1 = "$cookieValue" . "::numeric";
> ...
>
> $result = $dbh->prepare ( "insert into kunden ( kid, nachname, ..., )
> values ( ?, ?, ... )" );
>
> $sqls->bind_param(1, $user_nummer);
> $sqls->bind_param(2, $nachname);
> ...
>
> $sqls->execute();
> $sqls->finish();
>
> That will take care of your inserting issues. the "bind_param()" function
> takes care of the variables and puts them in a 'proper' form.
>
>
> As for your select statement with the $arg1, ..., $argN, the bind_param()
> function will work on those arguments too, I'd just suggest to use the
> process of double quoting your string concatenation assignments upfront:
>
> $arg1 = "$someVariable" . "::someText";
>
> Hope this helps.

No, the question here is to call a self-defined PLpgsql-function.
This is the function:
CREATE OR REPLACE FUNCTION neue_auktion ( numeric, text, text, TIMESTAMP,
TIMESTAMP, numeric, numeric, numeric ) RETURNS text AS $$
DECLARE
my_kunden_id ALIAS FOR $1;
my_name ALIAS FOR $2;
my_beschreibung ALIAS FOR $3;
my_startzeit ALIAS FOR $4;
my_endzeit ALIAS FOR $5;
my_startpreis ALIAS FOR $6;
my_preis ALIAS FOR $7;
my_kategorie ALIAS FOR $8;

BEGIN

INSERT INTO auktionen ( kid, name, beschreibung, startzeit, endzeit,
startpreis, preis, katid ) VALUES ( my_kunden_id, my_name, my_beschreibung,
my_startzeit, my_endzeit, my_startpreis, my_startpreis, my_kategorie );

RETURN 'OK';
END;
$$
LANGUAGE plpgsql;

if a call it on the psql-shell, I do it with this syntax:
SELECT neue_auktion ( 1::numeric, 'robot'::text, 'robot'::text, '1999-01-08
04:05:06'::timestamp, '1999-01-08 04:05:06'::timestamp, 10::numeric,
10::numeric, 1::numeric);

this works.

But in perl there is something that doesn't fit. I try it now this way:
$arg1 = $cookieValue . "::numeric";
$arg2 = "$ueberschrift" . "::text";
$arg3 = "$beschreibung" . "::text";
$arg4 = "$system_zeit" . "::timestamp";
$arg5 = "2001-11-11 11:11:11" . "::timestamp";
$arg6 = "$startpreis" . "::numeric";
$arg7 = "$startpreis" . "::numeric";
$arg8 = "$kategorie_nummer" . "::numeric";


$result = $dbh->prepare ( "SELECT neue_auktion ( $arg1, '$arg2', '$arg3',
'$arg4', '$arg5', $arg6, $arg7, $arg8 ) " ) or die "Vorbereitung nicht
durchfuehrbar!\n";
$result->execute() or die "Abfrage nicht ausfuehrbar -suchprofil!
$DBI::errstr\n";
$result->finish();

But there is still a error-dump which "invalid syntax for Typ timestamp:
»2000-01-01 08:08:23::timestamp«

I tried it also without brackets for the time-values:
$arg4 = $system_zeit . "::timestamp";
$arg5 = 2001-11-11 11:11:11 . "::timestamp";

But this has the same effect.

Gruss Christian

--
Lust, ein paar Euro nebenbei zu verdienen? Ohne Kosten, ohne Risiko!
Satte Provisionen für GMX Partner: http://www.gmx.net/de/go/partner

RE: Calling a PostgreSQL function via DBI

This gets back to the point of my earlier posting.

Louis's suggestion of using bind_param() is probably correct and the prefer=
red way of doing things.

However, his alternative solution with, for instance,
$arg4 =3D "$system_zeit" . "::timestamp";
does not work, because putting quotes around the variable ("$system_zeit") =
does *not* produce a string with quote marks in it. This is elementary Per=
l syntax. And it is very easy to verify with a test program.

If you want quotes, use $dbh->quote() as I suggested in the earlier posting:
$arg4 =3D $dbh->quote( $system_zeit ) . "::timestamp";

-Will

-----Original Message-----
From: Christian Stalp [mailto:christian.stalp@gmx.de]=20
Sent: Thursday 23 February 2006 15:09
To: dbi-users@perl.org
Subject: Re: Calling a PostgreSQL function via DBI



> Christian,
> As an example, I'd do the following:
>=20
> $arg1 =3D "$cookieValue" . "::numeric";
> ...
>=20
> $result =3D $dbh->prepare ( "insert into kunden ( kid, nachname, ..., )
> values ( ?, ?, ... )" );
>=20
> $sqls->bind_param(1, $user_nummer);
> $sqls->bind_param(2, $nachname);
> ...
>=20
> $sqls->execute();
> $sqls->finish();
>=20
> That will take care of your inserting issues. the "bind_param()" function
> takes care of the variables and puts them in a 'proper' form.
>=20
>=20
> As for your select statement with the $arg1, ..., $argN, the bind_param()
> function will work on those arguments too, I'd just suggest to use the
> process of double quoting your string concatenation assignments upfront:
>=20
> $arg1 =3D "$someVariable" . "::someText";
>=20
> Hope this helps.

No, the question here is to call a self-defined PLpgsql-function.
This is the function:
CREATE OR REPLACE FUNCTION neue_auktion ( numeric, text, text, TIMESTAMP,
TIMESTAMP, numeric, numeric, numeric ) RETURNS text AS $$
DECLARE
my_kunden_id ALIAS FOR $1;
my_name ALIAS FOR $2;
my_beschreibung ALIAS FOR $3;
my_startzeit ALIAS FOR $4;
my_endzeit ALIAS FOR $5;
my_startpreis ALIAS FOR $6;
my_preis ALIAS FOR $7;
my_kategorie ALIAS FOR $8;
=20
BEGIN
=20
INSERT INTO auktionen ( kid, name, beschreibung, startzeit, endzeit,
startpreis, preis, katid ) VALUES ( my_kunden_id, my_name, my_beschreibung,
my_startzeit, my_endzeit, my_startpreis, my_startpreis, my_kategorie );

RETURN 'OK';
END;
$$
LANGUAGE plpgsql;

if a call it on the psql-shell, I do it with this syntax:
SELECT neue_auktion ( 1::numeric, 'robot'::text, 'robot'::text, '1999-01-08
04:05:06'::timestamp, '1999-01-08 04:05:06'::timestamp, 10::numeric,
10::numeric, 1::numeric);

this works.

But in perl there is something that doesn't fit. I try it now this way:
$arg1 =3D $cookieValue . "::numeric";
$arg2 =3D "$ueberschrift" . "::text";
$arg3 =3D "$beschreibung" . "::text";
$arg4 =3D "$system_zeit" . "::timestamp";
$arg5 =3D "2001-11-11 11:11:11" . "::timestamp";
$arg6 =3D "$startpreis" . "::numeric";
$arg7 =3D "$startpreis" . "::numeric";
$arg8 =3D "$kategorie_nummer" . "::numeric";


$result =3D $dbh->prepare ( "SELECT neue_auktion ( $arg1, '$arg2', '$arg3',
'$arg4', '$arg5', $arg6, $arg7, $arg8 ) " ) or die "Vorbereitung nicht
durchfuehrbar!\n";
$result->execute() or die "Abfrage nicht ausfuehrbar -suchprofil!
$DBI::errstr\n";
$result->finish();

But there is still a error-dump which "invalid syntax for Typ timestamp:
=BB2000-01-01 08:08:23::timestamp=AB

I tried it also without brackets for the time-values:
$arg4 =3D $system_zeit . "::timestamp";
$arg5 =3D 2001-11-11 11:11:11 . "::timestamp";

But this has the same effect.

Gruss Christian

--=20
Lust, ein paar Euro nebenbei zu verdienen? Ohne Kosten, ohne Risiko!
Satte Provisionen für GMX Partner: http://www.gmx.net/de/go/partner


- - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - - =20
This e-mail and any attachments may contain information which is confidenti=
al, proprietary, privileged or otherwise protected by law. The information =
is solely intended for the named addressee (or a person responsible for del=
ivering it to the addressee). If you are not the intended recipient of this=
message, you are not authorized to read, print, retain, copy or disseminat=
e this message or any part of it. If you have received this e-mail in error=
, please notify the sender immediately by return e-mail and delete it from =
your computer.

Re: Calling a PostgreSQL function via DBI

--------------040502040706070605050804
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Below in RED, is the post that I addressed with my initial comments,
nowhere in there is there anything about "CREATE"'ing any fuctions
whatsoever.
Below in BLUE, is what was just put out by Christian, can somebody point
out to me where the "new" issue of creating fuctions came into play?

And again, in now a third attempt to clarify to Will, nowhere did I
state that double quoting around a variable in PERL, is going to produce
a string with quote marks????????? Please Will, pay attention to what
I've written/write and don't paraphrase/quote me, because you are taking
everything I write, out of context and adding your own invalid ideas to
them.

I think at this point, Christian needs to specify better what the real
issue is, or if there are several, to identify those concisely, rather
then clumping several issues together as one.



Christian Stalp wrote:

>Back to the problem with my plpgsql-procedures. I have another problem with
>another procedure, with even more aguments:
>
>$arg1 = $cookieValue . "::numeric";
>$arg2 = $ueberschrift . "::text";
>$arg3 = $beschreibung . "::text";
>$arg4 = $system_zeit . "::timestamp";
>$arg5 = "2001-11-11 11:11:11" . "::timestamp";
>$arg6 = $startpreis . "::numeric";
>$arg7 = $startpreis . "::numeric";
>$arg8 = $kategorie_nummer . "::numeric";
>
>
>$result = $dbh->prepare ( "SELECT neue_auktion ( $arg1, '$arg2', '$arg3',
>'$arg4', '$arg5', $arg6, $arg7, $arg8 ) " ) or die "Vorbereitung nicht
>durchfuehrbar!\n";
>$result->execute() or die "Abfrage nicht ausfuehrbar -suchprofil!
>$DBI::errstr\n";
>$result->finish();
>
>I know, shouldn't do it with interpolate variables but how ever it should
>work. The error-dump I get with this function is:
>
>"Fehler »Syntaxfehler« bei »::« at character 123" which means:
>Syntaxerror at >>::<< at character 123
>
>And how I can avoid interpolate variables? Is it simmular to this:
>$dbh->do( qq{INSERT INTO kunden ( kid, nachname, vorname, strasse, plz,
>email, wohnort, bankid, kontonr) VALUES(?,?,?,?,?,?,?,?,?)},
> undef,
> $user_nummer,
> $nachname,
> $vorname,
> $strasse,
> $plz,
> $email,
> $wohnort,
> $bankid,
> $kontonummer
> ) or die "Kann nicht eintragen kunden\n";
>
>Thank you.
>
>Gruss Christian
>
>

This gets back to the point of my earlier posting.

Louis's suggestion of using bind_param() is probably correct and the preferred way of doing things.

However, his alternative solution with, for instance,
$arg4 = "$system_zeit" . "::timestamp";
does not work, because putting quotes around the variable ("$system_zeit") does *not* produce a string with quote marks in it. This is elementary Perl syntax. And it is very easy to verify with a test program.

If you want quotes, use $dbh->quote() as I suggested in the earlier posting:
$arg4 = $dbh->quote( $system_zeit ) . "::timestamp";

-Will

-----Original Message-----
From: Christian Stalp [mailto:christian.stalp@gmx.de]
Sent: Thursday 23 February 2006 15:09
To: dbi-users@perl.org
Subject: Re: Calling a PostgreSQL function via DBI




>> Christian,
>> As an example, I'd do the following:
>>
>> $arg1 = "$cookieValue" . "::numeric";
>> ...
>>
>> $result = $dbh->prepare ( "insert into kunden ( kid, nachname, ..., )
>> values ( ?, ?, ... )" );
>>
>> $sqls->bind_param(1, $user_nummer);
>> $sqls->bind_param(2, $nachname);
>> ...
>>
>> $sqls->execute();
>> $sqls->finish();
>>
>> That will take care of your inserting issues. the "bind_param()" function
>> takes care of the variables and puts them in a 'proper' form.
>>
>>
>> As for your select statement with the $arg1, ..., $argN, the bind_param()
>> function will work on those arguments too, I'd just suggest to use the
>> process of double quoting your string concatenation assignments upfront:
>>
>> $arg1 = "$someVariable" . "::someText";
>>
>> Hope this helps.
>

No, the question here is to call a self-defined PLpgsql-function.
This is the function:
CREATE OR REPLACE FUNCTION neue_auktion ( numeric, text, text, TIMESTAMP,
TIMESTAMP, numeric, numeric, numeric ) RETURNS text AS $$
DECLARE
my_kunden_id ALIAS FOR $1;
my_name ALIAS FOR $2;
my_beschreibung ALIAS FOR $3;
my_startzeit ALIAS FOR $4;
my_endzeit ALIAS FOR $5;
my_startpreis ALIAS FOR $6;
my_preis ALIAS FOR $7;
my_kategorie ALIAS FOR $8;

BEGIN

INSERT INTO auktionen ( kid, name, beschreibung, startzeit, endzeit,
startpreis, preis, katid ) VALUES ( my_kunden_id, my_name, my_beschreibung,
my_startzeit, my_endzeit, my_startpreis, my_startpreis, my_kategorie );

RETURN 'OK';
END;
$$
LANGUAGE plpgsql;

if a call it on the psql-shell, I do it with this syntax:
SELECT neue_auktion ( 1::numeric, 'robot'::text, 'robot'::text, '1999-01-08
04:05:06'::timestamp, '1999-01-08 04:05:06'::timestamp, 10::numeric,
10::numeric, 1::numeric);

this works.

But in perl there is something that doesn't fit. I try it now this way:
$arg1 = $cookieValue . "::numeric";
$arg2 = "$ueberschrift" . "::text";
$arg3 = "$beschreibung" . "::text";
$arg4 = "$system_zeit" . "::timestamp";
$arg5 = "2001-11-11 11:11:11" . "::timestamp";
$arg6 = "$startpreis" . "::numeric";
$arg7 = "$startpreis" . "::numeric";
$arg8 = "$kategorie_nummer" . "::numeric";


$result = $dbh->prepare ( "SELECT neue_auktion ( $arg1, '$arg2', '$arg3',
'$arg4', '$arg5', $arg6, $arg7, $arg8 ) " ) or die "Vorbereitung nicht
durchfuehrbar!\n";
$result->execute() or die "Abfrage nicht ausfuehrbar -suchprofil!
$DBI::errstr\n";
$result->finish();

But there is still a error-dump which "invalid syntax for Typ timestamp:
»2000-01-01 08:08:23::timestamp«

I tried it also without brackets for the time-values:
$arg4 = $system_zeit . "::timestamp";
$arg5 = 2001-11-11 11:11:11 . "::timestamp";

But this has the same effect.

Gruss Christian

-- Lust, ein paar Euro nebenbei zu verdienen? Ohne Kosten, ohne Risiko!
Satte Provisionen für GMX Partner: http://www.gmx.net/de/go/partner - -
- - - - - Appended by Scientific-Atlanta, Inc. - - - - - - - This e-mail
and any attachments may contain information which is confidential,
proprietary, privileged or otherwise protected by law. The information
is solely intended for the named addressee (or a person responsible for
delivering it to the addressee). If you are not the intended recipient
of this message, you are not authorized to read, print, retain, copy or
disseminate this message or any part of it. If you have received this
e-mail in error, please notify the sender immediately by return e-mail
and delete it


--------------040502040706070605050804--

Re: Calling a PostgreSQL function via DBI

> >
> Christian,
> As an example, I'd do the following:
>
> $arg1 = "$cookieValue" . "::numeric";
> ...
>
> $result = $dbh->prepare ( "insert into kunden ( kid, nachname, ..., )
> values ( ?, ?, ... )" );
>
> $sqls->bind_param(1, $user_nummer);
> $sqls->bind_param(2, $nachname);
> ...
>
> $sqls->execute();
> $sqls->finish();
>
> That will take care of your inserting issues. the "bind_param()" function
> takes care of the variables and puts them in a 'proper' form.
>
>
> As for your select statement with the $arg1, ..., $argN, the bind_param()
> function will work on those arguments too, I'd just suggest to use the
> process of double quoting your string concatenation assignments upfront:
>
> $arg1 = "$someVariable" . "::someText";
>
> Hope this helps.
>

Hello Lois,
$sqls->bind_param(1, $user_nummer);
$sqls->bind_param(2, $nachname);

What kind of object is this, $sqls? Or is it just another DBI-handler?

Gruss Christian


--
DSL-Aktion wegen großer Nachfrage bis 28.2.2006 verlängert:
GMX DSL-Flatrate 1 Jahr kostenlos* http://www.gmx.net/de/go/dsl

Re: Calling a PostgreSQL function via DBI

ah I see, in my case is it $result:
$result = $dbh->prepare ( "SELECT neue_auktion ( ?, ?, ?, ?, ?, ?, ?, ? ) "
) or die "Vorbereitung nicht durchfuehrbar!\n";
$result->bind_param ( 1, $arg1 );
$result->bind_param ( 2, $arg2 );
$result->bind_param ( 3, $arg3 );
$result->bind_param ( 4, $arg4 );
$result->bind_param ( 5, $arg5 );
$result->bind_param ( 6, $arg6 );
$result->bind_param ( 7, $arg7 );
$result->bind_param ( 8, $arg8 );
$result->execute() or die "Abfrage nicht ausfuehrbar -suchprofil!
$DBI::errstr\n";
$result->finish();

--
Telefonieren Sie schon oder sparen Sie noch?
NEU: GMX Phone_Flat http://www.gmx.net/de/go/telefonie

Re: Calling a PostgreSQL function via DBI

> Halo Christian,
> $sqls is the object returned by the prepare() method, as in:
>
> my $sqls = $dbh->prepare( "insert into table $table ( userNumber,
> lastName) values ( ?, ?)" );
>
> $sqls->bind_param(1, $user_nummer);
> $sqls->bind_param(2, $nachname);
>
> but, if i understand your last message correctly, the problem is with
> only the timestamp variable, that you are setting?
>
>
>

Hello Louis,
yes i seems to be so. I tried it with differnt versions.
Now this is where I stand:
$arg1 = $cookieValue;
$arg2 = $dbh->quote ( $ueberschrift ) . "::text";
$arg3 = $dbh->quote ( $beschreibung ) . "::text";
$arg4 = $dbh->quote ( $system_zeit ) . "::timestamp";
$arg5 = $dbh->quote ( "2001-11-11 11:11:11" ) . "::timestamp";
$arg6 = $startpreis;
$arg7 = $startpreis;
$arg8 = $kategorie_nummer;


$result = $dbh->prepare ( "SELECT neue_auktion ( ?, ?, ?, ?, ?, ?, ?, ? ) "
) or die "Vorbereitung nicht durchfuehrbar!\n";
$result->bind_param ( 1, $arg1 );
$result->bind_param ( 2, $arg2 );
$result->bind_param ( 3, $arg3 );
$result->bind_param ( 4, $arg4 );
$result->bind_param ( 5, $arg5 );
$result->bind_param ( 6, $arg6 );
$result->bind_param ( 7, $arg7 );
$result->bind_param ( 8, $arg8 );
$result->execute() or die "Abfrage nicht ausfuehrbar -suchprofil!
$DBI::errstr\n";
$result->finish();

And now I get this error again, that the argument 'startzeit' (english
start-time) is of type timestamp without time zone but the expression has
the type text. "Hint: you should make a typecast", but this is what I'm
doing all the time.

Gruss Christian

--
Telefonieren Sie schon oder sparen Sie noch?
NEU: GMX Phone_Flat http://www.gmx.net/de/go/telefonie

RE: Calling a PostgreSQL function via DBI

Louis,

Perhaps you could attempt to explain, politely this time, what you meant
by the following.

> As for your select statement with the $arg1, ..., $argN, the
bind_param() function will work=20
> on those arguments too, I'd just suggest to use the process of double
quoting your string=20
> concatenation assignments upfront:
>
> $arg1 =3D "$someVariable" . "::someText";

What exactly do you believe adding the double quotes does to alter the
string value stored in $arg1? How is it any different from the
following:
$arg1 =3D $someVariable . "::someText";

Please spare us the rudeness while you're at it and just stick to the
technical issues.

-Will

-----Original Message-----
From: Louis Gonzales [mailto:louis.gonzales@linuxlouis.net]=20
Sent: Thursday 23 February 2006 15:47
To: Christian Stalp
Cc: dbi-users@perl.org; alexander@foken.de
Subject: Re: Calling a PostgreSQL function via DBI


And again, in now a third attempt to clarify to Will, nowhere did I=20
state that double quoting around a variable in PERL, is going to produce

a string with quote marks????????? Please Will, pay attention to what=20
I've written/write and don't paraphrase/quote me, because you are taking

everything I write, out of context and adding your own invalid ideas to=20
them.


- - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - - =20
This e-mail and any attachments may contain information which is confidenti=
al, proprietary, privileged or otherwise protected by law. The information =
is solely intended for the named addressee (or a person responsible for del=
ivering it to the addressee). If you are not the intended recipient of this=
message, you are not authorized to read, print, retain, copy or disseminat=
e this message or any part of it. If you have received this e-mail in error=
, please notify the sender immediately by return e-mail and delete it from =
your computer.

Re: Calling a PostgreSQL function via DBI

> Based on what you have here, this will bind all of the "arg" variables
> with the respective 'positional' "?"s I'd even venture to say, you can
> put a little loop on a single bind_param( $i, $arg$i ); where your:
> i = index counter # haven't tried that yet, but think it would work.
>
Thats nice style, I like it. But first it should work anyway.

--
Telefonieren Sie schon oder sparen Sie noch?
NEU: GMX Phone_Flat http://www.gmx.net/de/go/telefonie

Re: Calling a PostgreSQL function via DBI

--------------040801050305060204050508
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Thank you Will, I'll opt for discontinuing this discussion with you. I
apologize to the dbi-user community in general.

Sincerely,

--------------040801050305060204050508--

Re: Calling a PostgreSQL function via DBI

> Ja Christian,
> Ich verstehe ein bischen Deutsch, besonders "startzeit" :) Ich habe ein
> buch ueber des Lebens Albert Schweizer gelesen, so ich kann 'basic'
> Duetsch verstehen.

Wow, not bad. And you learned it just with this one book?
>
> From one of the earlier emails I saw, your variable needs to be:
> '2001-11-11 11:11:11'::timestamp
> Is that how it needs to go into your db table?
>
Thats right, that is the way how the psql-shell accepted it:
SELECT neue_auktion ( 1::numeric, 'robot'::text, 'robot'::text, '1999-01-08
04:05:06'::timestamp, '1999-01-08 04:05:06'::timestamp, 10::numeric,
10::numeric, 1::numeric);

I think that the numeric 'type-cast' are redundant, but I put it in.

Gruss Christian

--
Lust, ein paar Euro nebenbei zu verdienen? Ohne Kosten, ohne Risiko!
Satte Provisionen für GMX Partner: http://www.gmx.net/de/go/partner

RE: Calling a PostgreSQL function via DBI

Christian Stalp [mailto:christian.stalp@gmx.de] wrote:

> yes i seems to be so. I tried it with differnt versions.
> Now this is where I stand:
> $arg1 = $cookieValue;
> $arg2 = $dbh->quote ( $ueberschrift ) . "::text";
> $arg3 = $dbh->quote ( $beschreibung ) . "::text";
> $arg4 = $dbh->quote ( $system_zeit ) . "::timestamp";
> $arg5 = $dbh->quote ( "2001-11-11 11:11:11" ) . "::timestamp";
> $arg6 = $startpreis;
> $arg7 = $startpreis;
> $arg8 = $kategorie_nummer;
>
>
> $result = $dbh->prepare ( "SELECT neue_auktion ( ?, ?, ?, ?, ?, ?, ?, ? )
> "
> ) or die "Vorbereitung nicht durchfuehrbar!\n";
> $result->bind_param ( 1, $arg1 );
> $result->bind_param ( 2, $arg2 );
> $result->bind_param ( 3, $arg3 );
> $result->bind_param ( 4, $arg4 );
> $result->bind_param ( 5, $arg5 );
> $result->bind_param ( 6, $arg6 );
> $result->bind_param ( 7, $arg7 );
> $result->bind_param ( 8, $arg8 );
> $result->execute() or die "Abfrage nicht ausfuehrbar -suchprofil!
> $DBI::errstr\n";
> $result->finish();
>
> And now I get this error again, that the argument 'startzeit' (english
> start-time) is of type timestamp without time zone but the expression has
> the type text. "Hint: you should make a typecast", but this is what I'm
> doing all the time.

First of all, you should use *either* quote() or placeholders, not both.
Binding a value to a placeholder quotes the value for you automatically.

Second, the typecast is not part of the actual value, so you should not be
including it in the bind value.

See if this works:

my $sth = $dbh->prepare(<<"EndOfSQL");
SELECT neue_auktion ( ?, ?::text, ?::text,
?::timestamp, ?::timestamp,
?, ?, ? )
EndOfSQL

$sth->execute($cookieValue, $ueberschrift, $beschreibung,
$system_zeit, "2001-11-11 11:11:11",
$startpreis, $startpreis, $kategorie_nummer);

Ronald

RE: Calling a PostgreSQL function via DBI

In that case $dbh->quote() is considered the portable way of inserting quot=
e marks, in that the underlying db driver, which is hidden from your code, =
determines in what form those quote marks will be constructed, so as to be =
compatible with your particular database.

If you try 'man DBI' or read one of the other available references, it will=
make this point clear.

However, using prepare() and bind_param() is even more portable, if you can=
get it to work for calling predefined functions. Others on this list migh=
t have more experience in that area than I do.

In particular, I'm uncomfortable with forms like 1::numeric, etc., where yo=
ur code contains assumptions about the data types of your columns. If you =
let DBI control it through its knowledge of the schema, then you don't have=
to put those assumptions into your code. bind_param() takes care of that =
for you, so your code can be more maintainable and portable.

-Will

-----Original Message-----
From: Christian Stalp [mailto:christian.stalp@gmx.de]=20
Sent: Thursday 23 February 2006 16:32
To: Louis Gonzales
Cc: dbi-users@perl.org
Subject: Re: Calling a PostgreSQL function via DBI



> Ja Christian,
> Ich verstehe ein bischen Deutsch, besonders "startzeit" :) Ich habe ein=
> buch ueber des Lebens Albert Schweizer gelesen, so ich kann 'basic'=20
> Duetsch verstehen.

Wow, not bad. And you learned it just with this one book?
>=20
> From one of the earlier emails I saw, your variable needs to be: =
> '2001-11-11 11:11:11'::timestamp
> Is that how it needs to go into your db table?
>=20
Thats right, that is the way how the psql-shell accepted it:
SELECT neue_auktion ( 1::numeric, 'robot'::text, 'robot'::text, '1999-01-08
04:05:06'::timestamp, '1999-01-08 04:05:06'::timestamp, 10::numeric,
10::numeric, 1::numeric);

I think that the numeric 'type-cast' are redundant, but I put it in.

Gruss Christian

--=20
Lust, ein paar Euro nebenbei zu verdienen? Ohne Kosten, ohne Risiko!
Satte Provisionen für GMX Partner: http://www.gmx.net/de/go/partner


- - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - - =20
This e-mail and any attachments may contain information which is confidenti=
al, proprietary, privileged or otherwise protected by law. The information =
is solely intended for the named addressee (or a person responsible for del=
ivering it to the addressee). If you are not the intended recipient of this=
message, you are not authorized to read, print, retain, copy or disseminat=
e this message or any part of it. If you have received this e-mail in error=
, please notify the sender immediately by return e-mail and delete it from =
your computer.

Re: Calling a PostgreSQL function via DBI

> Bitte kannst du das probieren fuer spass? ( English: Could you just
> try this for fun :)
>
> $arg5 = "\'2001-11-11 11:11:11\'" . "::timestamp";
>
> Was werde denn passiert? (what would happen then? )

The same effect,
this is the dump on the website:

Software error:

[quote]
Abfrage nicht ausfuehrbar -suchprofil! FEHLER: Spalte »startzeit« hat Typ
timestamp without time zone, aber der Ausdruck hat Typ text
HINT: Sie müssen den Ausdruck umschreiben oder eine Typumwandlung
vornehmen.
CONTEXT: SQL-Anweisung »INSERT INTO auktionen ( kid, name, beschreibung,
startzeit, endzeit, startpreis, preis, katid ) VALUES ( $1 , $2 , $3 ,
$4 , $5 , $6 , $7 , $8 )«
PL/pgSQL function "neue_auktion" line 13 at SQL statement
[/quote]

Is it something wrong with the function? But it works with psql!

> Ich habe zwei semestern Duetsch gelmacht bei die Universitait. Meine
> frau ist high school Deutsch lehrerin.

Ahh, ok. Thats explains a lot. :-)

Gruss Christian


--
Telefonieren Sie schon oder sparen Sie noch?
NEU: GMX Phone_Flat http://www.gmx.net/de/go/telefonie

Re: Calling a PostgreSQL function via DBI

--------------080102050001070108030504
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Christian Stalp wrote:

>>Bitte kannst du das probieren fuer spass? ( English: Could you just
>>try this for fun :)
>>
>>$arg5 = "\'2001-11-11 11:11:11\'" . "::timestamp";
>>
>>Was werde denn passiert? (what would happen then? )
>>
>>
>
>The same effect,
>this is the dump on the website:
>
>Software error:
>
>[quote]
>Abfrage nicht ausfuehrbar -suchprofil! FEHLER: Spalte »startzeit« hat Typ
>timestamp without time zone, aber der Ausdruck hat Typ text
>HINT: Sie müssen den Ausdruck umschreiben oder eine Typumwandlung
>vornehmen.
>CONTEXT: SQL-Anweisung »INSERT INTO auktionen ( kid, name, beschreibung,
>startzeit, endzeit, startpreis, preis, katid ) VALUES ( $1 , $2 , $3 ,
>$4 , $5 , $6 , $7 , $8 )«
>PL/pgSQL function "neue_auktion" line 13 at SQL statement
>[/quote]
>
>Is it something wrong with the function? But it works with psql!
>
>
>
>>Ich habe zwei semestern Duetsch gelmacht bei die Universitait. Meine
>>frau ist high school Deutsch lehrerin.
>>
>>
>
>Ahh, ok. Thats explains a lot. :-)
>
>Gruss Christian
>
>
>
>
A couple of things come to mind at this point:
One, the datatype that you are trying to insert the constructed "$arg5"
into, what are the constraints on that? Is it necessary to identify a
timezone, as seems to be complained about from the above output? It
looks like this might be the strongest clue to the 'root cause.'

Two, you didn't actually put ( $1, $2, ... ) , Did you? In your code,
that still needs to remain as the question marks.

I truly think at this juncture, based on doing: $arg5 =
"\'2001-11-11 11:11:11\'" . "::timestamp"
and the erroneous output, stating that there needs to be a timezone
identified, that perhaps the table attribute of timestamp, of your
TIMESTAMP datatype, is asking for - and i'm just speculating here -
something of the form:

$arg5 = "\'2001-11-11 11:11:11-5:00\'" . "::timestamp" # this would be
for an EST timezone....

I think the quoting is acceptable, but please let me know how you are
declaring the target datatype? that makes all the world of difference... ;)

I'm looking up the different timestamp datatypes in the posgreSQL
documentation and I've used these several times before, they can take
many different formats.

--------------080102050001070108030504--

RE: Calling a PostgreSQL function via DBI

> See if this works:
>
> my $sth = $dbh->prepare(<<"EndOfSQL");
> SELECT neue_auktion ( ?, ?::text, ?::text,
> ?::timestamp, ?::timestamp,
> ?, ?, ? )
> EndOfSQL
>
> $sth->execute($cookieValue, $ueberschrift, $beschreibung,
> $system_zeit, "2001-11-11 11:11:11",
> $startpreis, $startpreis, $kategorie_nummer);
>
> Ronald

I don't know if this is what you mean, or should I put it in exactly as you
wrote? I tried this:
$arg1 = $cookieValue;
$arg2 = $dbh->quote ( $ueberschrift );
$arg3 = $dbh->quote ( $beschreibung );
$arg4 = $system_zeit;
$arg5 = "2001-11-11 11:11:11";
$arg6 = $startpreis;
$arg7 = $startpreis;
$arg8 = $kategorie_nummer;

$result = $dbh->prepare ( "SELECT neue_auktion ( ?, ?::text, ?::text,
?::timestamp, ?::timestamp, ?, ?, ? ) " ) or die "Vorbereitung nicht
durchfuehrbar!\n";
$result->bind_param ( 1, $arg1 );
$result->bind_param ( 2, $arg2 );
$result->bind_param ( 3, $arg3 );
$result->bind_param ( 4, $arg4 );
$result->bind_param ( 5, $arg5 );
$result->bind_param ( 6, $arg6 );
$result->bind_param ( 7, $arg7 );
$result->bind_param ( 8, $arg8 );
$result->execute() or die "Abfrage nicht ausfuehrbar -suchprofil!
$DBI::errstr\n";
$result->finish();

But its crazy, it has the same result. Still the error-code that the column
'zeitwert' has ... no, one moment its »startpreis«. We are one step further!


Gruss Chritian

--
Lust, ein paar Euro nebenbei zu verdienen? Ohne Kosten, ohne Risiko!
Satte Provisionen für GMX Partner: http://www.gmx.net/de/go/partner

Re: Calling a PostgreSQL function via DBI

--------------090509070308090903080209
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Christian,
On looking at your output once more, if you're trying to insert a
string, "2001-11-11 11:11:11::timestamp" into a TIMESTAMP datatype, this
won't work.
That's why it's complaining about a typecast... this at least makes
logical sense to me.
You'd have to change the datatype to accept a string, which is what
you're trying to fill with a value.

Does that makes sense?

--------------090509070308090903080209--

RE: Calling a PostgreSQL function via DBI

Christian Stalp [mailto:christian.stalp@gmx.de] wrote:
> > See if this works:
> >
> > my $sth = $dbh->prepare(<<"EndOfSQL");
> > SELECT neue_auktion ( ?, ?::text, ?::text,
> > ?::timestamp, ?::timestamp,
> > ?, ?, ? )
> > EndOfSQL
> >
> > $sth->execute($cookieValue, $ueberschrift, $beschreibung,
> > $system_zeit, "2001-11-11 11:11:11",
> > $startpreis, $startpreis, $kategorie_nummer);
> >
> > Ronald
>
> I don't know if this is what you mean, or should I put it in exactly as
> you
> wrote? I tried this:
> $arg1 = $cookieValue;
> $arg2 = $dbh->quote ( $ueberschrift );
> $arg3 = $dbh->quote ( $beschreibung );
> $arg4 = $system_zeit;
> $arg5 = "2001-11-11 11:11:11";
> $arg6 = $startpreis;
> $arg7 = $startpreis;
> $arg8 = $kategorie_nummer;
>
> $result = $dbh->prepare ( "SELECT neue_auktion ( ?, ?::text, ?::text,
> ?::timestamp, ?::timestamp, ?, ?, ? ) " ) or die "Vorbereitung nicht
> durchfuehrbar!\n";
> $result->bind_param ( 1, $arg1 );
> $result->bind_param ( 2, $arg2 );
> $result->bind_param ( 3, $arg3 );
> $result->bind_param ( 4, $arg4 );
> $result->bind_param ( 5, $arg5 );
> $result->bind_param ( 6, $arg6 );
> $result->bind_param ( 7, $arg7 );
> $result->bind_param ( 8, $arg8 );
> $result->execute() or die "Abfrage nicht ausfuehrbar -suchprofil!
> $DBI::errstr\n";
> $result->finish();
>
> But its crazy, it has the same result. Still the error-code that the
> column
> 'zeitwert' has ... no, one moment its >startpreis<. We are one step
> further!

As I said, *you should not use both quote() and placeholders at the same
time*. When you bind a value to a placeholder, DBI quotes the value for
you.


Try the code exactly as I wrote it (except that you can add in the error
messages and change the variable names, of course).


If that doesn't work, try passing the type to bind_param():

$sth->bind_param($p_num, $bind_value, $bind_type);

You'll have to look at the docs for PostgreSQL to see how to specify the
bind type.


Ronald

RE: Calling a PostgreSQL function via DBI

That the way:
$arg1 = $cookieValue;
$arg2 = $dbh->quote ( $ueberschrift );
$arg3 = $dbh->quote ( $beschreibung );
$arg4 = $system_zeit;
$arg5 = "2001-11-11 11:11:11";
$arg6 = $startpreis;
$arg7 = $startpreis;
$arg8 = $kategorie_nummer;

$result = $dbh->prepare ( "SELECT neue_auktion ( ?, ?::text, ?::text,
?::timestamp, ?::timestamp, ?::numeric, ?::numeric, ?::numeric ) " ) or die
"Vorbereitung nicht durchfuehrbar!\n";
$result->bind_param ( 1, $arg1 );
$result->bind_param ( 2, $arg2 );
$result->bind_param ( 3, $arg3 );
$result->bind_param ( 4, $arg4 );
$result->bind_param ( 5, $arg5 );
$result->bind_param ( 6, $arg6 );
$result->bind_param ( 7, $arg7 );
$result->bind_param ( 8, $arg8 );
$result->execute() or die "Abfrage nicht ausfuehrbar -suchprofil!
$DBI::errstr\n";
$result->finish();

This works, it writes into the database. Hurra, yea :-)))))

Gruss Christian

--
DSL-Aktion wegen großer Nachfrage bis 28.2.2006 verlängert:
GMX DSL-Flatrate 1 Jahr kostenlos* http://www.gmx.net/de/go/dsl

Re: Calling a PostgreSQL function via DBI

--------------030609020206060104010809
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Christian Stalp wrote:

>That the way:
>$arg1 = $cookieValue;
>$arg2 = $dbh->quote ( $ueberschrift );
>$arg3 = $dbh->quote ( $beschreibung );
>$arg4 = $system_zeit;
>$arg5 = "2001-11-11 11:11:11";
>$arg6 = $startpreis;
>$arg7 = $startpreis;
>$arg8 = $kategorie_nummer;
>
>$result = $dbh->prepare ( "SELECT neue_auktion ( ?, ?::text, ?::text,
>?::timestamp, ?::timestamp, ?::numeric, ?::numeric, ?::numeric ) " ) or die
>"Vorbereitung nicht durchfuehrbar!\n";
>$result->bind_param ( 1, $arg1 );
>$result->bind_param ( 2, $arg2 );
>$result->bind_param ( 3, $arg3 );
>$result->bind_param ( 4, $arg4 );
>$result->bind_param ( 5, $arg5 );
>$result->bind_param ( 6, $arg6 );
>$result->bind_param ( 7, $arg7 );
>$result->bind_param ( 8, $arg8 );
>$result->execute() or die "Abfrage nicht ausfuehrbar -suchprofil!
>$DBI::errstr\n";
>$result->finish();
>
>This works, it writes into the database. Hurra, yea :-)))))
>
>Gruss Christian
>
>
>
Hey good job everybody with their suggestions! Especially to Ronald.

--------------030609020206060104010809--

RE: Calling a PostgreSQL function via DBI

>
> my $sth = $dbh->prepare(<<"EndOfSQL");
> SELECT neue_auktion ( ?, ?::text, ?::text,
> ?::timestamp, ?::timestamp,
> ?, ?, ? )
> EndOfSQL
>
> $sth->execute($cookieValue, $ueberschrift, $beschreibung,
> $system_zeit, "2001-11-11 11:11:11",
> $startpreis, $startpreis, $kategorie_nummer);
>
> Ronald
>
>
This looks very tricky and cryptic. What does it mean? I mean you should
insert the statement "SELECT neue_auktion ( ?, ?::text, ?::text,
?::timestamp, ?::timestamp, ?, ?, ? )" into any perl-function or what is the
meaning of "<<"EndOfSQL"" ? Never seen this.

Gruss Christian

--
Lust, ein paar Euro nebenbei zu verdienen? Ohne Kosten, ohne Risiko!
Satte Provisionen für GMX Partner: http://www.gmx.net/de/go/partner

RE: Calling a PostgreSQL function via DBI

I'm glad it worked.

However I'm still concerned with why you need the nonportable form of ??::t=
ext etc.

Is this because you are running a user defined function which has no schema=
associated with it the way a regular table or view would?

Is there some way to make it more portable, i.e. not needing to specify exp=
licit types for columns?

-Will

-----Original Message-----
From: Christian Stalp [mailto:christian.stalp@gmx.de]=20
Sent: Thursday 23 February 2006 17:46
To: dbi-users@perl.org
Subject: RE: Calling a PostgreSQL function via DBI


That the way:
$arg1 =3D $cookieValue;
$arg2 =3D $dbh->quote ( $ueberschrift );
$arg3 =3D $dbh->quote ( $beschreibung );
$arg4 =3D $system_zeit;
$arg5 =3D "2001-11-11 11:11:11";
$arg6 =3D $startpreis;
$arg7 =3D $startpreis;
$arg8 =3D $kategorie_nummer;

$result =3D $dbh->prepare ( "SELECT neue_auktion ( ?, ?::text, ?::text,
?::timestamp, ?::timestamp, ?::numeric, ?::numeric, ?::numeric ) " ) or die
"Vorbereitung nicht durchfuehrbar!\n";
$result->bind_param ( 1, $arg1 );
$result->bind_param ( 2, $arg2 );
$result->bind_param ( 3, $arg3 );
$result->bind_param ( 4, $arg4 );
$result->bind_param ( 5, $arg5 );
$result->bind_param ( 6, $arg6 );
$result->bind_param ( 7, $arg7 );
$result->bind_param ( 8, $arg8 );
$result->execute() or die "Abfrage nicht ausfuehrbar -suchprofil!
$DBI::errstr\n";
$result->finish();

This works, it writes into the database. Hurra, yea :-)))))

Gruss Christian

--=20
DSL-Aktion wegen großer Nachfrage bis 28.2.2006 verlängert:
GMX DSL-Flatrate 1 Jahr kostenlos* http://www.gmx.net/de/go/dsl


- - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - - =20
This e-mail and any attachments may contain information which is confidenti=
al, proprietary, privileged or otherwise protected by law. The information =
is solely intended for the named addressee (or a person responsible for del=
ivering it to the addressee). If you are not the intended recipient of this=
message, you are not authorized to read, print, retain, copy or disseminat=
e this message or any part of it. If you have received this e-mail in error=
, please notify the sender immediately by return e-mail and delete it from =
your computer.

RE: Calling a PostgreSQL function via DBI

Christian Stalp [mailto:christian.stalp@gmx.de] wrote:
> >
> > my $sth = $dbh->prepare(<<"EndOfSQL");
> > SELECT neue_auktion ( ?, ?::text, ?::text,
> > ?::timestamp, ?::timestamp,
> > ?, ?, ? )
> > EndOfSQL
> >
> > $sth->execute($cookieValue, $ueberschrift, $beschreibung,
> > $system_zeit, "2001-11-11 11:11:11",
> > $startpreis, $startpreis, $kategorie_nummer);
> >
> This looks very tricky and cryptic. What does it mean? I mean you should
> insert the statement "SELECT neue_auktion ( ?, ?::text, ?::text,
> ?::timestamp, ?::timestamp, ?, ?, ? )" into any perl-function or what is
> the
> meaning of "<<"EndOfSQL"" ? Never seen this.
>

It's just a way of creating a string, called a here-document. It allows me
to format the SQL statement nicely. It's documented in perldata if you want
to know more about it.

Here's another way of doing the same thing:

my $sth = $dbh->prepare("SELECT neue_auktion ( ?, ?::text, ?::text,
?::timestamp, ?::timestamp, ?, ?, ? )");

$sth->execute($cookieValue, $ueberschrift, $beschreibung,
$system_zeit, "2001-11-11 11:11:11",
$startpreis, $startpreis, $kategorie_nummer);

Ronald

RE: Calling a PostgreSQL function via DBI

> I'm glad it worked.
>
> However I'm still concerned with why you need the nonportable form of
> ??::text etc.
>
> Is this because you are running a user defined function which has no
> schema associated with it the way a regular table or view would?
>
> Is there some way to make it more portable, i.e. not needing to specify
> explicit types for columns?
>
> -Will
>

Yes this is true. Its a PLpgsql-function. This runs only on PostgreSQL and I
think this "::type"-form is also only usable under postgreSQL?!? But I have
to do it this way. Its a project for the university, and its focus on
databases. So it doesn't has to be portable for other databases. It is
important that we become familiar with database-procedures.

Gruss Christian

--
Lust, ein paar Euro nebenbei zu verdienen? Ohne Kosten, ohne Risiko!
Satte Provisionen für GMX Partner: http://www.gmx.net/de/go/partner

RE: Calling a PostgreSQL function via DBI

Vielen Glueck mit ihren Aufgabe an der Uni.

-Will

-----Original Message-----
From: Christian Stalp [mailto:christian.stalp@gmx.de]=20
Sent: Thursday 23 February 2006 18:24
To: Rutherdale, Will
Cc: dbi-users@perl.org
Subject: RE: Calling a PostgreSQL function via DBI



> I'm glad it worked.
>=20
> However I'm still concerned with why you need the nonportable form of
> ??::text etc.
>=20
> Is this because you are running a user defined function which has no
> schema associated with it the way a regular table or view would?
>=20
> Is there some way to make it more portable, i.e. not needing to specify
> explicit types for columns?
>=20
> -Will
>=20

Yes this is true. Its a PLpgsql-function. This runs only on PostgreSQL and I
think this "::type"-form is also only usable under postgreSQL?!? But I have
to do it this way. Its a project for the university, and its focus on
databases. So it doesn't has to be portable for other databases. It is
important that we become familiar with database-procedures.

Gruss Christian

--=20
Lust, ein paar Euro nebenbei zu verdienen? Ohne Kosten, ohne Risiko!
Satte Provisionen für GMX Partner: http://www.gmx.net/de/go/partner


- - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - - =20
This e-mail and any attachments may contain information which is confidenti=
al, proprietary, privileged or otherwise protected by law. The information =
is solely intended for the named addressee (or a person responsible for del=
ivering it to the addressee). If you are not the intended recipient of this=
message, you are not authorized to read, print, retain, copy or disseminat=
e this message or any part of it. If you have received this e-mail in error=
, please notify the sender immediately by return e-mail and delete it from =
your computer.

RE: Calling a PostgreSQL function via DBI

> Vielen Glueck mit ihren Aufgabe an der Uni.
>
> -Will
>

8O Who else speaks german on this list ;-) This is really funny. :-)
And this is my university
[url]http://fh-web1.informatik.fh-wiesbaden.de/go.cfm/fb/6/s prachid/2/lpid/0/sid/0.html[/url]

Other question, has anybody experences with PLperl on postgreSQL, thats
looks facinating. And I think my next function will be reallized in PLperl.

Gruss Christian

--
DSL-Aktion wegen großer Nachfrage bis 28.2.2006 verlängert:
GMX DSL-Flatrate 1 Jahr kostenlos* http://www.gmx.net/de/go/dsl

Re: Calling a PostgreSQL function via DBI

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Some minor notes and corrections. First, hopefully the most important
thing to learn from this thread is to always use placeholders. It just
saves you so much pain and trouble. :)

Louis Gonzales asked:
> Two, you didn't actually put ( $1, $2, ... ) , Did you? In your code,
> that still needs to remain as the question marks.

Actually, the $1 form is perfectly acceptable, and even preferable, as it
can be easier to read and handles the case of the same placeholder used more
than once in a statement in a more efficient manner. You can also ue the
":foo" format which is more flexible still. DBD::Oracle and others handle
non-"?" placeholders, although the portability factor is probably not very
important when one is dealing with database functions.


Christian Stalp asks:
> Other question, has anybody experences with PLperl on postgreSQL, thats
> looks facinating. And I think my next function will be reallized in PLperl.

Yes, Pl/Perl is amazing. All the power of Perl, directly in your database.
It is so much easier than fighting with the traditional procedural
languages such as pl/sql and pl/pgsql. You can even do things like use
DBI from within your PL/Perl functions. Best of all, you can now write your
triggers in Pl/Perl. How cool is that?


Will Rutherdale said:
> In particular, I'm uncomfortable with forms like 1::numeric, etc., where
> your code contains assumptions about the data types of your columns. If
> you let DBI control it through its knowledge of the schema, then you don't
> have to put those assumptions into your code. bind_param() takes care
> of that for you, so your code can be more maintainable and portable.

Those forms can be very important, as PostgreSQL supports polymorphic
functions, so you *must* tell it what data type you are using if you have
two or more functions with the same name. If you don't, however, and I am
guessing that is the case here, it is usually safe to leave them out.

DBI does not "control through its knowledge of the schema" - it is a pure
abstraction layer, which has no knowledge of any schema (nor should it).
It expects the user to tell it what types through methods such as bind_param.
Alternatively, DBD::Pg has the ability (with some caveats) to pass a
preparable statement to Postgres and let *it* worry about the quoting
when executing.

- --
Greg Sabino Mullane greg@endpoint.com greg@turnstep.com
PGP Key: 0x14964AC8 200602232024
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFD/mLgvJuQZxSWSsgRArY1AKD056Kxb2UJ79UpmiHHpuNp5ZAHxwCe IL2e
uoYzNZKbOsArfRLBAaSeWPo=
=lCYA
-----END PGP SIGNATURE-----

RE: Calling a PostgreSQL function via DBI

Thanks, Greg. That's very informative.

I really ought to dig into DBI/DBD internals in the near future,
probably using postgreSQL as an example.

I use postgreSQL at home sometimes but not currently at work.

-Will

-----Original Message-----
From: Greg Sabino Mullane [mailto:greg@turnstep.com]=20
Sent: Thursday 23 February 2006 20:42
To: dbi-users@perl.org
Subject: Re: Calling a PostgreSQL function via DBI



-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Some minor notes and corrections. First, hopefully the most important
thing to learn from this thread is to always use placeholders. It just
saves you so much pain and trouble. :)
=20
Louis Gonzales asked:
> Two, you didn't actually put ( $1, $2, ... ) , Did you? In your code,
> that still needs to remain as the question marks.
=20
Actually, the $1 form is perfectly acceptable, and even preferable, as
it
can be easier to read and handles the case of the same placeholder used
more
than once in a statement in a more efficient manner. You can also ue the
":foo" format which is more flexible still. DBD::Oracle and others
handle
non-"?" placeholders, although the portability factor is probably not
very
important when one is dealing with database functions.


Christian Stalp asks:
> Other question, has anybody experences with PLperl on postgreSQL,
thats
> looks facinating. And I think my next function will be reallized in
PLperl.
=20
Yes, Pl/Perl is amazing. All the power of Perl, directly in your
database.
It is so much easier than fighting with the traditional procedural
languages such as pl/sql and pl/pgsql. You can even do things like use
DBI from within your PL/Perl functions. Best of all, you can now write
your
triggers in Pl/Perl. How cool is that?


Will Rutherdale said:
> In particular, I'm uncomfortable with forms like 1::numeric, etc.,
where
> your code contains assumptions about the data types of your columns.
If
> you let DBI control it through its knowledge of the schema, then you
don't
> have to put those assumptions into your code. bind_param() takes care
> of that for you, so your code can be more maintainable and portable.
=20
Those forms can be very important, as PostgreSQL supports polymorphic
functions, so you *must* tell it what data type you are using if you
have
two or more functions with the same name. If you don't, however, and I
am
guessing that is the case here, it is usually safe to leave them out.

DBI does not "control through its knowledge of the schema" - it is a
pure
abstraction layer, which has no knowledge of any schema (nor should it).
It expects the user to tell it what types through methods such as
bind_param.
Alternatively, DBD::Pg has the ability (with some caveats) to pass a
preparable statement to Postgres and let *it* worry about the quoting
when executing.



- - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - - =20
This e-mail and any attachments may contain information which is confidenti=
al, proprietary, privileged or otherwise protected by law. The information =
is solely intended for the named addressee (or a person responsible for del=
ivering it to the addressee). If you are not the intended recipient of this=
message, you are not authorized to read, print, retain, copy or disseminat=
e this message or any part of it. If you have received this e-mail in error=
, please notify the sender immediately by return e-mail and delete it from =
your computer.