Re: Calling a PostgreSQL function via DBI
- From: christian.stalp@xxxxxx (Christian Stalp)
- Date: Thu, 23 Feb 2006 21:08:59 +0100 (MET)
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
.
- References:
- Re: Calling a PostgreSQL function via DBI
- From: Louis Gonzales
- Re: Calling a PostgreSQL function via DBI
- Prev by Date: RE: Slow Performance When Using DBI, otherwise Not
- Next by Date: RE: Calling a PostgreSQL function via DBI
- Previous by thread: Re: Calling a PostgreSQL function via DBI
- Next by thread: Re: Calling a PostgreSQL function via DBI
- Index(es):
Relevant Pages
|
|
