search

sevar83 / android-spatialite

Spatialite database JNI wrapper for Android
101 stars 48 forks source link

table tablename has no column named colname (code 1) #3

Closed shannah closed 6 years ago

shannah commented 6 years ago

I have installed this lib, and it is sort of working, but the spatialite stuff isn't working properly.

E.g. I have this:

            String sql = "CREATE TABLE test_pt (";
            sql += "id INTEGER NOT NULL PRIMARY KEY,";
            sql += "name TEXT NOT NULL)";
            db.execSQL(sql);
            // creating a POINT Geometry column
            sql = "SELECT AddGeometryColumn('test_pt', ";
            sql += "'geom', 4326, 'POINT', 'XY')";
            db.execSQL(sql);

That works fine.

But then when I try to insert into this table with 

            int i;
            for (i = 0; i < 100; i++)
            {
                Log.p("Inserting point "+i);
              // for POINTs we'll use full text sql statements
              sql = "INSERT INTO test_pt (id, name, geom) VALUES (";
              sql += i + 1;
              sql += ", 'test POINT #";
              sql += i + 1;
              sql += "', GeomFromText('POINT(";
              sql += i / 1000.0;
              sql += " ";
              sql += i / 1000.0;
              sql += ")', 4326))";
              db.execSQL(sql);
            }

I get

android.database.sqlite.SQLiteException: table test_pt has no column named geom (code 1): , while compiling: INSERT INTO test_pt (id, name, geom) VALUES (1, 'test POINT #1', GeomFromText('POINT(0.0 0.0)', 4326))
04-30 14:14:38.851  3137  3183 W System.err:    at org.spatialite.database.SQLiteConnection.nativePrepareStatement(Native Method)
04-30 14:14:38.851  3137  3183 W System.err:    at org.spatialite.database.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:922)
04-30 14:14:38.851  3137  3183 W System.err:    at org.spatialite.database.SQLiteConnection.prepare(SQLiteConnection.java:525)
04-30 14:14:38.851  3137  3183 W System.err:    at org.spatialite.database.SQLiteSession.prepare(SQLiteSession.java:599)
04-30 14:14:38.851  3137  3183 W System.err:    at org.spatialite.database.SQLiteProgram.<init>(SQLiteProgram.java:59)
04-30 14:14:38.852  3137  3183 W System.err:    at org.spatialite.database.SQLiteStatement.<init>(SQLiteStatement.java:37)
04-30 14:14:38.852  3137  3183 W System.err:    at org.spatialite.database.SQLiteDatabase.executeSql(SQLiteDatabase.java:1675)
04-30 14:14:38.852  3137  3183 W System.err:    at org.spatialite.database.SQLiteDatabase.execSQL(SQLiteDatabase.java:1619)
04-30 14:14:38.852  3137  3183 W System.err:    at com.codename1.spatialite.AndroidSpatialiteDB.execute(AndroidSpatialiteDB.java:67)
04-30 14:14:38.852  3137  3183 W System.err:    at com.codename1.spatialite.demo.SpatialiteDemo.start(SpatialiteDemo.java:106)
04-30 14:14:38.852  3137  3183 W System.err:    at com.codename1.spatialite.demo.SpatialiteDemoStub.run(SpatialiteDemoStub.java:141)
04-30 14:14:38.852  3137  3183 W System.err:    at com.codename1.ui.Display.processSerialCalls(Display.java:1129)
04-30 14:14:38.852  3137  3183 W System.err:    at com.codename1.ui.Display.mainEDTLoop(Display.java:924)
04-30 14:14:38.852  3137  3183 W System.err:    at com.codename1.ui.RunnableWrapper.run(RunnableWrapper.java:120)
04-30 14:14:38.852  3137  3183 W System.err:    at com.codename1.impl.CodenameOneThread$1.run(CodenameOneThread.java:60)
04-30 14:14:38.852  3137  3183 W System.err:    at java.lang.Thread.run(Thread.java:818)

I tried changing it to use SQLiteStatement.executeInsert(sql) instead, and I get a similar thing:

SQLiteStatement stmt = db.compileStatement(sql);
stmt.executeInsert();
System.err: android.database.sqlite.SQLiteException: table test_pt has no column named geom (code 1): , while compiling: INSERT INTO test_pt (id, name, geom) VALUES (1, 'test POINT #1', GeomFromText('POINT(0.0 0.0)', 4326))
04-30 14:41:42.549  6021  6045 W System.err:    at org.spatialite.database.SQLiteConnection.nativePrepareStatement(Native Method)
04-30 14:41:42.549  6021  6045 W System.err:    at org.spatialite.database.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:922)
04-30 14:41:42.549  6021  6045 W System.err:    at org.spatialite.database.SQLiteConnection.prepare(SQLiteConnection.java:525)
04-30 14:41:42.549  6021  6045 W System.err:    at org.spatialite.database.SQLiteSession.prepare(SQLiteSession.java:599)
04-30 14:41:42.549  6021  6045 W System.err:    at org.spatialite.database.SQLiteProgram.<init>(SQLiteProgram.java:59)
04-30 14:41:42.549  6021  6045 W System.err:    at org.spatialite.database.SQLiteStatement.<init>(SQLiteStatement.java:37)
04-30 14:41:42.549  6021  6045 W System.err:    at org.spatialite.database.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1011)
04-30 14:41:42.549  6021  6045 W System.err:    at com.codename1.spatialite.AndroidSpatialiteDB.execute(AndroidSpatialiteDB.java:93)
04-30 14:41:42.549  6021  6045 W System.err:    at com.codename1.spatialite.demo.SpatialiteDemo.start(SpatialiteDemo.java:106)
04-30 14:41:42.549  6021  6045 W System.err:    at com.codename1.spatialite.demo.SpatialiteDemoStub.run(SpatialiteDemoStub.java:141)
04-30 14:41:42.549  6021  6045 W System.err:    at com.codename1.ui.Display.processSerialCalls(Display.java:1129)
04-30 14:41:42.549  6021  6045 W System.err:    at com.codename1.ui.Display.mainEDTLoop(Display.java:924)
04-30 14:41:42.549  6021  6045 W System.err:    at com.codename1.ui.RunnableWrapper.run(RunnableWrapper.java:120)
04-30 14:41:42.549  6021  6045 W System.err:    at com.codename1.impl.CodenameOneThread$1.run(CodenameOneThread.java:60)
04-30 14:41:42.549  6021  6045 W System.err:    at java.lang.Thread.run(Thread.java:818)

Am I doing something wrong?

It appears the it is installed correctly as when I print out the version info using:

Cursor stmt01 = executeQuery("SELECT spatialite_version();");
            if (stmt01.next()) {
                sb.append("\t").append("SPATIALITE_VERSION: " + stmt01.getRow().getString(0));
                sb.append("\n");
            }
            stmt01.close();
            stmt01 = executeQuery("SELECT proj4_version();");
            if (stmt01.next()) {
                sb.append("\t").append("PROJ4_VERSION: " + stmt01.getRow().getString(0));
                sb.append("\n");
            }
            stmt01.close();
            stmt01 = executeQuery("SELECT geos_version();");
            if (stmt01.next()) {
                sb.append("\t").append("GEOS_VERSION: " + stmt01.getRow().getString(0));
                sb.append("\n");
            }
            stmt01.close();

(*Note: The Cursor/executeQuery are wrapper Classes/methods).

I get:

System.out:     SPATIALITE_VERSION: 4.3.0a
04-30 14:41:42.494  6021  6045 I System.out:    PROJ4_VERSION: Rel. 4.8.0, 6 March 2012
04-30 14:41:42.494  6021  6045 I System.out:    GEOS_VERSION: 3.4.2-CAPI-1.8.2 r0
sevar83 commented 6 years ago

Hi, I'm sorry to hear that. Would you try to see the return value of the AddGeometryColumn statement? And also try chaning the it to rawExecSQL() and rawQuery() . And please make sure db is Spatialite not the platform SQLite. I'll try to see it tomorrow.

On Mon, 30 Apr 2018 at 16:49 Steve Hannah notifications@github.com wrote:

I have installed this lib, and it is sort of working, but the spatialite stuff isn't working properly.

E.g. I have this:

        String sql = "CREATE TABLE test_pt (";
        sql += "id INTEGER NOT NULL PRIMARY KEY,";
        sql += "name TEXT NOT NULL)";
        db.execSQL(sql);
        // creating a POINT Geometry column
        sql = "SELECT AddGeometryColumn('test_pt', ";
        sql += "'geom', 4326, 'POINT', 'XY')";
        db.execSQL(sql);

