Re: Calling a PostgreSQL function via DBI

[Louis Gonzales]

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.