4.2 Beta SQLite Questions

[pdwyer]

I assume you are compiling in unicode mode (just checking)

If you open the database file in notepad (if it's not big) you can see the text. if notepad has opened the file as a unicode file then the text should be in there somewhere and visible (readable).

The reason I mention this is to determine if you have an issue putting the data in, or getting it out and displaying it.

split the problem up so to speak

[thanos]

Thanks for your response!

I assume you are compiling in unicode mode (just checking)

Yes. I already set the UTF-8 as the "Sourcefile Text Encoding" and i choose the "Create unicode executable" option in the compiler options in the Purebasic environment.

If you open the database file in notepad (if it's not big) you can see the text. if notepad has opened the file as a unicode file then the text should be in there somewhere and visible (readable).

When i open the database file in notepad i can see the data correctly as i entered them.
Do you have any idea?

[gekkonier]

Is the selected font for display capable to show the desired signs?

[thanos]

Thank you for the post.

Is the selected font for display capable to show the desired signs?

Yes. I tried with several fonts (including Courier New Greek and Arial Greek) but unfortunately the results was the same
Regards.

Thanos

[pdwyer]

When i open the database file in notepad i can see the data correctly as i entered them.

And if you hit save as on that notepad text under encoding it's already saying "Unicode" and not "Ansi" ?

If you get the text that comes back from the DB and display it in a messagerequester does it display okay? If it's bad there, then it would seem that the PB sqlite implimentation is net getting the text back from the DB correctly

If you look here http://www.sqlite.org/capi3ref.html#sqlite3_column_blob There are a lot of cases where SQLite can convert the text between formats depending on the order.

This text below might not help you much using PBs SQLite but then it is just beta so it's not going to surprise anyone if there are still bugs in it. You can use this info to go and get the date yourself though if you like.

(It may not be a bug of course )

If the result is a BLOB or UTF-8 string then the sqlite3_column_bytes() routine returns the number of bytes in that BLOB or string. If the result is a UTF-16 string, then sqlite3_column_bytes() converts the string to UTF-8 and then returns the number of bytes. If the result is a numeric value then sqlite3_column_bytes() uses sqlite3_snprintf() to convert that value to a UTF-8 string and returns the number of bytes in that string. The value returned does not include the zero terminator at the end of the string. For clarity: the value returned is the number of bytes in the string, not the number of characters.

Strings returned by sqlite3_column_text() and sqlite3_column_text16(), even empty strings, are always zero terminated. The return value from sqlite3_column_blob() for a zero-length blob is an arbitrary pointer, possibly even a NULL pointer.

The sqlite3_column_bytes16() routine is similar to sqlite3_column_bytes() but leaves the result in UTF-16 in native byte order instead of UTF-8. The zero terminator is not included in this count.

These routines attempt to convert the value where appropriate. For example, if the internal representation is FLOAT and a text result is requested, sqlite3_snprintf() is used internally to do the conversion automatically. The following table details the conversions that are applied:


Internal
Type Requested
Type Conversion


NULL INTEGER Result is 0
NULL FLOAT Result is 0.0
NULL TEXT Result is NULL pointer
NULL BLOB Result is NULL pointer
INTEGER FLOAT Convert from integer to float
INTEGER TEXT ASCII rendering of the integer
INTEGER BLOB Same as for INTEGER->TEXT
FLOAT INTEGER Convert from float to integer
FLOAT TEXT ASCII rendering of the float
FLOAT BLOB Same as FLOAT->TEXT
TEXT INTEGER Use atoi()
TEXT FLOAT Use atof()
TEXT BLOB No change
BLOB INTEGER Convert to TEXT then use atoi()
BLOB FLOAT Convert to TEXT then use atof()
BLOB TEXT Add a zero terminator if needed


The table above makes reference to standard C library functions atoi() and atof(). SQLite does not really use these functions. It has its on equavalent internal routines. The atoi() and atof() names are used in the table for brevity and because they are familiar to most C programmers.

Note that when type conversions occur, pointers returned by prior calls to sqlite3_column_blob(), sqlite3_column_text(), and/or sqlite3_column_text16() may be invalidated. Type conversions and pointer invalidations might occur in the following cases:


The initial content is a BLOB and sqlite3_column_text() or sqlite3_column_text16() is called. A zero-terminator might need to be added to the string.



The initial content is UTF-8 text and sqlite3_column_bytes16() or sqlite3_column_text16() is called. The content must be converted to UTF-16.



The initial content is UTF-16 text and sqlite3_column_bytes() or sqlite3_column_text() is called. The content must be converted to UTF-8.


Conversions between UTF-16be and UTF-16le are always done in place and do not invalidate a prior pointer, though of course the content of the buffer that the prior pointer points to will have been modified. Other kinds of conversion are done in place when it is possible, but sometime it is not possible and in those cases prior pointers are invalidated.

The safest and easiest to remember policy is to invoke these routines in one of the following ways:


sqlite3_column_text() followed by sqlite3_column_bytes()
sqlite3_column_blob() followed by sqlite3_column_bytes()
sqlite3_column_text16() followed by sqlite3_column_bytes16()

In other words, you should call sqlite3_column_text(), sqlite3_column_blob(), or sqlite3_column_text16() first to force the result into the desired format, then invoke sqlite3_column_bytes() or sqlite3_column_bytes16() to find the size of the result. Do not mix call to sqlite3_column_text() or sqlite3_column_blob() with calls to sqlite3_column_bytes16(). And do not mix calls to sqlite3_column_text16() with calls to sqlite3_column_bytes().

[thanos]

pdwyer, thank you for the post and the useful informations about SQlite!

And if you hit save as on that notepad text under encoding it's already saying "Unicode" and not "Ansi" ?

In notepad it is saying Ansi but i think this is not right, because as you can see (i took the following block right from the notepad) everything is correct:
3 00-00-03 ΠΑΠΑΙΩΑΝΝΟΥΚΥΡΙΑΚΟΣ

and i also checked the database's encoding using the sqlite-manager addin for Firefox (http://code.google.com/p/sqlite-manager/) and it was UTF-8.
I also download the trial version of SQLite Maesto (http://www.sqlmaestro.com/products/sqlite/maestro/) and i saw the data correctry with their encoding.

If you get the text that comes back from the DB and display it in a messagerequester does it display okay? If it's bad there, then it would seem that the PB sqlite implimentation is net getting the text back from the DB correctly

No. I already tried this option and the Messagerequester returns the same bad results
Beyond the above i just wrote a simple wrapper in Visual Basic to check the data using sample VB data controls. It retrieved the data correctly.
Any idea?
Regards.

Thanos

[pdwyer]

Progress maybe

If it says "ANSI" it may well be using the non unicode code page. If you know the values you can view the DB in a hex editor to be sure.

One other thing to check and I'm not sure why it's like this, there are two places to set the source as UTF8. Once in the compile area, once in the preferences-editor-defaults. and these can be different (I'm sure someone here can explain the difference) I think the one in preferences needs to be set (too?)

Something to check though

[thanos]

Thanks again.

If it says "ANSI" it may well be using the non unicode code page. If you know the values you can view the DB in a hex editor to be sure.

I dir view the database in a HEX editor and everything was okay.

One other thing to check and I'm not sure why it's like this, there are two places to set the source as UTF8. Once in the compile area, once in the preferences-editor-defaults. and these can be different (I'm sure someone here can explain the difference) I think the one in preferences needs to be set (too?)

I always set both the settings to UTF-8 because i'm Greek so i write non Ansi characters all the time.
Another idea?
Regards

Thanos

[Karbon]

What version of the SQLite lib is the latest beta supporting? Any luck on allowing PB to somehow dynamically link the SQLite lib so it can be updated independently of PB? That would really make this feature perfect!

[ts-soft]

> What version of the SQLite lib is the latest beta supporting?
3.5.4.1

[Karbon]

Thanks!

That really emphasizes the importance of being able to slide in a DLL or static library without waiting on the PB update. People that create and distribute SQLite databases created with the latest version might not be able to open those same databases using PB since the version supported is almost always going to be out of date. It doesn't happen all the time but with the rate that SQLite releases anything is possible.. SQLite is already at 3.5.7 with 3.5.4 only being released at the beginning of March.

[ts-soft]

Code: Select all

ImportC "sqlite3.lib"
  sqlite3_libversion()
  sqlite3_version.s{20}
EndImport

Debug PeekS(sqlite3_libversion(), #PB_Any, #PB_Ascii) 

Debug sqlite3_version 

greetings
Thomas

[Fred]

Thanks!

That really emphasizes the importance of being able to slide in a DLL or static library without waiting on the PB update. People that create and distribute SQLite databases created with the latest version might not be able to open those same databases using PB since the version supported is almost always going to be out of date. It doesn't happen all the time but with the rate that SQLite releases anything is possible.. SQLite is already at 3.5.7 with 3.5.4 only being released at the beginning of March.

PB final will ship with lastest SQLite version (beta lib use an old version because it was written almost a year ago !), and the binary API of sqlite is stable, which means than the 3.x branch will read any kind of database. This is mostly fixes/improvement which are coming with new version (which are no criticals). If a really major sqlite issue is found, we can issue a new PB lib easily.

[pdwyer]

What if we need to use an old lib because sqlite released a version that broke an app? We wouldn't necessarily want to keep using an old version of PB to support an old app due to an sqlite dependancy.

Will you just have different versions of your lib for download somewhere?

[Karbon]

Hi Fred. The version of SQLite that the latest beta is using now isn't a year old -- unless 3.5.4 isn't the version currently supported. 3.5.4 was just released in December.

I thought it would be something easy to do that would add a ton of flexibility to PB to allow the author to choose the SQLite version they needed. Guess not.