Re: [castor-user] castor puts table and column names in quotation marks

Wed, 18 Apr 2007 10:20:36 -0700 

Thanks for that information.
If Castor would not quote table and column names, it would be impossible to use case sensitive names with postgresql. On the other hand it sounds quite difficult to me to find out that postgresql silently converts names that are not quoted in DDL script to lower case while Castor creates queries that preserve case. 


For Castor to be consistent, we may need to add quoting also to DDL 
generator for postgresql to preserve case.


Regards
Ralf


Abid Hussain schrieb:

Hi Ralf,


thanks for help. I got the problem solved by changing all table and 
column names in my mapping file to lower case, e.g.:

<map-to table="Log_Module" /> changed to
<map-to table="log_module" />

The problem was (and this is for all postgres users):

When you use table/column names in mixed case (e.g. first character is 
upper case, rest is lower case), postgres somehow requires that the 
names are written in quotes. So it seems that my jdbc-driver (or 
castor?) putted all names in quotes, because in my mapping file they 
where in mixed case.
When I created the tables, I didn't put the names in quotes, although 
they where written in mixed case, e.g.:

CREATE TABLE Log_Module (
    Id INTEGER DEFAULT nextval('Log_Module_Id_Seq') PRIMARY KEY,
    Node_Name VARCHAR,
    Date BIGINT,
    Module VARCHAR,
    Value DOUBLE,
    Node_Id INTEGER NOT NULL
);

But the actual names in my DB after creating the tables where in lower 
case. That is because, when you create a table with names in mixed case 
without quoting them, postgres folds them to lower case.


Finally I have to say, that this really sucks!

Best regards,

Abid


Ralf Joachim schrieb:

Hi Abid,


as far as I recall the quoting of table and column names is intended 
to allow column names with spaces which are supported by some database 
engines.



Do you know if postgresql supports column names with spaces and how 
they should be quoted (e.g. with single instead of double quotes)?


What's the database driver you are using?

Regards
Ralf


Abid Hussain schrieb:

Hi everybody,


I'm new to castor, so maybe I'm getting something wrong. My problem 
is that, when trying to store an object in my Postgres 8.1 DB, castor 
puts the name of the table and of the columns in quotation marks, 
which doesn't work, like (see also stack trace below):
INSERT INTO "Log_Module" 
("Id","Node_Id","Node_Name","Module","Value","Date") VALUES 
(?,?,?,?,?,?)

The correct statement would be:

INSERT INTO Log_Module (Id,Node_Id,Node_Name,Module,Value,Date) 
VALUES (?,?,?,?,?,?)


The class associated with the object is:
public class ModuleLoggerEntity {
    private int id;
    private int nodeId;
    private String module;
    private double value;
    private long date;
    private String name;

    // Getters/Setters, Constructor...
}

My mapping file is:
<mapping>
    <class name="scatterweb.logger.ModuleLoggerEntity" identity="id">
        <cache-type type="time-limited" />
        <map-to table="Log_Module" />
        <field name="id" type="integer">
            <sql name="Id" type="integer" />
        </field>
        <!-- more fields... -->
    </class>
    <key-generator name="SEQUENCE">
        <param name="sequence" value="log_module_id_seq"/>
    </key-generator>
</mapping>

My JDC config is:
<jdo-conf>
    <database name="scatterweb" engine="postgresql">
        <driver url="jdbc:postgresql://localhost:5432/scatterweb"
            class-name="org.postgresql.Driver">
            <param name="user" value="xxx" />
            <param name="password" value="xxx" />
        </driver>
        <mapping href="LogModuleMapping.xml" />
    </database>
    <transaction-demarcation mode="local">
        <transaction-manager name="local" />
    </transaction-demarcation>
</jdo-conf>


Below you see the stack trace. Does anybody know what's going wrong 
in this case?


Best regards,

Abid


FATAL: [org.exolab.castor.jdo.engine.SQLStatementCreate] A fatal 
error occurred while creating/updating 
scatterweb.logger.ModuleLoggerEntity
 using SQL: INSERT INTO "Log_Module" 
("Id","Node_Id","Node_Name","Module","Value","Date") VALUES 
(?,?,?,?,?,?)

java.sql.SQLException: ERROR: relation "Log_Module" does not exist

    at 
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471) 

    at 
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256) 

    at 
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175) 

    at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:389) 

    at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330) 

    at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:282) 

    at 
org.exolab.castor.jdo.engine.SQLStatementCreate.executeStatement(SQLStatementCreate.java:277) 


    at org.exolab.castor.jdo.engine.SQLEngine.create(SQLEngine.java:313)

    at 
org.exolab.castor.persist.ClassMolder.create(ClassMolder.java:694)

    at org.exolab.castor.persist.LockEngine.create(LockEngine.java:496)

    at 
org.castor.persist.AbstractTransactionContext.walkObjectsToBeCreated(AbstractTransactionContext.java:817) 

    at 
org.castor.persist.AbstractTransactionContext.create(AbstractTransactionContext.java:777) 

    at 
org.exolab.castor.jdo.engine.AbstractDatabaseImpl.create(AbstractDatabaseImpl.java:302) 


    at scatterweb.logger.ModuleLogger.flush(ModuleLogger.java:157)
    at scatterweb.logger.ModuleLogger.save(ModuleLogger.java:188)
    at scatterweb.logger.TestLogger.main(TestLogger.java:40)

