Thank you buddy for yr time, but I think it's not a sql mode problem. I defined the datasource to be available for JBoss, as any other database. then, I lookued up the datasource to get the connection for my database and schema.
I finished the above, and after running a simple sql statement against the database, I got SQLException stating that the table doesn't exist , I don't know why???
Better post your configuration xml and code to help better. Can you try to query DUAL table atleast to make sure the datasource configuration are correct and the problem is related to "table name and schema".
I tried another table, it gave me the same results. One noticed thing is that connection is established, but when you try to run an sql statement against a given table, it will give you an error message stating that table don't exist......
I thought Balu might have something there so I tried with and without the ';' at the end of the select - both worked.
But let me back up. I copied the code from your other code into one of my projects. Of course I changed the database and table name to match something I had. And everything worked. One caveat: I did not use server-side script in a JSP - instead I placed the code in a servlet and passed the resulting string to the JSP which accessed if via EL.
A few suggestions.
1) Do not call the datasource DefaultDS. Your other post that used MyDS is better.
2) Do not remove hsqldb-ds.xml - the DefaultDS is needed by several JBoss AS services. (You can replace it, but it takes some work. Ch15 of JBoss in Action provides the details)
3) Do what I did - put your code in a servlet, pass the string to the JSP and use EL. Not mandatory, but saner.
Here is a recap of everything you have to do, starting with a fresh install of JBoss AS 5.0.0.GA:
1) Copy the mysql JDBC JAR file to server/default/lib
2) Copy your mysql-ds.xml file to server/default/deploy
3) Copy your WAR file to server/default/deploy
4) Start the app server and access the JSP