Anne.Kirchhellen (AT) gmx (DOT) de wrote:
Hi (at the first time ;-)

My App (up to 1500 Users, up to 15 Million Records in
some tables in a SQL-Server-DB) offers as a specially
feature Data-Export. The Export-Functionally is one of
the most-used feature by Users. Exports are controlled
and done by a Report, controlled and done by a Tableview
and last by a feature I named query-container.
This query-container can connect to a SQL-Server-DB,
to a Access-MDB, to Schema-Ini-guided Text-Files and to
a SQLite-DB. Here can the user perform user-defined
sql-select-queries, which result first will be displayed
in a table, optionally followed by the export to one or
more files (more files = separate files if select the
query blob-colums (as bitmaps).

The exported Data goes (rarely) to a DB, often to
CSV-Textfiles and a Excel-Sheet, to a portable Access-MDB
on Laptops and now (I want) to a Sqlite-DB to offer the
Data displayed by a Browser on a Pocket-PC (Windows-
Mobile (Win CE)).
If the user performs a user-defined Query I dont know,
which colums he select and which Type the columns are - while
ever its a __user-defined__ query such as select fieldone
as name from customer . and so on.

The query-container creates a SQLite-DB and export Data with
correct type. But the Browser on Pocket-PC never knows, what
the User has done. And now display a Bitmap in a Blob-Type-
Column in a Formview-Text-Control as Text, while
sqlite3_column_type returns SQLITE_TEXT for a Blob-Type.

How can I eliminate this problem ? I think, to know
the correct columntype is fundamental, significant

Can anyone help me?

Best Greetings from Germany
Anne

Anne,

We have a similar function to yours and use XML as the transfer medium
because of its extreme generality. The column names are returned by the
Sqlite API as the queries are executed and are then used as XML
entities. All data are stored in text format as required by XML.

You could use CSV to go to Excel, SQL to etc. As for type you
can place whatever type you choose into your transfer data stream, but
the formats you would generally use would all require the data to be
transformed into TEXT. Sqlite returns the actual data type to make that
possible. You can make whatever transformation is needed by your
destination using atoi, atof and sprintf at the destination.

If you don't want to use the Sqlite API then you have probably chosen
the wrong software tool. Sqlite is aimed at embedded systems where it
is linked into applications, not as a replacement for enterprise SQL
servers.
JS

Anne,
People with English as a first language can have difficulty
understanding the description of manifest typing in the Sqlite
documenation. When you have less English it is more difficult :-).

Sqlite returns the column name and type.

Here is a code fragment which returns the column name and declared type
from Sqlite. It may help. For example if you declare your colmn name
as "HELGA": and its type as "BLB" you will be able to identify it when
you read the row.

/ sql_dc_srch
Lookup a reserved declared type table for a match and return a token.
Perform a binary search.*/
SQL_DECL_TYPES sql_dc_srch (char *dt) {

SQL_DECL_TYPES high;
SQL_DECL_TYPES low;
SQL_DECL_TYPES mid;
int result;

low = dc_first + 1;
high = dc_last;
while (low <= high) {
mid = (low + high) / 2;

result = strcmp(dt, sql_res_types[mid]);

if (result == 0) return(mid);

if (result < 0) high = mid - 1;
else low = mid + 1;
} /*while*/

return(dc_last);
} /*sql_dc_srch*/

