Re: java.sql.SQLException: ORA-06550: line 1, column 7:,PLS-00201: identifier 'CONT_ORDER_INS' must be declared
I am 99.99% sure that this is a privilege issue. Does the user executing the procedure own it, or is it in another schema? Larry On 3/20/07, Hendry [EMAIL PROTECTED] wrote: Just tried it, and it still produce the same error. Regards, Hendry Clinton Begin wrote: Try specifying the owning schema. The error suggests that it can't find the proc. Clinton
Re: Abator generated SqlMap.xml files
Abator only uses the catalog and schema in the file names if you specify
them in your configuration. I don't know Postgres, but with SQL Server you
can leave the catalog out of the table configuration elements and specify
the default database name on the connection URL.
Jeff Butler
On 3/19/07, Mark Volkmann [EMAIL PROTECTED] wrote:
When I run Abator against a MySQL database I get files with names
like {table-name}_SqlMap.xml.
When I run against Postgres or SQL Server I get files with names like
{database_name}_{table-name}_SqlMap.xml.
Is there anything I can do to get Abator to not add the database name
prefix for Postgres and SQL Server so I don't have to change my code
when the database type changes?
Re: Locale problems?
Larry, Thanks for the response. I'll have a look at the example you have suggested. I am using a date jdbc type for the date field as I only need dates in the form -MM-dd rather than the extended datetime format. Here are some more interesting things to think about: 1) I can successfully persist into the same table using other methods in the SqlMap. Therefore the date field is successfully populated. 2) To reiterate - the code works fine in my local environment and when connecting to the remote database. This seems to point to the error being in the code rather than the database. The query being run to cause the error is a very simple select * from statement, matching on one varchar field. It uses a resultMap, which directly maps onto a class. I can remove the resultMap and map directly using resultClass but the same error occurs. This seems to suggest it isn't the resultMap. Any clues? I'm lost for ideas right now. Thanks, Andrew Quoting Larry Meadors [EMAIL PROTECTED]: Are you using strings for dates or dates for dates? If you are using strings (a really bad idea, btw) you can set the locale on the command line when you start Java, or in code: http://www.exampledepot.com/egs/java.util/SetDefLocale.html Larry On 3/20/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: All, I have an application that uses SqlMaps. I have it working fine on my local machine. I also have it working fine when I use a remote database hosted by my service provider. I have trouble when the exact same application code is then used on the server hosted by my service provider. This throws an error when trying to read an object from the database. It fails to parse the date. All dates are in the format -MM-dd. My local machine is in the UK and the service provider's server in the USA. I am wondering if there is some kind of locale issue here that iBatis needs to take into account? Does anyone have any advice they can give me as it is really frustratiing as I know the code works on my local machine Best regards, Andrew
Re: Abator generated SqlMap.xml files
I use Postgres with Abator and don't have the db name in the
$TABLE_SqlMap.xml file names. Here's what one of my table elements
looks like:
table schema= tableName=SUGGESTION
domainObjectName=Suggestion
property name=useActualColumnNames value=false/
/table
The schema is then specified on the JDBC URL
(connectionURL=jdbc:postgresql://localhost/suggestions), like Jeff
said.
Cheers,
Chris
On Tue, 2007-03-20 at 07:17 -0500, Jeff Butler wrote:
Abator only uses the catalog and schema in the file names if you
specify them in your configuration. I don't know Postgres, but with
SQL Server you can leave the catalog out of the table configuration
elements and specify the default database name on the connection URL.
Jeff Butler
On 3/19/07, Mark Volkmann [EMAIL PROTECTED] wrote:
When I run Abator against a MySQL database I get files with
names
like {table-name}_SqlMap.xml.
When I run against Postgres or SQL Server I get files with
names like
{database_name}_{table-name}_SqlMap.xml.
Is there anything I can do to get Abator to not add the
database name
prefix for Postgres and SQL Server so I don't have to change
my code
when the database type changes?
Re: ibatis, XML and special characters
Hi It is a German Language character umlaut here is more information about it http://en.wikipedia.org/wiki/Germanic_umlaut I try to change the database driver encoding, i am using DB2 on As400 as database, and see if it works. But the feature in ibatis to change encoding on DOM builder will be great, specially if you dealing with non - english database. Ashish On 3/19/07, Clinton Begin [EMAIL PROTECTED] wrote: Yeah, that's definitely a character encoding mismatch. You can either set your database driver to a different encoding (check your docs), or we could possibly introduce a feature in iBATIS that will let you set the encoding on the DOM builder so that it understands whichever character is causing the problem. Which character is it though? It almost looks like it was complaining about -- (double dashes), which are reserved for closing comments IIRC. Clinton On 3/19/07, Ashish Kulkarni [EMAIL PROTECTED] wrote: Hi I have posted another thread with detail explaination this is the thread http://www.nabble.com/forum/ViewPost.jtp?post=9499524framed=y if you can take a look and tell me if you have any solution for it Ashish On 3/18/07, j-lists [EMAIL PROTECTED] wrote: I would guess that this is a database encoding problem, have you confirmed that the s are causing a problem or just the non-ascii characters? If the driver is getting generating invalid UTF16 for Java (internally) that might be causing the failure... unfortunately this is a real pain to debug and fix. Bulk loaders can fill varchars with badly encoded text that give trouble. Just a theory. -J On 3/12/07, Yuvraj Shinde [EMAIL PROTECTED] wrote: You can use CDATA tag. From: Ashish Kulkarni [mailto: [EMAIL PROTECTED] Sent: Monday, March 12, 2007 5:47 AM To: user-java@ibatis.apache.org Subject: ibatis, XML and special characters Hi I am using ibatis to run a SQL statement which returns a XML as output as shown below, select id=getItemSpeicalNotes resultClass=xml parameterClass=java.math.BigDecimal xmlResultName=ITEMSPECIALNOTES SELECT P706DETL as SPECIALNOTE , PDLINE as PDLINENOTES FROM PU1706, PU1013 WHERE P706ORD = PDPORD and PDSPEC = P706SPEC and PDPORD = #value# order by P706LINE /select I am having issues when the data returned by SQL statement has special characters like or umlots ΓΌ, how do i handle this, is there a way to tell ibatis to parse these special characters and make it XML complaint before creating XML result Ashish
Re: Abator generated SqlMap.xml files
On Mar 20, 2007, at 9:47 AM, Chris Lamey wrote:
I use Postgres with Abator and don't have the db name in the
$TABLE_SqlMap.xml file names. Here's what one of my table elements
looks like:
table schema= tableName=SUGGESTION
domainObjectName=Suggestion
property name=useActualColumnNames value=false/
/table
I used the wrong term in my email. The generated files include the
schema name, not the database name. I see in your table elements that
you set the schema to . I can't do that since my databases are in a
named schema. For now I've solved the problem by doing the following
in my Ant build file after abator runs. schema; is an XML entity
reference whose value is the name of my schema.
move todir=gen/sqlmaps
fileset dir=gen/sqlmaps
include name=*_SqlMap.xml/
/fileset
mapper type=glob from=schema;_* to=*/
/move
The schema is then specified on the JDBC URL
(connectionURL=jdbc:postgresql://localhost/suggestions), like Jeff
said.
Postgres has a notion of a search path that is a list of schemas
that are searched to find databases.
I think I need to add my schema to the search path, but I don't know
how to do that.
Any ideas?
Cheers,
Chris
On Tue, 2007-03-20 at 07:17 -0500, Jeff Butler wrote:
Abator only uses the catalog and schema in the file names if you
specify them in your configuration. I don't know Postgres, but with
SQL Server you can leave the catalog out of the table configuration
elements and specify the default database name on the connection URL.
Jeff Butler
On 3/19/07, Mark Volkmann [EMAIL PROTECTED] wrote:
When I run Abator against a MySQL database I get files with
names
like {table-name}_SqlMap.xml.
When I run against Postgres or SQL Server I get files with
names like
{database_name}_{table-name}_SqlMap.xml.
Is there anything I can do to get Abator to not add the
database name
prefix for Postgres and SQL Server so I don't have to change
my code
when the database type changes?
Re: Locale problems?
The column is a DATE. I tried changing this to a DATETIME but the same error occurred. Interestingly - I have other tables with a DATETIME that work fine. The only difference to take into account though is that all the datetime columns are system generated where as the date field that is throwing read errors has orginated from a form field. This field is correctly inserted into the database but then has the parse error on the read. If I had errors on my local machine I would point the finger at the code but as it works fine and only fails on my service provider's server I am thinking it has to be something else? Andrew Quoting Graeme J Sweeney [EMAIL PROTECTED]: On Tue, 20 Mar 2007, [EMAIL PROTECTED] wrote: Thanks for the response. I'll have a look at the example you have suggested. I am using a date jdbc type for the date field as I only need dates in the form -MM-dd rather than the extended datetime format. Is the column type DATE or DATETIME ? -- Graeme -
Re: Abator generated SqlMap.xml files
On Tue, 2007-03-20 at 10:06 -0500, Mark Volkmann wrote: Postgres has a notion of a search path that is a list of schemas that are searched to find databases. I think I need to add my schema to the search path, but I don't know how to do that. Any ideas? It's been a while since I used it, but I think you just set the search path runtime parameter for that db user in postgres. So you'd login as the user your app will connect as and run this through pgsql: SET search_path to '$user, public, order, geo'; Then verify: SHOW search_path; It should be set for that user for subsequent connections. More detail here: http://www.postgresql.org/docs/current/static/ddl-schemas.html#DDL-SCHEMAS-PATH If that works, I think you don't need to specify the schema in Abator's table element (provided you don't have tables with the same name in different schemas). Cheers, Chris
Postgres JDBC driver
On Mar 20, 2007, at 10:06 AM, Mark Volkmann wrote: Postgres has a notion of a search path that is a list of schemas that are searched to find databases. I think I need to add my schema to the search path, but I don't know how to do that. Any ideas? I'm making progress. I have some code that just uses JDBC and it works. My schema name is sal. My database name is XAdemo. My database URL is jdbc:postgresql://localhost:5432/XAdemo. I'm using the JDBC driver org.postgresql.Driver in postgresql-8.2-504.jdbc3.jar. In my JDBC code, I run this after gettting a Statement. stmt.execute(set search_path = sal;); This seems essential for specifying the schema so that the XAdemo database can be found. All this works with straight JDBC. To duplicate this for my code that uses iBATIS under JBoss, I added this to my postgres-ds.xml file that specifies JNDI datasources. new-connection-sqlset search_path = sal;/new-connection-sql However, when running with iBATIS I still get the following error message. Apparently wrong driver class specified for URL: class: org.postgresql.Driver, url: jdbc:postgresql://localhost:5432/XAdemo) What does wrong driver class mean? I verified that org.postgresql.Driver is in postgresql-8.2-504.jdbc3.jar and that JAR is in my WAR. I'm not sure what else to check.
Re: Postgres JDBC driver
The issue is that you're specifying a postgres driver as a JBoss-wide JNDI resource but the postgres jar isn't in the JBoss classpath. Putting the jar in a war won't do because the JNDI resource is shared among all apps. You'll need to put it either in the server's lib dir or the top level lib dir. Also, I'm surprised you have run the SET for every SQL call. I can set it once via psql and not have to worry about it again. On Tue, 2007-03-20 at 10:52 -0500, Mark Volkmann wrote: On Mar 20, 2007, at 10:06 AM, Mark Volkmann wrote: Postgres has a notion of a search path that is a list of schemas that are searched to find databases. I think I need to add my schema to the search path, but I don't know how to do that. Any ideas? I'm making progress. I have some code that just uses JDBC and it works. My schema name is sal. My database name is XAdemo. My database URL is jdbc:postgresql://localhost:5432/XAdemo. I'm using the JDBC driver org.postgresql.Driver in postgresql-8.2-504.jdbc3.jar. In my JDBC code, I run this after gettting a Statement. stmt.execute(set search_path = sal;); This seems essential for specifying the schema so that the XAdemo database can be found. All this works with straight JDBC. To duplicate this for my code that uses iBATIS under JBoss, I added this to my postgres-ds.xml file that specifies JNDI datasources. new-connection-sqlset search_path = sal;/new-connection-sql However, when running with iBATIS I still get the following error message. Apparently wrong driver class specified for URL: class: org.postgresql.Driver, url: jdbc:postgresql://localhost:5432/XAdemo) What does wrong driver class mean? I verified that org.postgresql.Driver is in postgresql-8.2-504.jdbc3.jar and that JAR is in my WAR. I'm not sure what else to check.
Postgres and generated keys
Abator generates this in an SqlMap.xml file. insert id=abatorgenerated_insert parameterClass=com.savealot.xademo.model.Address !-- WARNING - This element is automatically generated by Abator for iBATIS, do not modify. This element was generated on Tue Mar 20 11:15:50 CDT 2007. -- insert into sal.address (street, city, state, zip) values (#street:VARCHAR#, #city:VARCHAR#, #state:VARCHAR#, #zip:INTEGER#) selectKey resultClass=java.lang.Integer keyProperty=id Postgres /selectKey /insert When I attempt an insert that uses this, it chokes on Postgres inside the selectKey element. Perhaps I'm doing something wrong in my AbatorConfig.xml. Here's the part that pertains to the address table. table schema=sal tableName=address domainObjectName=Address generatedKey column=id sqlStatement=Postgres identity=true/ /table Is the value for the sqlStatement attribute wrong?
Re: Locale problems?
On Tue, 20 Mar 2007, [EMAIL PROTECTED] wrote: The only difference to take into account though is that all the datetime columns are system generated where as the date field that is throwing read errors has orginated from a form field. This field is correctly inserted into the database but then has the parse error on the read. Which database is it? For test purposes, ignore the date comming from the form and just insert new Date(). Does that work when deployed on the service provider box? -- Graeme -
Re: Locale problems?
Hi Graeme,
From your earlier posts:
1. Local server, local DB works
2. Local server, remote DB works.
3. Remote server, remote DB fails.
- correct.
Without actually seeing the log/stacktrace, I wondering whether
1. It's the conversion from the DB to DATE or
2. Some parsing you're doing on the Date object.
- I let ibatis do all the parsing via the sqlmap configuration to java
object, so no parsing done by me. It looks to me like it is the
conversion from the DB to Date, but within ibatis. I have a service
class that calls the getUserByEmailAddress map. It passes a String to
the map and then when it executes throws the stace trace below. Like I
mentioned before - it doesn't do this in either of the first two
scenarios above.
Here are the other pieces of information:
DB Structure
id bigint(11) PRIMARY KEY
title enum('Mr','Mrs','Ms','Miss','Dr','Prof','Rev')
firstName varchar(60)
lastName varchar(60)
gender enum('Male','Female')
dob date NULL
telephoneNumber varchar(30)
emailAddress varchar(100)
password varchar(12)
newsletter enum('yes','no')
preferredCurrency bigint(11)
activationKey varchar(12)
activated enum('yes','no')
lastLogin datetime
SqlMap XML file - i snipped a few other lines out for unused statements
---
?xml version=1.0 encoding=UTF-8 standalone=no?
!DOCTYPE sqlMap PUBLIC -//iBATIS.com//DTD SQL Map 2.0//EN
http://www.ibatis.com/dtd/sql-map-2.dtd;
sqlMap namespace=User
resultMap id=userResult class=user
result property=id column=id/
result property=title column=title/
result property=firstName column=firstName/
result property=lastName column=lastName/
result property=gender column=gender/
result property=dob column=dob jdbcType=date/
result property=telephoneNumber column=telephoneNumber/
result property=emailAddress column=emailAddress/
result property=password column=password/
result property=newsletter column=newsletter/
result property=preferredCurrency column=preferredCurrency/
result property=activationKey column=activationKey/
result property=activated column=activated/
result property=lastLogin column=lastLogin/
/resultMap
statement id=insertUser parameterClass=user
insert into User (id, title, firstName, lastName, gender, dob,
telephoneNumber, emailAddress,
password, newsletter, preferredCurrency,
activationKey, activated, lastLogin)
values (#id#, #title#, #firstName#, #lastName#, #gender#, #dob#,
#telephoneNumber#, #emailAddress#,
#password#, #newsletter#, #preferredCurrency#,
#activationKey#, #activated#, #lastLogin#)
/statement
statement id=getUserByEmailAddress resultMap=userResult
parameterClass=string
select * from User where emailAddress = #value#
/statement
/sqlMap
DB record
-
(107, Mr, test, test, Male, 1975-01-15, 8989898, emailaddress edited,
123456, yes, 34, QsVUNAA2, no, 2007-03-14 03:54:08)
Stack trace - there are more than one errors!
---
Inside UserAction - userCreate
Mar 20, 2007 10:29:50 AM org.apache.struts.action.RequestProcessor
processException
WARNING: Unhandled Exception thrown: class
com.ibatis.dao.client.DaoException
Mar 20, 2007 10:29:50 AM org.apache.catalina.core.StandardWrapperValve
invoke
SEVERE: Servlet.service() for servlet action threw exception
com.ibatis.dao.client.DaoException: Failed to execute queryForObject -
id [getUserByEmailAddress], parameterObject [emailaddress edited].
Cause: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
uk/co/toydreams/shop/persistence/sqlmapdao/sql/User.xml.
--- The error occurred while applying a result map.
--- Check the User.userResult.
--- Check the result mapping for the 'lastLogin' property.
--- Cause: com.ibatis.common.exception.NestedRuntimeException: Error
setting properties of 'BaseObject [107, null, null, null]'. Cause:
java.text.ParseException: Unparseable date: 1975-01-15
Caused by: java.text.ParseException: Unparseable date: 1975-01-15
Caused by: com.ibatis.common.exception.NestedRuntimeException: Error
setting properties of 'BaseObject [107, null, null, null]'. Cause:
java.text.ParseException: Unparseable date: 1975-01-15
Caused by: java.text.ParseException: Unparseable date: 1975-01-15
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
uk/co/toydreams/shop/persistence/sqlmapdao/sql/User.xml.
--- The error occurred while applying a result map.
--- Check the User.userResult.
--- Check the result mapping for the 'lastLogin' property.
--- Cause: com.ibatis.common.exception.NestedRuntimeException: Error
setting properties of 'BaseObject [107, null, null, null]'. Cause:
java.text.ParseException: Unparseable date: 1975-01-15
Caused by: java.text.ParseException: Unparseable date: 1975-01-15
Caused by: com.ibatis.common.exception.NestedRuntimeException: Error
setting properties of 'BaseObject [107, null, null, null]'. Cause:
java.text.ParseException:
Re: java.sql.SQLException: ORA-06550: line 1, column 7:,PLS-00201: identifier 'CONT_ORDER_INS' must be declared
Nope, the user owns it, and it is in the same schema. I've also tried calling it using plain jdbc connection, and it works. Regards, Hendry Larry Meadors wrote: I am 99.99% sure that this is a privilege issue. Does the user executing the procedure own it, or is it in another schema? Larry
