SQLite3 BaseFunction-Include for Win + Lin + Unicode

ts-soft · Post by **ts-soft** » Tue Aug 07, 2007 6:17 pm

Thanks Thorsten1867

I have updated the source, but only the second version:
http://www.purebasic.fr/english/viewtop ... 667#194667

Thorsten1867 · Post by **Thorsten1867** » Tue Aug 07, 2007 7:53 pm

Convert old ANSI database to new unicode database:

Code: Select all

; Convert SQLite-Database (ANSI -> Unicode)
; by Thorsten Hoeppner

XIncludeFile "SQLite3_Include.pbi"

If Not SQLiteInit()
  MessageRequester("Error", "Couldn't init SQLite3 lib") : End
EndIf

Structure SQLiteMasterStructure
  Name.s
  sql.s
EndStructure

Procedure.b ConvertDB(dbfile.s) ; Konvertiere von ANSI nach Unicode
  If Not FileExist(dbfile) : ProcedureReturn #False : EndIf
  Define.SQ3_TABLEMAP TableMap
  NewList Table.SQLiteMasterStructure()
  
  CopyFile(dbfile, StringField(dbfile,1,".")+".asc")
  
  ; ===== Load as ANSI =====
  hDB1.l = SQLiteOpen(dbfile, #True)
  If hDB1
    ;{ --- Get table names ---
    If SQLiteGetTable(hDB1, "SELECT name, sql  FROM sqlite_master WHERE type='table';", TableMap)
      While SQLiteNextRow(TableMap)
        AddElement(Table())
        Table()\Name = SQLiteColValue(TableMap,1)
        Table()\sql = SQLiteColValue(TableMap,2)
      Wend 
    EndIf ;}
    ;{ --- Read table content ---
    If CreateFile(0, "SQLDump.sql")
      ForEach Table()
        If SQLiteGetTable(hDB1, "SELECT * FROM "+Table()\Name+";", TableMap)
          WriteStringN(0, Table()\sql)
          While SQLiteNextRow(TableMap)
            SQLiteSelectCol(TableMap, 1)
            sql$ = "INSERT INTO "+Table()\Name+" VALUES ('"+SQLiteValue(TableMap)+"'"
            While SQLiteNextCol(TableMap)
              sql$ + ", '"+SQLiteValue(TableMap)+"'"
            Wend
            sql$ + ");"
            sql$ = ReplaceString(sql$, Chr(10), " ")
            sql$ = RemoveString(sql$, Chr(13))
            WriteStringN(0, sql$, #PB_UTF8)
          Wend
          SQliteFreeTable(TableMap)
        EndIf
      Next
      CloseFile(0)
    EndIf ;}
    SQLiteClose(hDB1)
  EndIf
  
  SQLiteEnd()
  If Not SQLiteInit()
    MessageRequester("Error", "Couldn't init SQLite3 lib")
  EndIf
  
  ;{ ===== Save as unicode =====
  If ReadFile(1, "SQLDump.sql")
    DeleteFile(dbfile)
    hDB.l = SQLiteOpen(dbfile)
    If hDB
      SQLiteExecute(hDB, "BEGIN TRANSACTION;")
      While Eof(1) = 0 
        SQLiteExecute(hDB, ReadString(1))
      Wend
      If SQLiteExecute(hDB, "CREATE TABLE DBInfo (id INTEGER PRIMARY KEY, format TEXT);")
        SQLiteExecute(hDB, "INSERT INTO DBInfo (format) VALUES('unicode');")
      EndIf
      SQLiteExecute(hDB, "COMMIT;")
      SQLiteClose(hDB)
    Else
      ProcedureReturn #False
    EndIf
    CloseFile(1)
  Else
    ProcedureReturn #False
  EndIf ;}
  
  DeleteFile("SQLDump.sql")
  
  ProcedureReturn #True
EndProcedure

Procedure.b CheckDBCodePage(dbfile.s) ; Überprüfe CodePage
  If Not FileExist(dbfile) : ProcedureReturn #False : EndIf
  Define.SQ3_TABLEMAP TableDB
  hDB = SQLiteOpen(dbfile)
  If hDB
    If SQLiteGetTable(hDB, "Select format From DBInfo;", TableDB) 
      SQliteFreeTable(TableDB)
    ElseIf SQLiteErrorMsg(hDB) = "no such table: DBInfo"
      SQLiteClose(hDB)
      ProcedureReturn #True
    EndIf 
  EndIf
  SQLiteClose(hDB)
  ProcedureReturn #False
EndProcedure

If CheckDBCodePage("test.db") ; Überprüfe Codepage (Datenbank-Klasse)
  ConvertDB("test.db")
EndIf

SQLiteEnd()

Xombie · Post by **Xombie** » Fri Aug 17, 2007 6:51 pm

Thanks so much for these updated functions. Much more simple than the mess I was dealing with before. And it seems faster than before...? Although that may be a newer dll.

So, my question - what'd be the easiest/fastest (possible?) way to include the sqlite dll in the app to use with your latest include?

Thanks!

Thorsten1867 · Post by **Thorsten1867** » Mon Aug 20, 2007 2:26 pm

I think I found a bug:

Code: Select all

XIncludeFile("SQLite3_Include.pbi")

SQLiteInit()

Procedure.s CheckRSK(hDB.l) 
  Define.SQ3_TABLEMAP TableDB 
  found.b = #False : test$ = "Test"
  SQLiteGetTable(hDB, "SELECT opt FROM worte WHERE wortstamm = '"+test$+"' ;", TableDB)
  While SQLiteNextRow(TableDB) 
    Select SQLiteColValue(TableDB, 1)
      Case "1"
        ; --- found ---
        Break 
    EndSelect
  Wend
  SQliteFreeTable(TableDB)
  ProcedureReturn "Test"
EndProcedure

hDB = SQLiteOpen("TestDB.db")
SQLiteExecute(hDB, "CREATE TABLE worte (id INTEGER PRIMARY KEY, wortstamm TEXT, endung TEXT, opt TEXT);")
SQLiteExecute(hDB, "INSERT INTO worte (wortstamm, endung, opt) VALUES('Test', 'e', '1');")
; --------------------------------------------------
Result$ = CheckRSK(hDB) : Debug "Result: "+Result$
; --------------------------------------------------
SQLiteClose(hDB)

SQLiteEnd() ; Close SQLite

The result of the procedure is "1" and not "Test"?????

Post by **Kiffi** » Mon Aug 20, 2007 2:32 pm

Thorsten1867 wrote:The result of the procedure is "1" and not "Test"?????

please check the last line of your CheckRSK()-Proc.

Greetings ... Kiffi

Thorsten1867 · Post by **Thorsten1867** » Mon Aug 20, 2007 2:37 pm

I return "Test", but the result is "1".

Post by **Kiffi** » Mon Aug 20, 2007 2:46 pm

Thorsten1867 wrote:I return "Test", but the result is "1".

<SorryForGerman>
Wo ist denn jetzt Dein Problem? Wieso gibst Du immer 'Test' zurück (unabhängig vom Ergebnis)?
</SorryForGerman>

Greetings ... Kiffi

Thorsten1867 · Post by **Thorsten1867** » Mon Aug 20, 2007 2:57 pm

[german]
Ich habe den Orginalcode auf ein Minimum reduziert und ich gebe Test zurück, damit ich sicher bin, das der Fehler nicht an einer Variablen liegen kann. Egal was ich zurückgebe ich erhalte immer "1", was eigentlich völlig unmöglich sein sollte. Scheinbar liegt das Problem an dem "Select" in Verbindung mit "SQLiteColValue()".
[/german]

Post by **Kiffi** » Mon Aug 20, 2007 3:14 pm

ah, now i see. This is really very strange...

Greetings ... Kiffi

// Edit:

Here ist a little workaround for this strange bug. By avoiding the
'break'-Statement, your Procedure returns the correct value. But
nevertheless i don't know, why this happens...

Code: Select all

Procedure.s CheckRSK(hDB.l)
  Define.SQ3_TABLEMAP TableDB
  Define.l RowCounter
  found.b = #False : test$ = "Test"
  SQLiteGetTable(hDB, "SELECT opt FROM worte WHERE wortstamm = '"+test$+"' ;", TableDB)
  
  For RowCounter = 1 To SQLiteRows(TableDB)
  
    SQLiteSelectRow(TableDB, RowCounter)
    SQLiteSelectCol(TableDB, 1)
    
    Select SQLiteValue(TableDB)
      Case "1"
        ; --- found ---
        RowCounter = SQLiteRows(TableDB)
    EndSelect
    
  Next
    
  SQliteFreeTable(TableDB)
  
  ProcedureReturn "Test"
  
EndProcedure

Thorsten1867 · Post by **Thorsten1867** » Mon Aug 20, 2007 4:28 pm

I don't know why, but this seems to work, too:

Code: Select all

Procedure.s CheckRSK(hDB.l)
  Define.SQ3_TABLEMAP TableDB
  found.b = #False : test$ = "Test"
  SQLiteGetTable(hDB, "SELECT opt FROM worte WHERE wortstamm = '"+test$+"' ;", TableDB)
  While SQLiteNextRow(TableDB)
    opt$ = SQLiteColValue(TableDB, 1)
    Select opt$
      Case "1"
        ; --- found ---
        Break
    EndSelect
  Wend
  SQliteFreeTable(TableDB)
  ProcedureReturn "Test"
EndProcedure

Heathen · Post by **Heathen** » Mon Aug 20, 2007 10:44 pm

Thanks for this man, it will make my life alot easier!

Heathen · Post by **Heathen** » Thu Aug 23, 2007 4:39 am

Well, merged it into my engine and it's running great! Very nice work man.

michel51 · Post by **michel51** » Sat Sep 01, 2007 3:20 pm

ts-soft wrote:For advanced using, i have add bindings-functions, all functions with unicode-support!
Last update: 07.08.2007

I've dowloaded this last version of "SQLite3_Include.pbi" and tried to run with the first (and other) example codes.(see the first posts)
But I get only this error message (dont' know, how to post an image :roll: )

Code: Select all

PureBasic - Error

purebasic.asm: 1263:Invalid mnemonic 'SUB'
purebasic.asm: 1264:Invalid mnemonic 'CALL'
purebasic.asm: 1265:Invalid mnemonic 'ADD'
purebasic.asm: 1585:Invalid mnemonic 'SUB'
purebasic.asm: 1586:Invalid mnemonic 'CALL'
purebasic.asm: 1587:Invalid mnemonic 'ADD'
purebasic.asm: 3120:Invalid mnemonic 'SUB'
purebasic.asm: 3121:Invalid mnemonic 'CALL'
purebasic.asm: 3122:Invalid mnemonic 'ADD'
purebasic.asm: 3939:Parameter syntax error (parameter 1)
Error: Assambler

This message occures also if I try only the include

Code: Select all

XIncludeFile "SQLite3_Include.pbi"
EnableExplicit

It seems, that is a error within the include file - but I cannot think it me.
But it should run with MacOsX
Only the FIRST version was running. That's posted by me in May '07

What is wrong?

ts-soft · Post by **ts-soft** » Sat Sep 01, 2007 8:52 pm

@michel51
Sorry, i have no "Apfelchen" Macintosh, so i can't help.

CSAUER · Post by **CSAUER** » Mon Sep 10, 2007 1:39 pm

You are right. The actual Beta of PB for Mac is not working with the latest code. It should not be a problem of the SQLite-include, it should be PB for Mac in general. I cannot find out where it comes from, but I passed Fred a few example codes to go deep into details.
I don't know about the actual status. Hopefully he will get PB for Mac fully compatible, so that every standard PB4 code works fine.

Another question from my side:
Does anybody have an idea, how to explore table names?
I know that there is a ".tables" command, but it does not work with SQLiteGetTable and SQLiteExecute does not bring a result.
If you key into SQLite.exe it works fine.