/ sql_column_name_type
Given a prep'd statement and a column index, return the column name
and declared type. Returns dc_last on error.*/
SQL_DECL_TYPES sql_column_name_type (sqlite3_stmt *sqst, int idx, char *nm,
int bl, char *prec) {

int a;
char argnm[128]; /*Name of argument to function.*/
char *cn; /*Column name.*/
char *dc = NULL; /*Declared type in text.*/
char dt[128]; /*Parsed declared type.*/
char fnm[128]; /*Name of function.*/
char *pt;
SQL_DECL_TYPES tok;
char *wkp;

/*Get column name.*/
cn = (char *)sqlite3_column_name(sqst, idx);

/*Is it a function?*/
pt = cn;
while (*pt 0) {
if (*pt == '(') {
/*It is a function. Extract name and arg. Convert fn name
to upper case.*/
wkp = cn;
a = 0;
while (wkp < pt) {
fnm[a++] = *wkp & 0xdf;
wkp++;
} /*while*/
fnm[a] = 0; /*Func name.*/
dc = fnm;

pt++;
a = 0;
while ((*pt 0) && (*pt != ')')) argnm[a++] = *pt++;
argnm[a] = 0;
cn = argnm;

break; /*To exit loop.*/
} /*if*/
pt++;
} /*while*/

/*Get declared type if we don't already have it.*/
if (dc == NULL) dc = (char *)sqlite3_column_decltype(sqst, idx);

/*May be something like DECIMAL(6.2).*/
a = 0;
pt = dt;
wkp = dc;
while ((*wkp != '(') && (*wkp 0) && (a < 128)) {
*pt++ = *wkp++;
a++;
} /*while*/
*wkp = 0;

/*Tokenize the declared type.*/
tok = sql_dc_srch(dt);

/*Get the precision.*/
pt = prec;
if (tok == dc_decimal) {
if (*wkp == '{') wkp++;
while ((*wkp != '}') && (*wkp 0)) *pt++ = *wkp++;
} /*if*/
*pt = 0;

/*Move the name. Strip a table name.*/
pt = nm;
wkp = cn;
while ((*wkp 0) && (*wkp != '.')) wkp++; /*Strip table.*/
if (*wkp == '.') wkp++;
else wkp = cn;
a = 0;
while ((*wkp 0) && (a < bl)) {
*pt++ = *wkp++;
a++;
} /*while*/
*pt = 0;

return(tok);
} /*sql_column_name_type*/

Anne.Kirchhellen (AT) gmx (DOT) de wrote:
Hi John

Thanks for your answer :-)


>>You could use CSV to go to Excel, SQL to etc. *snip*

I do that all very succesful with AD and LE-DB very rarely
got problems.


>>If you don't want to use the Sqlite API then you have probably
>>chosen

i want to use SQLite, because it's a embedded SQL-DB and working on
my Pocket-PC. I think, it's a very good tool with fast results and
simple handling.


>>You can make whatever transformation is needed by your destination
>>using >atoi, atof and sprintf at the destination.

yes, i know but the problem -i cannot be solve- is a DB with
a unbeknown (? (sorry, got some problems too with my englisch))
field-structure. I "select * " and find fields. one of this
fields (in ever the same column) contains a bitmap, but i don't
know, which field/column it is.
i have to do ask the api "which field/column is a blob and have
to show as a bitmap via a bitmapwrapper?". unfortunately the
api-function sqlite3_column_type() returns SQLITE_TEXT for the
Blob-Column in my Test-App.

Best greetings from Germany
Anne

They say "You cannot teach an old dog new tricks", but we are not dogs
and have to learn :-). I am glad that you have got Sqlite to work for you.
Best regards from the South Pacific.

Anne.Kirchhellen (AT) gmx (DOT) de wrote:
>U Nachricht
>>Von: John Stanton <johns (AT) viacognis (DOT) com>
>>An: sqlite-users (AT) sqlite (DOT) org
>>Betreff: Re: [sqlite] BLB
>>Datum: Sat, 13 May 2006 08:17:29 -0700


>>People with English as a first language can have difficulty
>>understanding the description of manifest typing in the Sqlite
>>documenation. When you have less English it is more difficult :-).

Yes, it is ;-)

but the good message is: I have solve my problem :-D
And its so simple. And its really famous. But i have to do
a "mind-jump". It is similar to the jump from C to C To think
object-oriented, lost the top-down-control and handled by events
was heavy as i started with c++ 10 years ago.

My awareness, the internal SQLite-Column-Type is absolutely not
importand to me. I think, SQLite is always clever enough, to
choose one of the correct internal storage-classes. I only fetch
the declared type and use this to control my Wrapper-Class to cast
a lot of Variant-Type-Values before Write and after Read and
it runs ;-)

Indeed the Name "sqlite3_column_type()" is confusing. The Name
suggest, that the return applies to all rows. If i understand the
the meaning of this Function, a better name was
"sqlite3_current_cell_type(). *hmmm* Imho should sqlite3_column_type()
works in interaction with the sqlite-interpreted results from
userdefined sqlite3_column_decltype()

Maybe sometime at the moment, not important to me.

Greetings from Germany
Anne