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:[EMAIL PROTECTED] 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. :) 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. - - - - - - - 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.
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 [EMAIL PROTECTED] [EMAIL PROTECTED] PGP Key: 0x14964AC8 200602232024 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFD/mLgvJuQZxSWSsgRArY1AKD056Kxb2UJ79UpmiHHpuNp5ZAHxwCeIL2e uoYzNZKbOsArfRLBAaSeWPo= =lCYA -END PGP SIGNATURE-
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/sprachid/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
Vielen Glueck mit ihren Aufgabe an der Uni. -Will -Original Message- From: Christian Stalp [mailto:[EMAIL PROTECTED] 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. > > 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 - - - - - - - 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.
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
Christian Stalp [mailto:[EMAIL PROTECTED] 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 -Original Message- From: Christian Stalp [mailto:[EMAIL PROTECTED] Sent: Thursday 23 February 2006 17:46 To: dbi-users@perl.org Subject: 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 - - - - - - - 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.
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
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.
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
Christian Stalp [mailto:[EMAIL PROTECTED] 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
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?
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
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.
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
In that case $dbh->quote() is considered the portable way of inserting quote 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 might have more experience in that area than I do. 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. -Will -Original Message- From: Christian Stalp [mailto:[EMAIL PROTECTED] 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' > 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 - - - - - - - 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.
RE: Calling a PostgreSQL function via DBI
Christian Stalp [mailto:[EMAIL PROTECTED] 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
> 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
Thank you Will, I'll opt for discontinuing this discussion with you. I apologize to the dbi-user community in general. Sincerely,
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
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 > on those arguments too, I'd just suggest to use the process of double quoting your string > concatenation assignments upfront: > > $arg1 = "$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 = $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:[EMAIL PROTECTED] Sent: Thursday 23 February 2006 15:47 To: Christian Stalp Cc: dbi-users@perl.org; [EMAIL PROTECTED] Subject: Re: Calling a PostgreSQL function via DBI 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. - - - - - - - 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.
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
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
> > > 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
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: 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_idALIAS FOR $1;
my_name ALIAS FOR $2;
my_beschreibung ALIAS FOR $3;
my_startzeitALIAS FOR $4;
my_endzeit ALIAS FOR $5;
my_startpreis ALIAS FOR $6;
my_preisALIAS FOR $7;
my_kategorieALIAS 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,
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 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: 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_idALIAS FOR $1;
my_name ALIAS FOR $2;
my_beschreibung ALIAS FOR $3;
my_startzeitALIAS FOR $4;
my_endzeit ALIAS FOR $5;
my_startpreis ALIAS FOR $6;
my_preisALIAS FOR $7;
my_kategorieALIAS 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.
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_idALIAS FOR $1; my_name ALIAS FOR $2; my_beschreibung ALIAS FOR $3; my_startzeitALIAS FOR $4; my_endzeit ALIAS FOR $5; my_startpreis ALIAS FOR $6; my_preisALIAS FOR $7; my_kategorieALIAS 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
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:[EMAIL PROTECTED] Sent: Thursday 23 February 2006 10:28 To: Christian Stalp Cc: dbi-users@perl.org; [EMAIL PROTECTED] 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?
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 = $dbh->quote( $someVariable ) . "::someText"; -Will -Original Message- From: Louis Gonzales [mailto:[EMAIL PROTECTED] Sent: Thursday 23 February 2006 10:28 To: Christian Stalp Cc: dbi-users@perl.org; [EMAIL PROTECTED] 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.
Re: Calling a PostgreSQL function via DBI
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.
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
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:[EMAIL PROTECTED] http://www.foken.de/alexander/
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
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
-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 [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200602220909
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFD/HFEvJuQZxSWSsgRAkG7AKDoqzOxjg6OSpcDp/x9DYUwMVCNMgCfRwsD
flkczHWCUXi5If3sWW8Ee3I=
=LIPT
-END PGP SIGNATURE-
