[sqlite] Weird chars inserted
Hi people. I need some help I’m lost here. I’m writing an application using SQLite and whenever I insert Spanish characters I get this kind of strings: Mart�n (where it should read ‘Martín’) Ok, so if I open my database with SQLiteManager (the Firefox addin) my strings look weird like that. But SQLiteManager is able to insert and display any string correctly. And any string inserted by SQLiteManager displays all right in my app as well. So I must be doing something wrong when I insert the strings. This is my code: Note: szSQL is the wchar-string my app uses char szAux[2048]; ZeroMemory(szAux, 2048); WideCharToMultiByte(CP_ACP, WC_COMPOSITECHECK, szSQL, wcslen(szSQL), szAux, 2048, NULL, 0); int nRet= sqlite3_exec(m_hDB, szAux, NULL, 0, &pErrMsg); SQLiteManager tells me that my db is utf-16le. Im using vs2015 in a Windows10 64bit machine. I’d appreciate any help. Thanks in advance! Ariel M. Martin ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Weird chars inserted
Ariel, At 02:38 18/12/2016, you wrote: Hi people. I need some help Iâm lost here. Iâm writing an application using SQLite and whenever I insert Spanish characters I get this kind of strings: Mart�n (where it should read âMartÃnâ) Ok, so if I open my database with SQLiteManager (the Firefox addin) my strings look weird like that. But SQLiteManager is able to insert and display any string correctly. And any string inserted by SQLiteManager displays all right in my app as well. So I must be doing something wrong when I insert the strings. This is my code: Note: szSQL is the wchar-string my app uses char szAux[2048]; ZeroMemory(szAux, 2048); WideCharToMultiByte(CP_ACP, WC_COMPOSITECHECK, szSQL, wcslen(szSQL), szAux, 2048, NULL, 0); int nRet= sqlite3_exec(m_hDB, szAux, NULL, 0, &pErrMsg); SQLiteManager tells me that my db is utf-16le. Im using vs2015 in a Windows10 64bit machine. Since your DB is UTF-16LE encoded, you shouldn't convert your strings to UTF8. int nRet= sqlite3_exec(m_hDB, szSQL, NULL, 0, &pErrMsg); alone should work fine. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Weird chars inserted
On 12/17/2016 8:38 PM, Ariel M. Martin wrote: Note: szSQL is the wchar-string my app uses char szAux[2048]; ZeroMemory(szAux, 2048); WideCharToMultiByte(CP_ACP, WC_COMPOSITECHECK, szSQL, wcslen(szSQL), szAux, 2048, NULL, 0); Replace CP_ACP with CP_UTF8. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Weird chars inserted
On 12/18/2016 7:17 AM, Jean-Christophe Deschamps wrote: Since your DB is UTF-16LE encoded, you shouldn't convert your strings to UTF8. int nRet= sqlite3_exec(m_hDB, szSQL, NULL, 0, &pErrMsg); alone should work fine. No it should not. The encoding of the database is irrelevant here: sqlite3_exec still expects a UTF-8 encoded string. There are SQLite API functions that accept UTF-16 strings (e.g. sqlite3_bind_text16), but sqlite3_exec is not one of them. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unexpected 'no such column' with expression in subquery ORDER BY
I've read the documentation[1] a few times and I don't understand why I get: Error: no such column: z with a query like the one below: CREATE TABLE t1(x INTEGER, y INTEGER); INSERT INTO t1(x, y) VALUES(2, 1), (3, 2), (6, 4); CREATE TABLE t2(z INTEGER); INSERT INTO t2(z) VALUES(4); SELECT (SELECT y FROM t1 ORDER BY abs(x - z) LIMIT 1) FROM t2; I expect to get a result like: SELECT (SELECT y FROM t1 ORDER BY abs(x - 4) LIMIT 1) FROM t2; 2 The actual query I'm trying to write is more complicated and involves date functions; I hope this explains enough. I'm using 3.15.2: sqlite> .version SQLite 3.15.2 2016-11-28 19:13:37 bbd85d235f7037c6a033a9690534391ffeacecc8 I appreciate any help you can offer. Thanks in advance! Keith Maxwell [1] https://www.sqlite.org/lang_select.html#order-by ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected 'no such column' with expression in subquery ORDER BY
On 18 Dec 2016, at 1:57pm, Keith Maxwell wrote: >CREATE TABLE t1(x INTEGER, y INTEGER); >INSERT INTO t1(x, y) VALUES(2, 1), (3, 2), (6, 4); >CREATE TABLE t2(z INTEGER); >INSERT INTO t2(z) VALUES(4); >SELECT (SELECT y FROM t1 ORDER BY abs(x - z) LIMIT 1) FROM t2; I’m puzzled by your syntax. Your sub-select refers only to to table t1. That table has only columns x and y. It has no access to values for z. Now suppose, despite this, the sub-select returns some values to the outer SELECT. This leaves you with the outer select: SELECT (13, 3, 94) FROM t2 That doesn’t tell me what you’re trying to do. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected 'no such column' with expression in subquery ORDER BY
Your inner query only deals with what I've left in the quote below. Z doesn't exist to the inner query. On Sun, Dec 18, 2016 at 8:57 AM, Keith Maxwell wrote: > I've read the documentation[1] a few times and I don't understand why I > get: > CREATE TABLE t1(x INTEGER, y INTEGER); > SELECT (SELECT y FROM t1 ORDER BY abs(x - z) LIMIT 1) FROM t2; > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected 'no such column' with expression in subquery ORDER BY
Simon, Stephen thank you for trying to help. I'm afraid I don't follow Simon. z is a column in t2. The sub-select uses z in an expression in its order by clause: abs(x - z). What do you mean by "your sub-select only refers to table t1"? > SELECT (13, 3, 94) FROM t2 `LIMIT 1` on the sub-query means that it won't return more than one row; further z does exist to the inner query, as I've tried to show in the related example below: CREATE TABLE t1(x INTEGER, y INTEGER); INSERT INTO t1(x, y) VALUES(2, 1), (3, 2), (6, 4); CREATE TABLE t2(z INTEGER); INSERT INTO t2(z) VALUES(4); SELECT (SELECT z FROM t1 LIMIT 1) FROM t2; 4 4 To try and explain what the query was written to do in English: I have a list pairs of numbers x and y. I want to find the y corresponding to the x that is closest to each value of z. There are plenty of workarounds that I can use, but to me the query as written is valid, and I don't understand why I get "no such column". I can try and clean up a more real life example, but I think that makes it harder to follow by adding in date functions. Thanks for your help. Kind Regards Keith Maxwell On 18 December 2016 at 15:52, Stephen Chrzanowski wrote: > Your inner query only deals with what I've left in the quote below. Z > doesn't exist to the inner query. > > On Sun, Dec 18, 2016 at 8:57 AM, Keith Maxwell > wrote: > >> I've read the documentation[1] a few times and I don't understand why I >> get: >> CREATE TABLE t1(x INTEGER, y INTEGER); >> SELECT (SELECT y FROM t1 ORDER BY abs(x - z) LIMIT 1) FROM t2; >> > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected 'no such column' with expression in subquery ORDER BY
On 18 Dec 2016, at 6:13pm, Keith Maxwell wrote: > I'm afraid I don't follow Simon. z is a column in t2. The sub-select > uses z in an expression in its order by clause: abs(x - z). What do > you mean by "your sub-select only refers to table t1"? Then perhaps instead of > SELECT (SELECT y FROM t1 ORDER BY abs(x - z) LIMIT 1) FROM t2; You want to change which one is the sub-select: SELECT y FROM t1 ORDER BY abs(x - (SELECT z FROM t2)) LIMIT 1; Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected 'no such column' with expression in subquery ORDER BY
Simon Thanks again, maybe I didn't ask the question in the best way: why with the query below do I get "Error: no such column: z"? SELECT (SELECT y FROM t1 ORDER BY abs(x - z) LIMIT 1) FROM t2; There are workarounds or similar queries that produce the answer (an example is below). I posted because I want to understand the error message, or alternatively highlight the issue if the documentation could be clearer or there is a bug. SELECT (SELECT y FROM (SELECT abs(x - z), y FROM t1 ORDER BY 1 LIMIT 1)) FROM t2; Thank you but I don't immediately see how making the change you suggest helps. I appreciate that in my example, which I've probably over-simplified, there is only one value of z. In the more complicated real life example there are lots of values of z so I can't have `LIMIT 1` on the outer query. I appreciate your help! Kind Regards Keith Maxwell On 18 December 2016 at 18:20, Simon Slavin wrote: > > On 18 Dec 2016, at 6:13pm, Keith Maxwell wrote: > >> I'm afraid I don't follow Simon. z is a column in t2. The sub-select >> uses z in an expression in its order by clause: abs(x - z). What do >> you mean by "your sub-select only refers to table t1"? > > Then perhaps instead of > >> SELECT (SELECT y FROM t1 ORDER BY abs(x - z) LIMIT 1) FROM t2; > > You want to change which one is the sub-select: > > SELECT y FROM t1 ORDER BY abs(x - (SELECT z FROM t2)) LIMIT 1; > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected 'no such column' with expression in subquery ORDER BY
As stated, the Z does not exist to the inner query. The [ SELECT y FROM t1 ORDER BY abs(x - z) LIMIT 1 ] has no reference to what Z means. T2 may have it, but the inner query has no regards for the outer query. On Sun, Dec 18, 2016 at 1:41 PM, Keith Maxwell wrote: > Simon > > Thanks again, maybe I didn't ask the question in the best way: why > with the query below do I get "Error: no such column: z"? > > SELECT (SELECT y FROM t1 ORDER BY abs(x - z) LIMIT 1) FROM t2; > > There are workarounds or similar queries that produce the answer (an > example is below). I posted because I want to understand the error > message, or alternatively highlight the issue if the documentation > could be clearer or there is a bug. > > SELECT (SELECT y FROM (SELECT abs(x - z), y FROM t1 ORDER BY 1 LIMIT > 1)) FROM t2; > > Thank you but I don't immediately see how making the change you > suggest helps. I appreciate that in my example, which I've probably > over-simplified, there is only one value of z. In the more > complicated real life example there are lots of values of z so I can't > have `LIMIT 1` on the outer query. > > I appreciate your help! > > Kind Regards > > Keith Maxwell > > On 18 December 2016 at 18:20, Simon Slavin wrote: > > > > On 18 Dec 2016, at 6:13pm, Keith Maxwell > wrote: > > > >> I'm afraid I don't follow Simon. z is a column in t2. The sub-select > >> uses z in an expression in its order by clause: abs(x - z). What do > >> you mean by "your sub-select only refers to table t1"? > > > > Then perhaps instead of > > > >> SELECT (SELECT y FROM t1 ORDER BY abs(x - z) LIMIT 1) FROM t2; > > > > You want to change which one is the sub-select: > > > > SELECT y FROM t1 ORDER BY abs(x - (SELECT z FROM t2)) LIMIT 1; > > > > Simon. > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected 'no such column' with expression in subquery ORDER BY
On 18 Dec 2016, at 6:41pm, Keith Maxwell wrote: > Thanks again, maybe I didn't ask the question in the best way: why > with the query below do I get "Error: no such column: z"? > > SELECT (SELECT y FROM t1 ORDER BY abs(x - z) LIMIT 1) FROM t2; Because the inner SELECT does not have access to the details from the outer SELECT. It does not know what 'z' is. None of the tables in the inner SELECT have a column called 'z'. This works: SELECT y FROM t1 ORDER BY abs(x - (SELECT z FROM t2)) LIMIT 1; though I do not know if it does what you want it to do. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected 'no such column' with expression in subquery ORDER BY
Simon, Stephen Thank again. Did you try the other queries I've included? I've tried to include two that show sub-queries using z from the main query. The simplest one (not a useful query, just illustrates a sub-query using z): sqlite> SELECT (SELECT z FROM t1 LIMIT 1) FROM t2; 4 And the workaround that produces the answer: sqlite> SELECT (SELECT y FROM (SELECT abs(x - z), y FROM t1 ORDER BY 1 LIMIT 1)) FROM t2; 2 I can't reconcile what you're saying about z in the sub-query with what I see from the results above. A subtle point that might be getting lost is that I can use z elsewhere in the sub-query, just not in its `ORDER BY` clause. I appreciate your continued help. Kind Regards Keith On 18 December 2016 at 18:51, Simon Slavin wrote: > > On 18 Dec 2016, at 6:41pm, Keith Maxwell wrote: > >> Thanks again, maybe I didn't ask the question in the best way: why >> with the query below do I get "Error: no such column: z"? >> >> SELECT (SELECT y FROM t1 ORDER BY abs(x - z) LIMIT 1) FROM t2; > > Because the inner SELECT does not have access to the details from the outer > SELECT. It does not know what 'z' is. None of the tables in the inner > SELECT have a column called 'z'. > > This works: > > SELECT y FROM t1 ORDER BY abs(x - (SELECT z FROM t2)) LIMIT 1; > > though I do not know if it does what you want it to do. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected 'no such column' with expression in subquery ORDER BY
Correlated (outer) columns are not permitted in the ORDER BY clause -- only items from the SELECT list (or from the directly selected tables). Normally in a correlated subquery the outer columns are only (designed to be) used in the WHERE clause of the correlated subquery, although they may be accessed in the SELECT list. In other words, the SELECT ... FROM ... ORDER BY will work because you are sorting the result-set by data contained within the result set of the subquery. I have no idea what the standard has to say about the usage of an outer column in a correlated subquery order by clause however. > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Keith Maxwell > Sent: Sunday, 18 December, 2016 12:14 > To: SQLite mailing list > Subject: Re: [sqlite] Unexpected 'no such column' with expression in > subquery ORDER BY > > Simon, Stephen > > Thank again. Did you try the other queries I've included? I've tried > to include two that show sub-queries using z from the main query. > > The simplest one (not a useful query, just illustrates a sub-query using > z): > > sqlite> SELECT (SELECT z FROM t1 LIMIT 1) FROM t2; > 4 > > And the workaround that produces the answer: > > sqlite> SELECT (SELECT y FROM (SELECT abs(x - z), y FROM t1 ORDER BY 1 > LIMIT 1)) FROM t2; > 2 > > I can't reconcile what you're saying about z in the sub-query with > what I see from the results above. > > A subtle point that might be getting lost is that I can use z > elsewhere in the sub-query, just not in its `ORDER BY` clause. > > I appreciate your continued help. > > Kind Regards > > Keith > > On 18 December 2016 at 18:51, Simon Slavin wrote: > > > > On 18 Dec 2016, at 6:41pm, Keith Maxwell > wrote: > > > >> Thanks again, maybe I didn't ask the question in the best way: why > >> with the query below do I get "Error: no such column: z"? > >> > >> SELECT (SELECT y FROM t1 ORDER BY abs(x - z) LIMIT 1) FROM t2; > > > > Because the inner SELECT does not have access to the details from the > outer SELECT. It does not know what 'z' is. None of the tables in the > inner SELECT have a column called 'z'. > > > > This works: > > > > SELECT y FROM t1 ORDER BY abs(x - (SELECT z FROM t2)) LIMIT 1; > > > > though I do not know if it does what you want it to do. > > > > Simon. > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected 'no such column' with expression in subquery ORDER BY
Keith Brilliant! Thank you. > Correlated (outer) columns are not permitted in the ORDER BY clause At least I've learnt a new term [1]. Reading the documentation again [2], I think a change to the paragraph below would make this clearer: 3. Otherwise, if the ORDER BY expression is any other expression, it is evaluated and the returned value used to order the output rows. If the SELECT statement is a simple SELECT, then an ORDER BY may contain any arbitrary expressions. However, if the SELECT is a compound SELECT, then ORDER BY expressions that are not aliases to output columns must be exactly the same as an expression used as an output column. Maybe add a sentence: "If the SELECT is a correlated inner subquery then the ORDER BY expression may not include columns from the outer query." I'm also not sure if it would help to drop the word "any" from "any arbitrary expressions"? I'm afraid I didn't get very far trying to look up a standard. I appreciate your help! Thanks again. Kind Regards Keith Maxwell [1] https://en.wikipedia.org/wiki/Correlated_subquery [2] https://www.sqlite.org/lang_select.html#order-by ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unregister UDF, created with sqlite3_create_function_v2
Using the latest SQLite from VB6 and trying to unregister a UDF, created with sqlite3_create_function_v2. This registration works fine and the UDF's work fine. Now I am trying to unregister this UDF and the only hint how to do this that I could find was in the documentation of sqlite3_create_function_v2: > To delete an existing SQL function or aggregate, pass NULL pointers for all three function callbacks. I have tried this in VB6 like this: lRetVal = sqlite3_create_function_v2(ByVal lDBHdl, _ ByVal cConn.UTF8BytesPointerFromUTF16String(strFunctionName), _ ByVal 0, _ ByVal 0, _ ByVal 0, _ ByVal 0, _ ByVal 0, _ ByVal 0, _ ByVal 0) This seems to work in that lRetVal is zero, but I can still use the UDF in SQL. I know I could close the connection and re-register the remaining UDF's but that has drawbacks and seems unnecessary. Any suggestions how to do this? RBS ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Weird chars inserted
Igor, On 12/18/2016 7:17 AM, Jean-Christophe Deschamps wrote: Since your DB is UTF-16LE encoded, you shouldn't convert your strings to UTF8. int nRet= sqlite3_exec(m_hDB, szSQL, NULL, 0, &pErrMsg); alone should work fine. No it should not. The encoding of the database is irrelevant here: sqlite3_exec still expects a UTF-8 encoded string. Oops, of course that's it. I'm so used to have this kind of mistake reported about _SQLite3_Exec() using the wrapper that I use and support that I didn't realize the OP was using the bare SQLite API. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Weird chars inserted
Hi Martin, I had a go using a terminal session, with default encoding UTF-8. Try using the hex( ) and unicode( ) functions to check what is actually stored in the sqlite table. I put a couple of rows at the end of an existing simple table kevin@kevin-Aspire-V5-571G:~$ sqlite3 /home/kevin/dir_md5sum_db.sqlite SQLite version 3.15.2 2016-11-28 19:13:37 Enter ".help" for usage hints. sqlite> SELECT dir_name, hex(dir_name), dir_md5sum, hex(dir_md5sum), unicode(dir_md5sum) FROM dir_md5sum ...> where rowid >= 194576; 194576|kev|6B6576|í|C3AD|237 194577|kev2|6B657632|�|ED|65533 sqlite> .quit kevin@kevin-Aspire-V5-571G:~$ I tried changing the terminal session to UTF-16, but I ended up with a mess of Chinese characters. the � is a place marker inserted by the program that displays on your screen for a character the program doesn't recognize. Check http://www.fileformat.info/info/unicode/char/00ed/index.htm as a reference. I use Linux (Xubuntu) regs, Kev From: "Ariel M. Martin" To: " sqlite-users@mailinglists.sqlite.org" Subject: [sqlite] Weird chars inserted Message-ID: <835952.64146...@smtp113.mail.ne1.yahoo.com> Content-Type: text/plain; charset="utf-8" Hi people. I need some help I’m lost here. I’m writing an application using SQLite and whenever I insert Spanish characters I get this kind of strings: Mart�n (where it should read ‘Martín’) Ok, so if I open my database with SQLiteManager (the Firefox addin) my strings look weird like that. But SQLiteManager is able to insert and display any string correctly. And any string inserted by SQLiteManager displays all right in my app as well. So I must be doing something wrong when I insert the strings. This is my code: Note: szSQL is the wchar-string my app uses char szAux[2048]; ZeroMemory(szAux, 2048); WideCharToMultiByte(CP_ACP, WC_COMPOSITECHECK, szSQL, wcslen(szSQL), szAux, 2048, NULL, 0); int nRet= sqlite3_exec(m_hDB, szAux, NULL, 0, &pErrMsg); SQLiteManager tells me that my db is utf-16le. Im using vs2015 in a Windows10 64bit machine. I’d appreciate any help. Thanks in advance! Ariel M. Martin ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Weird chars inserted
Inquiring minds want to know how a selection of 5 values resulted in a result list of 6 values ... > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Kevin > Sent: Sunday, 18 December, 2016 17:25 > To: Ariel M. Martin > Cc: sqlite mailing list > Subject: [sqlite] Weird chars inserted > > Hi Martin, > > I had a go using a terminal session, with default encoding UTF-8. > > Try using the hex( ) and unicode( ) functions to check what is actually > stored in the sqlite table. > > I put a couple of rows at the end of an existing simple table > > kevin@kevin-Aspire-V5-571G:~$ sqlite3 /home/kevin/dir_md5sum_db.sqlite > SQLite version 3.15.2 2016-11-28 19:13:37 > Enter ".help" for usage hints. > sqlite> SELECT dir_name, hex(dir_name), dir_md5sum, hex(dir_md5sum), > unicode(dir_md5sum) FROM dir_md5sum > ...> where rowid >= 194576; > 194576|kev|6B6576|í|C3AD|237 > 194577|kev2|6B657632|�|ED|65533 > sqlite> .quit > kevin@kevin-Aspire-V5-571G:~$ > > > > I tried changing the terminal session to UTF-16, but I ended up with a > mess of Chinese characters. > > > > > > > the � is a place marker inserted by the program that displays on your > screen for a character the program doesn't recognize. > > Check http://www.fileformat.info/info/unicode/char/00ed/index.htm > > as a reference. > > I use Linux (Xubuntu) > > > regs, > > Kev > > > From: "Ariel M. Martin" > To: " sqlite-users@mailinglists.sqlite.org" > > Subject: [sqlite] Weird chars inserted > Message-ID: <835952.64146...@smtp113.mail.ne1.yahoo.com> > Content-Type: text/plain; charset="utf-8" > > Hi people. I need some help I’m lost here. > I’m writing an application using SQLite and whenever I insert Spanish > characters > I get this kind of strings: > Mart�n (where it should read ‘Martín’) > > Ok, so if I open my database with SQLiteManager (the Firefox addin) my > strings look > weird like that. But SQLiteManager is able to insert and display any > string correctly. > And any string inserted by SQLiteManager displays all right in my app > as well. > > So I must be doing something wrong when I insert the strings. This is > my code: > > Note: szSQL is the wchar-string my app uses > > char szAux[2048]; > ZeroMemory(szAux, 2048); > WideCharToMultiByte(CP_ACP, WC_COMPOSITECHECK, szSQL, > wcslen(szSQL), szAux, 2048, NULL, 0); > > int nRet= sqlite3_exec(m_hDB, szAux, NULL, 0, &pErrMsg); > > > SQLiteManager tells me that my db is utf-16le. Im using vs2015 in a > Windows10 64bit machine. > > I’d appreciate any help. > Thanks in advance! > > > Ariel M. Martin > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Weird chars inserted
On 19 December 2016 at 08:24, Kevin wrote: > Hi Martin, > > I had a go using a terminal session, with default encoding UTF-8. > > Try using the hex( ) and unicode( ) functions to check what is actually > stored in the sqlite table. > > I put a couple of rows at the end of an existing simple table > > kevin@kevin-Aspire-V5-571G:~$ sqlite3 /home/kevin/dir_md5sum_db.sqlite > SQLite version 3.15.2 2016-11-28 19:13:37 > Enter ".help" for usage hints. > sqlite> SELECT dir_name, hex(dir_name), dir_md5sum, hex(dir_md5sum), > unicode(dir_md5sum) FROM dir_md5sum >...> where rowid >= 194576; > 194576|kev|6B6576|í|C3AD|237 > 194577|kev2|6B657632|�|ED|65533 > sqlite> .quit > kevin@kevin-Aspire-V5-571G:~$ > Hi Kevin, The problem here lies in whatever inserted these rows. sqlite just stores what it is given - it is up to the application to take care of encoding issues. In this case the "kev" row has been inserted using utf-8 encoding, so when you retrieve this value sqlite emits the bytes 0xC3 0xAD (exactly as they were stored), which your terminal interprets as utf-8 and renders the character í. The "kev2" row however is not utf-8 encoded. The dir_md5sum column contains a single byte 0xED, which is not valid utf-8 (the encoding specifies that when the highest bit is on, there is more information about the current character in the next byte). When you retrieve this value sqlite emits the byte 0xED (exactly as it was stored). Your terminal tries to interpret this as utf-8, but since it is not valid it instead inserts a unicode replacement character (U+FFFD). Sqlite3's unicode() function makes the same U+FFFD replacement when encountering an invalid encoding which is where the 65533 comes from. -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unregister UDF, created with sqlite3_create_function_v2
On 12/19/2016 03:32 AM, Bart Smissaert wrote: Using the latest SQLite from VB6 and trying to unregister a UDF, created with sqlite3_create_function_v2. This registration works fine and the UDF's work fine. Now I am trying to unregister this UDF and the only hint how to do this that I could find was in the documentation of sqlite3_create_function_v2: To delete an existing SQL function or aggregate, pass NULL pointers for all three function callbacks. I have tried this in VB6 like this: lRetVal = sqlite3_create_function_v2(ByVal lDBHdl, _ ByVal cConn.UTF8BytesPointerFromUTF16String(strFunctionName), _ ByVal 0, _ ByVal 0, _ ByVal 0, _ ByVal 0, _ ByVal 0, _ ByVal 0, _ ByVal 0) This seems to work in that lRetVal is zero, but I can still use the UDF in SQL. I know I could close the connection and re-register the remaining UDF's but that has drawbacks and seems unnecessary. Any suggestions how to do this? Were the encoding and number-of-arguments (the third and fourth) arguments passed to the two invocations of sqlite3_create_function_v2() the same? Dan. RBS ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unregister UDF, created with sqlite3_create_function_v2
Thanks, that was it. I had done the encoding the same (but also tried 0), but the arguments were always set at 0. Now with the arguments the same as at registration it works fine. Maybe this should be added to the documentation of create_function. RBS On Mon, Dec 19, 2016 at 7:03 AM, Dan Kennedy wrote: > On 12/19/2016 03:32 AM, Bart Smissaert wrote: > >> Using the latest SQLite from VB6 and trying to unregister a UDF, created >> with sqlite3_create_function_v2. >> This registration works fine and the UDF's work fine. >> Now I am trying to unregister this UDF and the only hint how to do this >> that I could find >> was in the documentation of sqlite3_create_function_v2: >> >> To delete an existing SQL function or aggregate, pass NULL pointers for >>> >> all three function callbacks. >> >> I have tried this in VB6 like this: >> >> lRetVal = sqlite3_create_function_v2(ByVal lDBHdl, _ >> ByVal >> cConn.UTF8BytesPointerFromUTF16String(strFunctionName), _ >> ByVal 0, _ >> ByVal 0, _ >> ByVal 0, _ >> ByVal 0, _ >> ByVal 0, _ >> ByVal 0, _ >> ByVal 0) >> >> This seems to work in that lRetVal is zero, but I can still use the UDF in >> SQL. >> I know I could close the connection and re-register the remaining UDF's >> but >> that has drawbacks >> and seems unnecessary. >> >> Any suggestions how to do this? >> > > > Were the encoding and number-of-arguments (the third and fourth) arguments > passed to the two invocations of sqlite3_create_function_v2() the same? > > Dan. > > > > >> >> RBS >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
