B4J Question Reading SQL DB script from a file

[Chris2]

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:

org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "��C": syntax error)

Using the same script/query string copied/pasted directly into the app works OK though.

For example (see attached example app also):

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 Sub

Using line 24 generates an error...

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

But using line 25 instead (which gets the same sql text as is in the file) works ok:

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")
);
true

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.

 

[aeric]

You need to save the text file with UTF8 encoding.