That works fine.

But then when I try to insert into this table with

        int i;
        for (i = 0; i < 100; i++)
        {
            Log.p("Inserting point "+i);
          // for POINTs we'll use full text sql statements
          sql = "INSERT INTO test_pt (id, name, geom) VALUES (";
          sql += i + 1;
          sql += ", 'test POINT #";
          sql += i + 1;
          sql += "', GeomFromText('POINT(";
          sql += i / 1000.0;
          sql += " ";
          sql += i / 1000.0;
          sql += ")', 4326))";
          db.execSQL(sql);
        }

I get

android.database.sqlite.SQLiteException: table test_pt has no column named geom (code 1): , while compiling: INSERT INTO test_pt (id, name, geom) VALUES (1, 'test POINT #1', GeomFromText('POINT(0.0 0.0)', 4326)) 04-30 14:14:38.851 3137 3183 W System.err: at org.spatialite.database.SQLiteConnection.nativePrepareStatement(Native Method) 04-30 14:14:38.851 3137 3183 W System.err: at org.spatialite.database.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:922) 04-30 14:14:38.851 3137 3183 W System.err: at org.spatialite.database.SQLiteConnection.prepare(SQLiteConnection.java:525) 04-30 14:14:38.851 3137 3183 W System.err: at org.spatialite.database.SQLiteSession.prepare(SQLiteSession.java:599) 04-30 14:14:38.851 3137 3183 W System.err: at org.spatialite.database.SQLiteProgram.(SQLiteProgram.java:59) 04-30 14:14:38.852 3137 3183 W System.err: at org.spatialite.database.SQLiteStatement.(SQLiteStatement.java:37) 04-30 14:14:38.852 3137 3183 W System.err: at org.spatialite.database.SQLiteDatabase.executeSql(SQLiteDatabase.java:1675) 04-30 14:14:38.852 3137 3183 W System.err: at org.spatialite.database.SQLiteDatabase.execSQL(SQLiteDatabase.java:1619) 04-30 14:14:38.852 3137 3183 W System.err: at com.codename1.spatialite.AndroidSpatialiteDB.execute(AndroidSpatialiteDB.java:67) 04-30 14:14:38.852 3137 3183 W System.err: at com.codename1.spatialite.demo.SpatialiteDemo.start(SpatialiteDemo.java:106) 04-30 14:14:38.852 3137 3183 W System.err: at com.codename1.spatialite.demo.SpatialiteDemoStub.run(SpatialiteDemoStub.java:141) 04-30 14:14:38.852 3137 3183 W System.err: at com.codename1.ui.Display.processSerialCalls(Display.java:1129) 04-30 14:14:38.852 3137 3183 W System.err: at com.codename1.ui.Display.mainEDTLoop(Display.java:924) 04-30 14:14:38.852 3137 3183 W System.err: at com.codename1.ui.RunnableWrapper.run(RunnableWrapper.java:120) 04-30 14:14:38.852 3137 3183 W System.err: at com.codename1.impl.CodenameOneThread$1.run(CodenameOneThread.java:60) 04-30 14:14:38.852 3137 3183 W System.err: at java.lang.Thread.run(Thread.java:818)

I tried changing it to use SQLiteStatement.executeInsert(sql) instead, and I get a similar thing:

SQLiteStatement stmt = db.compileStatement(sql); stmt.executeInsert();

