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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
