Import SQL from text file

General
1

I had a SQL dump file in text format, that I would like to import and run. But every time I receive and SQL error.
The SQL is perfect, the problem occurs only when I import the file.

this is the code that I’m using:

Content of SQL dump file:

[code]CREATE TABLE BANCOS (
CODIGO INTEGER NOT NULL,
DESCRICAO VARCHAR(250)
);

CREATE TABLE CEPS (
CODIGO INTEGER NOT NULL,
CEP VARCHAR(8) NOT NULL,
RUA VARCHAR(80) NOT NULL,
BAIRRO VARCHAR(80) NOT NULL,
CIDADE VARCHAR(80) NOT NULL,
UF VARCHAR(2) NOT NULL
);[/code]

The Xojo code to import the file and call Execute SQL

[code] Dim t As TextInputStream
Dim f As FolderItem
f = Volume(0).Child(“Xojo”).Child(“dump.sql”)

If f.exists Then
t = TextInputStream.Open(f)
t.Encoding = Encodings.UTF8

Dim imported As String
L = 0
While Not t.EOF
  L = L + 1
  If L = 1 Then
    imported = t.ReadLine + chr(13) + chr(10)
  Else
    imported = imported + t.ReadLine + chr(13) + chr(10)
  End If
Wend

dbN.SQLExecute(imported)

msgbox  dbn.ErrorMessage 'I receive and SQL error here acussing on first caracter of the SQL code inside dump file.

End If[/code]

2 
Dim imported As String
imported = t.ReadAll
dbN.SQLExecute(imported)
3

Did you read:

https://sqlite.org/cli.html#converting_an_entire_database_to_an_ascii_text_file

Also: you do not explain how you generated the .dump file…

4

If I recall correctly… SQLExecute executes ONE SQL statement… not a “script”…
My Tadpole SQLite manager has to parse a “script” into individual statements and execute them one at a time

5

[quote=415002:@Dave S]If I recall correctly… SQLExecute executes ONE SQL statement… not a “script”…
My Tadpole SQLite manager has to parse a “script” into individual statements and execute them one at a time[/quote]
I had no issue running OPs SQL script as a SQLExecute in testing, and have never had an issue like you describe.

I would guess that if it’s balking when trying to read a text file that there’s a hidden BOM or something that’s making it unhappy

6

well all I can say is this…

DB_PROJECT.DB_Execute("Create table abc (test1 text); Create table xyz (test2 text);")

Executed with no errors… but only the first statement actually was executed… the 2nd was ignored
no error message… but no table was created either.

7

In the Terminal: $ sqlite3 ex1 .dump | gzip -c >ex1.dump.gz
The generated text file (dmp) is not an exported text file. It is a gz file, and once uncompressed it looks like a script, so Jean-Yves looks correct (not tested).

I checked right now the code shared in the sqlite.org page (link above) in the Terminal (El Capitan). I do not know how to do that with Xojo.

Paulo: do you have to do the import once or many times ?

This line (below) does not works.

zcat ex1.dump.gz | sqlite3 ex2
8

This code does not work; I receive "Syntax error near “create”…

Dim imported As String imported = t.ReadAll dbN.SQLExecute(imported)

I stored the same SQL inside a constant and works fine.

9

[quote=415046:@Paulo Cezar]This code does not work; I receive "Syntax error near “create”…

Dim imported As String imported = t.ReadAll dbN.SQLExecute(imported)

I stored the same SQL inside a constant and works fine.[/quote]
That would mean that it’s an encoding issue.

Change it to

t.ReadAll(encodings.UTF8)

And see if that helps.

10

Read my Conversation about this. In short, it wroks fine reading the dump.txt file a line at a time (once correctly coded: it tooks me minutes to remove some errors I’ve done: I was not enough concentrated).

No constant used.

My dump.txt file was generated using El Capitan Terminal (and looks different than what Paulo shared).