I am getting error while creating table in derby db Please help .
first i am searching if the table is present in the database if not i am creating the table but i am getting and wierd exception this same query is working fine in Oracle local database.
String driver = "org.apache.derby.jdbc.EmbeddedDriver";
String dbName = "ServerDetails";
String connectionURL = "jdbc:derby:" + dbName + ";create=true";
Class.forName(driver);
Connection conn = DriverManager.getConnection(connectionURL);
PreparedStatement ps = null;
ResultSet rs = null;
//PreparedStatement createPS = null;
ResultSet createRS = null;
//String checkQuery = "select * FROM ALL_TABLES WHERE table_name like UPPER('XX_SERVER_DETAILS')";
String tableScript = "CREATE TABLE XX_SERVER_DETAILS"
+ "("
+ " USERNAME1 VARCHAR2(10 BYTE) NOT NULL,"
+ " PASSWORD1 VARCHAR2(10 BYTE) NOT NULL,"
+ " SERVER1 VARCHAR2(50 BYTE) NOT NULL,"
+ " USERNAME2 VARCHAR2(10 BYTE) NOT NULL,"
+ " PASSWORD2 VARCHAR2(10 BYTE) NOT NULL,"
+ " SERVER2 VARCHAR2(50 BYTE) NOT NULL"
+ ")";
DatabaseMetaData dbmd = conn.getMetaData();
rs = dbmd.getTables(null, null, "XX_SERVER_DETAILS",null);
if(rs.next())
{
System.out.println("Table "+rs.getString("TABLE_NAME")+"already exists !!");
}
else
{
System.out.println("Write your create table function here !!!");
ps = conn.prepareStatement(tableScript);
createRS = ps.executeQuery();
//createPS = conn.prepareStatement(tableScript);
//createRS = createPS.executeQuery();
conn.commit();
ps.close();
rs.close();
//createPS.close();
//createRS.close();
}
Stack trace
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "(" at line 1, column 53.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at persistencetier.DatabaseConn.server_Connection(DatabaseConn.java:157)
at presentationtier.InstanceController.<init>(InstanceController.java:106)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at java.lang.Class.newInstance(Class.java:374)
at sun.reflect.misc.ReflectUtil.newInstance(ReflectUtil.java:51)
at javafx.fxml.FXMLLoader$ValueElement.processAttribute(FXMLLoader.java:736)
at javafx.fxml.FXMLLoader$InstanceDeclarationElement.processAttribute(FXMLLoader.java:780)
at javafx.fxml.FXMLLoader$Element.processStartElement(FXMLLoader.java:185)
at javafx.fxml.FXMLLoader$ValueElement.processStartElement(FXMLLoader.java:568)
at javafx.fxml.FXMLLoader.processStartElement(FXMLLoader.java:2356)
at javafx.fxml.FXMLLoader.load(FXMLLoader.java:2172)
at javafx.fxml.FXMLLoader.load(FXMLLoader.java:2069)
at javafx.fxml.FXMLLoader.load(FXMLLoader.java:2830)
at javafx.fxml.FXMLLoader.load(FXMLLoader.java:2809)
at javafx.fxml.FXMLLoader.load(FXMLLoader.java:2795)
at javafx.fxml.FXMLLoader.load(FXMLLoader.java:2782)
at javafx.fxml.FXMLLoader.load(FXMLLoader.java:2771)
at presentationtier.StartController.aolAction(StartController.java:43)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at sun.reflect.misc.Trampoline.invoke(MethodUtil.java:75)
at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at sun.reflect.misc.MethodUtil.invoke(MethodUtil.java:279)
at javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(FXMLLoader.java:1451)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:69)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:217)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:170)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:38)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:37)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:92)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:35)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:92)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:35)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:92)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:53)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:28)
at javafx.event.Event.fireEvent(Event.java:171)
at javafx.scene.Node.fireEvent(Node.java:6866)
at javafx.scene.control.Button.fire(Button.java:179)
at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:193)
at com.sun.javafx.scene.control.skin.SkinBase$4.handle(SkinBase.java:336)
at com.sun.javafx.scene.control.skin.SkinBase$4.handle(SkinBase.java:329)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:64)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:217)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:170)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:38)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:37)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:92)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:35)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:92)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:35)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:92)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:35)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:92)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:53)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:33)
at javafx.event.Event.fireEvent(Event.java:171)
at javafx.scene.Scene$MouseHandler.process(Scene.java:3369)
at javafx.scene.Scene$MouseHandler.process(Scene.java:3209)
at javafx.scene.Scene$MouseHandler.access$1900(Scene.java:3164)
at javafx.scene.Scene.impl_processMouseEvent(Scene.java:1582)
at javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2267)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:250)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:173)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:292)
at com.sun.glass.ui.View.handleMouseEvent(View.java:530)
at com.sun.glass.ui.View.notifyMouse(View.java:924)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.access$100(WinApplication.java:17)
at com.sun.glass.ui.win.WinApplication$3$1.run(WinApplication.java:67)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Syntax error: Encountered "(" at line 1, column 53.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 92 more
Caused by: ERROR 42X01: Syntax error: Encountered "(" at line 1, column 53.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
... 86 more
It was a silly mistake Sorry for posting the Question the Script will be Something like this
String tableScript = "CREATE TABLE XX_SERVER_DETAILS"
+ "("
+ " USERNAME1 VARCHAR(10) NOT NULL,"
+ " PASSWORD1 VARCHAR(10) NOT NULL,"
+ " SERVER1 VARCHAR(50) NOT NULL,"
+ " USERNAME2 VARCHAR(10) NOT NULL,"
+ " PASSWORD2 VARCHAR(10) NOT NULL,"
+ " SERVER2 VARCHAR(50) NOT NULL"
+ ")";
Derby doesnot accept varchar2 .and as i was reusing query of oracle it gave the error.