[sqlite] not support utf8.

[sonygod]

class Main {
	static function main() {
		var cnx = sys.db.Sqlite.open("./backup.data");
		trace('create table now');
		cnx.request("
				CREATE TABLE IF NOT EXISTS fa_customer (
				id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
				
				nickname text(255)
				
				
				
				)");

		trace('create table finish');

		var sql = 'INSERT INTO fa_customer (nickname) VALUES (${cnx.quote("豪鬼")})';
		trace(sql);
		cnx.request(sql);

		trace("sqlite init finish");
	}
}

build.hxml

-cp src
-D analyzer-optimize
-main Main
--each
--hl test.hl
--next
--cpp bin

cpp version

I:\test\testcpp\bin>Main.exe
src/Main.hx:5: create table now
src/Main.hx:16: create table finish
src/Main.hx:19: INSERT INTO fa_customer (nickname) VALUES ('璞')
Error : ValueException

hl verion

I:\test\testcpp>hl test.hl
src/Main.hx:5: create table now
src/Main.hx:16: create table finish
src/Main.hx:19: INSERT INTO fa_customer (nickname) VALUES ('豪鬼')
src/Main.hx:22: sqlite init finish

[jonasmalacofilho]

The offending line is:

hxcpp/src/hx/libs/sqlite/Sqlite.cpp

Line 239 in 652acae

if( sqlite3_prepare(db->db,sql.utf8_str(),sql.length,&statement,&tl) != SQLITE_OK )

sqlite3_prepare needs the number of bytes, not characters.

@hughsando I'm not familiar enough hxcpp strings... what's the best way to fix this?

---

Reduced example:

class Test {
	static function main() {
		var cnx = sys.db.Sqlite.open(":memory:");
		trace(cnx.request("SELECT '豪鬼' test").next());
		cnx.close();
	}
}

Breakpoint on sqlite3_prepare:

Breakpoint 1, sqlite3_prepare (db=0x5555557bc478, zSql=0x7ffff7958258 "SELECT '豪鬼' test", 
    nBytes=16, ppStmt=0x7fffffffdc38, pzTail=0x7fffffffdc40)
    at /home/jonas/Code/hxcpp/project/thirdparty/sqlite-3.23.1/sqlite3.c:120119

[sonygod]

@ConstNW, could you take look?

[ConstNW]

Probably it's the same problem as #880
There is sqlite3_prepare16 for utf-16 strings

[jonasmalacofilho]

The problem isn't the string encoding, but rather that we're sending the number of characters; all sqlite3_prepare variants expect a number of bytes in nBytes.

Also, there should be no major advantage in sending the statement in UTF-16, even if that's the encoding internally used by hxcpp:

The use of the UTF-8 interfaces is preferred, as SQLite currently does all parsing using UTF-8. The UTF-16 interfaces are provided as a convenience. The UTF-16 interfaces work by converting the input text into UTF-8, then invoking the corresponding UTF-8 interface.

[ConstNW]

if there is no actual bytes length and the string is NULL-terminated then it can be calc by sqlite

If the nByte argument is negative, then zSql is read up to the first zero terminator.

[jonasmalacofilho]

I had disregarded that alternative because while maybe rare, null characters can be present in Unicode strings, including in Hxcpp and SQLite.

SQLite does discourage their use in strings:

The result of expressions involving strings with embedded NULs is undefined.
—Binding Values To Prepared Statements

but that note is buried somewhat deep in the docs.

Also, I imagine storage/retrieval of strings with nulls works fine (even if it's undefined behavior), which means that there might be some code using this after all.

---

utf8_str internally calls TConvertToUTF8, which does seem to support reporting back the final number of bytes. It could be interesting to expose that in utf8_str too.

hxcpp/src/String.cpp

Line 291 in 652acae

char *TConvertToUTF8(const char16_t *inStr, int *ioLen, hx::IStringAlloc *inBuffer,bool throwInvalid)

[ConstNW]

at haxe code string values with NULL are encoded to hex

	public function quote(s:String) {
		if (s.indexOf("\000") >= 0) {
			var hexChars = new Array<String>();
			for (i in 0...s.length)
				hexChars.push(StringTools.hex(StringTools.fastCodeAt(s, i), 2));
			return "x'" + hexChars.join("") + "'";
		}
		return "'" + s.split("'").join("''") + "'";
	}

[jonasmalacofilho]

That's a good point!