I'm trying to connect to two DBs and then copy tables by inserting rows from one table to another through java using JDBC. I'm using sqlite-jdbc-3.7.2.jar plugin.The setup is in Eclipse Luna. I'm getting below error when I execute the java code. I queried it on SpatialiteGUI it works fine. Delete query is working fine, but insert. Similar to this issue but SRID is configured in my case, it's giving results in spatialite gui (as suggested by @BradHards).

pyspatialite: Connot insert geometry

java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such column: SRID)

    import java.sql.*;

    public class SegregateDB
    {
      public static void main( String args[] )
      {
          //Both DB have same table structure.


          try{
          Class.forName("org.sqlite.JDBC");


          String vanillaDBPath="C:\\all data\\MyBT\\NR dev\\jettyGeoHub\\BTPlantDB_sqlite\\vanilla.sqlite"; // target DB
          String PWAPath="C:\\all data\\MyBT\\NR dev\\jettyGeoHub\\BTPlantDB_sqlite\\BTPlantDB.sqlite"; // Source DB

          Connection connOldDb = DriverManager.getConnection("jdbc:sqlite:"+ PWAPath);
          //add connection to vanilla DB.

          //newDbName = newDbName + "auftraege.db";
          Connection connNewDb = DriverManager.getConnection("jdbc:sqlite:"+ vanillaDBPath);

          connNewDb.prepareStatement("ATTACH DATABASE \"" + PWAPath + "\" AS  pwa").execute();

          connNewDb.prepareStatement("ATTACH DATABASE \"" + vanillaDBPath + "\" AS  van").execute();

          connNewDb.prepareStatement("DELETE FROM van.attachment_ofl WHERE EXCHANGE_1141_CODE IN (SELECT EXCHANGE_1141_CODE FROM pwa.attachment_ofl);").execute();
          connNewDb.prepareStatement("insert into van.attachment_ofl select * from pwa.attachment_ofl;").execute();

          connNewDb.close();
          connOldDb.close();

          System.out.println("Operation done successfully");
          }
          catch(Exception e){
              System.err.println( e.getClass().getName() + ": " + e.getMessage() );
              System.exit(0);

          }
      }
    }
  • Can you do "create table van.test as select * from pwa.attachment_ofl;? If you can, can you also insert rows into this new van.test? – user30184 Oct 10 '14 at 13:19
  • Can you specify a bit more about which version(s) of spatialite you're using, and show where you are loading the spatialite extension? – BradHards Oct 24 '14 at 10:05
  • @user30184 Yes I'm able to execute connNewDb.prepareStatement(" create table van.test as select * from pwa.attachment_ofl;").execute(); But the "connNewDb.prepareStatement("insert into van.attachment_ofl select * from pwa.attachment_ofl;").execute();" is giving the same error as mentioned earlier. – rpandey Oct 27 '14 at 6:13
  • @BradHards I'm using version 4.2.0 – rpandey Oct 27 '14 at 6:21
  • @BradHards I'm using version 4.2.0. By spatialite extension do you mean the jar file for sqlite-jdbc.jar or something else? Sorry I'm new to this :( – rpandey Oct 27 '14 at 6:28

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.