SQLite3 BaseFunction-Include for Win + Lin + Unicode

michel51 · Post by **michel51** » Mon Sep 10, 2007 5:32 pm

CSAUER wrote: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.

Yes, I'm trying to build a small DB, but it seems to be as you say.

I can open the library (see the code)

Code: Select all

sqlite3DLL = OpenLibrary(0, "/usr/lib/libsqlite3.dylib")
If sqlite3DLL
  Debug "Lib reachable, Nr: "+Str(sqlite3DLL)
  
  sqlite3_libversion = GetFunction(sqlite3DLL, "sqlite3_version")

  Debug sqlite3_libversion
  If sqlite3_libversion = 0
    Debug "no function 'libversion'"
  Else
    Debug "function 'libversion' reachable"
  EndIf   

  sqlite3_open = GetFunction(sqlite3DLL, "sqlite3_open")
  Debug sqlite_open
EndIf

but the functions are returning "0" (=zero).
Or is my code wrong? Anything missing? I don't think so.

Thorsten1867 · Post by **Thorsten1867** » Mon Sep 10, 2007 5:52 pm

CSAUER wrote: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.

Code: Select all

SELECT name, sql  FROM sqlite_master WHERE type='table';

Xombie · Post by **Xombie** » Mon Sep 10, 2007 6:53 pm

I need a little help. I'm using the code from the first page under the"For advanced using..." post as "SQLite3_Include.pbi" for a project. I then have the latest SQLite3 dll file.

I've compiled the program and put it out on a shared (networked) directory along with the dll. I can run the program just fine. However, other users crash the program on the first "SQLiteGetTable" call.

Can anyone think of a reason why it would work for me but not anyone else? I run the same program they are running - from the shared directory that is not on my computer.

Thanks!

Fangbeast · Post by **Fangbeast** » Tue Sep 11, 2007 3:55 am

File/network locking issues perhaps? Are they accessing the dll at the same times as you? Sqlite isn't multiuser/multiaccess so that could be an issue.

Only thing I can think of as I tried this last year on our network at home. There was a bloke called Weavster who solved this by making a free sqlite server (done in pb) to give the appearance of multiuser/multiaccess with the dll and that solved my problem at the time.

CSAUER · Post by **CSAUER** » Tue Sep 11, 2007 11:06 am

Yes, he did a Sqlite server and as far as I know he sold it to Realbasic, that's why it is no longer available for free. But I am not sure with these facts.

Thanks to solve my question. Here I have got another one:
How to explore all fields of a table. I know that it can be done with an ordernary "SELECT *", but what if the table is empty. The "SELECT *" returns no result. Any idea? Another "sqlite_..." table?

Xombie · Post by **Xombie** » Tue Sep 11, 2007 12:27 pm

I thought of that but, no. I'm 100% sure it isn't. I only open a connection to the sqlite database to write to it and then I close the connection. Also, when I had them try it nobody else was using it.

One other thing - this seemed to happen only when I use this library+dll. When I used... I think Flype's method I never had the problem.

Any other ideas?

Fangbeast · Post by **Fangbeast** » Tue Sep 11, 2007 1:48 pm

CSAUER wrote:Yes, he did a Sqlite server and as far as I know he sold it to Realbasic, that's why it is no longer available for free. But I am not sure with these facts.

Thanks to solve my question. Here I have got another one:
How to explore all fields of a table. I know that it can be done with an ordernary "SELECT *", but what if the table is empty. The "SELECT *" returns no result. Any idea? Another "sqlite_..." table?

CSAUER, he wasn't the one who sold it to realbasic and was still offerring it for free to anyone who wanted it. I got a license from him some time ago and the information is still in the forum about it.

As for your question, if you mean get the column names from a table, use:

"PRAGMA table_info(MyTableName)

Fangbeast · Post by **Fangbeast** » Tue Sep 11, 2007 1:55 pm

Xombie wrote:I thought of that but, no. I'm 100% sure it isn't. I only open a connection to the sqlite database to write to it and then I close the connection. Also, when I had them try it nobody else was using it.

One other thing - this seemed to happen only when I use this library+dll. When I used... I think Flype's method I never had the problem.

Any other ideas?

Just a silly idea but, what does the path reference to the file look like on yours and their computers. Are you all running the same O/S?

Is their path reference too long? (theirs). Can you debug the path and have a look?

Does it have backslashes or forward slashes in the path reference to the file or a mixture?

I've found that different O/S's handle network path references differently and that may crash the program.

Turn OnError lines support in the program and write a handler for it so we can see what the other computers are doing before they crash. If it's an SQLITE dll error, get the message from sqlite3_errmsg to see what that tells you.

That's all I can think of for now.

Xombie · Post by **Xombie** » Tue Sep 11, 2007 2:25 pm

