Thread:
Good morning,
This may well be a daft question, but I'm confused and I can't find anything referring to this in the PostgreSQL
or DBI books, bear with me, I'm new.
I have one table containing a respondents contact details, e.g. name, address fields, telephone numbers, etc.
Some of the telephone number cells are blank/ empty. When I query in psql, using
"SELECT telephone from sample" the return shows all columns, including the blanks (as expected).
Using the Perl DBI I try to pull out telephone again and bind the result to a pre-defined variable $telephone.
e.g.
$sth = $dbh->prepare("SELECT telephone from sample");
$sth->execute();
$sth->bind_columns(undef, \$telephone);
while ( $sth->fetch ) {
print STDERR "$telephone\n";
}
$sth->finish;
This fails with "Statement has no result to bind(perhaps you need to call execute first)"
This code works fine for a field that is always populated e.g. name in place of telephone in line 1.
Is my code fatally flawed or do I need to deal with the blank cells differently when the data is written to the
database in the beginning, or before the bind. Any help greatly appreciated.
(Running: Linux 7.2, Perl 5.6, Postgres 7.1.3, DBI 1.32.)
Richard
**********************************************************************
The information contained in this email is confidential and is intended for the recipient only. If you have received it
inerror, please notify us immediately by reply email and then delete it from your system. Please do not copy it or use
itfor any purposes, or disclose its contents to any other person or store or copy this information in any medium. The
viewscontained in this email are those of the author and not necessarily those of Lorien plc.
Thank you for your co-operation.
**********************************************************************
On Thu, 2003-12-18 at 08:40, Mace, Richard wrote:
>
> $sth = $dbh->prepare("SELECT telephone from sample");
> $sth->execute();
> $sth->bind_columns(undef, \$telephone);
> while ( $sth->fetch ) {
> print STDERR "$telephone\n";
> }
> $sth->finish;
>
> This fails with "Statement has no result to bind(perhaps you need to
> call execute first)"
>
> This code works fine for a field that is always populated e.g. name in
> place of telephone in line 1.
If a cell is NULL, I think it is undefined in DBI
Try changing your select to
SELECT telephone from sample WHERE telephone IS NOT NULL
On Thu, Dec 18, 2003 at 08:40:15AM -0000, Mace, Richard wrote:
> I have one table containing a respondents contact details, e.g. name,
> address fields, telephone numbers, etc. Some of the telephone number
> cells are blank/ empty. When I query in psql, using "SELECT telephone from
> sample" the return shows all columns, including the blanks (as expected).
>
> Using the Perl DBI I try to pull out telephone again and bind the result
> to a pre-defined variable $telephone. e.g.
>
> $sth = $dbh->prepare("SELECT telephone from sample");
> $sth->execute();
> $sth->bind_columns(undef, \$telephone);
You shouldn't need undef as the first argument to bind_columns(). The DBI
doc says, "For compatibility with old scripts, the first parameter will
be ignored if it is 'undef' or a hash reference."
> while ( $sth->fetch ) {
> print STDERR "$telephone\n";
> }
> $sth->finish;
>
> This fails with "Statement has no result to bind(perhaps you need to
> call execute first)"
This typically happens if execute() failed, in which case you should see
the reason if $dbh has the RaiseError or PrintError attributes turned on.
You could also do this:
$sth->execute() or die "execute failed: ", $dbh->errstr;
execute() could fail if the SQL statement has a syntax error or if one
of the referenced columns or tables doesn't exist. Are you sure the
column and table names are spelled correctly?
> This code works fine for a field that is always populated e.g. name in
> place of telephone in line 1.
>
> Is my code fatally flawed or do I need to deal with the blank cells
> differently when the data is written to the database in the beginning,
> or before the bind. Any help greatly appreciated.
DBI should return undef for NULL values; aside from checking for undef
so you don't get "Use of uninitialized value" warnings, you shouldn't
need to do anything special.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Oliver Elphick <olly@lfix.co.uk> writes:
> On Thu, 2003-12-18 at 08:40, Mace, Richard wrote:
>> This code works fine for a field that is always populated e.g. name in
>> place of telephone in line 1.
> If a cell is NULL, I think it is undefined in DBI
I'm not much of a DBI user, but one would hope that fetch() represents
null values by storing an undef, rather than failing. Anything else
would indicate utter brain death on the part of the DBI designers.
Richard's example code looks fine to me, but I wonder if it is an exact
copy of his real code. The error message ("Statement has no result to
bind") is pretty specific and it's hard to see how it would come out
from a bind operation on a SELECT statement. I am wondering about
simple typos like applying the bind_columns call to the wrong statement
handle.
regards, tom lane
Thanks for your help everyone.
I have to admit to using the wrong DBD Driver; or copying an older script with
reference to an inferior version I used previously. Let that be a lesson to us
all !!
thanks once again,
Richard.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 18 December 2003 15:05
To: olly@lfix.co.uk
Cc: Mace, Richard; pgsql-novice@postgresql.org
Subject: Re: [NOVICE]
Oliver Elphick <olly@lfix.co.uk> writes:
> On Thu, 2003-12-18 at 08:40, Mace, Richard wrote:
>> This code works fine for a field that is always populated e.g. name in
>> place of telephone in line 1.
> If a cell is NULL, I think it is undefined in DBI
I'm not much of a DBI user, but one would hope that fetch() represents
null values by storing an undef, rather than failing. Anything else
would indicate utter brain death on the part of the DBI designers.
Richard's example code looks fine to me, but I wonder if it is an exact
copy of his real code. The error message ("Statement has no result to
bind") is pretty specific and it's hard to see how it would come out
from a bind operation on a SELECT statement. I am wondering about
simple typos like applying the bind_columns call to the wrong statement
handle.
regards, tom lane
**********************************************************************
The information contained in this email is confidential and is intended for the recipient only. If you have received it
inerror, please notify us immediately by reply email and then delete it from your system. Please do not copy it or use
itfor any purposes, or disclose its contents to any other person or store or copy this information in any medium. The
viewscontained in this email are those of the author and not necessarily those of Lorien plc.
Thank you for your co-operation.
**********************************************************************
Hi Lee, On Thursday 15 January 2004 00:53, you wrote: > Hello > > I have the program working now. I log in to the system as root, then do the > command: "su - postgres", and it starts. You mean the server actually starts with the command 'su -postgres' ? Weird. Start command should be something like this (as postgres): /usr/local/pgsql/bin/postmaster -D /usr/share/pgdata & With '&' you don't have to open another terminal, just press enter and your at prompt again. Then I open up another terminal > window, and issue the command "su - postgres". I get an error message from > the line in the code that executes the postmaster command, saying the > postmaster.pid already exista, and is another postmaster pid running? > However I am able to execute some commands successfully. So maybe RHEL3 really starts the server with that. The error comes because you already have started it. Anyway, you can ignore the message, I believe it won't do anything as it is already running. > > I can create a new user from the second terminal window that has a second > postgres session logged in. However when I issue the command "su - > databaseuser" it says that there is no user by that name. I try "su > database user" with the same results, whether trying this command from > postgres user or from root prompt in second terminal window. As postgres you are creating a database user, not a system user ? So you can not su databaseuser, ie. become that user on the system itself (su is for substitute user). This user could however connect to a db via php or some other interface. If given rights to do so in pg_hba.conf. RHEL 3 obviously is not same as RH 9. Anyway, I hope this helps. And for later on, you might find this helpful (the bit about upgrading PgSQL): http://doc.trustix.org/cgi-bin/trustixdoc.cgi?PostgreSQL BR, Aarni > > Lee -- ------------------------------------------------- Aarni Ruuhimäki | Megative Tmi | KYMI.com