If my Table is as follows: create table Music ( id integer not null primary key, classificationCode integer, input text) <<
Table: id classificationCode input ---------- ------------------ --------- 1 1 aaa 2 0 1345 3 1 asdf At this point, sqlite3_prepare(gpst_SqliteInstance, "SELECT id, classificationCode, input FROM MUSIC WHERE input >= ? LIMIT 1;", -1,&pst_SearchPrepareStmt, 0); Can I bind the unsigned short value [ie., like 0x0065 for English and 0x3045 for Japanese] to its corresponding string value.is it possible. Unsigned short temp; For eg, If temp = 0x0065 then its corresponding english string 'a' should come while binding.It works out by using sprintf();But If temp = 0x30E4 then its corresponding Japanese string should come.For this sprintf() is not working. Can anyone please help to solve this. Regards, Mahalakshmi -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Stephen Oberholtzer Sent: Friday, February 29, 2008 2:54 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Prepare Statement On Thu, Feb 28, 2008 at 9:22 AM, Mahalakshmi.m <[EMAIL PROTECTED]> wrote: > > > Hi, > My table looks like: > Id Name > 1 1aaa > 2 01345 > 3 1asdf > > I want to bind unsigned short as text. i.e, If the Unsighed short is > 0x0061 I want to bind it as 'a'. > > My Prepare statement is as follows: > > Unsigned char u8_ClassificationCode=1; > > Unsigned short u16_Input=0x0061; > > if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC > WHERE Name >= '%d%c' LIMIT 1;",-1,&pst_SearchPrepareStmt,0)!= > SQLITE_OK) > > { > > return SQLITE_DB_ERROR; > > } > > sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode); > > sqlite3_bind_text16(pst_SearchPrepareStmt,2,(char > *)u16_Input,-1,SQLITE_STATIC); > > } > Since nobody else mentioned it: there's something seriously wrong with your database design. But first: Your usage of sqlite3_bind_text16 is incorrect. The fourth argument, -1, means "My string is NUL-terminated. Use strlen() to figure out how long my string is and use that.". However, for that to always work correctly, u16_input needs to be an array with a NUL terminator: >> unsigned short u16_input[] = { 'a', '\0' }; << Anyway, back to what I was saying: your database design needs rethinking. 1NF (http://en.wikipedia.org/wiki/First_normal_form) states that a column should only have one value. However, you seem to be combining *two* values (Classification Code and Input) into one column (Name). Therefore, you should be doing this: >> create table Music ( id integer not null primary key, classificationCode integer, input text) << Table: id classificationCode input ---------- ------------------ --------- 1 1 aaa 2 0 1345 3 1 asdf At this point, you would do this: >> sqlite3_prepare(gpst_SqliteInstance, "SELECT id, classificationCode, input FROM MUSIC WHERE classificationCode = ? AND input >= ? LIMIT 1;", -1,&pst_SearchPrepareStmt, 0); << Note that, if you you want the original form, you can do >> sqlite3_prepare(gpst_SqliteInstance, "SELECT id, classificationCode || input as Name FROM MUSIC WHERE classificationCode = ? AND input >= ? LIMIT 1;", -1,&pst_SearchPrepareStmt, 0); << This will convert classificationCode to a string and join it against the 'input' column to return your original Name. >> sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode); sqlite3_bind_text(pst_SearchPrepareStmt, 2, "a", -1, SQLITE_STATIC); << This also means you can index the string portion of your Name column separately, and quickly search for something with a specific name without knowing its classification. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE _______________________________________________ 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
