Android Question SQLite Error I do'nt understand why?

[Guenter Becker]

Hello

I have two identical sqlite 3 databases testA and testB. Each with the a table test and the columns ID and text.
I want to copy a record from tableA.test to tableB.test doing this:

sql.ExecNonQuery2("ATTACH DATABASE ? AS clone",Array As String(DBFolder & "/testB.db"))
sql.ExecNonQuery("INSERT INTO clone.test SELECT * FROM test Where text='A'")

The Attachment runs without failure but the insert fails:

ExecuteMemoryTable: SELECT name FROM sqlite_master WHERE type = 'table'
Error occurred on line: 651 (Test_example)
android.database.sqlite.SQLiteException: no such column: text[B] [/B](code 1 SQLITE_ERROR[1]): , while compiling: INSERT INTO clone.test SELECT * FROM test Where text='A'
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.-$$Nest$smnativePrepareStatement(SQLiteConnection.java:0)
    at android.database.sqlite.SQLiteConnection$PreparedStatementCache.createStatement(SQLiteConnection.java:2012)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatementLI(SQLiteConnection.java:1548)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1576)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:994)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:614)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:63)
    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:34)
    at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:2772)
    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:2692)
    at anywheresoftware.b4a.sql.SQL.ExecNonQuery(SQL.java:74)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:777)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:354)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:157)
    at anywheresoftware.b4a.keywords.Common.CallSub4(Common.java:1169)
    at anywheresoftware.b4a.keywords.Common.CallSubNew3(Common.java:1132)
    at b4a.example3.customlistview$ResumableSub_PanelClickHandler.resume(customlistview.java:805)
    at b4a.example3.customlistview._panelclickhandler(customlistview.java:748)
    at b4a.example3.customlistview._panel_click(customlistview.java:735)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:229)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:209)
    at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:80)
    at android.view.View.performClick(View.java:8464)
    at android.view.View.performClickInternal(View.java:8441)
    at android.view.View.-$$Nest$mperformClickInternal(View.java:0)
    at android.view.View$PerformClick.run(View.java:32966)
    at android.os.Handler.handleCallback(Handler.java:959)
    at android.os.Handler.dispatchMessage(Handler.java:100)
    at android.os.Looper.loopOnce(Looper.java:257)
    at android.os.Looper.loop(Looper.java:342)
    at android.app.ActivityThread.main(ActivityThread.java:9634)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:619)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:929)

I am irritated about this error because both dabases do have a field text?
Does anyone worked with attached databases and give me a working example?

 

[Alex_197]

Why do you need this line

sql.ExecNonQuery2("ATTACH DATABASE ? AS clone",Array As String(DBFolder & "/testB.db"))

If you want to copy a record from table A (TestA) to table B (TestB) is al you need to do

SQLA.Initialize(File.DirDocuments, DBFileNameA, False)
SQLB.Initialize(File.DirDocuments, DBFileNameB, False)

and then read from tableA and insert into tableB

 

[Guenter Becker]

Why do you need this line

sql.ExecNonQuery2("ATTACH DATABASE ? AS clone",Array As String(DBFolder & "/testB.db"))

If you want to copy a record from table A (TestA) to table B (TestB) is al you need to do

SQLA.Initialize(File.DirDocuments, DBFileNameA, False)
SQLB.Initialize(File.DirDocuments, DBFileNameB, False)

and then read from tableA and insert into tableB

Hi,
the solution you pointed out did not meet my question!

If you copy the record your way you have to deal with the different column types by reading in the values because the resultset does not have the possibility to have a command like rs.getObject. The only way to copy direct from table to table is to attach the database that's why sqlite has this command.

I like to understand why the shown sql commands will work using a database management softare like DBBrowser but nit in B4X. The commands are correct but in B4X they will raise the named error.

 

[Alex_197]

Hi,
the solution you pointed out did not meet my question!

