Android Question Problem copying table to attached DB

[RB Smissaert]

Trying to copy a table from the main SQLite database (main) to an attached database. I am using SQLCipher. The table I am copying has this DDL:

CREATE TABLE [ADDRESSES]([EMIS_NUMBER] INTEGER PRIMARY KEY, [HOUSE] TEXT, [STREET] TEXT, [LOCALITY] TEXT, [TOWN] TEXT, [POSTCODE] TEXT, [HOME_PHONE] TEXT, [MOBILE_PHONE] TEXT, [E_MAIL] TEXT, [LATITUDE] REAL, [LONGITUDE] REAL)

The table I am copying to has exactly the same DDL but has a different name: ADDRESSES_NEW

The new database file is attached like this:

ATTACH DATABASE '/storage/3637-6230/Android/data/b4a.sqlitelight1/files/CopyTest.db3' AS 'DBA'

And this runs fine.

The SQL that copies the table data is like this:

INSERT INTO [DBA.ADDRESSES_NEW] SELECT * FROM [ADDRESSES]

This gives me the following error:

net.sqlcipher.database.SQLiteConstraintException: UNIQUE constraint failed: DBA.ADDRESS_NEW.EMIS_NUMBER: INSERT INTO [DBA.ADDRESSES_NEW] SELECT * FROM [ADDRESSES]

I have checked the table DBA.ADDRESSES_NEW prior to the insert and it is indeed an empty table with no data. So how can we can get this constraint violation? EMIS_NUMBER is an integer primary key in both tables and the source table ADDRESSES has no null values in the EMIS_NUMBER column.

Doing:

INSERT INTO [ADDRESSES_NEW] SELECT * FROM [ADDRESSES]

So with no database name identifier, gives the same result.

Strangely, if I do:

INSERT INTO [DBA.ADDRESSES_NEW] SELECT * FROM [main.ADDRESSES]

Then I get this error:

net.sqlcipher.database.SQLiteConstraintException: no such table: main.ADDRESSES: INSERT INTO [DBA.ADDRESSES_NEW] SELECT * FROM [main.ADDRESSES]

So, it seems somehow there is something wrong with attaching the other database file.


RBS

 

[RB Smissaert]

Trying to copy a table from the main SQLite database (main) to an attached database. I am using SQLCipher. The table I am copying has this DDL:

CREATE TABLE [ADDRESSES]([EMIS_NUMBER] INTEGER PRIMARY KEY, [HOUSE] TEXT, [STREET] TEXT, [LOCALITY] TEXT, [TOWN] TEXT, [POSTCODE] TEXT, [HOME_PHONE] TEXT, [MOBILE_PHONE] TEXT, [E_MAIL] TEXT, [LATITUDE] REAL, [LONGITUDE] REAL)

The table I am copying to has exactly the same DDL but has a different name: ADDRESSES_NEW

The new database file is attached like this:

ATTACH DATABASE '/storage/3637-6230/Android/data/b4a.sqlitelight1/files/CopyTest.db3' AS 'DBA'

And this runs fine.

The SQL that copies the table data is like this:

INSERT INTO [DBA.ADDRESSES_NEW] SELECT * FROM [ADDRESSES]

This gives me the following error:

net.sqlcipher.database.SQLiteConstraintException: UNIQUE constraint failed: DBA.ADDRESS_NEW.EMIS_NUMBER: INSERT INTO [DBA.ADDRESSES_NEW] SELECT * FROM [ADDRESSES]

I have checked the table DBA.ADDRESSES_NEW prior to the insert and it is indeed an empty table with no data. So how can we can get this constraint violation? EMIS_NUMBER is an integer primary key in both tables and the source table ADDRESSES has no null values in the EMIS_NUMBER column.

Doing:

INSERT INTO [ADDRESSES_NEW] SELECT * FROM [ADDRESSES]

So with no database name identifier, gives the same result.

