[sqlite] Problem with SQLite version 3.9.2 together with user authentication extension

[Simon Slavin]

On 25 Nov 2015, at 8:25pm, Ulrich Telle  wrote:

> SELECT name, rootpage, sql FROM 'main'.sqlite_master ORDER BY rowid

Can you try that again without the quotes around "main" ?

SELECT name, rootpage, sql FROM main.sqlite_master ORDER BY rowid

Simon.

[sqlite] Problem with SQLite version 3.9.2 together with user authentication extension

[Ulrich Telle]

Am 26.11.2015 um 01:30 schrieb Simon Slavin:
>
> On 25 Nov 2015, at 8:25pm, Ulrich Telle  wrote:
>
>> SELECT name, rootpage, sql FROM 'main'.sqlite_master ORDER BY rowid
>
> Can you try that again without the quotes around "main" ?
>
> SELECT name, rootpage, sql FROM main.sqlite_master ORDER BY rowid

No. The reason is that this is an SQL statement generated by SQLite 
itself internally while initializing an empty database.

In fact, I finally found out that the lines 379-384 in build.c (or 
93143-93148 in the amalgamation) are the cause of the problem:

#if SQLITE_USER_AUTHENTICATION
   else if( pParse->db->auth.authLeveldb->auth.authLevel!=0 && 
pParse->db->auth.authLeveldb->auth.authLevel==UAUTH_Unknown ){
 sqlite3UserAuthInit(pParse->db);
   }
   if( pParse->db->auth.authLevel

[sqlite] FOREIGN KEY enhancement request

[no...@null.net]

On Wed Nov 25, 2015 at 10:45:07AM +, Simon Slavin wrote:
> 
> REFERENCES foreign-table ( column-name [[, column-name]]) ...
> 
> It would be useful if rather than having to specify a column name for
> each item one could specify a fixed value (or perhaps an expression,
> though that might be more complicated to handle).  This means that,
> if some customers were dealers and others weren't you could ensure
> you gave discounts only to dealers using
> 
> REFERENCES customers ('dealer', id)

Which column in the customers table should contain the fixed value or
expression?

-- 
Mark Lawrence

[sqlite] drop, create and copy a table

[R Smith]

On 2015/11/26 4:08 AM, H?ctor Fiandor wrote:
> Dear Mr. Hipp and other members:
>
> I have a table where I have introduced a lot of information during 2015.
>
> In December 31, I have to select the records to be continue in 2016.
> Previously, I have obtained a copy of the 2015 table for any consult.
>
> In 2016, the year start with a table "cleaned" of the old records, with the
> records to be continued as the firsts, for these reasons, I thought to drop
> the table and create a new one, "cleaned" of any history related to 2015,
> with the Id starting in "1".
>
> Really, I don?t know that making the step (2) suggested by Mr. Hipp followed
> by step (3) without dropping the table "clean" the history of the table and
> start the Id with "1".
>
> Really, I have not used de BEGIN...COMMINT commands.
>
> I will test the routines as suggested by Mr. Hipp.
>
> Any suggestion will be received as a gift.

Hi Hector,

This above statement reads very different to what you have asked before. 
There is obvious a language difficulty between us so I will say the next 
bit as verbose as possible (please forgive me if it sounds overly 
convoluted).

We now understand that you wish to create the new table and copy from 
the old table, but only SOME information, not all of it, because you 
want to remove very old data that is no longer needed and begin a new 
set when starting the year 2016.

The best way to do that is as Dr. Hipp suggested by first renaming the 
current (old) table to something else, such as "temp_mytable",
ALTER TABLE "mytable" RENAME TO "temp_mytable";

then create the new empty table (which will reset all the AUTO-INC 
values etc.). In this step be sure to recreate all Index and Trigger 
objects for the new table,
CREATE TABLE (Col1 INT, Col2 TEXT, etc. ... LastCol);

then copy the values you want to keep from the old table with a 
select-insert, like this:
INSERT INTO mytable (Col1, Col2, ... LastCol) SELECT Col1, Col2, ... 
LastCol FROM temp_mytable WHERE DateStamp > '2013';  (Or however you 
want to filter out the unneeded records),

then, after that is done, simply remove the old table:
DROP TABLE "temp_mytable";

I understand from your original writing you were looking for a faster 
way to do it, or to do it with less steps. This is something that will 
not be done many times, just once, so there is no need to find a more 
efficient way. You can easily script the process above and just execute 
it using the command line utility or from inside your program.

Hope that sheds a bit more light, Cheers.
Ryan

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

[Kirill Müller]

On 25.11.2015 16:32, Clemens Ladisch wrote:
> Kirill M?ller wrote:
>> For a left join with a subquery on the right-hand side, that subquery
>> doesn't seem to be flattened.
> This is rule 3 of .
Thanks, missed that. While true, I wonder if this rule might be relaxed 
a bit. The SQL is produced by a very generic SQL query generator, which 
just uses subqueries for everything table-like.


-Kirill

[sqlite] drop, create and copy a table

[Héctor Fiandor]

Dear Mr. Smith and Dr. Hipp:

Thanks very much for your answers. I have learned a lot from your messages.

Reviewing old message I found one from Mr. Smith that confirm this routines,
that I have found previously but I miss something, and need to do all this
in three "phases", I need to close the application after 1st, start again in
the 2nd, close again, a so for.

As you say, it can be done easily in one "phase". I have to trial and learn.

Thanks very much again.

I will tell you about my progress.

Ing. H?ctor Fiandor
hfiandor at ceniai.inf.cu


-Mensaje original-
De: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] En nombre de R Smith
Enviado el: jueves, 26 de noviembre de 2015 02:46 a. m.
Para: sqlite-users at mailinglists.sqlite.org
Asunto: Re: [sqlite] drop, create and copy a table