If you copy the record your way you have to deal with the different column types by reading in the values because the resultset does not have the possibility to have a command like rs.getObject. The only way to copy direct from table to table is to attach the database that's why sqlite has this command.

I like to understand why the shown sql commands will work using a database management softare like DBBrowser but nit in B4X. The commands are correct but in B4X they will raise the named error.

you can use my solution as a plan B

 

[LucaMs]

I've only tested it in B4J and it works (I'm attaching the project).

See if my code matches yours exactly.

 

[aeric]

I've only tested it in B4J and it works (I'm attaching the project).

See if my code matches yours exactly.

If it works in B4J then probably B4A needs a newer version of SQLite driver.

 

[Guenter Becker]

you can use my solution as a plan B

I the meantime I prepared it and going to examin the solution from lucas. Anyway thank you for the quick response.

 

[Guenter Becker]

If it works in B4J then probably B4A needs a newer version of SQLite driver.

Hello.
working with B4A 13.70 and SQLDriver 1.5 SQLCipher 1.7. Do you know if there are newer Drivers and if yes where to grab them.

 

[Guenter Becker]

I've only tested it in B4J and it works (I'm attaching the project).

See if my code matches yours exactly.

Hi LucaMS,
as professional as I know you. Examined your project and found that it's exactly the same as I did in B4A. The difference in my Project is only:

If fileexists(DBFolder,"testA") = false then
 file.copy(file.dirassets,"testA", DBFolder,"testA")
 file.copy(file.dirassets,"testA", DBFolder,"testB")
'DBFolder = XUI.Defaultfolder
end if

B4A 13.7, SQL 1.5 SQKCIPHER 1.7
Any Idea why it's working in B4J but makes problems in B4A? May be Erel is also interested and has a comment.

 

[teddybear]

It is not related to the SQL driver.

android.database.sqlite.SQLiteException: no such column: text (code 1 SQLITE_ERROR[1]): , while compiling: INSERT INTO clone.test SELECT * FROM test Where text='A'

The log has told you no such column text in the table test.
Check if the schema of the table test has the column text.
The project works.

 

[Guenter Becker]

It is not related to the SQL driver.

The log has told you no such column text in the table test.
Check if the schema of the table test has the column text.
The project works.

That was the first I did.

 

[Guenter Becker]

Hello,
I checked the code in my project against the code from lucas: found no difference.
I deleted my code.
I copied the code from lucas to my B4A Project.
I used the function "clean Project".
I used the function "project configuration" to create a new one.
I started the project and I could not believe it works!!
Still don't know why! Still don't kno where was the problem. But this is not my first time sometimes things don't go well and a couple of time later on without having done any changes they work.
So I move on to live with the miracle.
Many thank you for all of you helping to solve the solution

 

[sirjo66]

Very well !!!!

just remember, though, that this isn't the first time Luca has worked miracles

 

[MrKim]

According to Google:

SQLCipher can you use ATTACH DATABASE

Yes, you can use ATTACH DATABASE in SQLCipher. It is fully supported and commonly used to copy data between databases or query multiple databases at once.
However, because SQLCipher databases are encrypted, you must provide the decryption key for the attached database using an extended syntax or a PRAGMA statement immediately after attaching.
Here are the two ways to attach an encrypted database:

Method 1: The Extended KEY Syntax (Recommended)
SQLCipher extends SQLite’s standard ATTACH syntax to let you pass the encryption key directly in the command.

sql
-- Attach an encrypted database named 'archive.db' and give it the alias 'archive'
ATTACH DATABASE 'archive.db' AS archive KEY 'your-secret-password';

Use code with caution.


Method 2: Using PRAGMA
If you prefer standard SQLite syntax, you can attach the database first and then use a schema-specific PRAGMA to provide the key.

sql
-- 1. Attach the database
ATTACH DATABASE 'archive.db' AS archive;

-- 2. Provide the key specifically for the 'archive' schema
PRAGMA archive.key = 'your-secret-password';