I should've added this, too ...
If I'm creating my own DB/Schema, I like to name the tables plural --
Users, Roles, Transactions, LineItems, etc, but name the classes
singular -- User, Role, Transaction, LineItem, etc. This almost always
ensures you won't use a DB reserved word and to me makes more sense (the
Users table is a collection of all of the users, not just one, but a
User object represents a single object from the Users table).
/dev/mrg
-----Original Message-----
From: Gentry, Michael (Contractor) [mailto:michael_gentr..anniemae.com]
Sent: Tuesday, June 07, 2005 2:06 PM
To: cayenne-use..bjectstyle.org
Subject: 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:26:05 EDT