On 2015/11/26 4:08 AM, H?ctor Fiandor wrote:
> Dear Mr. Hipp and other members:
>
> I have a table where I have introduced a lot of information during 2015.
>
> In December 31, I have to select the records to be continue in 2016.
> Previously, I have obtained a copy of the 2015 table for any consult.
>
> In 2016, the year start with a table "cleaned" of the old records, with
the
> records to be continued as the firsts, for these reasons, I thought to
drop
> the table and create a new one, "cleaned" of any history related to 2015,
> with the Id starting in "1".
>
> Really, I don?t know that making the step (2) suggested by Mr. Hipp
followed
> by step (3) without dropping the table "clean" the history of the table
and
> start the Id with "1".
>
> Really, I have not used de BEGIN...COMMINT commands.
>
> I will test the routines as suggested by Mr. Hipp.
>
> Any suggestion will be received as a gift.

Hi Hector,

This above statement reads very different to what you have asked before. 
There is obvious a language difficulty between us so I will say the next 
bit as verbose as possible (please forgive me if it sounds overly 
convoluted).

We now understand that you wish to create the new table and copy from 
the old table, but only SOME information, not all of it, because you 
want to remove very old data that is no longer needed and begin a new 
set when starting the year 2016.

The best way to do that is as Dr. Hipp suggested by first renaming the 
current (old) table to something else, such as "temp_mytable",
ALTER TABLE "mytable" RENAME TO "temp_mytable";

then create the new empty table (which will reset all the AUTO-INC 
values etc.). In this step be sure to recreate all Index and Trigger 
objects for the new table,
CREATE TABLE (Col1 INT, Col2 TEXT, etc. ... LastCol);

then copy the values you want to keep from the old table with a 
select-insert, like this:
INSERT INTO mytable (Col1, Col2, ... LastCol) SELECT Col1, Col2, ... 
LastCol FROM temp_mytable WHERE DateStamp > '2013';  (Or however you 
want to filter out the unneeded records),

then, after that is done, simply remove the old table:
DROP TABLE "temp_mytable";

I understand from your original writing you were looking for a faster 
way to do it, or to do it with less steps. This is something that will 
not be done many times, just once, so there is no need to find a more 
efficient way. You can easily script the process above and just execute 
it using the command line utility or from inside your program.

Hope that sheds a bit more light, Cheers.
Ryan



___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FOREIGN KEY enhancement request

[Simon Slavin]

On 26 Nov 2015, at 7:42am, nomad at null.net wrote:

> Which column in the customers table should contain the fixed value or
> expression?

The items listed in the REFERENCES clause should be handled in the same order 
as they would if they were all column names.  It's just that instead of looking 
all of them up in the child table, some of them can be fixed strings.

