Re: [sqlite] Can DBI supply a more specific foreign key mismatch error? -- Mea culpa

[felix]

Alright, I screwed up and shot off my big mouth.  I guess getting
older hasn't taught me every lesson under the sun yet.

I had read so much documentation that I thought it was version 3, not
3.6.19, that introduced foreign keys.  Add no complaints about either
REFERENCES as a column constraint or executing the pragma, and a
minimalist error message, and I was pretty frustrated.  In that frame
of mind, I had the wrong expectations and frame of mind for your
responses, and misinterpreted them.

I'll hide back in lurker mode and maybe come out after I've grown a
bit older, and hopefully wiser and more open monded.

-- 
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
 Felix Finch: scarecrow repairman & rocket surgeon / fe...@crowfix.com
  GPG = E987 4493 C860 246C 3B1E  6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can DBI supply a more specific foreign key mismatch error?

[Jay A. Kreibich]

On Thu, Oct 13, 2011 at 07:52:59PM -0700, fe...@crowfix.com scratched on the 
wall:
> On Fri, Oct 14, 2011 at 02:17:20AM +0700, Dan Kennedy wrote:
> > On 10/14/2011 01:29 AM, fe...@crowfix.com wrote:
> > > I'm working on a project which generates tables from a config file,
> > > and it seems to be happy on a Mac OSX running 3.4.0, but Linux running
> > > 3.7.8 complains about an insert with the unhelpful message
> > >
> > >  (foreign key mismatch)

> It works on MacOSX with sqlite 3.4.0, and the exact same code fails
> under Linux with sqlite 3.7.8,

  It does *not* "work" under 3.4.0, as that version of SQLite does
  not have support for foreign keys...  The issue, be it a bad
  foreign key statement or bad data, is still there.  That version of
  SQLite is simply too old to even know what a foreign key is,
  never mind when one might be wrong.

> but if I use sqlite to repeat the same failing SQL statement,
> it works fine under Linux.

  For the likely reason Dan gave: the command line does not have
  foreign key checks enabled by default.  Unless you explicitly turned
  foreign key support on in the command line tool before repeating the
  relevant statements, your command-line test proves nothing... it
  did not "work fine" under Linux.  The error condition was not
  reported because the error condition was never checked-- not because
  there were no errors.

> But I repeat myself.

  And we heard you.  And responded.  Try listening.

> > > I've checked all the data and don't see what it is complaining about,
> > > and if I dump the SQL to a file and execute the same commands that
> > > way, it works, and so does adding the single record in question using
> > > sqlite3.
> > 
> > Error message only shows up if foreign keys are enabled ("PRAGMA
> > foreign_keys = ON"). They are disabled by default.
> 
> Sorry to be grumpy, but what does this have to do with the price of
> red grapefruit on Mars Colony Three?  

  If keys are disabled, your tests are invalid.

> If I get the error message,
> by your own statement that must mean the pragma enabled foreign keys.

  And, as Dan points out, *not* getting an error message does NOT mean
  the error is not there-- it can also mean the error test was disabled.

  As it is, by default.
  
  Hence there is a very good chance your "tests" using the command line
  tool are meaningless.

> What I want to know is if there is any way to get more better
> reporting, such as the column or constraint it is upset about.

  No.  As several have said.

> The message is not helpful.  I have checked all the columns, all the
> values, and they are set up properly.

  And, as Dan was trying to point out, if those "test" included stuff
  like using the command line tool, the tests were likely invalid.  All
  indicators still point to your database having a foreign key issue.

> If there is no such more better
> reporting, ok, I will take other approaches.  If you don't know the
> answer, you could either say so or say something useful or say nothing.

  You might want to check into who Dan is before making such statements.

  As with your foreign key tests, just because you don't see the value
  of the statement doesn't mean there is no value there.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can DBI supply a more specific foreign key mismatch error?

[Pavel Ivanov]

>> >      (foreign key mismatch)
>>
>> It means there is a problem with a foreign key definition in the
>> database schema. Either an FK specifies parent columns that do not
>> exist, or parent columns that are not a PRIMARY KEY or UNIQUE.
>
> It works on MacOSX with sqlite 3.4.0, and the exact same code fails
> under Linux with sqlite 3.7.8, but if I use sqlite to repeat the same
> failing SQL statement, it works fine under Linux.

It could mean that PRAGMA foreign_keys = ON is executed in your
application and not executed in the command line utility. Do you
execute that before trying the same failing SQL statement?


Pavel


On Thu, Oct 13, 2011 at 10:52 PM,   wrote:
> On Fri, Oct 14, 2011 at 02:17:20AM +0700, Dan Kennedy wrote:
>> On 10/14/2011 01:29 AM, fe...@crowfix.com wrote:
>> > I'm working on a project which generates tables from a config file,
>> > and it seems to be happy on a Mac OSX running 3.4.0, but Linux running
>> > 3.7.8 complains about an insert with the unhelpful message
>> >
>> >      (foreign key mismatch)
>>
>> It means there is a problem with a foreign key definition in the
>> database schema. Either an FK specifies parent columns that do not
>> exist, or parent columns that are not a PRIMARY KEY or UNIQUE.
>
> It works on MacOSX with sqlite 3.4.0, and the exact same code fails
> under Linux with sqlite 3.7.8, but if I use sqlite to repeat the same
> failing SQL statement, it works fine under Linux.
>
> But I repeat myself.
>
>> > I've checked all the data and don't see what it is complaining about,
>> > and if I dump the SQL to a file and execute the same commands that
>> > way, it works, and so does adding the single record in question using
>> > sqlite3.
>>
>> Error message only shows up if foreign keys are enabled ("PRAGMA
>> foreign_keys = ON"). They are disabled by default.
>
> Sorry to be grumpy, but what does this have to do with the price of
> red grapefruit on Mars Colony Three?  If I get the error message,
> by your own statement that must mean the pragma enabled foreign keys.
>
> What I want to know is if there is any way to get more better
> reporting, such as the column or constraint it is upset about.  The
> message is not helpful.  I have checked all the columns, all the
> values, and they are set up properly.  If there is no such more better
> reporting, ok, I will take other approaches.  If you don't know the
> answer, you could either say so or say something useful or say nothing.
>
> --
>            ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
>     Felix Finch: scarecrow repairman & rocket surgeon / fe...@crowfix.com
>  GPG = E987 4493 C860 246C 3B1E  6477 7838 76E9 182E 8151 ITAR license #4933
> I've found a solution to Fermat's Last Theorem but I see I've run out of room 
> o
> ___
> 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] Can DBI supply a more specific foreign key mismatch error?

[Simon Slavin]

On 14 Oct 2011, at 3:52am, fe...@crowfix.com wrote:

> What I want to know is if there is any way to get more better
> reporting, such as the column or constraint it is upset about.  The
> message is not helpful.

Sorry, you can't get a better one.

> I have checked all the columns, all the
> values, and they are set up properly.  If there is no such more better
> reporting, ok, I will take other approaches.

Try doing a PRAGMA integrity_check.  I don't know that it does catch FOREIGN 
KEY errors, but it might.

> If you don't know the
> answer, you could either say so or say something useful or say nothing.

He's trying to be helpful and doesn't know that that answer doesn't help you.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can DBI supply a more specific foreign key mismatch error?

[felix]

On Fri, Oct 14, 2011 at 02:17:20AM +0700, Dan Kennedy wrote:
> On 10/14/2011 01:29 AM, fe...@crowfix.com wrote:
> > I'm working on a project which generates tables from a config file,
> > and it seems to be happy on a Mac OSX running 3.4.0, but Linux running
> > 3.7.8 complains about an insert with the unhelpful message
> >
> >  (foreign key mismatch)
> 
> It means there is a problem with a foreign key definition in the
> database schema. Either an FK specifies parent columns that do not
> exist, or parent columns that are not a PRIMARY KEY or UNIQUE.

It works on MacOSX with sqlite 3.4.0, and the exact same code fails
under Linux with sqlite 3.7.8, but if I use sqlite to repeat the same
failing SQL statement, it works fine under Linux.

But I repeat myself.

> > I've checked all the data and don't see what it is complaining about,
> > and if I dump the SQL to a file and execute the same commands that
> > way, it works, and so does adding the single record in question using
> > sqlite3.
> 
> Error message only shows up if foreign keys are enabled ("PRAGMA
> foreign_keys = ON"). They are disabled by default.

Sorry to be grumpy, but what does this have to do with the price of
red grapefruit on Mars Colony Three?  If I get the error message,
by your own statement that must mean the pragma enabled foreign keys.

What I want to know is if there is any way to get more better
reporting, such as the column or constraint it is upset about.  The
message is not helpful.  I have checked all the columns, all the
values, and they are set up properly.  If there is no such more better
reporting, ok, I will take other approaches.  If you don't know the
answer, you could either say so or say something useful or say nothing.

-- 
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
 Felix Finch: scarecrow repairman & rocket surgeon / fe...@crowfix.com
  GPG = E987 4493 C860 246C 3B1E  6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables)

[Frank Missel]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Petite Abeille
> Sent: 14 October 2011 03:58
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] How to use SQLite as a data source in Excel (tables
and
> pivottables)
> 
> Pivot tables can be populated from another worksheet, a data source, an
> OLAP cube, etc...

Yes, indeed.

> That number is pretty high these days, but yes there is no point using an
> intermediary worksheet. Get the data directly from source. Also, you may
> want to pre-process, e.g. summarize, your data in the database already as
> much as you can before hand. Excel is not a speed daemon when confronted
> with a truck load of data.
> 
> You can also create and save the pivot table as an offline OLAP cube, with
> hierarchical dimensions & all (Microsoft Query + OLAP Cube Wizard).

Row max is slightly more than a million rows now which is a lot better than
in Excel 2003. 
However, the data sets that I use often go above that number of rows.

Pre-processing is an option that I sometimes use. But  it then freezes the
representation of the data in that form. The ability to change the view of
the data in Pivottables is a very nice feature.

I find that Excel has decent performance in Pivottable calculations,
especially so with the newer versions.

I might look into OLAP cubes, although I find it adds yet another
computational layer. 
Probably the best option for now is to stick with the ODBC driver and just
accept the hassle of creating a new DSN for every individual database which
requires some registry manipulation which is a bit messy but can be done.

I am still dreaming and hoping  that someone might have the perfect solution
for just using the SQLite database as a proper OLE DB data source which
seems to be the way to do it nowadays or using the generic ODBC driver as
the data source without having the create an individual DSN.


/Frank

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables)

[Frank Missel]

> boun...@sqlite.org] On Behalf Of Bart Smissaert
> Sent: 14 October 2011 04:05
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] How to use SQLite as a data source in Excel (tables
and
> pivottables)
> 
> It looks you can't make a pivot table directly from an array.
> What you could do though is write the array to a text file and base the
array
> on that file as an external data source via a text driver.
> Another option is build your pivot table in code, not using the Excel
pivot
> table object.
> 
> RBS

I do sometimes use the option of building the Pivottable through usage of
the SELECT  and GROUP BY. However, it is then frozen in that form. The nice
thing about Pivottables is that the row and column fields can be changed on
the spur to get a new view of the data.

As for text files I find that they introduce yet another layer. The data is
already coming from somewhere else then stored in SQLite, and now they then
have to go to a text file to then be imported to Excel. Also, this may
create new challenges with the data types being recognized correctly, and
more importantly: I could not find a way to programmatically get Excel to
take a text file as basis of a Pivottable. Sure, doing it manually is no
problem at all but back with Excel 2003, I and some others tried to get it
done through Automation (Excels COM object model) -- it simple could not be
done (I almost suspect this was so by design from MS). Perhaps it is
different in Excel 2007 / 2010, but I could imagine not.

All in all it would be nice to just use the SQLite database as a proper data
source like you can with Oracle, SQL server and a number of other databases
/ data sources. But I guess the best for now will be to just accept creating
a DSN for each individual database and use the ODBC driver. Its a bit messy
programmatically as you have to access the Registry but it can be done.

/Frank

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 答复: Is SQLite supporting x64?

[GB]

Richard Hipp schrieb am 25.09.2011 18:46:
> You can see at http://www.sqlite.org/checklists/3070800#c9 that we do 100%
> MC/DC testing on SQLite for both Win32 and Win64 with no issues detected.

But when I do an x64 build with MSVC2010 I get several warnings about
64Bit values being assigned to 32Bit variables, mainly results of
pointer arithmetics being assigned to ints. It doesn't seem that these
issues will result in real problems but you never know... Did I miss
some #defines to do for 64Bit?

regards
gerd
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow JOIN on ROWID

[Fabian]

2011/10/12 Petite Abeille 

>
> Now the join is performed only 250 times, adding just a small overhead
> compare the the bare bone query without the join.
>
> The short of it: minimize the amount of work upfront :)
>
>
Thank you very much! This approach solved the problem. However, in my
situation I need to select a lot more columns than just 'id' from
'mail_header', and when I look at the resulting query it appears it is
selecting all those columns twice.

Would it be smart to change the query so that the inner loop only selects
mail_header.id, and adding a third join that fetches the extra columns from
'mail_header'? Or would the performance penalty from adding a third join
out-weight the advantage of selecting less columns in the inner loop?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables)

[Bart Smissaert]

It looks you can't make a pivot table directly from an array.
What you could do though is write the array to a text file and base the array on
that file as an external data source via a text driver.
Another option is build your pivot table in code, not using the Excel
pivot table
object.

RBS


On Thu, Oct 13, 2011 at 8:44 PM, Bart Smissaert
 wrote:
>> My problem is that the data basis of the Pivottable will sometimes be
> millions of rows
>
> OK, I haven't got that problem and my pivots are based on a sheet range.
> Sheet range is based on a variant array obtained from SQLite.
> I will need to check, but I think you can use an array for the basis of a 
> pivot.
>
> RBS
>
>
> On Thu, Oct 13, 2011 at 8:36 PM, Frank Missel  wrote:
>> Hi Bart,
>>
>>> Interesting. Why, if you had a well performing VB wrapper, did you go this
>>> route?
>>
>> 1. Implementing the wrapper in the project code would also take some coding,
>> and I found that using the C API would not be that much extra work. Thus I
>> could save a layer, which was good as the project had several other layers
>> already.
>>
>> 2. The project required heavy data loads. I thought that I could get better
>> performance and control of data validation, i.e. I could decide exactly how
>> much and what to have.
>>
>> 3. It was a bit fascinating to get to work close to engine -- minimalistic
>> and effective is always fascinating :-).
>>
>>> >  If anyone have any solution for this or any other, easier alternative
>>> > way of
>>> accessing an SQLite database as a data source programmatically
>>>
>>> This is exactly what I do and no problem at all for example to produce a
>> pivot
>>> table based on data from SQLite.
>>
>> Interesting, how do you get the data from the table or view into Excel to be
>> the basis of the Pivottable?
>> Do you paste it to a worksheet (perhaps as arrays) that then becomes the
>> basis of the Pivottable?
>>
>> My problem is that the data basis of the Pivottable will sometimes be
>> millions of rows, i.e. many more than can be contained in a worksheet.
>> But when referencing the data source directly as a proper data source the
>> number of rows are not limited to the maximum number of allowed rows in a
>> worksheet.
>>
>> /Frank
>>
>> ___
>> 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] How to use SQLite as a data source in Excel (tables and pivottables)

[Petite Abeille]

On Oct 13, 2011, at 9:36 PM, Frank Missel wrote:

> Interesting, how do you get the data from the table or view into Excel to be
> the basis of the Pivottable?
> Do you paste it to a worksheet (perhaps as arrays) that then becomes the
> basis of the Pivottable?

Pivot tables can be populated from another worksheet, a data source, an OLAP 
cube, etc...


> My problem is that the data basis of the Pivottable will sometimes be
> millions of rows, i.e. many more than can be contained in a worksheet. 
> But when referencing the data source directly as a proper data source the
> number of rows are not limited to the maximum number of allowed rows in a
> worksheet.

That number is pretty high these days, but yes there is no point using an 
intermediary worksheet. Get the data directly from source. Also, you may want 
to pre-process, e.g. summarize, your data in the database already as much as 
you can before hand. Excel is not a speed daemon when confronted with a truck 
load of data.

You can also create and save the pivot table as an offline OLAP cube, with 
hierarchical dimensions & all (Microsoft Query + OLAP Cube Wizard).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables)

[Bart Smissaert]

> My problem is that the data basis of the Pivottable will sometimes be
millions of rows

OK, I haven't got that problem and my pivots are based on a sheet range.
Sheet range is based on a variant array obtained from SQLite.
I will need to check, but I think you can use an array for the basis of a pivot.

RBS


On Thu, Oct 13, 2011 at 8:36 PM, Frank Missel  wrote:
> Hi Bart,
>
>> Interesting. Why, if you had a well performing VB wrapper, did you go this
>> route?
>
> 1. Implementing the wrapper in the project code would also take some coding,
> and I found that using the C API would not be that much extra work. Thus I
> could save a layer, which was good as the project had several other layers
> already.
>
> 2. The project required heavy data loads. I thought that I could get better
> performance and control of data validation, i.e. I could decide exactly how
> much and what to have.
>
> 3. It was a bit fascinating to get to work close to engine -- minimalistic
> and effective is always fascinating :-).
>
>> >  If anyone have any solution for this or any other, easier alternative
>> > way of
>> accessing an SQLite database as a data source programmatically
>>
>> This is exactly what I do and no problem at all for example to produce a
> pivot
>> table based on data from SQLite.
>
> Interesting, how do you get the data from the table or view into Excel to be
> the basis of the Pivottable?
> Do you paste it to a worksheet (perhaps as arrays) that then becomes the
> basis of the Pivottable?
>
> My problem is that the data basis of the Pivottable will sometimes be
> millions of rows, i.e. many more than can be contained in a worksheet.
> But when referencing the data source directly as a proper data source the
> number of rows are not limited to the maximum number of allowed rows in a
> worksheet.
>
> /Frank
>
> ___
> 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] How to use SQLite as a data source in Excel (tables and pivottables)

[Frank Missel]

Hi Bart,

> Interesting. Why, if you had a well performing VB wrapper, did you go this
> route?

1. Implementing the wrapper in the project code would also take some coding,
and I found that using the C API would not be that much extra work. Thus I
could save a layer, which was good as the project had several other layers
already.

2. The project required heavy data loads. I thought that I could get better
performance and control of data validation, i.e. I could decide exactly how
much and what to have.

3. It was a bit fascinating to get to work close to engine -- minimalistic
and effective is always fascinating :-).

> >  If anyone have any solution for this or any other, easier alternative
> > way of
> accessing an SQLite database as a data source programmatically
> 
> This is exactly what I do and no problem at all for example to produce a
pivot
> table based on data from SQLite.

Interesting, how do you get the data from the table or view into Excel to be
the basis of the Pivottable?
Do you paste it to a worksheet (perhaps as arrays) that then becomes the
basis of the Pivottable?

My problem is that the data basis of the Pivottable will sometimes be
millions of rows, i.e. many more than can be contained in a worksheet. 
But when referencing the data source directly as a proper data source the
number of rows are not limited to the maximum number of allowed rows in a
worksheet.

/Frank

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can DBI supply a more specific foreign key mismatch error?

[Petite Abeille]

On Oct 13, 2011, at 9:17 PM, Dan Kennedy wrote:

> It means there is a problem with a foreign key definition in the
> database schema. Either an FK specifies parent columns that do not
> exist, or parent columns that are not a PRIMARY KEY or UNIQUE.

It also mean there is a problem in how SQLite reports such issue... each 
constraint has a name... providing that name when reporting such error would 
have been helpful... sigh...

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables)

[Bart Smissaert]

Hi Frank,

> So when referencing an SQLite database from Excel you also use the ODBC
driver I guess.

I don't access SQLite this way. Only access through this VB wrapper.

> I now use the C API directly

Interesting. Why, if you had a well performing VB wrapper, did you go
this route?

>  If anyone have any solution for this or any other, easier alternative way of
accessing an SQLite database as a data source programmatically

This is exactly what I do and no problem at all for example to produce
a pivot table
based on data from SQLite.

RBS


On Thu, Oct 13, 2011 at 8:10 PM, Frank Missel  wrote:
> Hi Bart,
>
>> boun...@sqlite.org] On Behalf Of Bart Smissaert
>> No, the wrapper is not used that way and I don't think it can be used that
>> way.
>> The SQLite database is dealt with in VBA or VB6 code via this wrapper.
>> I suppose you could compare it to using ADO with a DSN-less connection.
>>
>> RBS
>
> Okay, that's what I thought.
> I did try the wrapper a couple of years ago and found it very well designed
> and performing; I can also recommend it for VB 6, VBA or VBScript.
> I now use the C API directly -- sort of my own wrapper for some special
> purposes.
>
> So when referencing an SQLite database from Excel you also use the ODBC
> driver I guess.
> This brings me to my main remaining issue which is to avoid having to create
> an individual data source for each SQLite database.
>
> If anyone have any solution for this or any other, easier alternative way of
> accessing an SQLite database as a data source programmatically through the
> Excel COM object model (in order to e.g. create a Pivottable), I would be
> very eager to hear about it :-).
>
>
> /Frank
>
> ___
> 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] Can DBI supply a more specific foreign key mismatch error?

[Dan Kennedy]

On 10/14/2011 01:29 AM, fe...@crowfix.com wrote:

I'm working on a project which generates tables from a config file,
and it seems to be happy on a Mac OSX running 3.4.0, but Linux running
3.7.8 complains about an insert with the unhelpful message

 (foreign key mismatch)


It means there is a problem with a foreign key definition in the
database schema. Either an FK specifies parent columns that do not
exist, or parent columns that are not a PRIMARY KEY or UNIQUE.


I've checked all the data and don't see what it is complaining about,
and if I dump the SQL to a file and execute the same commands that
way, it works, and so does adding the single record in question using
sqlite3.


Error message only shows up if foreign keys are enabled ("PRAGMA
foreign_keys = ON"). They are disabled by default.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables)

[Frank Missel]

Hi Bart,

> boun...@sqlite.org] On Behalf Of Bart Smissaert
> No, the wrapper is not used that way and I don't think it can be used that
> way.
> The SQLite database is dealt with in VBA or VB6 code via this wrapper.
> I suppose you could compare it to using ADO with a DSN-less connection.
> 
> RBS

Okay, that's what I thought. 
I did try the wrapper a couple of years ago and found it very well designed
and performing; I can also recommend it for VB 6, VBA or VBScript.
I now use the C API directly -- sort of my own wrapper for some special
purposes.

So when referencing an SQLite database from Excel you also use the ODBC
driver I guess.
This brings me to my main remaining issue which is to avoid having to create
an individual data source for each SQLite database.

If anyone have any solution for this or any other, easier alternative way of
accessing an SQLite database as a data source programmatically through the
Excel COM object model (in order to e.g. create a Pivottable), I would be
very eager to hear about it :-).
 

/Frank

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables)

[Bart Smissaert]

Hi Frank,

> But are you saying that you are use the wrapper itself as a data source in
Excel?

No, the wrapper is not used that way and I don't think it can be used that way.
The SQLite database is dealt with in VBA or VB6 code via this wrapper.
I suppose you could compare it to using ADO with a DSN-less connection.

RBS


On Thu, Oct 13, 2011 at 7:41 PM, Frank Missel  wrote:
> Hi Bart,
>
> Okay, but I thought that the wrapper was just for working with the SQLite
> database and then later when you wanted to use the database as a data source
> that you would then still use the ODBC driver.
>
> But are you saying that you are use the wrapper itself as a data source in
> Excel?
> And if so, how do you specify the wrapper as a data source?
>
>
> /Frank
>
>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>> boun...@sqlite.org] On Behalf Of Bart Smissaert
>> Sent: 14 October 2011 02:21
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] How to use SQLite as a data source in Excel (tables
> and
>> pivottables)
>>
>> Hi Frank,
>>
>> This VB wrapper is not an ODBC driver, so there is no DSN.
>> The database file is set in the connection string:
>>
>> Function OpenDB([FileName As String],
>>                            [EncrKey As String],
>>                            [EnableVBFunctions As Boolean = True]) As
> Boolean
>>
>> Member of vbRichClient4.cConnection
>>
>>
>> RBS
>>
>>
>> On Thu, Oct 13, 2011 at 7:11 PM, Frank Missel  wrote:
>> > Bart, thanks for the offer, but we found the cause of the problems in
>> > the data type naming of the columns.
>> >
>> > Have you found a way to avoid having to define DSN's for each
>> > individual database?
>> >
>> > /Frank
>> >
>
> ___
> 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] How to use SQLite as a data source in Excel (tables and pivottables)

[Frank Missel]

Hi Bart,

Okay, but I thought that the wrapper was just for working with the SQLite
database and then later when you wanted to use the database as a data source
that you would then still use the ODBC driver.

But are you saying that you are use the wrapper itself as a data source in
Excel? 
And if so, how do you specify the wrapper as a data source?


/Frank


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Bart Smissaert
> Sent: 14 October 2011 02:21
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] How to use SQLite as a data source in Excel (tables
and
> pivottables)
> 
> Hi Frank,
> 
> This VB wrapper is not an ODBC driver, so there is no DSN.
> The database file is set in the connection string:
> 
> Function OpenDB([FileName As String],
>[EncrKey As String],
>[EnableVBFunctions As Boolean = True]) As
Boolean
> 
> Member of vbRichClient4.cConnection
> 
> 
> RBS
> 
> 
> On Thu, Oct 13, 2011 at 7:11 PM, Frank Missel  wrote:
> > Bart, thanks for the offer, but we found the cause of the problems in
> > the data type naming of the columns.
> >
> > Have you found a way to avoid having to define DSN's for each
> > individual database?
> >
> > /Frank
> >

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can DBI supply a more specific foreign key mismatch error?

[felix]

I'm working on a project which generates tables from a config file,
and it seems to be happy on a Mac OSX running 3.4.0, but Linux running
3.7.8 complains about an insert with the unhelpful message

(foreign key mismatch)

I've checked all the data and don't see what it is complaining about,
and if I dump the SQL to a file and execute the same commands that
way, it works, and so does adding the single record in question using
sqlite3.

Is there some way to get more details on what mismatched, such as
turning on a debug flag, upping verbosity, or using DBI's trace
mechanism (which I once used ages ok and remember as being meant for
DBI/DBD developers rather than mere SQL hackers :-)

-- 
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
 Felix Finch: scarecrow repairman & rocket surgeon / fe...@crowfix.com
  GPG = E987 4493 C860 246C 3B1E  6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] MyJSQLView Version 3.31 Released

[danap]

MyJSQLView Version 3.31 Released

The MyJSQLView project is pleased to release v3.31 to the public.
Included with this update is several corrections for bugs involved
with the import of large SQL dumps and table definition generation
for unique keys. A multi-language PDF export inclusion is now
implemented via unicode embedded fonts. The Query Frame in the
application has now been expanded to allow input of direct SQL
statements for execution.

Dana M. Proctor
MyJSQLView Project Manager
http://dandymadeproductions.com/projects/MyJSQLView/

MyJSQLView provides an easy to use free Open Source Java based user
interface frontend for viewing, adding, editing, or deleting entries
in several mainstream databases including SQLite. A query frame allows
the building of complex SQL statements. The application allows easy
sorting, searching, and import/export of table data.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables)

[Bart Smissaert]

Hi Frank,

This VB wrapper is not an ODBC driver, so there is no DSN.
The database file is set in the connection string:

Function OpenDB([FileName As String],
   [EncrKey As String],
   [EnableVBFunctions As Boolean = True]) As Boolean

Member of vbRichClient4.cConnection


RBS


On Thu, Oct 13, 2011 at 7:11 PM, Frank Missel  wrote:
> Bart, thanks for the offer, but we found the cause of the problems in the
> data type naming of the columns.
>
> Have you found a way to avoid having to define DSN's for each individual
> database?
>
> /Frank
>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>> boun...@sqlite.org] On Behalf Of Bart Smissaert
>> Sent: 14 October 2011 01:35
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] How to use SQLite as a data source in Excel (tables
> and
>> pivottables)
>>
>> I use SQLite extensively as a data source in Excel and have never come
> across
>> this problem.
>> Is use Olaf Schmidt's VB wrapper vbRichClient4 and vb_cairo_sqlite.
>> If you can send me a workbook that clearly demonstrates the problem then I
>> can see if I can deal with it with the above wrapper. I am sure there will
> be no
>> problem at all.
>>
>> RBS
>>
>
> ___
> 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] How to use SQLite as a data source in Excel (tables and pivottables)

[Frank Missel]

Bart, thanks for the offer, but we found the cause of the problems in the
data type naming of the columns.

Have you found a way to avoid having to define DSN's for each individual
database?

/Frank

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Bart Smissaert
> Sent: 14 October 2011 01:35
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] How to use SQLite as a data source in Excel (tables
and
> pivottables)
> 
> I use SQLite extensively as a data source in Excel and have never come
across
> this problem.
> Is use Olaf Schmidt's VB wrapper vbRichClient4 and vb_cairo_sqlite.
> If you can send me a workbook that clearly demonstrates the problem then I
> can see if I can deal with it with the above wrapper. I am sure there will
be no
> problem at all.
> 
> RBS
> 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables)

[Frank Missel]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Simon Slavin
> 
> would something like
> 
> SELECT CAST (theValue AS REAL) AS thisValueReal FROM ...
> 
> force the driver to recognise that the value it was getting was REAL ?
> 
> Simon.

I did not have high hopes, but it did work!

That led me to wonder why that could be. I found out that it has to do with
the declared type name of the column.
"DECIMAL" is no good. However, if you declare a column as "REAL" it works
without any CAST function.
If you column is an expression, however, you still have to use the CAST
function. But at least it can be brought to work.

Worth mentioning is that the cell format is still "General" but it now
really works as a decimal (i.e. you can sum on it).

The above is surprising as Christian Werner writes the following in the
documentation:

" Since October 14th, 2001, the driver supports the data types SQL_INTEGER,
SQL_TINYINT, SQL_SMALLINT, SQL_FLOAT, SQL_DOUBLE, SQL_DATE, SQL_TIME,
SQL_TIMESTAMP, and SQL_VARCHAR."

However, for it to work in Excel, you have to define columns as one of the
following:
INTEGER
REAL
DATE
VARCHAR

I'll let him know.

--

Still, if anyone knows how to use the ADO.NET driver that could also be
interesting, since the ODBC driver is a bit bothersome in that you
apparently have to define a Data Source Name for each individual database
that you want to access -- I haven't found any way to work around that. If
one chooses just the "SQLite3 Datasource" as data source there is an error
message to the effect that the data source contains no visible tables. For
other data sources a particular database can be specified as part of the
selection process.


/Frank Missel

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Malformed database error when using FTS3/4

[Dan Kennedy]

On 10/13/2011 10:45 PM, Wendland, Michael wrote:

I've encountered an error running 3.7.8 and 3.7.7.1 that seems rather strange.

CREATE VIRTUAL TABLE fts USING fts3( tags TEXT);
INSERT INTO fts (tags) VALUES ('tag1');
SELECT * FROM fts WHERE tags MATCH 'tag1';

Now close the database and reopen it, then try the following statements.

 UPDATE fts SET tags = 'tag1' WHERE rowid = 1;
 SELECT * FROM fts WHERE tags MATCH 'tag1';

Is anyone able to help?  (I haven't accidentally posted this to the wrong list 
have I?)


Thanks for reporting this. Now fixed here:

  http://www.sqlite.org/src/info/7e24645be2

Bug report:

  http://www.sqlite.org/src/info/9fd058691b

You are correct in that the bug only occurs when the FTS
table contains exactly one row. Bug was introduced in version
3.7.7.

Dan.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables)

[Bart Smissaert]

I use SQLite extensively as a data source in Excel and have never come
across this problem.
Is use Olaf Schmidt's VB wrapper vbRichClient4 and vb_cairo_sqlite.
If you can send me a workbook that clearly demonstrates the problem
then I can see if
I can deal with it with the above wrapper. I am sure there will be no
problem at all.

RBS


On Thu, Oct 13, 2011 at 5:48 PM, Frank Missel  wrote:
> I am trying to use data from an SQLite database as a data source for a
> Pivottable in an Excel sheet.
>
>
>
> By use of Micrsoft Query and the SQLite ODBC driver I can reference a table
> from an SQLite database either as a list in a worksheet or as basis for a
> Pivottable. This apparently works fine. However, there is an issue with the
> decimal data type which is not recognized, i.e. the cells are of the type
> "General" whereas the integer and date fields are represented with the
> correct cell format and function. Even if one does change the format of the
> cells containing data from a decimal field to "Number", they still do not
> work properly as numbers, i.e. the sum function does not work correctly.
>
>
>
> I have emailed with the author of the SQLite ODBC driver, Christian Werner,
> about the problem. He writes:
>
>
>
> "The problem is the typelessness of SQLite. In order to obtain column
> information early, a SELECT is prepared twice. The first gives the column
> names and potential type information.
>
> In the second phase the second select retrieves data. For computed columns,
> SQLite usually reports SQLITE_TEXT or even SQLITE_NULL in
> sqlite3_column_type. As long as an ODBC application retrieves in advance the
> correct typed values of a rowset, everything is fine. But that seems not to
> be the case for Excel/Query."
>
>
>
> I have also installed the System.SQlite.Data ADO.NET driver in the hope that
> perhaps it could be used as an OLE DB data source or other type of data
> source that could be chosen from Excel. However, it does not show in the
> various list of data sources so perhaps that is not possible.
>
>
>
> Does anyone have experience and/or ideas about how to use SQLite as a proper
> data source that can be accessed from Excel besides the ODBC driver which
> has the mentioned problems with decimal fields?
>
>
>
>
>
> /Frank Missel
>
> ___
> 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] How to use SQLite as a data source in Excel (tables and pivottables)

[Simon Slavin]

On 13 Oct 2011, at 5:48pm, Frank Missel wrote:

> "The problem is the typelessness of SQLite. In order to obtain column
> information early, a SELECT is prepared twice. The first gives the column
> names and potential type information.
> 
> In the second phase the second select retrieves data. For computed columns,
> SQLite usually reports SQLITE_TEXT or even SQLITE_NULL in
> sqlite3_column_type. As long as an ODBC application retrieves in advance the
> correct typed values of a rowset, everything is fine. But that seems not to
> be the case for Excel/Query."

would something like

SELECT CAST (theValue AS REAL) AS thisValueReal FROM ...

force the driver to recognise that the value it was getting was REAL ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to use SQLite as a data source in Excel (tables and pivottables)

[Frank Missel]

I am trying to use data from an SQLite database as a data source for a
Pivottable in an Excel sheet.

 

By use of Micrsoft Query and the SQLite ODBC driver I can reference a table
from an SQLite database either as a list in a worksheet or as basis for a
Pivottable. This apparently works fine. However, there is an issue with the
decimal data type which is not recognized, i.e. the cells are of the type
"General" whereas the integer and date fields are represented with the
correct cell format and function. Even if one does change the format of the
cells containing data from a decimal field to "Number", they still do not
work properly as numbers, i.e. the sum function does not work correctly.

 

I have emailed with the author of the SQLite ODBC driver, Christian Werner,
about the problem. He writes:

 

"The problem is the typelessness of SQLite. In order to obtain column
information early, a SELECT is prepared twice. The first gives the column
names and potential type information.

In the second phase the second select retrieves data. For computed columns,
SQLite usually reports SQLITE_TEXT or even SQLITE_NULL in
sqlite3_column_type. As long as an ODBC application retrieves in advance the
correct typed values of a rowset, everything is fine. But that seems not to
be the case for Excel/Query."

 

I have also installed the System.SQlite.Data ADO.NET driver in the hope that
perhaps it could be used as an OLE DB data source or other type of data
source that could be chosen from Excel. However, it does not show in the
various list of data sources so perhaps that is not possible.

 

Does anyone have experience and/or ideas about how to use SQLite as a proper
data source that can be accessed from Excel besides the ODBC driver which
has the mentioned problems with decimal fields?

 

 

/Frank Missel

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Malformed database error when using FTS3/4

[Tim Streater]

On 13 Oct 2011 at 16:59, Filip Navara  wrote: 

> Reproduced on Windows, SQLite 3.7.8.

Seems OK here, OS X 10.7.2:

Second-Mini% sqlite3 wiggy
-- Loading resources from /Users/tim/.sqliterc
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE VIRTUAL TABLE fts USING fts3( tags TEXT);
sqlite> INSERT INTO fts (tags) VALUES ('tag1');
sqlite> SELECT * FROM fts WHERE tags MATCH 'tag1';
tags  
--
tag1  
sqlite> ^D
Second-Mini% sqlite3 wiggy
-- Loading resources from /Users/tim/.sqliterc
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> UPDATE fts SET tags = 'tag1' WHERE rowid = 1;
sqlite> SELECT * FROM fts WHERE tags MATCH 'tag1';
tags  
--
tag1  
sqlite> 


--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Malformed database error when using FTS3/4

[Petite Abeille]

On Oct 13, 2011, at 5:59 PM, Filip Navara wrote:

> Reproduced on Windows, SQLite 3.7.8.

Works ok on Mac OS X 10.6.8.

$ sqlite3 -version
3.7.8 2011-09-19 14:49:19 3e0da808d2f5b4d12046e05980ca04578f581177


sqlite> CREATE VIRTUAL TABLE fts USING fts3( tags TEXT);
sqlite> INSERT INTO fts (tags) VALUES ('tag1');
sqlite> SELECT * FROM fts WHERE tags MATCH 'tag1';
tag1
sqlite> UPDATE fts SET tags = 'tag1' WHERE rowid = 1;
sqlite> SELECT * FROM fts WHERE tags MATCH 'tag1';
tag1


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Malformed database error when using FTS3/4

[Filip Navara]

Reproduced on Windows, SQLite 3.7.8.

F.

On Thu, Oct 13, 2011 at 5:45 PM, Wendland, Michael
 wrote:
> Hello,
>
> I've encountered an error running 3.7.8 and 3.7.7.1 that seems rather 
> strange.  It's reproducible so far as I know.
>
> CREATE VIRTUAL TABLE fts USING fts3( tags TEXT);
> INSERT INTO fts (tags) VALUES ('tag1');
> SELECT * FROM fts WHERE tags MATCH 'tag1';
>
> You can run an UPDATE to change the value in the tags field and rerun that 
> SELECT statement without error.  Do not create a second row, as this error 
> seems to appear only when there is a single row in the virtual table.  Now 
> close the database and reopen it, then try the following statements.
>
>                UPDATE fts SET tags = 'tag1' WHERE rowid = 1;
>                SELECT * FROM fts WHERE tags MATCH 'tag1';
>
> That SELECT returns "Error: database disk image is malformed".  Taking a dump 
> before and after the UPDATE shows that a segdir entry is being assigned a 
> different value despite the tags entry remaining exactly the same.  Changing 
> the value further does not rectify the situation, nor does using a different 
> string.  Running a "PRAGMA integrity_check" returns "ok", so the underlying 
> database is fine (and you can use other tables and any rows you insert after 
> just fine).  Inserting a new row and then changing the value rectifies the 
> problem.  Tests seem to indicate that it doesn't matter what the rowid is (or 
> the  sequence of inserts and deletes have been run on the virtual table 
> beforehand), the problem appears when changing the only row in the virtual 
> table.
>
> I've reproduced this using a CLI compiled from the autoconf source 
> amalgamation on RHEL 5.5 (in a VM) and the precompiled Windows CLI.
>
> Is anyone able to help?  (I haven't accidentally posted this to the wrong 
> list have I?)
>
> - Michael
> ___
> 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] Malformed database error when using FTS3/4

[Wendland, Michael]

Hello,

I've encountered an error running 3.7.8 and 3.7.7.1 that seems rather strange.  
It's reproducible so far as I know.

CREATE VIRTUAL TABLE fts USING fts3( tags TEXT);
INSERT INTO fts (tags) VALUES ('tag1');
SELECT * FROM fts WHERE tags MATCH 'tag1';

You can run an UPDATE to change the value in the tags field and rerun that 
SELECT statement without error.  Do not create a second row, as this error 
seems to appear only when there is a single row in the virtual table.  Now 
close the database and reopen it, then try the following statements.

UPDATE fts SET tags = 'tag1' WHERE rowid = 1;
SELECT * FROM fts WHERE tags MATCH 'tag1';

That SELECT returns "Error: database disk image is malformed".  Taking a dump 
before and after the UPDATE shows that a segdir entry is being assigned a 
different value despite the tags entry remaining exactly the same.  Changing 
the value further does not rectify the situation, nor does using a different 
string.  Running a "PRAGMA integrity_check" returns "ok", so the underlying 
database is fine (and you can use other tables and any rows you insert after 
just fine).  Inserting a new row and then changing the value rectifies the 
problem.  Tests seem to indicate that it doesn't matter what the rowid is (or 
the  sequence of inserts and deletes have been run on the virtual table 
beforehand), the problem appears when changing the only row in the virtual 
table.

I've reproduced this using a CLI compiled from the autoconf source amalgamation 
on RHEL 5.5 (in a VM) and the precompiled Windows CLI.

Is anyone able to help?  (I haven't accidentally posted this to the wrong list 
have I?)

- Michael
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] internal column header not case sensitive?

[Richard Hipp]

On Thu, Oct 13, 2011 at 9:52 AM, Nelson, Bjorn <
bjorn.nel...@morganstanley.com> wrote:

>
> This seems to be a bug.  Is there a way to submit this as a bug
> somewhere
>


http://www.sqlite.org/src/info/fa7bf5ec94801e7e
http://www.sqlite.org/src/info/e43da426e6

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma PAGE_COUNT != pragma page_count

[Black, Michael (IS)]

Thanks for the quick fix (Microsoft comparison withheld to protect the 
guilty...)



But...

zLeft[0]&0xf  



A bit obtuse, isn't it?  And relying on luck 'o the lettersand not bullet 
proof if another pragma is added.



Wouldn't this be much clearer and future proof?



if (UpperToLower(zLeft[0])=='p') {



int isQuick = (UpperToLower(zLeft[0])=='q');



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Pavel Ivanov [paiva...@gmail.com]
Sent: Thursday, October 13, 2011 9:45 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] pragma PAGE_COUNT != pragma page_count

FYI: http://www.sqlite.org/src/info/150592b4b4.

Pavel


On Thu, Oct 13, 2011 at 10:34 AM, Black, Michael (IS)
 wrote:
> Or rather user UpperToLower...I just found that one...
>
>
>
>
>
> if( UpperToLower(zLeft[0])=='p' ){
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Black, Michael (IS) [michael.bla...@ngc.com]
> Sent: Thursday, October 13, 2011 9:30 AM
> To: marshall.cl...@parashift.com; General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] pragma PAGE_COUNT != pragma page_count
>
> Easy fix me thinkst...this section was only check for lower-case 'p' -- 
> otherwise returning MaxPgCnt.
>
> so pAGECOUNT works OK.  Just add the tolower()...
>
>
>
>  if( sqlite3StrICmp(zLeft,"page_count")==0
>   || sqlite3StrICmp(zLeft,"max_page_count")==0
>  ){
>int iReg;
>if( sqlite3ReadSchema(pParse) ) goto pragma_out;
>sqlite3CodeVerifySchema(pParse, iDb);
>iReg = ++pParse->nMem;
>if( tolower(zLeft[0])=='p' ){
>  sqlite3VdbeAddOp2(v, OP_Pagecount, iDb, iReg);
>}else{
>  sqlite3VdbeAddOp3(v, OP_MaxPgcnt, iDb, iReg, sqlite3Atoi(zRight));
>}
>sqlite3VdbeAddOp2(v, OP_ResultRow, iRe
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Marshall Cline [marshall.cl...@parashift.com]
> Sent: Thursday, October 13, 2011 9:21 AM
> To: sqlite-users@sqlite.org
> Subject: EXT :[sqlite] pragma PAGE_COUNT != pragma page_count
>
> Symptoms: "pragma page_count" returns the correct number of pages, but
> "pragma PAGE_COUNT" always returns 1073741823:
>
>
>
> % sqlite3 sample.db
>
> SQLite version 3.7.8 2011-09-19 14:49:19
>
> Enter ".help" for instructions
>
> Enter SQL statements terminated with a ";"
>
> sqlite> pragma page_count;
>
> 3
>
> sqlite> pragma PAGE_COUNT;
>
> 1073741823
>
>
>
>
>
> Cause: in the code below, the line "if( zLeft[0]=='p' ){" does a
> case-sensitive comparison - if the first letter isn't lower-case 'p', the
> code assumes the pragma must be max_page_count.
>
>
>
>  /*
>
>  **  PRAGMA [database.]max_page_count
>
>  **  PRAGMA [database.]max_page_count=N
>
>  **
>
>  ** The first form reports the current setting for the
>
>  ** maximum number of pages in the database file.  The
>
>  ** second form attempts to change this setting.  Both
>
>  ** forms return the current setting.
>
>  **
>
>  **  PRAGMA [database.]page_count
>
>  **
>
>  ** Return the number of pages in the specified database.
>
>  */
>
>  if( sqlite3StrICmp(zLeft,"page_count")==0
>
>   || sqlite3StrICmp(zLeft,"max_page_count")==0
>
>  ){
>
>int iReg;
>
>if( sqlite3ReadSchema(pParse) ) goto pragma_out;
>
>sqlite3CodeVerifySchema(pParse, iDb);
>
>iReg = ++pParse->nMem;
>
>if( zLeft[0]=='p' ){
>
>  sqlite3VdbeAddOp2(v, OP_Pagecount, iDb, iReg);
>
>}else{
>
>  sqlite3VdbeAddOp3(v, OP_MaxPgcnt, iDb, iReg, sqlite3Atoi(zRight));
>
>}
>
>sqlite3VdbeAddOp2(v, OP_ResultRow, iReg, 1);
>
>sqlite3VdbeSetNumCols(v, 1);
>
>sqlite3VdbeSetColName(v, 0, COLNAME_NAME, zLeft, SQLITE_TRANSIENT);
>
>  }else
>
>
>
>
>
> Thank you,
>
> Marshall
>
>
>
> ___
> 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
>
___
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

Re: [sqlite] pragma PAGE_COUNT != pragma page_count

[Pavel Ivanov]

FYI: http://www.sqlite.org/src/info/150592b4b4.

Pavel


On Thu, Oct 13, 2011 at 10:34 AM, Black, Michael (IS)
 wrote:
> Or rather user UpperToLower...I just found that one...
>
>
>
>
>
> if( UpperToLower(zLeft[0])=='p' ){
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Black, Michael (IS) [michael.bla...@ngc.com]
> Sent: Thursday, October 13, 2011 9:30 AM
> To: marshall.cl...@parashift.com; General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] pragma PAGE_COUNT != pragma page_count
>
> Easy fix me thinkst...this section was only check for lower-case 'p' -- 
> otherwise returning MaxPgCnt.
>
> so pAGECOUNT works OK.  Just add the tolower()...
>
>
>
>  if( sqlite3StrICmp(zLeft,"page_count")==0
>   || sqlite3StrICmp(zLeft,"max_page_count")==0
>  ){
>    int iReg;
>    if( sqlite3ReadSchema(pParse) ) goto pragma_out;
>    sqlite3CodeVerifySchema(pParse, iDb);
>    iReg = ++pParse->nMem;
>    if( tolower(zLeft[0])=='p' ){
>      sqlite3VdbeAddOp2(v, OP_Pagecount, iDb, iReg);
>    }else{
>      sqlite3VdbeAddOp3(v, OP_MaxPgcnt, iDb, iReg, sqlite3Atoi(zRight));
>    }
>    sqlite3VdbeAddOp2(v, OP_ResultRow, iRe
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Marshall Cline [marshall.cl...@parashift.com]
> Sent: Thursday, October 13, 2011 9:21 AM
> To: sqlite-users@sqlite.org
> Subject: EXT :[sqlite] pragma PAGE_COUNT != pragma page_count
>
> Symptoms: "pragma page_count" returns the correct number of pages, but
> "pragma PAGE_COUNT" always returns 1073741823:
>
>
>
> % sqlite3 sample.db
>
> SQLite version 3.7.8 2011-09-19 14:49:19
>
> Enter ".help" for instructions
>
> Enter SQL statements terminated with a ";"
>
> sqlite> pragma page_count;
>
> 3
>
> sqlite> pragma PAGE_COUNT;
>
> 1073741823
>
>
>
>
>
> Cause: in the code below, the line "if( zLeft[0]=='p' ){" does a
> case-sensitive comparison - if the first letter isn't lower-case 'p', the
> code assumes the pragma must be max_page_count.
>
>
>
>  /*
>
>  **  PRAGMA [database.]max_page_count
>
>  **  PRAGMA [database.]max_page_count=N
>
>  **
>
>  ** The first form reports the current setting for the
>
>  ** maximum number of pages in the database file.  The
>
>  ** second form attempts to change this setting.  Both
>
>  ** forms return the current setting.
>
>  **
>
>  **  PRAGMA [database.]page_count
>
>  **
>
>  ** Return the number of pages in the specified database.
>
>  */
>
>  if( sqlite3StrICmp(zLeft,"page_count")==0
>
>   || sqlite3StrICmp(zLeft,"max_page_count")==0
>
>  ){
>
>    int iReg;
>
>    if( sqlite3ReadSchema(pParse) ) goto pragma_out;
>
>    sqlite3CodeVerifySchema(pParse, iDb);
>
>    iReg = ++pParse->nMem;
>
>    if( zLeft[0]=='p' ){
>
>      sqlite3VdbeAddOp2(v, OP_Pagecount, iDb, iReg);
>
>    }else{
>
>      sqlite3VdbeAddOp3(v, OP_MaxPgcnt, iDb, iReg, sqlite3Atoi(zRight));
>
>    }
>
>    sqlite3VdbeAddOp2(v, OP_ResultRow, iReg, 1);
>
>    sqlite3VdbeSetNumCols(v, 1);
>
>    sqlite3VdbeSetColName(v, 0, COLNAME_NAME, zLeft, SQLITE_TRANSIENT);
>
>  }else
>
>
>
>
>
> Thank you,
>
> Marshall
>
>
>
> ___
> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma PAGE_COUNT != pragma page_count

[Black, Michael (IS)]

Or rather user UpperToLower...I just found that one...





if( UpperToLower(zLeft[0])=='p' ){



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Black, Michael (IS) [michael.bla...@ngc.com]
Sent: Thursday, October 13, 2011 9:30 AM
To: marshall.cl...@parashift.com; General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] pragma PAGE_COUNT != pragma page_count

Easy fix me thinkst...this section was only check for lower-case 'p' -- 
otherwise returning MaxPgCnt.

so pAGECOUNT works OK.  Just add the tolower()...



  if( sqlite3StrICmp(zLeft,"page_count")==0
   || sqlite3StrICmp(zLeft,"max_page_count")==0
  ){
int iReg;
if( sqlite3ReadSchema(pParse) ) goto pragma_out;
sqlite3CodeVerifySchema(pParse, iDb);
iReg = ++pParse->nMem;
if( tolower(zLeft[0])=='p' ){
  sqlite3VdbeAddOp2(v, OP_Pagecount, iDb, iReg);
}else{
  sqlite3VdbeAddOp3(v, OP_MaxPgcnt, iDb, iReg, sqlite3Atoi(zRight));
}
sqlite3VdbeAddOp2(v, OP_ResultRow, iRe



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Marshall Cline [marshall.cl...@parashift.com]
Sent: Thursday, October 13, 2011 9:21 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] pragma PAGE_COUNT != pragma page_count

Symptoms: "pragma page_count" returns the correct number of pages, but
"pragma PAGE_COUNT" always returns 1073741823:



% sqlite3 sample.db

SQLite version 3.7.8 2011-09-19 14:49:19

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> pragma page_count;

3

sqlite> pragma PAGE_COUNT;

1073741823





Cause: in the code below, the line "if( zLeft[0]=='p' ){" does a
case-sensitive comparison - if the first letter isn't lower-case 'p', the
code assumes the pragma must be max_page_count.



  /*

  **  PRAGMA [database.]max_page_count

  **  PRAGMA [database.]max_page_count=N

  **

  ** The first form reports the current setting for the

  ** maximum number of pages in the database file.  The

  ** second form attempts to change this setting.  Both

  ** forms return the current setting.

  **

  **  PRAGMA [database.]page_count

  **

  ** Return the number of pages in the specified database.

  */

  if( sqlite3StrICmp(zLeft,"page_count")==0

   || sqlite3StrICmp(zLeft,"max_page_count")==0

  ){

int iReg;

if( sqlite3ReadSchema(pParse) ) goto pragma_out;

sqlite3CodeVerifySchema(pParse, iDb);

iReg = ++pParse->nMem;

if( zLeft[0]=='p' ){

  sqlite3VdbeAddOp2(v, OP_Pagecount, iDb, iReg);

}else{

  sqlite3VdbeAddOp3(v, OP_MaxPgcnt, iDb, iReg, sqlite3Atoi(zRight));

}

sqlite3VdbeAddOp2(v, OP_ResultRow, iReg, 1);

sqlite3VdbeSetNumCols(v, 1);

sqlite3VdbeSetColName(v, 0, COLNAME_NAME, zLeft, SQLITE_TRANSIENT);

  }else





Thank you,

Marshall



___
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] pragma PAGE_COUNT != pragma page_count

[Black, Michael (IS)]

Easy fix me thinkst...this section was only check for lower-case 'p' -- 
otherwise returning MaxPgCnt.

so pAGECOUNT works OK.  Just add the tolower()...



  if( sqlite3StrICmp(zLeft,"page_count")==0
   || sqlite3StrICmp(zLeft,"max_page_count")==0
  ){
int iReg;
if( sqlite3ReadSchema(pParse) ) goto pragma_out;
sqlite3CodeVerifySchema(pParse, iDb);
iReg = ++pParse->nMem;
if( tolower(zLeft[0])=='p' ){
  sqlite3VdbeAddOp2(v, OP_Pagecount, iDb, iReg);
}else{
  sqlite3VdbeAddOp3(v, OP_MaxPgcnt, iDb, iReg, sqlite3Atoi(zRight));
}
sqlite3VdbeAddOp2(v, OP_ResultRow, iRe



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Marshall Cline [marshall.cl...@parashift.com]
Sent: Thursday, October 13, 2011 9:21 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] pragma PAGE_COUNT != pragma page_count

Symptoms: "pragma page_count" returns the correct number of pages, but
"pragma PAGE_COUNT" always returns 1073741823:



% sqlite3 sample.db

SQLite version 3.7.8 2011-09-19 14:49:19

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> pragma page_count;

3

sqlite> pragma PAGE_COUNT;

1073741823





Cause: in the code below, the line "if( zLeft[0]=='p' ){" does a
case-sensitive comparison - if the first letter isn't lower-case 'p', the
code assumes the pragma must be max_page_count.



  /*

  **  PRAGMA [database.]max_page_count

  **  PRAGMA [database.]max_page_count=N

  **

  ** The first form reports the current setting for the

  ** maximum number of pages in the database file.  The

  ** second form attempts to change this setting.  Both

  ** forms return the current setting.

  **

  **  PRAGMA [database.]page_count

  **

  ** Return the number of pages in the specified database.

  */

  if( sqlite3StrICmp(zLeft,"page_count")==0

   || sqlite3StrICmp(zLeft,"max_page_count")==0

  ){

int iReg;

if( sqlite3ReadSchema(pParse) ) goto pragma_out;

sqlite3CodeVerifySchema(pParse, iDb);

iReg = ++pParse->nMem;

if( zLeft[0]=='p' ){

  sqlite3VdbeAddOp2(v, OP_Pagecount, iDb, iReg);

}else{

  sqlite3VdbeAddOp3(v, OP_MaxPgcnt, iDb, iReg, sqlite3Atoi(zRight));

}

sqlite3VdbeAddOp2(v, OP_ResultRow, iReg, 1);

sqlite3VdbeSetNumCols(v, 1);

sqlite3VdbeSetColName(v, 0, COLNAME_NAME, zLeft, SQLITE_TRANSIENT);

  }else





Thank you,

Marshall



___
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] internal column header not case sensitive?

[Pavel Ivanov]

> This seems to be a bug.  Is there a way to submit this as a bug somewhere or 
> is this the proper way for me to raise this to someone's attention that would 
> be interested in fixing this?  Apologies for not understanding the bug 
> submission process better.

I'd say this is definitely a bug. And this mailing list is a proper
way to report bugs.


Pavel


On Thu, Oct 13, 2011 at 9:52 AM, Nelson, Bjorn
 wrote:
>
> On Oct 12, 2011, at 5:28 PM, Simon Slavin wrote:
>
>> Was that not what you were expecting ?  You are using count(2) not sum(2).
>
> I don't see why the result I am expecting would be different between count or 
> sum.  I wasn't try to get the values, 1 or 2 just results in a unique 
> difference but the counts should be different and they aren't.  With sum or 
> count, the result would only show the first column in both resulting columns, 
> and ignore that there were actually two unique columns, due to case 
> sensitivity in the expression, even with a unique AS label.  I would think 
> that it would at least see the AS label and use that as a reference point if 
> we couldn't go with assuming that expressions as column headers are case 
> sensitive.
>
> This seems to be a bug.  Is there a way to submit this as a bug somewhere or 
> is this the proper way for me to raise this to someone's attention that would 
> be interested in fixing this?  Apologies for not understanding the bug 
> submission process better.
>
> -Bjorn
>
> --
> NOTICE: Morgan Stanley is not acting as a municipal advisor and the opinions 
> or views contained herein are not intended to be, and do not constitute, 
> advice within the meaning of Section 975 of the Dodd-Frank Wall Street Reform 
> and Consumer Protection Act. If you have received this communication in 
> error, please destroy all electronic and paper copies and notify the sender 
> immediately. Mistransmission is not intended to waive confidentiality or 
> privilege. Morgan Stanley reserves the right, to the extent permitted under 
> applicable law, to monitor electronic communications. This message is subject 
> to terms available at the following link: 
> http://www.morganstanley.com/disclaimers. If you cannot access these links, 
> please notify us by reply message and we will send the contents to you. By 
> messaging with Morgan Stanley you consent to the foregoing.
> ___
> 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] pragma PAGE_COUNT != pragma page_count

[Marshall Cline]

Symptoms: "pragma page_count" returns the correct number of pages, but
"pragma PAGE_COUNT" always returns 1073741823:

 

% sqlite3 sample.db

SQLite version 3.7.8 2011-09-19 14:49:19

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> pragma page_count;

3

sqlite> pragma PAGE_COUNT;

1073741823

 

 

Cause: in the code below, the line "if( zLeft[0]=='p' ){" does a
case-sensitive comparison - if the first letter isn't lower-case 'p', the
code assumes the pragma must be max_page_count.

 

  /*

  **  PRAGMA [database.]max_page_count

  **  PRAGMA [database.]max_page_count=N

  **

  ** The first form reports the current setting for the

  ** maximum number of pages in the database file.  The 

  ** second form attempts to change this setting.  Both

  ** forms return the current setting.

  **

  **  PRAGMA [database.]page_count

  **

  ** Return the number of pages in the specified database.

  */

  if( sqlite3StrICmp(zLeft,"page_count")==0

   || sqlite3StrICmp(zLeft,"max_page_count")==0

  ){

int iReg;

if( sqlite3ReadSchema(pParse) ) goto pragma_out;

sqlite3CodeVerifySchema(pParse, iDb);

iReg = ++pParse->nMem;

if( zLeft[0]=='p' ){

  sqlite3VdbeAddOp2(v, OP_Pagecount, iDb, iReg);

}else{

  sqlite3VdbeAddOp3(v, OP_MaxPgcnt, iDb, iReg, sqlite3Atoi(zRight));

}

sqlite3VdbeAddOp2(v, OP_ResultRow, iReg, 1);

sqlite3VdbeSetNumCols(v, 1);

sqlite3VdbeSetColName(v, 0, COLNAME_NAME, zLeft, SQLITE_TRANSIENT);

  }else

 

 

Thank you,

Marshall

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] internal column header not case sensitive?

[Nelson, Bjorn]

On Oct 12, 2011, at 5:28 PM, Simon Slavin wrote:

> Was that not what you were expecting ?  You are using count(2) not sum(2).

I don't see why the result I am expecting would be different between count or 
sum.  I wasn't try to get the values, 1 or 2 just results in a unique 
difference but the counts should be different and they aren't.  With sum or 
count, the result would only show the first column in both resulting columns, 
and ignore that there were actually two unique columns, due to case sensitivity 
in the expression, even with a unique AS label.  I would think that it would at 
least see the AS label and use that as a reference point if we couldn't go with 
assuming that expressions as column headers are case sensitive.

This seems to be a bug.  Is there a way to submit this as a bug somewhere or is 
this the proper way for me to raise this to someone's attention that would be 
interested in fixing this?  Apologies for not understanding the bug submission 
process better.

-Bjorn

--
NOTICE: Morgan Stanley is not acting as a municipal advisor and the opinions or 
views contained herein are not intended to be, and do not constitute, advice 
within the meaning of Section 975 of the Dodd-Frank Wall Street Reform and 
Consumer Protection Act. If you have received this communication in error, 
please destroy all electronic and paper copies and notify the sender 
immediately. Mistransmission is not intended to waive confidentiality or 
privilege. Morgan Stanley reserves the right, to the extent permitted under 
applicable law, to monitor electronic communications. This message is subject 
to terms available at the following link: 
http://www.morganstanley.com/disclaimers. If you cannot access these links, 
please notify us by reply message and we will send the contents to you. By 
messaging with Morgan Stanley you consent to the foregoing.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "inaccuracy" in code/docu of sqlite3_finalize

[Jay Kreibich]

Sent from my iPhone

On Oct 13, 2011, at 12:45 AM,  Wolfgang Stöcher  wrote:

> From the docu of sqlite3_finalize:
> "The application must finalize every prepared statement [1] in order
> to avoid resource leaks."
> Ok, fine. But the given reason(s) seem not to be complete. After
> preparing and executing the SQL command "PRAGMA page_size;" without
> finalizing the prepared statement, all tables seem to be locked (at
> least any "DROP TABLE"-command fails). See the attached code-sample
> for reproducing this behaviour (in the second call of testSQLt3 with
> callFinalize=false).
> When running into this problem I was looking into the wrong
> direction for some time ...


Did you reset the statement?  Regardless of the statement you need to release 
the locks.  This requires calling _reset or _finalize (which calls _reset).



> The code also demonstrates a second "inaccuracy" that has cost me
> some time: when sqlite3_step returns with SQLITE_ROW, sqlite3_errmsg
> gives "unknown error" instead of something like "row available (no
> error)

The docs are quite clear that the return value of _errmsg is undefined if the 
last SQLite API call succeeds.  SQLITE_ROW is not an error condition.


  -j

> SQLite is a great piece of software. Maybe these hints help others
> to be even more effective with SQLite.
> Thank you for reading,
> Wolfgang
> 
> 
> Links:
> --
> [1] http://www.sqlite.org/c3ref/stmt.html
> ___
> 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] "inaccuracy" in code/docu of sqlite3_finalize

[Wolfgang Stöcher]

>From the docu of sqlite3_finalize:
 "The application must finalize every prepared statement [1] in order
to avoid resource leaks."
 Ok, fine. But the given reason(s) seem not to be complete. After
preparing and executing the SQL command "PRAGMA page_size;" without
finalizing the prepared statement, all tables seem to be locked (at
least any "DROP TABLE"-command fails). See the attached code-sample
for reproducing this behaviour (in the second call of testSQLt3 with
callFinalize=false).
 When running into this problem I was looking into the wrong
direction for some time ...
 The code also demonstrates a second "inaccuracy" that has cost me
some time: when sqlite3_step returns with SQLITE_ROW, sqlite3_errmsg
gives "unknown error" instead of something like "row available (no
error)".
 SQLite is a great piece of software. Maybe these hints help others
to be even more effective with SQLite.
 Thank you for reading,
 Wolfgang
  

Links:
--
[1] http://www.sqlite.org/c3ref/stmt.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to optimize this query on a very large database table

[Frank Chang]

Igor Tandetnik,
 
>> How come you only show one function? A user-defined aggregate function is 
>> actually represented by two C[++] functions - one that is called for every 
>> row 
>> and performs actual aggregation, and another that's called at the end of 
>> each 
>> group, reports the result and resets the state machine to prepare for the 
>> next 
>> group. You can use sqlite3_context to store state between invocations - see 
>> sqlite3_aggregate_context.

 We have defined 2 C++ function XStep and  XFinalize(shown below). 
The group by last name BLOB results look accurate. Thank you for your help.
 
void cIntersectingGroupCache::XFinalize(sqlite3_context *context){
 listCtx *p;  
 char *buf=NULL;

 buf = (char *) malloc ((sizeof(int) * 
((cIntersectingGroupCache*)(p->TheThis))->Column2.size())+ 4);
 if (buf == NULL)
  printf("malloc error in XFinalize, buf\n");
 
 sqlite3_result_blob(context,buf,
 (((cIntersectingGroupCache*)(p->TheThis))->Column2.size()*sizeof(int)) 
+ 4, free);
 
 ((cIntersectingGroupCache*)(p->TheThis))->Column2.clear();





}



 
 

  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] max id mystery

[Eugene N]

Hello, thanks for many corrections and suggestions;

I am new to php, so i was under the impression it had sqlite3 (now i
see why i cant edit my db from sqlite3 shell); i understand that my
approach is not right ( is shouldn't emulate primary key again)

the problem is (at least i think it is) in the fact that my id was
TEXT and a string "99" was bigger then "100" (sacii-wise?); i was
mislead by the fact that client app treats id as text, so i made it
text in db





2011/10/13, Swithun Crowe :
> Hello
>
> EN> I have an sqlite3 db that stores user-accounts (each user has an id);
>
> You say you have a sqlite3 database, but it looks like you are using PHP's
> sqlite 2 interface. Unless you have special requirements or limilations, I
> would consider using the PDO interface for sqlite3.
>
> Why not let the database handle the generation of user IDs? You seem to be
> emulating an auto increment primary key. It is possible to get the last
> generated ID back with sqlite_last_insert_rowid (or something similar in
> the other interfaces) - it isn't something you need to know in advance.
>
> EN> $query = "SELECT MAX(id) AS largestID FROM profiles";
> EN> $result_array = $database->arrayQuery($query, SQLITE_ASSOC);
> EN> foreach ($result_array[0] as $key => $value)
> EN> $max_id = $value;
> EN> $new_max_id = $max_id+1;
>
> It may just be a matter of style, but I wouldn't use a foreach loop to get
> one value out of an array. You could do the same with:
>
> $query = "SELECT MAX(id) AS largestID FROM profiles";
> $result_array = $database->arrayQuery($query, SQLITE_ASSOC);
> $new_max_id = $result_array[0]["largestID"] + 1;
>
> EN> $query =
> EN> 'INSERT INTO profiles (birthDate, company, country, email,
> facebook,
> EN> firstName, gender, id, lastName, skype, summary, twitter) ' .
> EN> 'VALUES ("' . $php_data->dateOfBirth . '"' .
> EN> ',"' . $php_data->company . '"' .
> EN> ',"' . $php_data->country . '"' .
> EN> ',"em...@gmail.com"' .
> EN> ',"' . $php_data->facebook . '"' .
> EN> ',"' . $php_data->firstName . '"' .
> EN> ',"' . $php_data->gender . '"' .
> EN> ',"' . $new_max_id . '"' .
> EN> ',"' . $php_data->lastName . '"' .
> EN> ',"' . $php_data->skype . '"' .
> EN> ',"' . $php_data->summary . '"' .
> EN> ',"' . $php_data->twitter . '");';
> EN> }
>
> It would be better to bind your pdp_data variables, so that you don't
> leave yourself open to SQL injection attacks. The sqlite3 and PDO
> interfaces let you do this.
>
> EN> The problem is as follows; The first id is 80; when the number of users
> EN> reaches 20 ( last id =100), the next id is never incremented! And new
> users
> EN> keep getting id=100,
> EN> although the DB primary key is incremented correctly;
>
> Is there a reason for starting at 80?
>
> If you put some error checking in your code, then that should let you know
> where the problem is.
>
> Swithun.
> ___
> 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] max id mystery

[Swithun Crowe]

Hello

EN> I have an sqlite3 db that stores user-accounts (each user has an id);

You say you have a sqlite3 database, but it looks like you are using PHP's 
sqlite 2 interface. Unless you have special requirements or limilations, I 
would consider using the PDO interface for sqlite3.

Why not let the database handle the generation of user IDs? You seem to be 
emulating an auto increment primary key. It is possible to get the last 
generated ID back with sqlite_last_insert_rowid (or something similar in 
the other interfaces) - it isn't something you need to know in advance.

EN> $query = "SELECT MAX(id) AS largestID FROM profiles";
EN> $result_array = $database->arrayQuery($query, SQLITE_ASSOC);
EN> foreach ($result_array[0] as $key => $value)
EN> $max_id = $value;
EN> $new_max_id = $max_id+1;

It may just be a matter of style, but I wouldn't use a foreach loop to get 
one value out of an array. You could do the same with:

$query = "SELECT MAX(id) AS largestID FROM profiles";
$result_array = $database->arrayQuery($query, SQLITE_ASSOC);
$new_max_id = $result_array[0]["largestID"] + 1;

EN> $query =
EN> 'INSERT INTO profiles (birthDate, company, country, email, facebook,
EN> firstName, gender, id, lastName, skype, summary, twitter) ' .
EN> 'VALUES ("' . $php_data->dateOfBirth . '"' .
EN> ',"' . $php_data->company . '"' .
EN> ',"' . $php_data->country . '"' .
EN> ',"em...@gmail.com"' .
EN> ',"' . $php_data->facebook . '"' .
EN> ',"' . $php_data->firstName . '"' .
EN> ',"' . $php_data->gender . '"' .
EN> ',"' . $new_max_id . '"' .
EN> ',"' . $php_data->lastName . '"' .
EN> ',"' . $php_data->skype . '"' .
EN> ',"' . $php_data->summary . '"' .
EN> ',"' . $php_data->twitter . '");';
EN> }

It would be better to bind your pdp_data variables, so that you don't 
leave yourself open to SQL injection attacks. The sqlite3 and PDO 
interfaces let you do this.

EN> The problem is as follows; The first id is 80; when the number of users
EN> reaches 20 ( last id =100), the next id is never incremented! And new users
EN> keep getting id=100,
EN> although the DB primary key is incremented correctly;

Is there a reason for starting at 80?

If you put some error checking in your code, then that should let you know 
where the problem is.

Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] max id mystery

[Eugene N]

Hello dear comrades!

I hope together we will be able to shed some light on a rather mysterious
occurrences which are plaguing me for the past few days;

I have an sqlite3 db that stores user-accounts (each user has an id); When a
new user wants to add his account to db, he sends
all his data fields as he wants them, but id is left as zero;

then a backend handles the request like this:

if ($php_data->id == 0) {

$query = "SELECT MAX(id) AS largestID FROM profiles";
$result_array = $database->arrayQuery($query, SQLITE_ASSOC);
foreach ($result_array[0] as $key => $value)
$max_id = $value;
$new_max_id = $max_id+1;

$query =
'INSERT INTO profiles (birthDate, company, country, email, facebook,
firstName, gender, id, lastName, skype, summary, twitter) ' .
'VALUES ("' . $php_data->dateOfBirth . '"' .
',"' . $php_data->company . '"' .
',"' . $php_data->country . '"' .
',"em...@gmail.com"' .
',"' . $php_data->facebook . '"' .
',"' . $php_data->firstName . '"' .
',"' . $php_data->gender . '"' .
',"' . $new_max_id . '"' .
',"' . $php_data->lastName . '"' .
',"' . $php_data->skype . '"' .
',"' . $php_data->summary . '"' .
',"' . $php_data->twitter . '");';
}

So, if there are 10 accounts in DB, a new user will have id=11;

The problem is as follows; The first id is 80; when the number of users
reaches 20 ( last id =100), the next id is never incremented! And new users
keep getting id=100,
although the DB primary key is incremented correctly;

Any ideas?

Thanks in advance!!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTimeOffset in SQLite

[Steffen Mangold]

Pavel wrotes:

>
> I don't know C#, but quick look at Microsoft's documentation shows that you 
> can get Ticks() from TimeSpan, save it into DB and then when you get Int64 
> from DB you can create TimeSpan from it.
>


Seems not to work entity framework cannot convert "long" to the type "TimeSpan".
I know I can make a "long" field and extend the entity class with a "TimeSpan" 
property that convert it.
But I want to find a "entity framework way" to do this.

Here the EDMX generator error:

Member Mapping specified is not valid. The type 
'Edm.DateTimeOffset[Nullable=False,DefaultValue=,Precision=]' of element 'Type 
' of member 'TestProperty' in type 'TestEntity' is not compatible with 'Typ' of 
member 'SQLite.integer[Nullable=False,DefaultValue=]' in type 'TestEntity'.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users