SQLilte issue when creating a database

[XCoder]

I wrote the following code to directly access the latest sqlite3.dll library to create a database:

Code: Select all

DatabaseFile$ = "Temp.db"
sqlite3_dbHandle = 0

Lib = OpenLibrary(#PB_Any, "D:\!Updates\Programming\PB\DLL experiments\sqlite3.dll")
psqlite3_open = GetFunction(Lib, "sqlite3_open")
hResult = CallCFunctionFast(psqlite3_open, @DatabaseFile$, @sqlite3_dbHandle)
CloseLibrary(Lib)

This seems to create a valid SQLite database but the database is named with the first letter of DatabaseFile$ ie "T" instead of "Temp.db".

How can I resolve this issue?

[I am experimenting with the sqlite3.dll library as I want to try to resolve the issue raised by jassing at viewtopic.php?t=81228 by writing an alternative function for opening a database with the latest sqlite3.dll library]

[mk-soft]

PB use unicode strings ..

not testet:

Code: Select all

DatabaseFile$ = "Temp.db"
sqlite3_dbHandle = 0

*dbFile = UTF8(DatabaseFile$)
;*dbFile = Ascii(DatabaseFile$)

Lib = OpenLibrary(#PB_Any, "D:\!Updates\Programming\PB\DLL experiments\sqlite3.dll")
psqlite3_open = GetFunction(Lib, "sqlite3_open")
hResult = CallCFunctionFast(psqlite3_open, *dbFile, @sqlite3_dbHandle)
CloseLibrary(Lib)
FreeMemory(*dbFile)

[XCoder]

@ mk-soft

Thanks, that has solved my problem.

[infratec]

Btw. why not:

Code: Select all

UseSQLiteDatabase("D:\!Updates\Programming\PB\DLL experiments\sqlite3.dll")

Then you don't need to do that lib stuff.
Or is there a complete new function inside?

[jassing]

There are features w/in SQLite that are not available w/in the confines of PB's "database' interface.
years ago, I bought additional license to get fred to add in fulltext indexing into the PB library.
So if you want FULL functionality of SQLite, you have to call the dll.

[infratec]

This works:
https://www.sqlite.org/2023/sqlite-dll- ... 410200.zip
https://www.sqlitetutorial.net/sqlite-full-text-search/

Code: Select all

UseSQLiteDatabase("sqlite3.dll")

DB = OpenDatabase(#PB_Any, ":memory:", "", "")
If DB
  
  If DatabaseQuery(DB, "SELECT sqlite_version()")
    If NextDatabaseRow(DB)
      Debug GetDatabaseString(DB, 0)
    EndIf
    FinishDatabaseQuery(DB)
  EndIf
  
  DatabaseUpdate(DB, "CREATE VIRTUAL TABLE posts USING fts5(title, body)")
  DatabaseUpdate(DB, "INSERT INTO posts(title,body) VALUES('Learn SQlite FTS5','This tutorial teaches you how to perform full-text search in SQLite using FTS5'), ('Advanced SQlite Full-text Search','Show you some advanced techniques in SQLite full-text searching'),('SQLite Tutorial','Help you learn SQLite quickly and effectively')")
 
  If DatabaseQuery(DB, "SELECT * FROM posts WHERE posts MATCH 'fts5'")
    While NextDatabaseRow(DB)
      Debug GetDatabaseString(DB, 0) + " " + GetDatabaseString(DB, 1)
    Wend
    FinishDatabaseQuery(DB)
  Else
    Debug DatabaseError()
  EndIf
  
  CloseDatabase(DB)
  
EndIf

Output:

3.41.2
Learn SQlite FTS5 This tutorial teaches you how to perform full-text search in SQLite using FTS5

[jassing]

Yes, at the time, you couldn't do that.
(FTS is no longer available w/in the library... )
I was using it as an example.. not everything is available -- at the time, you had to use the DLL; Fred put it in the built in. Now you can use the DLL w/in the built in. That wasn't the case back then. (purebasic continues to grow & mature)

[XCoder]

Btw. why not:

Code: Select all

UseSQLiteDatabase("D:\!Updates\Programming\PB\DLL experiments\sqlite3.dll")

Then you don't need to do that lib stuff.
Or is there a complete new function inside?

The PB SQLite library does not provide all the functionality available in the latest version of the sqlite3.dll file. I received this error message when trying to do a join using the PB SQLite library: "malformed database schema (RightOuterJoinDemo) - RIGHT and FULL OUTER JOINs are not currently supported". I looked through the PB forums for a solution when I came across the code you have suggested above to use the latest sqlite3.dll file. This solved the problem with joins. However, I noticed that this caused another issue, raised by jassing, when creating a table using the code

Code: Select all

Debug "Create? "+Str(DatabaseUpdate(0, "CREATE TABLE IncomingData(test TEXT primary key) WITHOUT ROWID;"))

For this reason, I decided to look into directly calling some of the functions in the latest sqlite3.dll file so that I could create a table in my own program (or should I call it an App these days ).

I was not aware of the FTS5 virtual table module. I'm not sure whether your solution using FTS5 completely solves the problem with CREATE TABLE ; According to https://www.sqlitetutorial.net/sqlite-full-text-search/ the FTS5 virtual table module cannot use types, constraints, or primary key.

[jassing]

The PB SQLite library does not provide all the functionality available in the latest version of the sqlite3.dll file.

You're correct. It's a stripped-down version of the full sqlite library; at the time I asked fred to include FTS, (I may be wrong, recollection and all) it was for size reasons. That might still be true.

If you want the full sqlite experience, you'll have to use the full dll directly. At least, that's been my experience. Not sure when the FTS was re-dropped from the PB library, but that is unfortunate...
[/quote]