Database migration problems

  • (2 Pages)
  • +
  • 1
  • 2

25 Replies - 819 Views - Last Post: 10 October 2013 - 02:17 AM Rate Topic: -----

#1 Random01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 06-October 13

Database migration problems

Posted 06 October 2013 - 10:53 PM

Ok, please somebody or everybody help me out here ..because i am a mess.
I cannot seem to make a connection with my database.

I keep getting an sqlexception "Connection failed".

public class DBConst {

  public static final String FILE = "C:/Users/Me/Desktop/ProjectsOPij2/Le10CDwDB/cds.fdb";
  public static final String DRIVERNAME = "org.firebirdsql.jdbc.FBDriver";
  public static final String URL = "jdbc:firebirdsql://localhost/"+ FILE;
  public static final String USER = "SYSDBA";
  public static final String PASS = "masterkey";

...

private void makeConnection() throws CDException {
    try {
      
      Class.forName(DBConst.DRIVERNAME);
      con = DriverManager.getConnection(DBConst.URL, DBConst.USER, DBConst.PASS);
    }
    catch (ClassNotFoundException e) {
      throw new CDException("Driver not loaded.");
    }
    catch (SQLException e) {
      throw new CDException("Connection failed.");      
    }
  }



What am i missing here??
Do i need to re-install Firebird for some reason? Something with the pathname? I just can't figure it out.

Is This A Good Question/Topic? 0
  • +

Replies To: Database migration problems

#2 Random01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 06-October 13

Re: Database migration problems

Posted 07 October 2013 - 01:15 AM

Furthermore: If i try to go for the migration of the old database to the new
I keep getting : "Incorrect database name 'd:/op/dataconversion/old_mysql_dump.sql'"


public static final String SOURCE = "D:/OP/Dataconversion/old_mysql_dump.sql";
	public static final String GOAL = "D:/OP/New.fdb"; 
	public static final String SUSER = "sysdba";
	public static final String GPASS= "masterkey";
	public static final String GDRIVER = "org.firebirdsql.jdbc.FBDriver";
	public static final String SURL = "jdbc:mysql://localhost/" + SOURCE;
	public static final String SUSER = "root";
	public static final String SPASS = "root";
	public static final String SDRIVER = "com.mysql.jdbc.Driver";
	public static final String SQUERY = "select name from Place";
	public static final String GQUERY = "insert into Place(name) values (?)";
	public static final String GURL = "jdbc:firebirdsql:localhost:3306" + GOAL ; 


But how can it say it's incorrect? Because that is the name of the file...what does it want from me?


The drivers load fine.
Was This Post Helpful? 0
  • +
  • -

#3 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2735
  • View blog
  • Posts: 11,521
  • Joined: 20-September 08

Re: Database migration problems

Posted 07 October 2013 - 02:07 AM

Full and verbaitm errors messages please
http://technojeeves....free/118-errors
Was This Post Helpful? 0
  • +
  • -

#4 Random01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 06-October 13

Re: Database migration problems

Posted 07 October 2013 - 02:46 AM

AH ok, apologies.

In the mean time I got the connection, but now i have a new problem:

drivers loaded
Connection made
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -204
Table unknown
PLACE
At line 1, column 13
at org.firebirdsql.jdbc.AbstractPreparedStatement.<init>(AbstractPreparedStatement.java:141)
at org.firebirdsql.jdbc.FBPreparedStatement.<init>(FBPreparedStatement.java:45)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at org.firebirdsql.jdbc.FBStatementFactory.createPreparedStatement(FBStatementFactory.java:89)
at org.firebirdsql.jdbc.AbstractConnection.prepareStatement(AbstractConnection.java:1187)
at org.firebirdsql.jdbc.AbstractConnection.prepareStatement(AbstractConnection.java:942)
at org.firebirdsql.jdbc.AbstractConnection.prepareStatement(AbstractConnection.java:896)
at org.firebirdsql.jdbc.AbstractConnection.prepareStatement(AbstractConnection.java:324)
at Convert.main(Convert.java:38)
Caused by: org.firebirdsql.gds.GDSException: Dynamic SQL Error
SQL error code = -204
Table unknown
PLACE
At line 1, column 13
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.readStatusVector(AbstractJavaGDSImpl.java:2214)
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.receiveResponse(AbstractJavaGDSImpl.java:2164)
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.iscDsqlPrepare(AbstractJavaGDSImpl.java:1551)
at org.firebirdsql.gds.impl.GDSHelper.prepareStatement(GDSHelper.java:190)
at org.firebirdsql.jdbc.AbstractStatement.prepareFixedStatement(AbstractStatement.java:1441)
at org.firebirdsql.jdbc.AbstractPreparedStatement.prepareFixedStatement(AbstractPreparedStatement.java:1282)
at org.firebirdsql.jdbc.AbstractPreparedStatement.<init>(AbstractPreparedStatement.java:138)
... 11 more
Was This Post Helpful? 0
  • +
  • -

#5 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2735
  • View blog
  • Posts: 11,521
  • Joined: 20-September 08

Re: Database migration problems

Posted 07 October 2013 - 02:55 AM

Quote

Table unknown
PLACE
Check character case and also check with the Firebird utilities (whatever they are) that the table does exist
Was This Post Helpful? 0
  • +
  • -

#6 Random01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 06-October 13

Re: Database migration problems

Posted 07 October 2013 - 03:06 AM

Well i wrote :
...
System.out.println("Connection made");
			String tableName = "Place";
			String query = "CREATE TABLE IF NOT EXISTS `" + tableName + "` (" +
			                    "  `name` varchar(80) NOT NULL," +
			                    