System.err: android.database.sqlite.SQLiteException: table test_pt has no column named geom (code 1): , while compiling: INSERT INTO test_pt (id, name, geom) VALUES (1, 'test POINT #1', GeomFromText('POINT(0.0 0.0)', 4326)) 04-30 14:41:42.549 6021 6045 W System.err: at org.spatialite.database.SQLiteConnection.nativePrepareStatement(Native Method) 04-30 14:41:42.549 6021 6045 W System.err: at org.spatialite.database.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:922) 04-30 14:41:42.549 6021 6045 W System.err: at org.spatialite.database.SQLiteConnection.prepare(SQLiteConnection.java:525) 04-30 14:41:42.549 6021 6045 W System.err: at org.spatialite.database.SQLiteSession.prepare(SQLiteSession.java:599) 04-30 14:41:42.549 6021 6045 W System.err: at org.spatialite.database.SQLiteProgram.(SQLiteProgram.java:59) 04-30 14:41:42.549 6021 6045 W System.err: at org.spatialite.database.SQLiteStatement.(SQLiteStatement.java:37) 04-30 14:41:42.549 6021 6045 W System.err: at org.spatialite.database.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1011) 04-30 14:41:42.549 6021 6045 W System.err: at com.codename1.spatialite.AndroidSpatialiteDB.execute(AndroidSpatialiteDB.java:93) 04-30 14:41:42.549 6021 6045 W System.err: at com.codename1.spatialite.demo.SpatialiteDemo.start(SpatialiteDemo.java:106) 04-30 14:41:42.549 6021 6045 W System.err: at com.codename1.spatialite.demo.SpatialiteDemoStub.run(SpatialiteDemoStub.java:141) 04-30 14:41:42.549 6021 6045 W System.err: at com.codename1.ui.Display.processSerialCalls(Display.java:1129) 04-30 14:41:42.549 6021 6045 W System.err: at com.codename1.ui.Display.mainEDTLoop(Display.java:924) 04-30 14:41:42.549 6021 6045 W System.err: at com.codename1.ui.RunnableWrapper.run(RunnableWrapper.java:120) 04-30 14:41:42.549 6021 6045 W System.err: at com.codename1.impl.CodenameOneThread$1.run(CodenameOneThread.java:60) 04-30 14:41:42.549 6021 6045 W System.err: at java.lang.Thread.run(Thread.java:818)

Am I doing something wrong?

It appears the it is installed correctly as when I print out the version info using:

Cursor stmt01 = executeQuery("SELECT spatialite_version();"); if (stmt01.next()) { sb.append("\t").append("SPATIALITE_VERSION: " + stmt01.getRow().getString(0)); sb.append("\n"); } stmt01.close(); stmt01 = executeQuery("SELECT proj4_version();"); if (stmt01.next()) { sb.append("\t").append("PROJ4_VERSION: " + stmt01.getRow().getString(0)); sb.append("\n"); } stmt01.close(); stmt01 = executeQuery("SELECT geos_version();"); if (stmt01.next()) { sb.append("\t").append("GEOS_VERSION: " + stmt01.getRow().getString(0)); sb.append("\n"); } stmt01.close();

(*Note: The Cursor/executeQuery are wrapper Classes/methods).

I get:

System.out: SPATIALITE_VERSION: 4.3.0a 04-30 14:41:42.494 6021 6045 I System.out: PROJ4_VERSION: Rel. 4.8.0, 6 March 2012 04-30 14:41:42.494 6021 6045 I System.out: GEOS_VERSION: 3.4.2-CAPI-1.8.2 r0

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/sevar83/android-spatialite/issues/3, or mute the thread https://github.com/notifications/unsubscribe-auth/AAHS1XqYUz-JAxwVJ-Qfk1dHdoq5VtIUks5ttxZ0gaJpZM4TsnN2 .

shannah commented 6 years ago

Thanks for the pointers. Looks like the AddGeometryColumn actually wasn't working. There were problems with my initialization. It is working now.

sevar83 commented 6 years ago

Nice to hear that. Any idea what was the mistake?

On Tue, 1 May 2018 at 16:59 Steve Hannah notifications@github.com wrote:

Closed #3 https://github.com/sevar83/android-spatialite/issues/3.

— You are receiving this because you commented.

Reply to this email directly, view it on GitHub https://github.com/sevar83/android-spatialite/issues/3#event-1603402307, or mute the thread https://github.com/notifications/unsubscribe-auth/AAHS1ab0cwgD3KSRk8uwN6SFSZ86cxquks5tuGoxgaJpZM4TsnN2 .

shannah commented 6 years ago

The call to AddGeometryColumn was returning the error Spatialite: AddGeometryColumn() error: unexpected metadata layout.

I then changed it so that it only runs SELECT InitSpatialMetadata() when it creates the database for the first time. I'm not sure if, before this, it hadn't been running at all, or if it was actually a problem with running it more than once on the same DB. When I made that change, it all started working though.