RE: Trouble with SQLServerDriver

From: Gentry, Michael \(Contractor\) ("Gentry,)
Date: Tue Jun 07 2005 - 14:05:47 EDT

  • Next message: Gentry, Michael \(Contractor\): "RE: Trouble with SQLServerDriver"

    Yeah, you have to be careful with DB reserved words. Our schema has a
    TransactionTable which maps to a Transaction object. Can't name the
    table Transaction, too, because that is an SQL word. The easiest thing
    for you to do is call your table UserTable (or similar), but name your
    class User so it functions as you are expecting.

    /dev/mrg

    -----Original Message-----
    From: Andrus Adamchik [mailto:andru..bjectstyle.org]
    Sent: Tuesday, June 07, 2005 12:20 PM
    To: cayenne-use..bjectstyle.org
    Subject: Re: Trouble with SQLServerDriver

    I'd guess this is because "User" is a SQLServer keyword and it requires
    special handling when used as identifier.

    We have a bug report already -
    http://objectstyle.org/cayenne/lists/cayenne-devel/2005/06/0003.html (I
    guess I'll raise it in priority as this seems to come up pretty often).

    One workaround would be to use a different name for this table. Another
    way (I haven't tested it, but it should probably work) is to enter
    [User]
    as a table name in CayenneModeler.

    Hope this helps
    Andrus

    > Hi.
    >
    > I'm trying to use Cayenne orm-tool for some small test
    > project using Microsoft SQL server database. And there
    > are many exceptions during all work with DataObjects,
    > for example:
    >
    > 1.)
    > ...
    > DataContext ctxt =
    > BasicServletConfiguration.getDefaultContext(request.getSession());
    > SelectQuery query = new SelectQuery(User.class);
    > List users = ctxt.performQuery(query);
    > ...
    >
    > throw
    > "
    > Caused by: java.sql.SQLException:
    > [Microsoft][SQLServer 2000 Driver for
    > JDBC][SQLServer]Incorrect syntax near the keyword
    > 'User'.
    > at
    > com.microsoft.jdbc.base.BaseExceptions.createException(Unknown
    > Source)
    > at
    > com.microsoft.jdbc.base.BaseExceptions.getException(Unknown
    > Source)
    > at
    > com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown
    > Source)
    > at
    > com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown
    > Source)
    > at
    >
    com.microsoft.jdbc.sqlserver.tds.TDSExecuteRequest.processReplyToken(Unk
    nown
    > Source)
    > at
    > com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown
    > Source)
    > at
    >
    com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Un
    known
    > Source)
    > at
    > com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown
    > Source)
    > at
    > com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown
    > Source)
    > at
    > com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown
    > Source)
    > at
    > com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown
    > Source)
    > at
    > com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(Unknown
    > Source)
    > at
    > com.microsoft.jdbc.base.BasePreparedStatement.executeQuery(Unknown
    > Source)
    > at
    > org.objectstyle.cayenne.access.DataNode.runSelect(DataNode.java:386)
    > at
    >
    org.objectstyle.cayenne.access.DataNode.performQueries(DataNode.java:350
    )
    > ... 23 more
    > "
    >
    > 2.)
    > ...
    > DataContext ctxt =
    > BasicServletConfiguration.getDefaultContext(request.getSession());
    > User anUser = (User)
    > ctxt.createAndRegisterNewObject("User");
    > anUser.setName("SomeName");
    > ctxt.commitChanges(Level.WARN);
    > ...
    >
    > throw next "Commit" exception:
    >
    > Caused by: java.sql.SQLException:
    > [Microsoft][SQLServer 2000 Driver for
    > JDBC][SQLServer]Syntax error converting the varchar
    > value 'User' to a column of data type int.
    > at
    > com.microsoft.jdbc.base.BaseExceptions.createException(Unknown
    > Source)
    > at
    > com.microsoft.jdbc.base.BaseExceptions.getException(Unknown
    > Source)
    > at
    > com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown
    > Source)
    > at
    > com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown
    > Source)
    > at
    >
    com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown
    > Source)
    > at
    > com.microsoft.jdbc.sqlserver.tds.TDSRequest.getRow(Unknown
    > Source)
    > at
    >
    com.microsoft.jdbc.sqlserver.SQLServerImplResultSet.positionCursor(Unkno
    wn
    > Source)
    > at com.microsoft.jdbc.base.BaseResultSet.next(Unknown
    > Source)
    > at
    >
    org.objectstyle.cayenne.dba.sybase.SybasePkGenerator.pkFromDatabase(Syba
    sePkGenerator.java:184)
    > at
    >
    org.objectstyle.cayenne.dba.JdbcPkGenerator.generatePkForDbEntity(JdbcPk
    Generator.java:325)
    > at
    >
    org.objectstyle.cayenne.access.util.PrimaryKeyHelper.createPermIdsForObj
    Entity(PrimaryKeyHelper.java:183)
    > at
    >
    org.objectstyle.cayenne.access.ContextCommit.createPrimaryKeys(ContextCo
    mmit.java:580)
    > at
    >
    org.objectstyle.cayenne.access.ContextCommit.commit(ContextCommit.java:1
    42)
    > at
    >
    org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.jav
    a:1266)
    > ... 17 more
    >
    > Can you help me? Where is problem?
    > I'm using Microsoft drivers:
    > msbase.jar
    > mssqlserver.jar
    > msutil.jar
    >
    >
    >
    >
    >
    > There some lines of code and xml files that was
    > generated by GUU tool:
    >
    > 1 - User.java:
    > "
    > import java.util.List;
    >
    > public class User extends
    > org.objectstyle.cayenne.CayenneDataObject {
    >
    > public static final String NAME_PROPERTY = "name";
    > public static final String ADDRESS_ARRAY_PROPERTY
    > = "addressArray";
    >
    > public static final String USER_ID_PK_COLUMN =
    > "user_id";
    >
    > public void setName(String name) {
    > writeProperty("name", name);
    > }
    >
    > public String getName() {
    > return (String) readProperty("name");
    > }
    >
    >
    > public void addToAddressArray(Address obj) {
    > addToManyTarget("addressArray", obj, true);
    > }
    >
    > public void removeFromAddressArray(Address obj) {
    > removeToManyTarget("addressArray", obj, true);
    > }
    >
    > public List getAddressArray() {
    > return (List) readProperty("addressArray");
    > }
    > }
    > "
    >
    > 2 - cayenne.xml:
    > "
    > <?xml version="1.0" encoding="utf-8"?>
    > <domains project-version="1.1">
    > <domain name="Domain">
    > <map name="Map" location="Map.map.xml"/>
    > <node name="DomainNode"
    > datasource="DomainNode.driver.xml"
    >
    > adapter="org.objectstyle.cayenne.dba.sqlserver.SQLServerAdapter"
    >
    > factory="org.objectstyle.cayenne.conf.DriverDataSourceFactory">
    > <map-ref name="Map"/>
    > </node>
    > </domain>
    > </domains>
    > "
    >
    > 3 - DomainNode.xml:
    > "
    > <?xml version="1.0" encoding="utf-8"?>
    > <driver project-version="1.1"
    > class="com.microsoft.jdbc.sqlserver.SQLServerDriver">
    > <url
    >
    value="jdbc:microsoft:sqlserver://Paradise;databaseName=TESTCAYENNE;"/>
    > <connectionPool min="1" max="20" />
    > <login userName="sa" password="lvivteamsql"/>
    > </driver>
    > "
    >
    > 4 - Map.xml
    > "
    > <?xml version="1.0" encoding="utf-8"?>
    > <data-map project-version="1.1">
    > <property name="defaultPackage" value="com.cayenne"/>
    > <property name="defaultSchema" value="dbo"/>
    > <db-entity name="Address" schema="dbo"
    > catalog="TESTCAYENEE">
    > <db-attribute name="address" type="VARCHAR"
    > isMandatory="true" length="50"/>
    > <db-attribute name="address_id" type="INTEGER"
    > isPrimaryKey="true" isMandatory="true" length="10"/>
    > <db-attribute name="user_id" type="INTEGER"
    > isMandatory="true" length="10"/>
    > </db-entity>
    > <db-entity name="User" schema="dbo"
    > catalog="TESTCAYENEE">
    > <db-attribute name="name" type="VARCHAR"
    > isMandatory="true" length="50"/>
    > <db-attribute name="user_id" type="INTEGER"
    > isPrimaryKey="true" isMandatory="true" length="10"/>
    > </db-entity>
    > <obj-entity name="Address"
    > className="com.cayenne.Address"
    > dbEntityName="Address">
    > <obj-attribute name="address"
    > type="java.lang.String" db-attribute-path="address"/>
    > </obj-entity>
    > <obj-entity name="User" className="com.cayenne.User"
    > dbEntityName="User">
    > <obj-attribute name="name" type="java.lang.String"
    > db-attribute-path="name"/>
    > </obj-entity>
    > <db-relationship name="toUser" source="Address"
    > target="User" toMany="false">
    > <db-attribute-pair source="user_id"
    > target="user_id"/>
    > </db-relationship>
    > <db-relationship name="addressArray" source="User"
    > target="Address" toMany="true">
    > <db-attribute-pair source="user_id"
    > target="user_id"/>
    > </db-relationship>
    > <obj-relationship name="toUser" source="Address"
    > target="User" db-relationship-path="toUser"/>
    > <obj-relationship name="addressArray" source="User"
    > target="Address" db-relationship-path="addressArray"/>
    > </data-map>
    > "
    >
    >
    >
    > __________________________________
    > Discover Yahoo!
    > Stay in touch with email, IM, photo sharing and more. Check it out!
    > http://discover.yahoo.com/stayintouch.html



    This archive was generated by hypermail 2.0.0 : Tue Jun 07 2005 - 14:05:52 EDT