Обсуждение: php cant see new table!!

HI all,
Im having a heck of a time here ...

Im running PostgreSQL 8.1 on windows XP.

I have created a new table in an existing database. The existing database
has a few schemas already built and some tables in these schemas.

I have sucessfully created a new table in a schema known as "st". I can view
the contents of my database and see that this table exists. However when I
attempt to connect to it via php, I get the following error:

Warning: pg_query(): Query failed: ERROR: relation "st.iam" does not exist
in D:\ms4w\Apache\htdocs\postgres\justconnect.php on line 8
There was an error! ERROR: relation "st.iam" does not exist
end script


When I change my query statement to use a differnt table in the same schema,
it connects without problem.

This is the php code Im running:

<?php

echo 'start script <br>';

pg_connect("dbname=playdb user=postgres password=xxxxx") or die("Couldn't
Connect: ".pg_last_error());
//$query = "SELECT * from st.dictionnary";   //***** this line works
$query = "SELECT * from st.IAM";        //*****  this line wont work
$query = pg_query($query);
if($query)
  echo "got it!";
else{
  echo "There was an error! ".pg_last_error();
}
echo '<br> end script <br>';

?>

Can anyone suggest a reason why this may be happening? Perhaps Ive missed
something important while creating the table?

Thanks
Jeremy

On Fri, Feb 17, 2006 at 10:55:10AM -0500, Sears, Jeremy wrote:
> I have sucessfully created a new table in a schema known as "st". I can view
> the contents of my database and see that this table exists. However when I
> attempt to connect to it via php, I get the following error:
>
> Warning: pg_query(): Query failed: ERROR: relation "st.iam" does not exist

Is the table name st.iam or st."IAM"?  If you don't understand the
difference then see the section about quoted identifiers in the
documentation:

http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS

--
Michael Fuhr

Hi,
Thanks for the response Michael. I have read over the documentation but I am
still unclear as to why php doesnt see my new table. I can see it in my
database, however php is unable to access it. I suspect that in the process
of creating the table I must have missed an essential command or somthing..
Can anyone suggest what Ive missed?

Thanks
Jeremy

this is the sql I used to creat the table:

CREATE TABLE "st"."IAM_appid" (
  "id" INTEGER NOT NULL,
  "application" CHAR(25) NOT NULL,
  CONSTRAINT "IAM_appid_id_key" UNIQUE("id")
) WITH OIDS;

COMMENT ON COLUMN "st"."IAM_appid"."id"
IS 'application ID';

COMMENT ON COLUMN "st"."IAM_appid"."application"
IS 'description';


-----Original Message-----
    * From: Michael Fuhr <mike ( at ) fuhr ( dot ) org>
    * To: "Sears, Jeremy" <Jeremy ( dot ) Sears ( at ) CCRS ( dot ) NRCan (
dot ) gc ( dot ) ca>
    * Subject: Re: php cant see new table!!
    * Date: Fri, 17 Feb 2006 21:39:42 -0700

On Fri, Feb 17, 2006 at 10:55:10AM -0500, Sears, Jeremy wrote:
> I have sucessfully created a new table in a schema known as "st". I can
view
> the contents of my database and see that this table exists. However when I
> attempt to connect to it via php, I get the following error:
>
> Warning: pg_query(): Query failed: ERROR: relation "st.iam" does not exist

Is the table name st.iam or st."IAM"?  If you don't understand the
difference then see the section about quoted identifiers in the
documentation:

http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-ID
ENTIFIERS

--
Michael Fuhr

On Thu, Feb 23, 2006 at 10:38:27AM -0500, Sears, Jeremy wrote:
> Thanks for the response Michael. I have read over the documentation but I am
> still unclear as to why php doesnt see my new table. I can see it in my
> database, however php is unable to access it. I suspect that in the process
> of creating the table I must have missed an essential command or somthing..
[...]
> this is the sql I used to creat the table:
>
> CREATE TABLE "st"."IAM_appid" (
>   "id" INTEGER NOT NULL,
>   "application" CHAR(25) NOT NULL,
>   CONSTRAINT "IAM_appid_id_key" UNIQUE("id")
> ) WITH OIDS;

Is this the same table you were trying to access when you got the
error?  The error in your original message was

> > Warning: pg_query(): Query failed: ERROR: relation "st.iam" does not exist

which isn't the same table.  Aside from that it still looks like
the problem is due to quoted identifiers.  Your code is probably
doing something like this:

SELECT * FROM st.IAM_appid;

when it should be doing this:

SELECT * FROM st."IAM_appid";

Notice the quotes around the table name in the second case.  Unquoted
identifiers are folded to lowercase, as you can see in the following
error message:

test=> SELECT * FROM st.IAM_appid;
ERROR:  relation "st.iam_appid" does not exist

Since you created the table with mixed case and quotes you'll always
have to use a quoted identifier and that exact case; some people
avoid quoted identifiers for that reason.  Without quotes you can
still use mixed case but the identifiers will be folded to lower
case:

test=> CREATE TABLE Foo (bAR integer);
CREATE TABLE
test=> \d
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | foo  | table | mfuhr
(1 row)

test=> \d foo
      Table "public.foo"
 Column |  Type   | Modifiers
--------+---------+-----------
 bar    | integer |

SELECT Bar FROM fOO;
 bar
-----
(0 rows)

--
Michael Fuhr

Jeremy

line a) $query = "SELECT * from st.IAM"; 
line b) CREATE TABLE "st"."IAM_appid" (...

Your table name is st.IAM_appid.You query looks just for st.IAM

but for the time, almost 10 days after your first post, I am sure you already sorted this.

Just for the record :)

g.-









--
/"\   ASCII Ribbon Campaign  .
\ / - NO HTML/RTF in e-mail  .
X  - NO Word docs in e-mail .
/ \ -----------------------------------------------------------------