I am trying to create an SQLIte database, build and populate its tables from code using the SQL object from jSQL library.
I want to read the SQL script/queries from a text file but am finding when I do that I get an error:
Using the same script/query string copied/pasted directly into the app works OK though.
For example (see attached example app also):
Using line 24 generates an error...
But using line 25 instead (which gets the same sql text as is in the file) works ok:
Reading the SQL from the file appears to be adding a couple of characters in the log.
Can anyone see what's wrong here and tell me how I can successfully get the SQL script from a file?
Many thanks.
I want to read the SQL script/queries from a text file but am finding when I do that I get an error:
Error:
org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "��C": syntax error)For example (see attached example app also):
Full app code:
Sub Process_Globals
Private fx As JFX
Private xui As XUI
Private MainForm As Form
Private Button1, Button2 As Button
Private sql As SQL
End Sub
#AdditionalJar: sqlite-jdbc-3.46.0.0
#AdditionalJar: slf4j-api-1.7.36
#AdditionalJar: slf4j-nop-1.7.36
Sub AppStart (Form1 As Form, Args() As String)
MainForm = Form1
MainForm.RootPane.LoadLayout("main")
MainForm.Show
End Sub
Private Sub Button1_Click
sql.InitializeSQLite("C:\B4J\test2\DB", "test2.db", True)
Dim qry As String = File.ReadString(File.DirAssets, "SQLScript.txt") 'generates exception: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "��C": syntax error)
' Dim qry As String = query 'query runs OK when read from sub query
Log(qry)
sql.AddNonQueryToBatch(qry, Null)
Dim SenderFilter As Object = sql.ExecNonQueryBatch("SQL")
Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
Log(Success)
sql.Close
End Sub
Sub query As String
Return _
$"CREATE TABLE "Table1" (
"id" INTEGER,
"col1" TEXT NOT NULL,
"col2" TEXT NOT NULL,
CONSTRAINT "PK_Units" PRIMARY KEY("id")
);"$
End SubUsing line 24 generates an error...
Using SQL from file:
WARNING: package com.sun.javafx.embed.swing.oldimpl not in javafx.swing
Waiting for debugger to connect...
Program started.
��CREATE TABLE "Table1" (
"id" INTEGER,
"col1" TEXT NOT NULL,
"col2" TEXT NOT NULL,
CONSTRAINT "PK_Units" PRIMARY KEY("id")
);
org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "��C": syntax error)
at org.sqlite.core.DB.newSQLException(DB.java:1179)
at org.sqlite.core.DB.newSQLException(DB.java:1190)
at org.sqlite.core.DB.throwex(DB.java:1150)
at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
at org.sqlite.core.NativeDB.prepare(NativeDB.java:132)
at org.sqlite.core.DB.prepare(DB.java:264)
at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:46)
at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:32)
at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:25)
at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:34)
at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:225)
at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:205)
at anywheresoftware.b4j.objects.SQL$2.run(SQL.java:264)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:840)
false
sql from Sub query:
WARNING: package com.sun.javafx.embed.swing.oldimpl not in javafx.swing
Waiting for debugger to connect...
Program started.
CREATE TABLE "Table1" (
"id" INTEGER,
"col1" TEXT NOT NULL,
"col2" TEXT NOT NULL,
CONSTRAINT "PK_Units" PRIMARY KEY("id")
);
trueReading the SQL from the file appears to be adding a couple of characters in the log.
Can anyone see what's wrong here and tell me how I can successfully get the SQL script from a file?
Many thanks.