• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Cannot SELECT from new database but can from old one

 
David Ellis Rogers
Greenhorn
Posts: 16
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am running Eclipse Luna 2 (4.4.2) on Mac OS Yosemite (10.10.5), using JDBC to a postgreSQL 9.4 database.  Can select a tuple (row) from an old running database (pg1).  However I create a new database (tempDb), put one row in it using pgAdmin3, and then cannot retrieve it via a SELECT command from a Java program.

CODE FOR BOTH CASES, WHERE ONLY COMMENTED 2 LINES (shown, another not shown changes database name) ARE ALTERNATED, DEPENDING ON TESTS:




TEST RESULT ON CONSOLE FOR DATABASE tempDb:

in reference listener - connection successful - LINE 122; user is postgres; password is ginkat8a; url is jdbc:postgresql://localhost:5432/tempDb
line 133, catch Logger
Nov 01, 2016 9:09:29 PM PaniniIndexing.views.FirstWbGuiPaniniIndexing$3 actionPerformed
SEVERE: ERROR: syntax error at or near "references"
  Position: 22
org.postgresql.util.PSQLException: ERROR: syntax error at or near "references"
  Position: 22
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)

TEST OUTPUT ON CONSOLE FOR DATABASE pg1, where the last line, containing the number 1 is correct:

line 118 - in listenr - after setup, URL is jdbc:postgresql://localhost:5432/pg1
in reference listener - connection successful - LINE 122; user is postgres; password is ginkat8a; url is jdbc:postgresql://localhost:5432/pg1
LINE 125 in reference listener - after SELECT
1

Which is correct.
/code]

It appears that the syntax of the 2 versions are the same.  So it could be the structure of the tempDb database? - but I do not see what.  I do not have the schemas named in either database.
The error states: : ERROR: syntax error at or near "references.  Why would it look for a schema name in one database, but not the other?

Any help would be greatly appreciated.
 
Knute Snortum
Bartender
Pie
Posts: 2882
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"SELECT sutraNum from references;"

I don't think you need the semicolon at the end.
 
David Ellis Rogers
Greenhorn
Posts: 16
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have not solved this issue, but have done some investigations.
1. Knute: true, the semicolon at the end does not need to be there, but it works with or without it.
2. Somewhere out there on the internet I ran into a statement that references is a reserved work, so I changed the table name from references to referes.
3. As the attached code/output indicates, on the old database pg1 the SELECT works but on the new database tempDb the SELECT does not work (it yields error that column sutranum does not exist).
4. Another segment of the program can access the metadata of the tempDb database, but does not allow an INSERT.
5. Finally, using the query tool in pgAdmin3, it yields an error for the referes relation in tempDb, stating that the relation referes does not exist.
6. I have checked the properties of both the referes relation/table against the pg1 ones.  No differences aside from a datatype. (Work.wid is a small int and sutraNum is character varying.)
See the code with comments below.
I am using the same port, connection.  What can it be?


The following code

results in the following output:
n-  and Create Output as a Reference List

line 125 - in listenr - after setup, URL is jdbc:postgresql://localhost:5432/pg1
in reference listener - connection successful - LINE 129; user is postgres; password is ginkat8a; url is jdbc:postgresql://localhost:5432/pg1
LINE 133 in reference listener - before SELECT
LINE 136 in reference listener - after SELECT
1
line 139 - in rs.next() - successful SELECT!
However, if the 2 commented lines are changed to process work with the database tempDb and its relation referes rather than pg1 and work, the following error is obtained:

n reference listener - connection successful - LINE 129; user is postgres; password is ginkat8a; url is jdbc:postgresql://localhost:5432/tempDb
LINE 133 in reference listener - before SELECT
line 144, catch Logger
Nov 03, 2016 7:09:20 PM PaniniIndexing.views.FirstWbGuiPaniniIndexing$3 actionPerformed
SEVERE: ERROR: column "sutranum" does not exist
  Position: 8
org.postgresql.util.PSQLException: ERROR: column "sutranum" does not exist
  Position: 8
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)

Another segment of the progam accesses the metadata of tempDb but does not allow an INSERT:

which produces output:

in writeSutraPageTimesToDB - after setting up password ginkat8a; user is postgres, URL is jdbc:postgresql://localhost:5432/tempDb
line 830 in writeSutraPageTimmesToDB - after con
Database version:  9.4.1
Driver name:  PostgreSQL Native Driver
Driver version:  PostgreSQL 9.4 JDBC4 (build 1200)
  NOW FOR METADATA:
Column #1
  Name: sutraNum
  Type: varchar
Column #2
  Name: page
  Type: int2
Column #3
  Name: times
  Type: int2
