[sqlite] SQLite++ 3.0 Released
Hello all. I am pleased to announce the release of SQLite++ version 3, the cross-platform C++ library for SQLite. It is designed to make using the SQLite C library easy without sacrificing performance. SQLite++ also supports *TwoFish encryption* and *ZLib compression*. SQLite++ consists of 4 main classes and a few utility classes for *BLOB handling*, *encryption* and *compression*. These classes make it super-easy to begin using SQLite immediately, without having to write heaps of infrastructure code as you would otherwise need to do. The main classes are: * SqliteDb * Dataset * Columns (Collection) * Column Open and query a database with just a few lines of code. Save yourself weeks of coding and debugging with this robust and easy to use class library. For more information, start at the SqlitePlus website: www.sqliteplus.com Best regards -Brett Goodman EzTools Software
Re: [sqlite] Need Type info with Views
Dennis, Thank you for the thoughtful reply. My position is that, anywhere type information is available or can otherwise be determined by the engine it should be provided. Views that are direct SELECT statements certainly fall into this category. Derived results should be too. Think of MS ADO (from OLEDB and ODBC). Type info is returned for views and derived results. How can we be expected to use result columns for which the type cannot be determined? Anyway, I wonder how difficult would it be to put this in. Dennis Cote wrote: EzTools Support wrote: I'm just wondering why I have not received any comment regarding this issue...? EzTools Support wrote: Hello DRH. This is further to the previous query regarding no type information being returned with Views. I had previously asked if this could be added, but have not received any comment. I'm sure anyone would agree that getting the column type data type back with views is no less important than getting it back with SELECTs. Why would it be? I need type info for any row-returning SQL command, be if from directly via SELECTs or indirectly from Views. Until this is fixed, I must tell my 3.0 customers they can't use Views :-( I agree. Views should behave the same as real tables where ever possible. If selects on tables return type information, then selects on views should also return type info. From your previous posts it looks like you are using the sqlite3_column_decltype function to get this info, but that is probably not correct. The sqlite3_column_type function should be used to get the type information for a column in a result set. It returns the type of the data actually being returned in that column. The function sqlite3_column_decltype is used to get the declaration type for a result column. This really only applies to result columns that are retrieved directly from a table column. What is the declaration type of the output of a function such as max(), count(), or like(), or the result of an expression using operators such as || or a relational operator like The output of a view is similarly disconnected from the tables and functions it is based on by the select statement used to define the view. Again you could argue that view result columns based directly on a table column should return the declared type of that table column. I agree that that might be nice, but I don't think it is a critical omission. I believe the current implementation is based on the idea that views don't contain any column declarations, therefore they don't have any declaration types. I think your other point about the table names not being handled the same way is incorrect. If I create a simple table and view; create table t (a, b); create view v as select * from t; and then do several select statements as show below select * from t; select * from v; select t.a, t.b from t; select v.a, v.b from v; I get the same behavior in both cases. I think that you are expecting to create the view using table names like this create view v2 as select t.a, t.b from t; and then do a simple select select * from v2; and have result columns with tables names from the underlying table. That would be incorrect. What would you expect as result column names for the following query? select v2.a, v2.b from v2; I believe you are looking at views as standins for select statements, when thay are better though of as a slightly different kind of table. You should be able to do most of the same things with views that you can do with tables. Most differences arise because views don't actually store their rows; you can't create an index on a view for example.
Re: [sqlite] Need Type info with Views
I'm just wondering why I have not received any comment regarding this issue...? EzTools Support wrote: Hello DRH. This is further to the previous query regarding no type information being returned with Views. I had previously asked if this could be added, but have not received any comment. I'm sure anyone would agree that getting the column type data type back with views is no less important than getting it back with SELECTs. Why would it be? I need type info for any row-returning SQL command, be if from directly via SELECTs or indirectly from Views. Until this is fixed, I must tell my 3.0 customers they can't use Views :-( regards -brett
[sqlite] SQLitePlus 3.0 Released
Greetings all. EzTools Software is pleased to announce the release of SQLitePlus 3.0, the COM wrapper and Database Manager tool for all versions of Windows 95-XP. Features include automatic encryption and compression. You can find out more about it at www.sqliteplus.com Best regards -Brett Goodman
[sqlite] Need Type info with Views
Hello DRH. This is further to the previous query regarding no type information being returned with Views. I had previously asked if this could be added, but have not received any comment. I'm sure anyone would agree that getting the column type data type back with views is no less important than getting it back with SELECTs. Why would it be? I need type info for any row-returning SQL command, be if from directly via SELECTs or indirectly from Views. Until this is fixed, I must tell my 3.0 customers they can't use Views :-( regards -brett
Re: [sqlite] Re: *** Please help ***
Thank you for that Miguel, but this is not good. DRH, if we don't get the type information returned for Views, what is the point of getting it back for SELECTs? Views are completely useless to SQLite users who rely on the type information to interpret the result sets. Is this something you can fix soon? Miguel Angel Latorre Díaz wrote: Read the documentation: "The first parameter is a prepared SQL statement. If this statement is a SELECT statement, the Nth column of the returned result set of the SELECT is a table column then the declared type of the table column is returned. If the Nth column of the result set is not at table column, then a NULL pointer is returned." - Original Message - From: "EzTools Support" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, September 20, 2004 11:43 PM Subject: [sqlite] Re: *** Please help *** Can someone please please answer the question, does sqlite3_column_decltype NOT return the column type for Views? I don't see how this wouldn't considered as a major bug. EzTools Support wrote: I found out why Views were returning all NULLs. I have made a modified version of sqlite3_exec. Mine checks for NULL returned from sqlite3_column_decltype, for which Sqlite3 now returns NULL. Am I right that this is so? My COM wrapper depends on having the column data types returned for Views. Can you please make it work again, as this is vital. thanks -brett EzTools Support wrote: Hello. Can someone please help with this issue. It may turn out to be a bug in Sqlite 3. I have the following fairly basic SQL View that works fine with Sqlite 2.x: CREATE VIEW Invoices AS SELECT Customers.CustomerID, Customers.CompanyName,Orders.OrderID, OrderDetails.ProductID, OrderDetails.Quantity FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID When I create and run this view in 3.x, I get all of the rows back, but all values are NULL! If I run the SELECT statement apart from the view, it I get the data back fine. But if I select * FROM Invoices, I get all of the rows back, but all values are NULL. Another interesting thing is that when selecting from the View, the column names are without the table prefix. That is, CustomerID, CompanyName, OrderID, et. But if I select from the tables directly, the column names have the table prefix, Customers.CustomerID, Customers.CompanyName, Orders.OrderID I have put the database up on my website here (Zip file): www.eztools-software.com/downloads/northwind.zip This is happening in all version 3.0.5-3.0.7. Can someone please download this DB and investigate. If you want to test the same view and data with a v2.x database, you can get the SqlitePlus28 download from here: www.eztools-software.com/downloads/sqliteplus.exe TIA -Brett Goodman
[sqlite] Re: *** Please help ***
Can someone please please answer the question, does sqlite3_column_decltype NOT return the column type for Views? I don't see how this wouldn't considered as a major bug. EzTools Support wrote: I found out why Views were returning all NULLs. I have made a modified version of sqlite3_exec. Mine checks for NULL returned from sqlite3_column_decltype, for which Sqlite3 now returns NULL. Am I right that this is so? My COM wrapper depends on having the column data types returned for Views. Can you please make it work again, as this is vital. thanks -brett EzTools Support wrote: Hello. Can someone please help with this issue. It may turn out to be a bug in Sqlite 3. I have the following fairly basic SQL View that works fine with Sqlite 2.x: CREATE VIEW Invoices AS SELECT Customers.CustomerID, Customers.CompanyName,Orders.OrderID, OrderDetails.ProductID, OrderDetails.Quantity FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID When I create and run this view in 3.x, I get all of the rows back, but all values are NULL! If I run the SELECT statement apart from the view, it I get the data back fine. But if I select * FROM Invoices, I get all of the rows back, but all values are NULL. Another interesting thing is that when selecting from the View, the column names are without the table prefix. That is, CustomerID, CompanyName, OrderID, et. But if I select from the tables directly, the column names have the table prefix, Customers.CustomerID, Customers.CompanyName, Orders.OrderID I have put the database up on my website here (Zip file): www.eztools-software.com/downloads/northwind.zip This is happening in all version 3.0.5-3.0.7. Can someone please download this DB and investigate. If you want to test the same view and data with a v2.x database, you can get the SqlitePlus28 download from here: www.eztools-software.com/downloads/sqliteplus.exe TIA -Brett Goodman
[sqlite] Re: *** Please help ***
I found out why Views were returning all NULLs. I have made a modified version of sqlite3_exec. Mine checks for NULL returned from sqlite3_column_decltype, for which Sqlite3 now returns NULL. Am I right that this is so? My COM wrapper depends on having the column data types returned for Views. Can you please make it work again, as this is vital. thanks -brett EzTools Support wrote: Hello. Can someone please help with this issue. It may turn out to be a bug in Sqlite 3. I have the following fairly basic SQL View that works fine with Sqlite 2.x: CREATE VIEW Invoices AS SELECT Customers.CustomerID, Customers.CompanyName,Orders.OrderID, OrderDetails.ProductID, OrderDetails.Quantity FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID When I create and run this view in 3.x, I get all of the rows back, but all values are NULL! If I run the SELECT statement apart from the view, it I get the data back fine. But if I select * FROM Invoices, I get all of the rows back, but all values are NULL. Another interesting thing is that when selecting from the View, the column names are without the table prefix. That is, CustomerID, CompanyName, OrderID, et. But if I select from the tables directly, the column names have the table prefix, Customers.CustomerID, Customers.CompanyName, Orders.OrderID I have put the database up on my website here (Zip file): www.eztools-software.com/downloads/northwind.zip This is happening in all version 3.0.5-3.0.7. Can someone please download this DB and investigate. If you want to test the same view and data with a v2.x database, you can get the SqlitePlus28 download from here: www.eztools-software.com/downloads/sqliteplus.exe TIA -Brett Goodman
[sqlite] Help please
There was a definition for MAX_BYTES_PER_ROW. I cannot find it anymore in 3.0.7. Is there still such a concept as maximum row size? TIA -Brett Goodman
[sqlite] *** Please help ***
Hello. Can someone please help with this issue. It may turn out to be a bug in Sqlite 3. I have the following fairly basic SQL View that works fine with Sqlite 2.x: CREATE VIEW Invoices AS SELECT Customers.CustomerID, Customers.CompanyName,Orders.OrderID, OrderDetails.ProductID, OrderDetails.Quantity FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID When I create and run this view in 3.x, I get all of the rows back, but all values are NULL! If I run the SELECT statement apart from the view, it I get the data back fine. But if I select * FROM Invoices, I get all of the rows back, but all values are NULL. Another interesting thing is that when selecting from the View, the column names are without the table prefix. That is, CustomerID, CompanyName, OrderID, et. But if I select from the tables directly, the column names have the table prefix, Customers.CustomerID, Customers.CompanyName, Orders.OrderID I have put the database up on my website here (Zip file): www.eztools-software.com/downloads/northwind.zip This is happening in all version 3.0.5-3.0.7. Can someone please download this DB and investigate. If you want to test the same view and data with a v2.x database, you can get the SqlitePlus28 download from here: www.eztools-software.com/downloads/sqliteplus.exe TIA -Brett Goodman
Re: [sqlite] No data with PRAGMA table_info
Sorry, I meant for 3.x. Does it work for anyone? try IeToolbox Passwords & Notes Keeper, Form Filler and much more www.ietoolbox.com [EMAIL PROTECTED] wrote: EzTools Support <[EMAIL PROTECTED]> writes: When I issue PRAGMA table_info for an existing table, I get the column names back, but no values. Does it work for anyone else? In 2.8.15, the command pragma table_info(tablename); works as it's intended, returning one row for each column in the table. Each row contains the cid, name, type, notnull, dflt_value and pk values. Derrell
[sqlite] No data with PRAGMA table_info
When I issue PRAGMA table_info for an existing table, I get the column names back, but no values. Does it work for anyone else? TIA -brett
[sqlite] No data with PRAGMA table_info
When I issue PRAGMA table_info for an existing table, I get the column names back, but no values. Does it work for anyone else? TIA -brett -- try IeToolbox Passwords & Notes Keeper, Form Filler and much more www.ietoolbox.com
Re: [sqlite] Encrypting data stored in database
Joey,
Where in the code did you do that? Was it easy to put in one or two places?
-brett
try IeToolbox Passwords & Notes Keeper, Form Filler and much more
www.ietoolbox.com
Joey Blankenship wrote:
Not to respond out of turn here, but we had a need to obfuscate the
plain text (due to some users that are a little too curious for their
own good) that was stored but did not want the performance burden that
encryption/decryption would place on the PocketPC. Prior to writing
and subsequent to reading, we just perform a simple mangle of the
read/written data. A byte NOT or XOR works pretty well and does not
cause an extreme performance hit. Again, no real encryption, but it
hides the data from casual viewing.
Joey.
At 05:34 AM 9/9/2004, you wrote:
Hello sqlite users,
Hello Dr. Hipp,
I would like to know if there is a way to encrpyt the data stored in
the database but to still be able to use SQL queries with plain text,
something like
CREATE TABLE TEST (fld)
INSERT INTO Test VALUES ('some string')
SELECT * FROM Test WHERE fld = 'some string'
but, when the sqlite database is opened in a file viewer, 'some
string' would not be visible but only it's encrypted form.
Regards,
George Ionescu
Re: [sqlite] How to find out encoding for a table
No one has answered this query yet. Please can someone help with it. Thanks. try IeToolbox Passwords & Notes Keeper, Form Filler and much more www.ietoolbox.com EzTools Support wrote: Hello all. I have found that I can create a table and write data to that table as either UTF-8 or UTF-16 by using "PRAGMA encoding...", so that all of the field names and text data are written as the encoding type. I need to be able to query (discover) the encoding of a table when the encoding is unknown. How do I do this? TIA -brett
[sqlite] How to find out encoding for a table
Hello all. I have found that I can create a table and write data to that table as either UTF-8 or UTF-16 by using "PRAGMA encoding...", so that all of the field names and text data are written as the encoding type. I need to be able to query (discover) the encoding of a table when the encoding is unknown. How do I do this? TIA -brett -- try IeToolbox Passwords & Notes Keeper, Form Filler and much more www.ietoolbox.com
[sqlite] Email Group Support Please
Hello. I seem to be in the database twice - I'm getting each email twice. Can the person in charge of the emailer please take care of this. Thank you -- try IeToolbox Passwords & Notes Keeper, Form Filler and much more www.ietoolbox.com
[sqlite] BLOBs and Encoding
Hello all. Does 3.x support BLOBs without encoding (via API) or do we need to encode it and use SQL INSERT? Also, I can't find any 8859 macros - is it only UTF-8 now? TIA for the help