Suppose you have a table with three types of customer and a "can discount" 
column.  Both dealers and agents have 'yes' in it and private buyers have 'no'.

I want to make sure that discounts are entered only for customers who may have 
them.  This can be enforced by a REFERENCES clause, but at the moment I do it 
by keeping a redundant column in my discount table, set to 'yes' on every row. 
I then use REFERENCES to relate this to the "can discount" column in the 
customer table.

This technique is elegant, it explains the business process in the schema, 
which makes it easier on external programs reading my database, and it requires 
less programming in my programming language.  But in SQLite it requires extra 
space: one extra value for every entry in the discount table.

Simon.

[sqlite] FOREIGN KEY enhancement request

[Bernardo Sulzbach]

I am not against this feature request, Simon. But enlighten my why
this cannot be done with a CHECK constraint? I may be missing
something very simple. Thanks.

[sqlite] FOREIGN KEY enhancement request

[Simon Slavin]

On 26 Nov 2015, at 1:36pm, Bernardo Sulzbach  
wrote:

> I am not against this feature request, Simon. But enlighten my why
> this cannot be done with a CHECK constraint? I may be missing
> something very simple. Thanks.

Can you refer to another table in a CHECK constraint ?  It never occurred to me.

Simon.

[sqlite] FOREIGN KEY enhancement request

[Bernardo Sulzbach]

I did not mean that. But if you want to check that a certain row has a
valid value like you described,

check ((job='dealer' or job='agent') or can_discount='no')

Could do the trick, right? If you want, you can be more aggressive and go for

check can_discount='no' or ((job='dealer' or job='agent') and
can_discount='yes')

On Thu, Nov 26, 2015 at 11:38 AM, Simon Slavin  wrote:
>
> On 26 Nov 2015, at 1:36pm, Bernardo Sulzbach  
> wrote:
>
>> I am not against this feature request, Simon. But enlighten my why
>> this cannot be done with a CHECK constraint? I may be missing
>> something very simple. Thanks.
>
> Can you refer to another table in a CHECK constraint ?  It never occurred to 
> me.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
Bernardo Sulzbach

[sqlite] FOREIGN KEY enhancement request

[Simon Slavin]

On 26 Nov 2015, at 1:52pm, Bernardo Sulzbach  
wrote:

> I did not mean that. But if you want to check that a certain row has a
> valid value like you described,
> 
>check ((job='dealer' or job='agent') or can_discount='no')

That works only when the "job" column is in the table the constraint is in.  In 
my case, there are two tables: client and sale.  The "job" column is in the 
client table, but it's the sale table where I need to know if the client 
deserves a discount.

Simon.

[sqlite] FOREIGN KEY enhancement request

[Bernardo Sulzbach]

Oh, OK. I thought they were in the same table.

On Thu, Nov 26, 2015 at 11:56 AM, Simon Slavin  wrote:
>
> On 26 Nov 2015, at 1:52pm, Bernardo Sulzbach  
> wrote:
>
>> I did not mean that. But if you want to check that a certain row has a
>> valid value like you described,
>>
>>check ((job='dealer' or job='agent') or can_discount='no')
>
> That works only when the "job" column is in the table the constraint is in.  
> In my case, there are two tables: client and sale.  The "job" column is in 
> the client table, but it's the sale table where I need to know if the client 
> deserves a discount.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
Bernardo Sulzbach

[sqlite] TCL link error

[to...@acm.org]

I?ve been compiling new versions for a long time now, and I just noticed this 
(so I don?t know how long it?s been there).

Here?s the relevant output from the compilation:

--
tclsqlite.c
lib.exe /NOLOGO /LIBPATH:c:\tcl\lib /OUT:libtclsqlite3.lib tclsqlite.lo
libsqlite3.lib tclstub85.lib
LINK : fatal error LNK1181: cannot open input file 'tclstub85.lib'
NMAKE : fatal error U1077: '"C:\Program Files (x86)\Microsoft Visual Studio 12.0
\VC\BIN\lib.exe"' : return code '0x49d'
--

Since I have TCL 86 installed (and not 85), I build with this:
nmake -f Makefile.msc libtcl=tcl86.lib tclsh_cmd=tclsh nawk=awk.exe

I do get all executables / DLLs and they seem to work OK but I?m not sure what 
the impact of the above error is.  It?s obviously a stub but what does the 
failure to link do at that point?  Do I still get correct binaries?

At any rate, shouldn?t the libtcl=tcl86.lib define also take care of this?

Thanks.

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

[Clemens Ladisch]

Kirill M?ller wrote:
> On 25.11.2015 16:32, Clemens Ladisch wrote:
>> Kirill M?ller wrote:
>>> For a left join with a subquery on the right-hand side, that subquery
>>> doesn't seem to be flattened.
>>
>> This is rule 3 of .
>
> I wonder if this rule might be relaxed a bit.

Only if you relax your requirement that the results must be correct.


In the general case, a left outer join can be rewritten like this:

  SELECT ... FROM A JOIN B ON ...
  UNION ALL
  SELECT ... FROM A WHERE NOT EXISTS (look up in B)

This query would be more likely to be flattenable, but also be slower.


Regards,
Clemens

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

[Kirill Müller]

On 26.11.2015 21:12, Clemens Ladisch wrote:
> Kirill M?ller wrote:
>> On 25.11.2015 16:32, Clemens Ladisch wrote:
>>> Kirill M?ller wrote:
 For a left join with a subquery on the right-hand side, that subquery
 doesn't seem to be flattened.
>>> This is rule 3 of .
>> I wonder if this rule might be relaxed a bit.
> Only if you relax your requirement that the results must be correct.
>
>
> In the general case, a left outer join can be rewritten like this:
>
>SELECT ... FROM A JOIN B ON ...
>UNION ALL
>SELECT ... FROM A WHERE NOT EXISTS (look up in B)
>
> This query would be more likely to be flattenable, but also be slower.
>
Thanks. Let's not focus on terminology -- I thought "flattening" was the 
right word to use, but it probably isn't. Of course I'm looking for 
correct results.

Originally, I attached a script but it seems that it's been stripped. 
I've pasted it below. I see no reason why the following two queries (1 
and 3 in the script) can't be executed with the same plans:

SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN t2 USING (a) LIMIT 1)
SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2) zzz2 
USING (a) LIMIT 1)

This is for two tables t1 and t2 with a single column "a". The script 
creates them and populates them with 20 rows each.


-Kirill


#!/bin/bash

db=test.sqlite

#if false; then
rm -f $db

n=20

sqlite3 $db "CREATE TABLE t1 (a int primary key)"
seq 1 $n | sqlite3 $db ".import /dev/stdin t1"

sqlite3 $db "CREATE TABLE t2 (a int primary key)"
seq 1 $n | sqlite3 $db ".import /dev/stdin t2"
#fi

q() {
 sqlite3 $db "EXPLAIN QUERY PLAN $1"
 time sqlite3 $db "$1"
}

q "SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN t2 USING (a) LIMIT 1)"
q "SELECT count(*) FROM (SELECT * FROM (SELECT * FROM t1) zzz1 LEFT JOIN 
t2 USING (a) LIMIT 1)"
q "SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2) 
zzz2 USING (a) LIMIT 1)"
q "SELECT count(*) FROM (SELECT * FROM t1 INNER JOIN (SELECT * FROM t2) 
zzz2 USING (a) LIMIT 1)"

[sqlite] FOREIGN KEY enhancement request

[Keith Medcalf]

That is incorrect.  Whether or not the customer "can_discount" is an attribute 
of the customer.  Whether or not a sale "has_discount" applied is an attribute 
of the sale.  They are not the same attribute, do not mean the same things, and 
are attributes of different tables.  

Business logic (implemented in the application or a trigger) can assure that 
you do not allow a sale to "has_discount" when the customer is not allowed 
"can_discount", but the relationship (FK) from the sale to the customer is the 
customer number (or whatever unique identifier you have assigned to the 
customer.

You need to normalize your data better.

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin
> Sent: Thursday, 26 November, 2015 06:57
> To: SQLite mailing list
> Subject: Re: [sqlite] FOREIGN KEY enhancement request
> 
> 
> On 26 Nov 2015, at 1:52pm, Bernardo Sulzbach 
> wrote:
> 
> > I did not mean that. But if you want to check that a certain row has a
> > valid value like you described,
> >
> >check ((job='dealer' or job='agent') or can_discount='no')
> 
> That works only when the "job" column is in the table the constraint is
> in.  In my case, there are two tables: client and sale.  The "job" column
> is in the client table, but it's the sale table where I need to know if
> the client deserves a discount.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

[Keith Medcalf]

Would it not be more efficient to say:

select 1 from t1 limit 1;

?

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Kirill M?ller
> Sent: Thursday, 26 November, 2015 15:03
> To: SQLite mailing list
> Subject: Re: [sqlite] Query flattening for left joins involving subqueries
> on the right-hand side
> 
> On 26.11.2015 21:12, Clemens Ladisch wrote:
> > Kirill M?ller wrote:
> >> On 25.11.2015 16:32, Clemens Ladisch wrote:
> >>> Kirill M?ller wrote:
>  For a left join with a subquery on the right-hand side, that subquery
>  doesn't seem to be flattened.
> >>> This is rule 3 of .
> >> I wonder if this rule might be relaxed a bit.
> > Only if you relax your requirement that the results must be correct.
> >
> >
> > In the general case, a left outer join can be rewritten like this:
> >
> >SELECT ... FROM A JOIN B ON ...
> >UNION ALL
> >SELECT ... FROM A WHERE NOT EXISTS (look up in B)
> >
> > This query would be more likely to be flattenable, but also be slower.
> >
> Thanks. Let's not focus on terminology -- I thought "flattening" was the
> right word to use, but it probably isn't. Of course I'm looking for
> correct results.
> 
> Originally, I attached a script but it seems that it's been stripped.
> I've pasted it below. I see no reason why the following two queries (1
> and 3 in the script) can't be executed with the same plans:
> 
> SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN t2 USING (a) LIMIT 1)
> SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2) zzz2
> USING (a) LIMIT 1)
> 
> This is for two tables t1 and t2 with a single column "a". The script
> creates them and populates them with 20 rows each.
> 
> 
> -Kirill
> 
> 
> #!/bin/bash
> 
> db=test.sqlite
> 
> #if false; then
> rm -f $db
> 
> n=20
> 
> sqlite3 $db "CREATE TABLE t1 (a int primary key)"
> seq 1 $n | sqlite3 $db ".import /dev/stdin t1"
> 
> sqlite3 $db "CREATE TABLE t2 (a int primary key)"
> seq 1 $n | sqlite3 $db ".import /dev/stdin t2"
> #fi
> 
> q() {
>  sqlite3 $db "EXPLAIN QUERY PLAN $1"
>  time sqlite3 $db "$1"
> }
> 
> q "SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN t2 USING (a) LIMIT 1)"
> q "SELECT count(*) FROM (SELECT * FROM (SELECT * FROM t1) zzz1 LEFT JOIN
> t2 USING (a) LIMIT 1)"
> q "SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2)
> zzz2 USING (a) LIMIT 1)"
> q "SELECT count(*) FROM (SELECT * FROM t1 INNER JOIN (SELECT * FROM t2)
> zzz2 USING (a) LIMIT 1)"
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] method for thousands separator via sed post processing

[Bruce Hohl]

Thanks to suggestions on this list I explored the sed post processing
avenue and found a solution.  There are quite a few sed docs at
http://sed.sourceforge.net  For my needs I adapted an example from
http://www-rohan.sdsu.edu/doc/sed.html (These docs are like ancient
scrolls.)

# add commas to interger strings, changing "1234567" to "1,234,567"
gsed ':a;s/\B[0-9]\{3\}\>/,&/;ta'

# add commas to numbers with decimal points and minus signs
gsed ':a;s/\(^\|[^0-9.]\)\([0-9]\+\)\([0-9]\{3\}\)/\1\2,\3/g;ta'

I adjusted the 2nd as follows to remove a space for each comma added which
preserves column layout:
sed ':a;s/\( \)\(^\|[^0-9.]\)\([0-9]\+\)\([0-9]\{3\}\)/\2\3,\4/g;ta'

It works like this:
sqlite> create table t(f1 real);
sqlite> insert into t values(100.123);
sqlite> insert into t values(12200.13);
$ sqlite3 -header -column test.db "select printf(\"%10.2f\",f1)from t;" |
sed ":a;s/\( \)\(^\|[^0-9.]\)\([0-9]\+\)\([0-9]\{3\}\)/\2\3,\4/g;ta";

printf("%10.2f",f1)
---
100.12
 12,200.13

This is sufficient for my needs since most of my output is numeric but I
did notice the following limitations with respect to dates and 'number'
strings:

1- The script looks for numbers [0-9] thus:
   dates formatted like '2015-12-31' become '2,015-12-31'
   number strings like '12345678' become '12,345,678'

2- Any numbers that begin in column 1 of each line fail
   the match criteria (SQLite default is left justified).

3- Work arounds:
   (1) Right justify via printf() where commas are wanted.
   (2) Concatenate non matching characters where commas not wanted:
   printf(\"%-12s\",\"* \" || Date) AS Date >> * 2015-12-31

[sqlite] sqlite3_errmsg and wide char

[Scott Robison]

On Thu, Nov 26, 2015 at 10:13 PM, Igor Korot  wrote:
>
> Hi,
> Is there any way to have "sqlite3_errmsg" function return a wide char
string?
> Or do a conversion in a portable way?
>
> Thank you.

The portable way would be to use the mbstowcs function from stdlib.h,
though it depends on what locales are supported by the system, so maybe not
as portable as you would like.

There isn't really a truly portable way of converting from char to wchar_t
based strings, given that there is no real guarantee about what exactly
wchar_t *is*. The ISO C90 standard merely defined it as "an integral type
whose range of values can represent distinct codes for all members of the
largest extended character set specified among the supported locales". In
theory, wchar_t could be a typedef of char if a platform only supported an
8 bit character set. Unicode was being designed at the time of C
standardization, but the first version of Unicode wasn't published for a
year or more after the C90 standard. Thus the wchar_t type doesn't have to
be Unicode,

In practical terms, I generally consider wchar_t to be a pre-2011 method of
storing Unicode. Even here there are no portability guarantees. Microsoft
went all in on Unicode in the early 1990s, back when it was only a two byte
encoding (UCS-2), so Microsoft compilers treat wchar_t as a two byte type.
Unicode 2.0 extended the Unicode character set in 1996 (I think) and
introduced UTF-16 as a compromise way of allowing systems that embraced
Unicode 1.0 (when it "guaranteed" a 16 bit character space) to support the
full space of Unicode code points from U+ to U+10 via surrogate
pairs. Modern posix systems (as far as I know) define wchar_t to be a 32
bit type, so you can't really convert to wchar_t in a portable way, because
you have to handle surrogate pairs on Windows vs simple code points in
posix (though this should be handled by mbstowcs if the platform supports
wchar_t as unicode).

If you only use ASCII or Latin-1 8 bit characters in your code, or are
willing to treat all char objects as ASCII or Latin-1, then you can convert
char strings to wchar_t strings by simply zero extending each character
while copying it. Something like this (without any error checking):

void copy_narrow_to_wide(wchar_t* dst, const char* src)
{
  while (*src) *(dst++) = (unsigned char)(*(src++));
  *dst = 0;
}

--
Scott Robison

[sqlite] SQLite3 API

[Robert W.Mills (Phoenix)]

To all,

I have written (in COBOL) an SQLite3 interface for GnuCOBOL and would 
like to add a routine that will return the number of rows that were 
selected, updated or deleted by the last executed SQL statement.

I currently have a routine, named DBSQL, that calls sqlite3_prepare_v2, 
sqlite3_step and sqlite3_finalize. I am using this routine for SQL 
statements (INSERT, UPDATE, DELETE, etc.) where any returned data is not 
required. Any data returned by sqlite3_step (SQLITE-ROW is true) is 
being ignored, although I do output a message indicating this has happened.

For SELECT statements I am doing the following (error checking not shown):

set rowcount to zero
call sqlite3_prepare_v2
   call sqlite3_step
   if sqlite-row then
 call sqlite3_column_count
 loop for each column
call sqlite3_column_type *> save value in an array
 end-loop
 loop while sqlite-row
   add 1to rowcount
   loop for each column
call sqlite3_column_x *> where x = int or text
*> save value in avariable
   end-loop
   call routine to process the returned data
   call sqlite3_step
 end-loop
   end-if
   sqlite3_finalize

Using this method I am able to return the number of rows selected and 
with the addition of some code around the DBSQL routine I can keep a 
count of the number of INSERTS.

But how do I get the number of rows UPDATEd or DELETEd?

Please note that I am NOT a C programmer.

regards,
Robert