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.