			sourceps = csource.prepareStatement(SOURCEQUERY);
			goalps = cgoal.prepareStatement(GOALQUERY);
			ResultSet rs = sourceps.executeQuery();
			
			while (rs.next()) {
				String name= rs.getString("name");
				goalps.setString(1, name);
				goalps.executeUpdate();
...


So the table has to exist either way right? Or am I seeing it wrong?
Was This Post Helpful? 0
  • +
  • -

#7 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2735
  • View blog
  • Posts: 11,521
  • Joined: 20-September 08

Re: Database migration problems

Posted 07 October 2013 - 03:08 AM

The code you just posted is incomplete so i can't tell
Was This Post Helpful? 0
  • +
  • -

#8 Random01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 06-October 13

Re: Database migration problems

Posted 07 October 2013 - 03:14 AM

import java.sql.*;


public class Convert {

	public static final String DSOURCE = "old";
	public static final String DBGOAL = "D:/OP/New.fdb"; 
	public static final String GOALUSER = "sysdba";
	public static final String GOALPASSWORD = "masterkey";
	public static final String GOALDRIVERNAME = "org.firebirdsql.jdbc.FBDriver";
	public static final String SOURCEURL = "jdbc:mysql://127.0.0.1/" + DSOURCE;
	public static final String SOURCEUSER= "old";
	public static final String SOURCEPASSWORD = "old";
	public static final String SOURCEDRIVERNAME = "com.mysql.jdbc.Driver";
	public static final String SOURCEQUERY = "select name from Place";
	public static final String GOALQUERY = "insert into Place(name) values (?)";
	public static final String GOALURL = "jdbc:firebirdsql://127.0.0.1/" + DBGOAL; 
	
	
	
	public static void main(String[] args) {
		try {
			Class.forName("org.firebirdsql.jdbc.FBDriver");
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println("drivers loaded");
		}catch (Exception e){ 
			e.printStackTrace();
		}

		Connection csource = null, cgoal = null;
		PreparedStatement sourceps = null, goalps = null;
		try { 
			csource = DriverManager.getConnection(SOURCEURL, SOURCEUSER, SOURCEPASSWORD );
			cgoal = DriverManager.getConnection(GOALURL , GOALUSER , GOALPASSWORD );
			
			System.out.println("Connection made");
03	                String tableName = "Place";
04	                String query = "CREATE TABLE IF NOT EXISTS `" + tableName + "` (" +
05	                                "  `name` varchar(80) NOT NULL," + 
06	                                   "  PRIMARY KEY (`name`)";
07	                sourceps = csource.prepareStatement(SOURCEQUERY);
08	                goalps = cgoal.prepareStatement(GOALQUERY);
09	                 ResultSet rs = sourceps.executeQuery();
10	             
11	            while (rs.next()) {
12	                String name= rs.getString("name");
13	                goalps.setString(1, name);
14	                goalps.executeUpdate();
			}
		}catch (Exception e) {
			e.printStackTrace();
			
		} finally { 
			try {
				csource.close();
				cgoal.close();
			}catch (SQLException e) {
					
				}
			}
		}
}
			



thanks for the quick replies btw.
Was This Post Helpful? 0
  • +
  • -

#9 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2735
  • View blog
  • Posts: 11,521
  • Joined: 20-September 08

Re: Database migration problems

Posted 07 October 2013 - 03:43 AM

a. Not sure why you're parameterising the table name at creation, since you hard code it for the select...
b. I would refrain from using backticks - they just make the syntax more like to get wrong in Java. They are not normally needed unless you're using bad names
c. Print the generated sql (you can use String.format for creation) and make sure it's right. afaicr there should be no comma after NOT NULL, but i could be wrong
Was This Post Helpful? 0
  • +
  • -

#10 Random01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 06-October 13

Re: Database migration problems

Posted 07 October 2013 - 10:06 AM

Ok, so i deleted the CREATE TABLE thing

Still have connection and everything, but now i have the following issue;

drivers loaded
Connection made
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544665. violation of PRIMARY or UNIQUE KEY constraint "INTEG_22" on table "PLAATS"
at org.firebirdsql.jdbc.AbstractPreparedStatement.internalExecute(AbstractPreparedStatement.java:798)
at org.firebirdsql.jdbc.AbstractPreparedStatement.executeQuery(AbstractPreparedStatement.java:179)
at Convert.main(Convert.java:69)
Caused by: org.firebirdsql.gds.GDSException: violation of PRIMARY or UNIQUE KEY constraint "INTEG_22" on table "PLAATS"
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.readStatusVector(AbstractJavaGDSImpl.java:2214)
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.receiveResponse(AbstractJavaGDSImpl.java:2164)
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.iscDsqlExecute2(AbstractJavaGDSImpl.java:1213)
at org.firebirdsql.gds.impl.GDSHelper.executeStatement(GDSHelper.java:227)
at org.firebirdsql.jdbc.AbstractPreparedStatement.internalExecute(AbstractPreparedStatement.java:794)
... 2 more






So pfff, how do i get around this?

If i add :
while (rs.next()) {
	        	int nr = rs.getInt(1);
				String name= rs.getString("name");

				goalps.setString(1,name)
   goalps.executeQuery();
				goalps.executeUpdate();


I get :

Invalid value for getInt() - ''Saint Morris'



Dno if the last part helps for a diagnosis, i know the variable nr can't have a value because there is no nr in Place ...but then it does give me a place name...i just feel im so close but ahhhh im honestly not good at this and need help.
Was This Post Helpful? 0
  • +
  • -

#11 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8334
  • View blog
  • Posts: 31,858
  • Joined: 06-March 08

Re: Database migration problems

Posted 07 October 2013 - 03:25 PM

''Saint Morris' sounds like unbalanced ' to me
Was This Post Helpful? 0
  • +
  • -

#12 Random01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 06-October 13

Re: Database migration problems

Posted 07 October 2013 - 11:45 PM

View Postpbl, on 07 October 2013 - 03:25 PM, said:

''Saint Morris' sounds like unbalanced ' to me


What do you mean please?

Do I have to go in the original database and remove the primary keys or something? That just seems wrong..

Everytime I want to select attributes and show them so I can clean them up to insert in the new database, it gives me the error that I am violating the PRIMARY KEY/ UNIQUE CONSTRAINTS.

But shouldn't I be able to just conjure the data?

On another occassion it just gave me :

JDBC4ResultSet@913dc1

Instead of the actual data.
I bet I'm gonna see every error known to man before i get to finish this project.
Was This Post Helpful? 0
  • +
  • -

#13 Random01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 06-October 13

Re: Database migration problems

Posted 08 October 2013 - 03:33 AM

Omg omg....I did it. Well at least, i can load data from the original database.

I don't know what it was but i just repeated the entire proces of installing firebird, IBExpert .. re-created the new database. Then reinstalled Cathedron and re-added the new database, ran catdoctor and now ... it works.

Im doubtful if it was a compatibility issue, seeing im using all the same latest versions of everything.
But ok whoo. First part is done. Now the migration actually begins.

So bare with me, i might need additional assistance. If anybodoy is willing, of course.
Was This Post Helpful? 0
  • +
  • -

#14 Random01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 06-October 13

Re: Database migration problems

Posted 08 October 2013 - 04:40 AM

Ok, say..When I want to migrate the data from table Place to the new database I get:

Connection made
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544665. violation of PRIMARY or UNIQUE KEY constraint "INTEG_22" on table "PLACE"
at org.firebirdsql.jdbc.AbstractPreparedStatement.internalExecute(AbstractPreparedStatement.java:798)
at org.firebirdsql.jdbc.AbstractPreparedStatement.executeQuery(AbstractPreparedStatement.java:179)
at Convert.main(Convert.java:71)
Caused by: org.firebirdsql.gds.GDSException: violation of PRIMARY or UNIQUE KEY constraint "INTEG_22" on table "PLACE"
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.readStatusVector(AbstractJavaGDSImpl.java:2214)
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.receiveResponse(AbstractJavaGDSImpl.java:2164)
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.iscDsqlExecute2(AbstractJavaGDSImpl.java:1213)
at org.firebirdsql.gds.impl.GDSHelper.executeStatement(GDSHelper.java:227)
at org.firebirdsql.jdbc.AbstractPreparedStatement.internalExecute(AbstractPreparedStatement.java:794)
... 2 more


Now, my question is. Do I have to, and can I temporarily disable constraints so I can further migrate the data without obstruction?
How do I do this in java code?
Or is this not possible and should I go directly into the new database model and delete the keys to later add them again, manually?
Was This Post Helpful? 0
  • +
  • -

#15 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2735
  • View blog
  • Posts: 11,521
  • Joined: 20-September 08

Re: Database migration problems

Posted 08 October 2013 - 05:02 AM

The first question that springs to mind is why is the source data (apparently) violating key constraints when it hits its target?
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2