Conecting to a PostgreSQL database

Smart GWT Technical Q&A
1

Im using StartGWT EE.

I have been connecting to a MySQL database through a Datasource and its been working fine. I have now moved to a PostgreSQL database, its got the same table sturcture.
I have edited the ds.xml file and the server.properties file so that it can work with the new database. But when i try and do a fetch on the datasource i get the following error

=== 2011-10-07 16:13:43,034 [15-7] WARN  RequestContext - dsRequest.execute() failed:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 12
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
        at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
        at com.isomorphic.sql.SQLDriver.getTransformedResults(SQLDriver.java:375)
        at com.isomorphic.sql.SQLDriver.executeQuery(SQLDriver.java:625)
        at com.isomorphic.sql.SQLDataSource.executeNativeQuery(SQLDataSource.java:387)
        at com.isomorphic.sql.SQLDataSource.executeNativeQuery(SQLDataSource.java:381)
        at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1225)
        at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:289)
        at com.isomorphic.sql.SQLDataSource.executeFetch(SQLDataSource.java:233)
        at com.isomorphic.datasource.DataSource.execute(DataSource.java:1195)
        at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:721)
        at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:658)
        at com.isomorphic.application.AppBase.execute(AppBase.java:491)
        at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:1839)
        at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:199)
        at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:156)
        at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:121)
        at com.isomorphic.servlet.IDACall.doPost(IDACall.java:73)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
        at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:152)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
        at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
        at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:390)
        at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
        at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
        at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
        at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:440)
        at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:230)
        at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
        at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
        at org.mortbay.jetty.Server.handle(Server.java:326)
        at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
        at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:943)
        at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:756)
        at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)
        at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
        at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:410)
        at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)

Here is my ds.xml file

<DataSource ID="login" dataFormat="iscServer" serverType="sql" dataSourceVersion="1" dbName="myDatabase" schema="security" tableName="user" autoDeriveSchema="true">
    <fields>        
        <field name="user_id" type="text" required="true" title="Username" primaryKey="true"   />
        <field name="password" type="Password" required="true" title="Password"/>
        <field name="username" type="text" hidden="true" />
        <field name="column_numbers" type="int" hidden="false" />
        <field name="name1" type="text" hidden="true" />
        <field name="name2" type="text" hidden="true" />
        <field name="sname" type="text" hidden="true" />
        <field name="email" type="text" hidden="true" />
    </fields>
            
    <dropExtraFields>false</dropExtraFields>
</DataSource>

Here is PostgreSql settings in the server.properties file

# -------------- SETTINGS FOR PostgreSQL --------------------  
  
# These are the settings for use with the PostgreSQL database.  

# name of the database to use
 sql.myDatabase.driver.databaseName: myDatabase
# sql.myDatabase.driver.context:

# username and password that can create and modify tables in that database  
# this user must have the following privileges for the system to function  
# properly: create/alter/drop table; create/drop sequences;  
# insert/update/replace/delete rows.  
 sql.myDatabase.driver.user: postgres
 sql.myDatabase.driver.password: postgres
 sql.myDatabase.database.ansiMode: false
 sql.myDatabase.driver.driverType: thin

# hostname and port where the database server is installed
 sql.myDatabase.driver.serverName: localhost
 sql.myDatabase.driver.portNumber: 5432
 sql.myDatabase.pool.enabled: true
 sql.myDatabase.interface.credentialsInURL: false
 sql.myDatabase.driverName: postgresql
 sql.myDatabase.driver.useUnicode: true
 sql.myDatabase.driver.networkProtocol: tcp
 sql.myDatabase.interface.type: driverManager

 sql.myDatabase.driver: org.postgresql.Driver
 sql.myDatabase.database.type: postgresql
 sql.myDatabase.autoJoinTransactions: true
 sql.myDatabase.driver.url: jdbc:postgresql://localhost:5433/myDatabase

Any help would be greatly appreciated

2

Please show the complete server log for the request (always include this).

3

Here is the server log for the request

