PureBasic Forums - English

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

pdwyer wrote: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).

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

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

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().

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

pdwyer wrote: 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

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

pdwyer wrote: 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?)

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