org.exolab.castor.jdo.PersistenceException: Nested error: 
java.sql.SQLException: ERROR: relation "Log_Module" does not exist: 
ERROR: relatio

n "Log_Module" does not exist

    at 
org.exolab.castor.jdo.engine.SQLStatementCreate.executeStatement(SQLStatementCreate.java:332) 


    at org.exolab.castor.jdo.engine.SQLEngine.create(SQLEngine.java:313)

    at 
org.exolab.castor.persist.ClassMolder.create(ClassMolder.java:694)

    at org.exolab.castor.persist.LockEngine.create(LockEngine.java:496)

    at 
org.castor.persist.AbstractTransactionContext.walkObjectsToBeCreated(AbstractTransactionContext.java:817) 

    at 
org.castor.persist.AbstractTransactionContext.create(AbstractTransactionContext.java:777) 

    at 
org.exolab.castor.jdo.engine.AbstractDatabaseImpl.create(AbstractDatabaseImpl.java:302) 


    at scatterweb.logger.ModuleLogger.flush(ModuleLogger.java:157)
    at scatterweb.logger.ModuleLogger.save(ModuleLogger.java:188)
    at scatterweb.logger.TestLogger.main(TestLogger.java:40)

Caused by: java.sql.SQLException: ERROR: relation "Log_Module" does 
not exist
    at 
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471) 

    at 
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256) 

    at 
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175) 

    at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:389) 

    at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330) 

    at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:282) 

    at 
org.exolab.castor.jdo.engine.SQLStatementCreate.executeStatement(SQLStatementCreate.java:277) 


    ... 9 more

Caused by: java.sql.SQLException: ERROR: relation "Log_Module" does 
not exist
    at 
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471) 

    at 
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256) 

    at 
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175) 

    at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:389) 

    at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330) 

    at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:282) 

    at 
org.exolab.castor.jdo.engine.SQLStatementCreate.executeStatement(SQLStatementCreate.java:277) 


    at org.exolab.castor.jdo.engine.SQLEngine.create(SQLEngine.java:313)

    at 
org.exolab.castor.persist.ClassMolder.create(ClassMolder.java:694)

    at org.exolab.castor.persist.LockEngine.create(LockEngine.java:496)

    at 
org.castor.persist.AbstractTransactionContext.walkObjectsToBeCreated(AbstractTransactionContext.java:817) 

    at 
org.castor.persist.AbstractTransactionContext.create(AbstractTransactionContext.java:777) 

    at 
org.exolab.castor.jdo.engine.AbstractDatabaseImpl.create(AbstractDatabaseImpl.java:302) 


    at scatterweb.logger.ModuleLogger.flush(ModuleLogger.java:157)
    at scatterweb.logger.ModuleLogger.save(ModuleLogger.java:188)
    at scatterweb.logger.TestLogger.main(TestLogger.java:40)

Caused by: java.sql.SQLException: ERROR: relation "Log_Module" does 
not exist
    at 
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471) 

    at 
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256) 

    at 
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175) 

    at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:389) 

    at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330) 

    at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:282) 

    at 
org.exolab.castor.jdo.engine.SQLStatementCreate.executeStatement(SQLStatementCreate.java:277) 


    at org.exolab.castor.jdo.engine.SQLEngine.create(SQLEngine.java:313)

    at 
org.exolab.castor.persist.ClassMolder.create(ClassMolder.java:694)

    at org.exolab.castor.persist.LockEngine.create(LockEngine.java:496)

    at 
org.castor.persist.AbstractTransactionContext.walkObjectsToBeCreated(AbstractTransactionContext.java:817) 

    at 
org.castor.persist.AbstractTransactionContext.create(AbstractTransactionContext.java:777) 

    at 
org.exolab.castor.jdo.engine.AbstractDatabaseImpl.create(AbstractDatabaseImpl.java:302) 


    at scatterweb.logger.ModuleLogger.flush(ModuleLogger.java:157)
    at scatterweb.logger.ModuleLogger.save(ModuleLogger.java:188)
    at scatterweb.logger.TestLogger.main(TestLogger.java:40)

org.exolab.castor.jdo.PersistenceException: This thread is already 
associated with a transaction in progress
    at 
org.exolab.castor.jdo.engine.LocalDatabaseImpl.begin(LocalDatabaseImpl.java:137) 


    at scatterweb.logger.ModuleLogger.flush(ModuleLogger.java:154)
    at scatterweb.logger.ModuleLogger.save(ModuleLogger.java:188)
    at scatterweb.logger.TestLogger.main(TestLogger.java:40)






--

Syscon Ingenieurbüro für Meß- und Datentechnik GmbH
Ralf Joachim
Raiffeisenstraße 11
72127 Kusterdingen
Germany

Tel.   +49 7071 3690 52
Mobil: +49 173 9630135
Fax    +49 7071 3690 98

Internet: www.syscon.eu
E-Mail: [EMAIL PROTECTED]

Sitz der Gesellschaft: D-72127 Kusterdingen
Registereintrag: Amtsgericht Stuttgart, HRB 382295
Geschäftsleitung: Jens Joachim, Ralf Joachim

---------------------------------------------------------------------
To unsubscribe from this list please visit:

   http://xircles.codehaus.org/manage_email























					Reply via email to