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:[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
