hi john,
with ojb 1.1 you can use ojb's quoting character that will be replaced by the dbms-specific character when generating the sql.
column names containing spaces may also be a problem with ojb 1.0.x because of the rather primitive parsing.
afaik ojb will use the index rather than the name of the column to retrieve data from resultset.
satisfied customers are always welcome ;)
jakob
JKramer schrieb:
Hi Jakob,
I just read your post after doing the following JDBC level testing and writing it up. It "appears" to me that OJB 1.0.3 is handling the quoted column and table names OK. Is there something more sublte going on that i'm missing?
.......................................................................
I've written some POJDBC to test the JTDS driver. Here's the main part of my query string using the square brackets to enclose the "unusual" field names.
This query executes just fine. And if I used the ResultSet.getString() as shown next, I get the data I'm looking for:
String effDate = rs.getString("eff date");
Note the absence of the square brackets above. So with JDBC, if I use square brackets in the SELECT and omit them in the getting the results then everybody is happy!
Adding the square brackets to the getString as they are used in the SELECT throws.
String effDate = rs.getString("[eff date]");
java.sql.SQLException: Invalid column name [eff date].
at net.sourceforge.jtds.jdbc.JtdsResultSet.findColumn(JtdsResultSet.java:938)
at net.sourceforge.jtds.jdbc.JtdsResultSet.getString(JtdsResultSet.java:1173)
at com.ascenditsolutions.dbtesting.ColumnNameTest.readLergRecords(ColumnNameTest.java:161)
at com.ascenditsolutions.dbtesting.ColumnNameTest.main(ColumnNameTest.java:118)
Exception in thread "main"
The actual SQLException above showing the brackets in the column name is what I was seening with the OJB as well, leading me to believe that OJB is properly utilizing the square brackets in both the query and the getString. Maybe perpared statment behaviour would be different?
I't seems that OJB is doing what it should be and JTDS is the problem.
Thanks for walking through this with me!
Another satisfied OJB Customer, John
PS: One last question. The ResultSet.getString(int pos) calls work in the JDBC test. Is there some way to force OJB to utilize the column IDs instead of the column name in reading the result sets?
===============================================================================================
Tried a few other things to walk around the problem a bit...
String baseQuery = "SELECT switch, 'eff date', [v-coord], [h-coord] FROM [Lerg 7] " +
"WHERE switch like '";
String effDate = rs.getString("'eff date'");
java.sql.SQLException: Invalid column name 'eff date'.
at net.sourceforge.jtds.jdbc.JtdsResultSet.findColumn(JtdsResultSet.java:938)
at net.sourceforge.jtds.jdbc.JtdsResultSet.getString(JtdsResultSet.java:1173)
at com.ascenditsolutions.dbtesting.ColumnNameTest.readLergRecords(ColumnNameTest.java:161)
at com.ascenditsolutions.dbtesting.ColumnNameTest.main(ColumnNameTest.java:118)
Exception in thread "main"
The following blows up when the query is executed:
String baseQuery = "SELECT switch, [eff date], v-coord, h-coord FROM [Lerg 7] " +
"WHERE switch like '";
java.sql.SQLException: Invalid column name 'v'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:364)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2778)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2214)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:597)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:343)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1184)
at com.ascenditsolutions.dbtesting.ColumnNameTest.readLergRecords(ColumnNameTest.java:156)
at com.ascenditsolutions.dbtesting.ColumnNameTest.main(ColumnNameTest.java:118)
Exception in thread "main"
Jakob Braeuchi wrote:
hi john,
quoting table- and column-names is supported in OJB 1.1 .
jakob
JKramer schrieb:
Revered Luminaries of the OJB Community:
First off, let me say that I'm FORCED to access data from a table provided by a client that utilizes column names with spaces, dashes, and forward slashes. Again NOT my choice or design - just wanted to get that out of the way! 8^) These guys are M$ zombies, what can I say!?
I'm using db-ojb-1.0.3 with jtds-1.0.3 connecting to MS Sql Server 2000.
(BTW... the application is also connecting to databases on PostgreSQL on a Linux server (my choices) which works great!)
The "closest" I can get to a successful query is to enclose the column names in square brackets within the repository file. Note that the table name also contains a space and enclosing it in square brackets seems to work fine.
The exceptions I'm seeing are:
org.apache.ojb.broker.PersistenceBrokerSQLException: SQLException during the execution of the query (for com.ascenditsolutions.lca.model.vo.rio.Lerg7VO): Invalid column name 'eff'
The related repository definition:
<field-descriptor id="4" name="effDate" column="eff date" jdbc-type="TIMESTAMP" />
OK, I'm not using "eff date" at this point in time, let's skip it for now and see how far we get... not far enough:
org.apache.ojb.broker.PersistenceBrokerException: Error reading class type: com.ascenditsolutions.lca.model.vo.rio.Lerg7VO from result set, current read field was vCoord
With repository definition:
<field-descriptor id="10" name="vCoord" column="v-coord" jdbc-type="VARCHAR" length="5" />
Getting "v-coord" is essential.
Now what?
I'm stuck. If someone has a suggestion on what I need to do, I'd really appreciate it. Otherwise, I'm considering utilizing a view with aliases for the malignant columns - will this work?
Thanks! John A simple and humble OJB User.
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