=== 2011-10-10 08:58:48,043 [33-6] INFO  RequestContext - URL: '/client/client/sc/IDACall', User-Agent: 'Mozilla/5.0 (Windows NT 5.1) AppleWebKit/535.1 (KHTML, like Gecko) Chrome/14.0.835.202 Safari/535.1': Safari with Accept-Encoding header
=== 2011-10-10 08:58:48,043 [33-6] DEBUG XML - Parsed XML from (in memory stream): 0ms
=== 2011-10-10 08:58:48,059 [33-6] DEBUG RPCManager - Processing 1 requests.
=== 2011-10-10 08:58:48,075 [33-6] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
        user_id:"a",
        password:"a"
    },
    operationConfig:{
        dataSource:"login",
        operationType:"fetch"
    },
    componentId:"isc_DynamicForm_0",
    appID:"builtinApplication",
    operation:"login_fetch",
    oldValues:{
        user_id:"a",
        password:"a"
    }
}
=== 2011-10-10 08:58:48,075 [33-6] INFO  IDACall - Performing 1 operation(s)
=== 2011-10-10 08:58:48,075 [33-6] DEBUG AppBase - [builtinApplication.login_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2011-10-10 08:58:48,075 [33-6] DEBUG AppBase - [builtinApplication.login_fetch] No public zero-argument method named '_login_fetch' found, performing generic datasource operation
=== 2011-10-10 08:58:48,075 [33-6] INFO  SQLDataSource - [builtinApplication.login_fetch] Performing fetch operation with
	criteria: {user_id:"a",password:"a"}	values: {user_id:"a",password:"a"}
=== 2011-10-10 08:58:48,090 [33-6] WARN  SQLWhereClause - [builtinApplication.login_fetch] Unable to find simple basetype for field password (declared type is Password)
=== 2011-10-10 08:58:48,090 [33-6] INFO  SQLDataSource - [builtinApplication.login_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
=== 2011-10-10 08:58:48,278 [33-6] INFO  SQLDataSource - [builtinApplication.login_fetch] Executing SQL query on 'myDatabase': SELECT user.column_numbers, user.email, user.name1, user.name2, user.password, user.sname, user.user_id, user.username FROM security.user WHERE (user.user_id='a' AND user.password='a')
=== 2011-10-10 08:58:48,278 [33-6] DEBUG PoolableSQLConnectionFactory - [builtinApplication.login_fetch] DriverManager fetching connection for myDatabase via jdbc url jdbc:postgresql://localhost:5433/myDatabase
=== 2011-10-10 08:58:48,278 [33-6] DEBUG PoolableSQLConnectionFactory - [builtinApplication.login_fetch] Passing credentials getConnection separately from JDBC URL
=== 2011-10-10 08:58:48,294 [33-6] DEBUG PoolableSQLConnectionFactory - [builtinApplication.login_fetch] Returning pooled Connection
=== 2011-10-10 08:58:48,294 [33-6] DEBUG SQLTransaction - [builtinApplication.login_fetch] Started new myDatabase transaction "9381256"
=== 2011-10-10 08:58:48,294 [33-6] INFO  SQLDriver - [builtinApplication.login_fetch] Executing SQL query on 'myDatabase': SELECT user.column_numbers, user.email, user.name1, user.name2, user.password, user.sname, user.user_id, user.username FROM security.user WHERE (user.user_id='a' AND user.password='a')
=== 2011-10-10 08:58:48,309 [33-6] WARN  RequestContext - dsRequest.execute() failed: 
=== 2011-10-10 08:58:48,309 [33-6] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
=== 2011-10-10 08:58:48,309 [33-6] DEBUG SQLTransaction - Rolling back myDatabase transaction "9381256"
=== 2011-10-10 08:58:48,309 [33-6] DEBUG RPCManager - non-DMI response, dropExtraFields: false
=== 2011-10-10 08:58:48,309 [33-6] DEBUG SQLTransaction - Ending myDatabase transaction "9381256"
4

Note this line:

This code from this line of code in the framework:

 context.staticLog.warn("dsRequest.execute() failed: ", e);

Some piece of downstream code, presumably in Postgres’ JDBC driver, has thrown an exception with a blank error message. Not very useful.

To get more information, you could add a DMI and put a try..catch around the call to dsRequest.execute(), then print the stack trace with the exception you catch.

5

I managed to find this exception which i think is from the blank exception that was getting thrown.

00:01:48.157 [ERROR] 08:55:48.001:XRP6:WARN:RPCManager:ERROR: syntax error at or near "." Position: 12, response: {operationId: "login_fetch", clientContext: undef, context: Obj, transactionNum: 0, httpResponseCode: 200, httpResponseText: "//isc_RPCResponseStart-->[{data:"ERROR: ..."[173], xmlHttpRequest: [object XMLHttpRequest], transport: "xmlHttpRequest", status: -1, clientOnly: undef, httpHeaders: Obj, isStructured: true, callbackArgs: null, results: Obj, data: "ERROR: syntax error at or near "."\n Pos..."[49], invalidateCache: false, isDSResponse: true, queueStatus: -1}
com.smartgwt.client.core.JsObject$SGWT_WARN: 08:55:48.001:XRP6:WARN:RPCManager:ERROR: syntax error at or near "."   Position: 12, response: {operationId: "login_fetch",
clientContext: undef,
context: Obj,
transactionNum: 0,
httpResponseCode: 200,
httpResponseText: "//isc_RPCResponseStart-->[{data:"ERROR: ..."[173],
xmlHttpRequest: [object XMLHttpRequest],
transport: "xmlHttpRequest",
status: -1,
clientOnly: undef,
httpHeaders: Obj,
isStructured: true,
callbackArgs: null,
results: Obj,
data: "ERROR: syntax error at or near "."\n  Pos..."[49],
invalidateCache: false,
isDSResponse: true,
queueStatus: -1} 	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) 	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) 	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) 	at java.lang.reflect.Constructor.newInstance(Constructor.java:513) 	at com.google.gwt.dev.shell.MethodAdaptor.invoke(MethodAdaptor.java:105) 	at com.google.gwt.dev.shell.MethodDispatch.invoke(MethodDispatch.java:71) 	at com.google.gwt.dev.shell.OophmSessionHandler.invoke(OophmSessionHandler.java:157) 	at com.google.gwt.dev.shell.BrowserChannelServer.reactToMessages(BrowserChannelServer.java:281) 	at com.google.gwt.dev.shell.BrowserChannelServer.processConnection(BrowserChannelServer.java:531) 	at com.google.gwt.dev.shell.BrowserChannelServer.run(BrowserChannelServer.java:352) 	at java.lang.Thread.run(Thread.java:662)