Strangely, if I do:

INSERT INTO [DBA.ADDRESSES_NEW] SELECT * FROM [main.ADDRESSES]

Then I get this error:

net.sqlcipher.database.SQLiteConstraintException: no such table: main.ADDRESSES: INSERT INTO [DBA.ADDRESSES_NEW] SELECT * FROM [main.ADDRESSES]

So, it seems somehow there is something wrong with attaching the other database file.


RBS

I tried this with the standard SQL library (so not SQLCipher) and exactly the same problem:

android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: ADDRESSES_NEW.EMIS_NUMBER (code 1555)

RBS

 

[RB Smissaert]

I tried this with the standard SQL library (so not SQLCipher) and exactly the same problem:

android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: ADDRESSES_NEW.EMIS_NUMBER (code 1555)

RBS

Made a little demo project and in that all works fine, so must be doing something wrong somewhere.
Even with the large table ADDRESSES it works all fine. Left this table out of the demo.

RBS

 

[RB Smissaert]

Made a little demo project and in that all works fine, so must be doing something wrong somewhere.
Even with the large table ADDRESSES it works all fine. Left this table out of the demo.

RBS

Of course the check if the data was copied OK should be like this:

 'Check the data copy has worked
 strSQL = "SELECT NAME FROM Table2 WHERE ID = 2"
 strName = Starter.SQL1.ExecQuerySingleResult(strSQL)
 Log("btnCopyTable_Click, name where ID = 2: " & strName)

So, select from Table2, not Table1.
Still not figured out what is going wrong in my code in the real project.

RBS

 

[RB Smissaert]

Trying to copy a table from the main SQLite database (main) to an attached database. I am using SQLCipher. The table I am copying has this DDL:

CREATE TABLE [ADDRESSES]([EMIS_NUMBER] INTEGER PRIMARY KEY, [HOUSE] TEXT, [STREET] TEXT, [LOCALITY] TEXT, [TOWN] TEXT, [POSTCODE] TEXT, [HOME_PHONE] TEXT, [MOBILE_PHONE] TEXT, [E_MAIL] TEXT, [LATITUDE] REAL, [LONGITUDE] REAL)

The table I am copying to has exactly the same DDL but has a different name: ADDRESSES_NEW

The new database file is attached like this:

ATTACH DATABASE '/storage/3637-6230/Android/data/b4a.sqlitelight1/files/CopyTest.db3' AS 'DBA'

And this runs fine.

The SQL that copies the table data is like this:

INSERT INTO [DBA.ADDRESSES_NEW] SELECT * FROM [ADDRESSES]

This gives me the following error:

net.sqlcipher.database.SQLiteConstraintException: UNIQUE constraint failed: DBA.ADDRESS_NEW.EMIS_NUMBER: INSERT INTO [DBA.ADDRESSES_NEW] SELECT * FROM [ADDRESSES]

I have checked the table DBA.ADDRESSES_NEW prior to the insert and it is indeed an empty table with no data. So how can we can get this constraint violation? EMIS_NUMBER is an integer primary key in both tables and the source table ADDRESSES has no null values in the EMIS_NUMBER column.

Doing:

INSERT INTO [ADDRESSES_NEW] SELECT * FROM [ADDRESSES]

So with no database name identifier, gives the same result.

Strangely, if I do:

INSERT INTO [DBA.ADDRESSES_NEW] SELECT * FROM [main.ADDRESSES]

Then I get this error:

net.sqlcipher.database.SQLiteConstraintException: no such table: main.ADDRESSES: INSERT INTO [DBA.ADDRESSES_NEW] SELECT * FROM [main.ADDRESSES]

So, it seems somehow there is something wrong with attaching the other database file.


RBS

All solved.
In the end this was something very simple and silly.
With all the testing a table ADDRESSES_NEW had gone into the main database file, instead of the attached file.
Must remember to always look first for the simple explanations of a bug!

RBS