No, it's the same path and everything - also a very short path. Also, SQLiteInit() executes without a problem and the call to SQLiteOpen(#PathDB) returns without an error. It's only when SQLiteGetTable() is called.

I've added a few messages in SQLiteGetTable to see if I can narrow this down a bit more and will post what I find out. However, if this error sounds familiar to someone, let me know.

Thanks!

Fangbeast · Post by **Fangbeast** » Tue Sep 11, 2007 3:15 pm

Xombie wrote:No, it's the same path and everything - also a very short path. Also, SQLiteInit() executes without a problem and the call to SQLiteOpen(#PathDB) returns without an error. It's only when SQLiteGetTable() is called.

I've added a few messages in SQLiteGetTable to see if I can narrow this down a bit more and will post what I find out. However, if this error sounds familiar to someone, let me know.

Thanks!

Sorry, that's all I can think of for now. Use SqLite every day for the last 4 years but haven't encountered it. Hope someone comes up with a solution for you.

Thorsten1867 · Post by **Thorsten1867** » Wed Sep 12, 2007 1:25 pm

Bug? Can anyone help me?

Hi Thorsten Hoeppner,
i discovered an Error with KvGS V 1.4.x :
0xC0000005
An attemped read or write to/from an address to which that process isn't allowed

Line:285
Modul:E:\Entwicklung\KvGS_V410\SQLite3_Include.pbi
Dll-Error:WrongParameter.

EIP=0x7C93AE22
EAX=0xFEE427E8
EBX=0x0
ECX=0xE8658DFF
EDX=0x330608
EBP=0x12FE78
EDI=0x330000
SegCS=0x1B
FFlags=0x10217

Code: Select all

; table-functions
Procedure.l SQliteFreeTable(*table.SQ3_TABLEMAP)
  If SQ3\free_table And *table
    SQ3\free_table(*table\Table) ; <<< line 285
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

pdwyer · Post by **pdwyer** » Wed Sep 12, 2007 1:59 pm

I'm missing something with this whole thread?

. It looks like many of you are not having problems with SQLite but with the PB wrapper. why not just use SQLite raw? It looks no harder to use than the wrapper functions.

Seems that if you have a PB wrapper around sqlite3_get_table() which is already a wrapper around sqlite3_exec() designed for some languages with lesser feaures (not PB) each with it's own memory cleanup to do that you're moving away from what makes PB so good, that it's small and fast.

Is there some benefit to be had from all this?

Please don't take this as trolling, I'm wanting to understand. If there's something good in it all I should start using it but the SQLite API is not complex. I have a little app I wrote in an evening for a japanese dictionary that works great. What am I missing?

it's only 200 lines of code with no includes at all

byo · Post by **byo** » Thu Sep 13, 2007 9:22 pm

Nice looking app, Paul.
Is it for download? I'm very interested in it as I study Japanese regularly.

pdwyer · Post by **pdwyer** » Fri Sep 14, 2007 10:40 am

Go for it. Let me know if there's any bugs

http://www.dwyer-family.net/download/kanji.zip

Some SQLite versions have issues with shift-jis text and try to read it as utf-8 and I haven't converted the dictionary to utf. Use the dll bundled in the zip (3.3.5). No setup, just unpack and run. The DB is based on EDICT (but has some conversions done) so feel free to use it for anything else, I'm going to build a flashcard proggie for it next. Needs asian fonts installed on XP.

(Sorry for the out of thread topic post)

mskuma · Post by **mskuma** » Tue Sep 25, 2007 1:31 pm

Thanks ts-soft for making this available. I have a question regarding its use for unicode. In the example you gave at the start, if you compile this for unicode, and run it from scratch - a new database is created of type UTF-16le which is probably to be expected (since open16 is used) and SQLite spec says 'if a new database file needs to be created, then sqlite3_open16() sets the internal text representation to UTF-16'. Thereafter in your example, you use SQliteExecute to create & populate the database. Since execute is using UTF8, I wonder whether this is ok? That is, shouldn't prepare16 be used to communicate the SQL to the (UTF-16) database as a UTF-16 string?

I'm asking about this because in the example I changed the source code to UTF-8 & set 'compile for unicode' option, and replacing some of the english chars to Japanese and found that in the example the Japanese strings displayed ok in the debug window, but when I viewed the same table in a 3rd party database admin tool, the data appeared to be corrupted. Maybe this is the fault of the tool, however I do wonder about whether it's ok to use execute in this test case. Alternatively, perhaps someone can convert the example to use prepare instead of execute, and I'll give it a go again.

I guess another way to confirm this situation is - has anyone used this code to store unicode text using UTF-8 PB source, and verified that the text data stored in the table is appearing as expected (no corruption) using a 3rd party db admin tool.

I'm new to sqlite, so I hope this all makes sense.. Thanks.