I have also tried making a custom query in the datasource file.

<operationBinding operationType="fetch" operationId="checkUserName">
	<tableClause>
	security.user 
	</tableClause>
	<selectClause>
	*
	</selectClause>
	<whereClause>
	$criteria.user_id = user_id AND
	$criteria.password = password
	</whereClause>    
</operationBinding>

So i think the exception was getting thrown as the query was trying to call the columns of the table like user.user_id when it should of called it like user_id or security.user.user_id.

Is there any way to force the query to drop the table name or to add the schema name to the column name?

6

It’s not clear how your tables are defined and what the correct qualification would be. Could you show the table schema, and the correct syntax to query it?

7

This is the create script for the table. “security” is the name of the schema that the table is in and “user” is the name of the table.

CREATE TABLE "security"."user"
(
  "password" character varying(20),
  name1 character varying(30),
  name2 character varying(30),
  sname character varying(30),
  user_id character varying(50) NOT NULL,
  email character varying(256),
  column_numbers integer DEFAULT 2,
  username character varying(120),
  CONSTRAINT pk_security_user_user_id PRIMARY KEY (user_id)
)

So to do a query for that table it should look like

SELECT * FROM security.user WHERE user_id='a' AND password='a'

or

SELECT * FROM security.user WHERE security.user.user_id='a' AND security.user.password='a'

But the query that it is doing is like this

SELECT * FROM security.user WHERE user.user_id='a' AND user.password='a'

So Postgres doesn’t know what “user.user_id” is as it needs the schema name added to the front like “security.user.user_id” or the table name dropped all together like “user_id”.

8

It looks like user is some kind of keyword in Postgres so it automaticly put “” around it when creating the table. So when i renamed it to user1 it worked fine.

9

Aha. Thanks for letting us know.