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:[EMAIL PROTECTED]
Sent: Thursday 23 February 2006 15:09
To: [email protected]
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 from your computer.