line 863 in writeSutraPageTimmesToDB - before INSERT
Nov 03, 2016 7:11:21 PM PaniniIndexing.views.CommonRefAndDB writeSutraPageTimesToDB
SEVERE: ERROR: column "sutranum" of relation "referes" does not exist
  Position: 22
org.postgresql.util.PSQLException: ERROR: column "sutranum" of relation "referes" does not exist
  Position: 22
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)

As the above shows, the JDBC query can access the column metadata; but it also is unable to access the column sutranum.  Using the query tool in pgAdmin3, I have the following queries:

The first is successful, the second gives the error:
ERROR:  relation "public.referes" does not exist
LINE 11:   public.referes
           ^
********** Error **********

ERROR: relation "public.referes" does not exist
SQL state: 42P01
Character: 101

Note:  same error occurs if public is omitted.
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First thing I have to ask is, putting Java aside completely, does this new database work using the postgres tools?
Can you connect to this db and query that table using that SQL?
Can you see the metadata?

There's no point pushing ahead with the Java side if postgres itself is not happy.
 
David Ellis Rogers
Greenhorn
Posts: 16
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Does not work with new DB from the terminal tool:



another run on the old dDB:



Hope I did queries correctly.  I did not define a schema in either DB.
 
David Ellis Rogers
Greenhorn
Posts: 16
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I just did the following code.  This was in the terminal monitor.  Note that in 2 posts back the same was also successful using the pgAdmin3 tool.



The following was done with the new DB:
 
Knute Snortum
Bartender
Pie
Posts: 2882
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First, are you in the correct database?  I see you in pg1 and tempDb.  Which one has the tables you're looking for?  Type \l (that's an el) to list databases.

Next, what is the table name?  Type \d to list all the tables.

Next, what are the columns in the table?  Type \d <table_name>, where <table_name> is the name of the table you want to query.

Now create your query and see if it has the data you expect.
 
David Ellis Rogers
Greenhorn
Posts: 16
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First, for the new DB tempDb, which is unsuccessful on the query, but meta stuff is OK:


Now for the old DB, which is successful:


Just will not run query on new DB.
 
Knute Snortum
Bartender
Pie
Posts: 2882
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Something is obviously wrong with the table referes on tempdb.  If you can rebuild the data, I'd drop and create the table again -- or create a new table.
 
David Ellis Rogers
Greenhorn
Posts: 16
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I deleted the table and recreated it.
As shown from below, the \l, \d, and \d referes work correctly.  They also act correctly on the old DB.
The also works, as does i.
But the does not.
The i does work.
But in Java the fails, not recognizing sutraNum.

I need the variables presElement, page, and times in my Java program.  I have searched the internet unsuccessfully for an INSERT command that will take variables of some syntax such as in cases 2-4 below.  Must I do some kind of weird update command?  Note that in cases 2 and 3 below the following hint is offered: “ Hint: There is an entry for table "referes", but it cannot be referenced from this part of the query”.  A little more hint would be appreciated.  There must be a way that thousands of programmers insert rows into tables referring only to variables and not literals.  I am running PostgreSQL 9.4.

I tried the following cases from Java:

Case 1 results:
Works, but I need variables, as in other cases.
Case 2 results:

Case 3 results:

Case 4 results:


tempDb=# \l


tempDb=# \d


tempDb=# \d referes

tempDb=# select * from referes;

tempDb=# select * from referes;


The following fails, not recognizing column sutraNum:

Above results in error:
 
Knute Snortum
Bartender
Pie
Posts: 2882
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You've been very thorough in your research of the problem.  I'm going to pull out and address problems on separate posts.

My understanding is that you want the values of the Java variables prevElement, page, and times to be inserted into your table.  So let's look at this statement:

What will print? The value of page is 'page'.  This is because between quotes, everything except for a few escape characters is printed literally.  To get the value of the variable page into the String, you have to do something like this:

But there is another mechanism for doing this specifically with SQL in Java called Prepared Statements.  It will know when an SQL value needs quotes or not.  See if you can incorporate Prepared Statements into your code.
 
Knute Snortum
Bartender
Pie
Posts: 2882
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're making your life hard when you use camelCase variable names in SQL, because SQL is case insensitive normally.  To make it case sensitive or include spaces in column names, you need to use double quotes:

Notice that when you're quoting a columns name you use double quotes and when you're quoting a string literal you use single quotes.  To get the above SELECT statement into a Java String you would have to do something like this:

Try creating the table with the column sutranum or sutra_num.  Those work without quotes.
 
David Ellis Rogers
Greenhorn
Posts: 16
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, that fixed it.
 
Wilson Scott
Greenhorn
Posts: 3
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic