Re: [sqlite] Can DBI supply a more specific foreign key mismatch error? -- Mea culpa
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?
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?
>> > (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?
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?
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)
> -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)
> 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?
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
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)
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)
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)
> 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)
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?
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)
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?
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)
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)
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)
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?
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
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)
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)
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)
> -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
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)
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)
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)
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
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
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
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
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?
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
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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-us
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
Re: [sqlite] pragma PAGE_COUNT != pragma page_count
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
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?
> 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
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?
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
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
>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
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
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
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
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