Re: [sqlite] Set Locale for upper() and lower() using a pragma variable
> I have created my own function which uses a global variable for the default > locale. (see attached) I guess I received your attachment because you sent this email to me directly but generally this list doesn't allow attachments - you should include your code into email. And about your code: I can't check it all I've never written user functions for SQLite. But your setLocale function is incorrect because your copy the characters from zLocale and don't copy zero-termination of the string. >> At application startup you fill this variable with value >> from table db_locale or with some default value if there's nothing in >> db_locale. Then each time you change your application's variable you >> save new value in table db_locale. That's it. > > I am struggling with this one, could someone help me out with sample code. > I figure that the code would go to openDatabase(..), but that is about all > I know at the moment. No, you won't use openDatabase and won't use any internal functions of SQLite. You will use sqlite3_open, sqlite3_prepare_v2, sqlite3_step and sqlite3_finalize just like with any regular access to SQLite database. Pavel On Fri, Mar 2, 2012 at 5:51 PM, Grace Simon Batumbya wrote: > > That's a wrong approach. First, you don't need to modify functions in > SQLite code, you need to create your own. Your function will convert > using locale saved in some variable in your application. > > I have created my own function which uses a global variable for the default > locale. (see attached) > > At application startup you fill this variable with value > from table db_locale or with some default value if there's nothing in > db_locale. Then each time you change your application's variable you > save new value in table db_locale. That's it. > > I am struggling with this one, could someone help me out with sample code. > I figure that the code would go to openDatabase(..), but that is about all > I know at the moment. > > Thanks. > > Regards, > > Grace Batumbya > Research Assistant | Seneca CDOT > Phone: 416-491-5050 x3548 > cdot.senecac.on.ca > > On 3/1/2012 09:48, Pavel Ivanov wrote: > > Given that there exists a table db_locale [CREATE TABLE db_locale (locale > text)], > what lines of code would be used to query that table from inside this > function? > > That's a wrong approach. First, you don't need to modify functions in > SQLite code, you need to create your own. Your function will convert > using locale saved in some variable in your application. At > application startup you fill this variable with value from table > db_locale or with some default value if there's nothing in db_locale. > Then each time you change your application's variable you save new > value in table db_locale. That's it. > > > Pavel > > > On Thu, Mar 1, 2012 at 9:33 AM, Grace Simon Batumbya > wrote: > > I found the function that I would need to modify (see below). > > static void icuCaseFunc16(sqlite3_context *p, int nArg, sqlite3_value > **apArg){ > const UChar *zInput; > UChar *zOutput; > int nInput; > int nOutput; > > UErrorCode status = U_ZERO_ERROR; > const char *zLocale = 0; > > assert(nArg==1 || nArg==2); > if( nArg==2 ){ > zLocale = (const char *)sqlite3_value_text(apArg[1]); > } > > zInput = sqlite3_value_text16(apArg[0]); > if( !zInput ){ > return; > } > nInput = sqlite3_value_bytes16(apArg[0]); > > nOutput = nInput * 2 + 2; > zOutput = sqlite3_malloc(nOutput); > if( !zOutput ){ > return; > } > > if( sqlite3_user_data(p) ){ > u_strToUpper(zOutput, nOutput/2, zInput, nInput/2, zLocale, &status); > }else{ > u_strToLower(zOutput, nOutput/2, zInput, nInput/2, zLocale, &status); > } > > if( !U_SUCCESS(status) ){ > icuFunctionError(p, "u_strToLower()/u_strToUpper", status); > return; > } > > sqlite3_result_text16(p, zOutput, -1, xFree); > } > > Given that there exists a table db_locale [CREATE TABLE db_locale (locale > text)], > what lines of code would be used to query that table from inside this > function? > > Grace Batumbya > Research Assistant | Seneca CDOT > Phone: 416-491-5050 x3548 > cdot.senecac.on.ca > > On 3/1/2012 08:56, Grace Batumbya wrote: > > Is there an example extension you know that I could look at that does this? > (i am a novice at SQLite) > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Pavel Ivanov [paiva...@gmail.com] > Sent: March
Re: [sqlite] Set Locale for upper() and lower() using a pragma variable
That's a wrong approach. First, you don't need to modify functions in SQLite code, you need to create your own. Your function will convert using locale saved in some variable in your application. I have created my own function which uses a global variable for the default locale. (see attached) At application startup you fill this variable with value from table db_locale or with some default value if there's nothing in db_locale. Then each time you change your application's variable you save new value in table db_locale. That's it. I am struggling with this one, could someone help me out with sample code. I figure that the code would go to openDatabase(..), but that is about all I know at the moment. Thanks. Regards, *Grace Batumbya* Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 cdot.senecac.on.ca <http://cdot.senecac.on.ca/> On 3/1/2012 09:48, Pavel Ivanov wrote: Given that there exists a table db_locale [CREATE TABLE db_locale (locale text)], what lines of code would be used to query that table from inside this function? That's a wrong approach. First, you don't need to modify functions in SQLite code, you need to create your own. Your function will convert using locale saved in some variable in your application. At application startup you fill this variable with value from table db_locale or with some default value if there's nothing in db_locale. Then each time you change your application's variable you save new value in table db_locale. That's it. Pavel On Thu, Mar 1, 2012 at 9:33 AM, Grace Simon Batumbya wrote: I found the function that I would need to modify (see below). static void icuCaseFunc16(sqlite3_context *p, int nArg, sqlite3_value **apArg){ const UChar *zInput; UChar *zOutput; int nInput; int nOutput; UErrorCode status = U_ZERO_ERROR; const char *zLocale = 0; assert(nArg==1 || nArg==2); if( nArg==2 ){ zLocale = (const char *)sqlite3_value_text(apArg[1]); } zInput = sqlite3_value_text16(apArg[0]); if( !zInput ){ return; } nInput = sqlite3_value_bytes16(apArg[0]); nOutput = nInput * 2 + 2; zOutput = sqlite3_malloc(nOutput); if( !zOutput ){ return; } if( sqlite3_user_data(p) ){ u_strToUpper(zOutput, nOutput/2, zInput, nInput/2, zLocale,&status); }else{ u_strToLower(zOutput, nOutput/2, zInput, nInput/2, zLocale,&status); } if( !U_SUCCESS(status) ){ icuFunctionError(p, "u_strToLower()/u_strToUpper", status); return; } sqlite3_result_text16(p, zOutput, -1, xFree); } Given that there exists a table db_locale [CREATE TABLE db_locale (locale text)], what lines of code would be used to query that table from inside this function? Grace Batumbya Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 cdot.senecac.on.ca On 3/1/2012 08:56, Grace Batumbya wrote: Is there an example extension you know that I could look at that does this? (i am a novice at SQLite) From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Pavel Ivanov [paiva...@gmail.com] Sent: March 1, 2012 8:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Set Locale for upper() and lower() using a pragma variable On Thu, Mar 1, 2012 at 8:50 AM, Grace Batumbya wrote: You can simply register your own lower/upper with one argument that looks wherever you want to know what locale to use. The part of registering a function to override lower/upper I think I understand. But if I wanted to persist the locale, so that even if I disconnect and reconnect it is still set to the last time I set it, how do I go about accomplishing this. Create a special table for that and store your last locale value in it. Then after reconnect read the locale value from this table. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users // DB's default locale char *DBLocale = 0; int nLength = 0; void setLocale(char *zLocale) { sqlite3_free(DBLocale); nLength = strlen(zLocale); DBLocale = (char *) sqlite3_malloc(nLength); strcpy(DBLocale, zLocale); } void deleteDBLocale(void * zLocale) { // do thing, since zLocale points to DBLocale. } // lower and upper using the DB locale static void lowerUpperDefaultLocale(sqlite3_context *p, int nArg, sqlite3_value **apArg) { sqlite3_value *args[2]; if (!DBLocale) { // hand off to inbuilt functions icuCaseFunc16(p, nArg, apArg); return; } args[0] = apArg[0]; args[1] = (sqlite3_value *)malloc(sizeof(sqlite3_value)); args[1]->db= apArg[0
Re: [sqlite] Set Locale for upper() and lower() using a pragma variable
> Given that there exists a table db_locale [CREATE TABLE db_locale (locale > text)], > what lines of code would be used to query that table from inside this > function? That's a wrong approach. First, you don't need to modify functions in SQLite code, you need to create your own. Your function will convert using locale saved in some variable in your application. At application startup you fill this variable with value from table db_locale or with some default value if there's nothing in db_locale. Then each time you change your application's variable you save new value in table db_locale. That's it. Pavel On Thu, Mar 1, 2012 at 9:33 AM, Grace Simon Batumbya wrote: > I found the function that I would need to modify (see below). > > static void icuCaseFunc16(sqlite3_context *p, int nArg, sqlite3_value > **apArg){ > const UChar *zInput; > UChar *zOutput; > int nInput; > int nOutput; > > UErrorCode status = U_ZERO_ERROR; > const char *zLocale = 0; > > assert(nArg==1 || nArg==2); > if( nArg==2 ){ > zLocale = (const char *)sqlite3_value_text(apArg[1]); > } > > zInput = sqlite3_value_text16(apArg[0]); > if( !zInput ){ > return; > } > nInput = sqlite3_value_bytes16(apArg[0]); > > nOutput = nInput * 2 + 2; > zOutput = sqlite3_malloc(nOutput); > if( !zOutput ){ > return; > } > > if( sqlite3_user_data(p) ){ > u_strToUpper(zOutput, nOutput/2, zInput, nInput/2, zLocale, &status); > }else{ > u_strToLower(zOutput, nOutput/2, zInput, nInput/2, zLocale, &status); > } > > if( !U_SUCCESS(status) ){ > icuFunctionError(p, "u_strToLower()/u_strToUpper", status); > return; > } > > sqlite3_result_text16(p, zOutput, -1, xFree); > } > > Given that there exists a table db_locale [CREATE TABLE db_locale (locale > text)], > what lines of code would be used to query that table from inside this > function? > > Grace Batumbya > Research Assistant | Seneca CDOT > Phone: 416-491-5050 x3548 > cdot.senecac.on.ca > > On 3/1/2012 08:56, Grace Batumbya wrote: > > Is there an example extension you know that I could look at that does this? > (i am a novice at SQLite) > ________________ > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Pavel Ivanov [paiva...@gmail.com] > Sent: March 1, 2012 8:52 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Set Locale for upper() and lower() using a pragma > variable > > On Thu, Mar 1, 2012 at 8:50 AM, Grace Batumbya > wrote: > > You can simply register your > own lower/upper with one argument that looks wherever you want to know > what locale to use. > > The part of registering a function to override lower/upper I think I > understand. > > But if I wanted to persist the locale, so that even if I disconnect and > reconnect it is still set to the last time I set it, how do I go about > accomplishing this. > > Create a special table for that and store your last locale value in > it. Then after reconnect read the locale value from this table. > > > Pavel > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Set Locale for upper() and lower() using a pragma variable
I found the function that I would need to modify (see below). |static void icuCaseFunc16(sqlite3_context *p, int nArg, sqlite3_value **apArg){ const UChar *zInput; UChar *zOutput; int nInput; int nOutput; UErrorCode status = U_ZERO_ERROR; const char *zLocale = 0; assert(nArg==1 || nArg==2); if( nArg==2 ){ zLocale = (const char *)sqlite3_value_text(apArg[1]); } zInput = sqlite3_value_text16(apArg[0]); if( !zInput ){ return; } nInput = sqlite3_value_bytes16(apArg[0]); nOutput = nInput * 2 + 2; zOutput = sqlite3_malloc(nOutput); if( !zOutput ){ return; } if( sqlite3_user_data(p) ){ u_strToUpper(zOutput, nOutput/2, zInput, nInput/2, zLocale, &status); }else{ u_strToLower(zOutput, nOutput/2, zInput, nInput/2, zLocale, &status); } if( !U_SUCCESS(status) ){ icuFunctionError(p, "u_strToLower()/u_strToUpper", status); return; } sqlite3_result_text16(p, zOutput, -1, xFree); } | Given that there exists a table db_locale [CREATE TABLE db_locale (locale text)], what lines of code would be used to query that table from inside this function? *Grace Batumbya* Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 cdot.senecac.on.ca <http://cdot.senecac.on.ca/> On 3/1/2012 08:56, Grace Batumbya wrote: Is there an example extension you know that I could look at that does this? (i am a novice at SQLite) From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Pavel Ivanov [paiva...@gmail.com] Sent: March 1, 2012 8:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Set Locale for upper() and lower() using a pragma variable On Thu, Mar 1, 2012 at 8:50 AM, Grace Batumbya wrote: You can simply register your own lower/upper with one argument that looks wherever you want to know what locale to use. The part of registering a function to override lower/upper I think I understand. But if I wanted to persist the locale, so that even if I disconnect and reconnect it is still set to the last time I set it, how do I go about accomplishing this. Create a special table for that and store your last locale value in it. Then after reconnect read the locale value from this table. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Set Locale for upper() and lower() using a pragma variable
Is there an example extension you know that I could look at that does this? (i am a novice at SQLite) From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Pavel Ivanov [paiva...@gmail.com] Sent: March 1, 2012 8:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Set Locale for upper() and lower() using a pragma variable On Thu, Mar 1, 2012 at 8:50 AM, Grace Batumbya wrote: >>You can simply register your >>own lower/upper with one argument that looks wherever you want to know >>what locale to use. > > The part of registering a function to override lower/upper I think I > understand. > > But if I wanted to persist the locale, so that even if I disconnect and > reconnect it is still set to the last time I set it, how do I go about > accomplishing this. Create a special table for that and store your last locale value in it. Then after reconnect read the locale value from this table. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Set Locale for upper() and lower() using a pragma variable
On Thu, Mar 1, 2012 at 8:50 AM, Grace Batumbya wrote: >>You can simply register your >>own lower/upper with one argument that looks wherever you want to know >>what locale to use. > > The part of registering a function to override lower/upper I think I > understand. > > But if I wanted to persist the locale, so that even if I disconnect and > reconnect it is still set to the last time I set it, how do I go about > accomplishing this. Create a special table for that and store your last locale value in it. Then after reconnect read the locale value from this table. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Set Locale for upper() and lower() using a pragma variable
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 29/02/12 12:23, Carl Desautels wrote: > I would like to be able to run one statement that sets the locale for > upper() and lower() If you register a function with the same name and number of arguments as a builtin one, then yours will take priority. You can simply register your own lower/upper with one argument that looks wherever you want to know what locale to use. It will not affect the one registered by ICU for two arguments. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk9Os04ACgkQmOOfHg372QREcwCgigKHsMfew3d54rfV7hTg5iI+ JXoAoLfDRHglplt/JNxt8JzY57PDTUkb =8+Au -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users