[sqlite] Re: Does SQLite support user-defined data-types ?
Jerome CORRENOZ <[EMAIL PROTECTED]> wrote: I'm starting with SQLite and I would like to know if it is possible to create user-defined data-types through the following SQL command: create type MyType ... ? No. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: How to specify collating sequences in an expression.
[EMAIL PROTECTED] wrote: (2) How do other SQL engines do this kind of thing? MS SQL Server supports a=b collate CollationName syntax. There are a few examples at http://msdn2.microsoft.com/en-us/library/ms179886.aspx This article also specifies a rather complicated set of rules for determining which collation should be used for a given comparison. MS SQL also supports defining multiple indexes on the same table and field(s), differing only in collation (and the optimizer is smart enough, most of the time, to use these indexes appropriately). I haven't tried it with SQLite, maybe it's also supported. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: How to specify collating sequences in an expression.
[EMAIL PROTECTED] wrote: (2) How do other SQL engines do this kind of thing? MS SQL Server supports a=b collate CollationName syntax. There are a few examples at http://msdn2.microsoft.com/en-us/library/ms179886.aspx This article also specifies a rather complicated set of rules for determining which collation should be used for a given comparison. MS SQL also supports defining multiple indexes on the same table and field(s), differing only in collation (and the optimizer is smart enough, most of the time, to use these indexes appropriately). I haven't tried it with SQLite, maybe it's also supported. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: special characters in sqlite3_bind_text
Dixon Hutchinson
wrote:
I am having a problem with single quotes in a C program.
Consider a simple table
CREATE TABLE t(comp TEXT);
Lets say I use sqlite3_prepare to prepare the following string:
"SELECT rowid FROM table WHERE comp=?;"
Shouldn't that be
SELECT rowid FROM t WHERE comp=?;
Should I be calling sqlite3_mprintf("abc'def") and passing that string
off to bind?
No you shouldn't. You should pass the text to sqlite3_bind_text as is,
with no escaping. The problem is elsewhere.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: DROP TABLE IF EXISTS my_table
Cecilia VIGNY wrote: I'm using SQLite with a PHP program and I would like to execute this SQL request : DROP TABLE IF EXISTS clients; When I test my program, this error occures : Warning: sqlite_query(): near "EXISTS": syntax error in on line 61 IF EXISTS clause is supported with SQLite v3.3.0 and up. It appears that you are using an earlier version. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: newbie ask saving
Hariyanto <[EMAIL PROTECTED]> wrote:
I have 2 table:
1. Table A :
no INTEGER PRIMARY KEY,
name varchar(15);
2. Table B :
no smallint, (Foreign key)
Address varchar(20);
Telpvarchar(10);
Usually I use this step when I save data :
- insert A (name) VALUES ("Mr.X");
- x = Select no_id FROM A where name = 'Mr.X'
- Insert B (no_id, Alamat, Telp) VALUES (x, 'x', '123');
Can't you use sqlite3_last_insert_rowid API?
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: Newbie SQL question
A.J.Millan <[EMAIL PROTECTED]> wrote: Does exist some method to erase records and to obtain the number of erased records at the same time? It is to say: SELECT count() FROM someTable WHERE some-condition; DELETE FROM someTable WHERE some-condition; in only one statement? See sqlite3_changes, sqlite3_total_changes Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: help with understanding the C interface
Patrick X <[EMAIL PROTECTED]> wrote: int sqlite3_open( const char *filename, /* Database filename (UTF-8) */ sqlite3 **ppDb /* OUT: SQLite db handle */ ); int sqlite3_open16( const void *filename, /* Database filename (UTF-16) */ sqlite3 **ppDb /* OUT: SQLite db handle */ ); the above functions have me a little confused. So sqlite3_open, does it returns a pointer to the open database or just the success or error code or both. It returns an error code via return value, and also returns a database handle by storing it in *ppDb. That's what it means for ppDb to be an OUT parameter. Second, if it returns a pointer to the open db is it needed to be stored in memory to pass it to the close or other functions within sqlite3. The client of SQLite needs to store sqlite3* handle. The handle is passed to most SQLite API calls to identify a particular database connection (the client may open more than one, to the same or different files). When the client is done with this database connection, it calls sqlite3_close passing the handle. After that, the handle is invalid and doesn't need to be stored any longer. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Busy timeout and prepare statements
McDermott, Andrew <[EMAIL PROTECTED]> wrote: I'm seeking some clarification regarding set_busy_timeout(). If I set this to some positive value does this work when using prepared statements (prepare, step, reset) or only when using sqlite3_exec(). Busy timeout does apply to sqlite3_step (prepare and reset cannot encounter busy state). Note that sqlite3_exec is implemented in terms of sqlite3_step et al. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: ATTACH and :memory: databases
Dave Gierok <[EMAIL PROTECTED]> wrote: I would like to attach a :memory: database to another :memory: database. How is this possible As far as I can tell, this is not possible. Why would you want to? What are you trying to achieve? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Leading zeros in strings
Robert Simpson <[EMAIL PROTECTED]>
wrote:
Issued from the 3.3.12 comand-line:
CREATE TABLE blah (ID INTEGER PRIMARY KEY, STUFF string NOT NULL);
INSERT INTO blah (STUFF) VALUES('00302');
SELECT * FROM blah;
1|302
Same query, slightly different table definition:
CREATE TABLE blah (ID INTEGER PRIMARY KEY, STUFF text NOT NULL);
INSERT INTO blah (STUFF) VALUES('00302');
SELECT * FROM blah;
1|00302
Should there be a difference between declaring a column as type
'text' and type 'string' ?
Read about SQLite type model here:
http://www.sqlite.org/datatype3.html
In particular the notion of column type affinity.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: SQL syntax issue?
Sherlock, Ric <[EMAIL PROTECTED]> wrote: But gives and error with the following statement (it will run fine on the same tables in Access) SELECT clients.cl_lname, clients.cl_title, price_profiles.pp_year, prices.pr_mfd, prices.pr_price FROM (clients LEFT JOIN price_profiles ON clients.cl_id = price_profiles.pp_client) LEFT JOIN prices ON price_profiles.pp_id = prices.pr_pp SQL error: no such column: clients.cl_lname Known SQLite limitation. Just drop the parentheses, you don't need them here. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Need help on build query.
Artem Yankovskiy wrote: I like delete some of table. DROP TABLE tableName; http://sqlite.org/lang_droptable.html Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: converting 1,234,567 to a number
T&B wrote:
I have some imported data, where some fields contain numbers with
commas denoting thousands separators. How can I change these to
actual numbers?
I tried using CAST, which only works with later SQLite versions, but
it doesn't seen to know the comma as the thousands marker. For
instance:
sqlite> SELECT CAST('1,234,567' AS REAL);
1.0
Your best bet is probably to clean up the data before passing it on to
SQLite. Just strip non-digit characters and convert to a number in
whatever language your application is written. SQL is ill suited for
string manipulation.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: Re: Need help on build query.
Artem Yankovskiy wrote: I know about DROP TABLE. I have not knew when compatibility DROP TABLE and SELECT... DROP TABLE (select name from sqlite_master where...) did not work. Can I build resembling query? No. You will have to run the select, store table names in memory, then build and run a separate DROP TABLE query for each table name. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: New (ish) to C== and new to SQLite error with prepare.
Paul Simpson <[EMAIL PROTECTED]> wrote: sqlite3_stmt **ppStmt; const char **pzTail; rc = (sqlPrepareAdd)(newdb, getDBVersion.c_str(),getDBVersion.length(),ppStmt,pzTail); Make it sqlite3_stmt* pStmt; const char* pzTail; rc = (sqlPrepareAdd)(newdb, getDBVersion.c_str(),getDBVersion.length(), &pStmt, &pzTail); When you pass in pointers, they should actually point to a valid locaction in memory. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: UPDATE base on certain values
jose isaias cabrera <[EMAIL PROTECTED]> wrote: I would like is to do something like this, UPDATE table SET ID = '88' if not = '88', Just have ID = '88' Overwriting a value with the same value is harmless. parent = '1171291314642' if null, parent = ifnull(parent, '1171291314642') Same idea. Unless "parent" is null, this just does parent=parent children = '', login = 'blah', notes = 'blah-blah' if null, status = 'o' WHERE ProjID = '88'; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: another
Anderson, James H (IT) wrote: If I run the following sql create table table_C as select A.col_1, B.col_2 from table_A A, table_B B where A.col_3 = B.col_4 The table_C is created with the following column names: "A.col_1", "B.col_2" It seems to me sqlite should strip off the alias qualifier and create the table as col_1, col_2 What do you think it should do for something like create table C as select A.col, B.col from A, B; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: SQL query - TOP
Allan, Mark wrote: Select Top * From PATIENTS WHERE PATIENT_PK NOT IN (SELECT TOP PATIENT_PK From PATIENTS Order By PATIENT_PK) Order By PATIENT_PK It would appear that the "TOP" syntax is not supported by SQLite (maybe just a Microsoft thing?). SQLite supports LIMIT and OFFSET clauses. To select rows starting from th one (it appears that's what you are doing), do SELECT PATIENT_PK From PATIENTS Order By PATIENT_PK LIMIT OFFSET ; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Update and insert questions
Jim Crafton <[EMAIL PROTECTED]> wrote: Yeah I think you're right. I changed the code to *not* use the bind functions, and just dump the values directly into the SQL statement ( I think this was a case of me trying to be too clever), and that works like a charm now. So I guess the moral of this is to use bind cautiously :) Realize that you can parameterize both the SET and the WHERE clauses, as in UPDATE Person SET LastName=?, FirstName=?, Address=?, Age=? WHERE LastName=? AND FirstName=? AND Address=? AND Age=?; Now you have a query with 8 parameters you can bind independently, to the same or different values. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: compare open table and attached database table
fangles <[EMAIL PROTECTED]> wrote: Hello, I am trying to compare a currently opened database table with a table from an attached database. Both tables have identical structures but the attached table has an extra record. The first lists records from the internal table NOT CONTAINED IN the attached table The second lists records NOT CONTAINED IN the internal table Select a.displayas AS displayas FROM addresses a INNER JOIN RemoteDb.addresses b ON a.displayas <> b.displayas This query doesn't do what you think it does. Once you get past the little syntax problem, you'll get displayas from every record in addresses table, each repeated multiple times. Make it select displayas from main.addresses where displayas not in (select displayas from RemoteDb.addresses) Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: What is wrong with this SELECT CASE statement?
RB Smissaert <[EMAIL PROTECTED]> wrote: Trying to update my mmdd integers to months with a SELECT CASE statement: SELECT CASE (CAST(DATE_OF_BIRTH / 100 AS INTEGER) - CAST(DATE_OF_BIRTH / 1 AS INTEGER) * 100) WHEN 1 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'January' WHEN 2 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'February' WHEN 3 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'March' WHEN 4 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'April' WHEN 5 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'May' WHEN 6 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'June' WHEN 7 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'July' WHEN 8 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'August' WHEN 9 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'September' WHEN 10 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'October' WHEN 11 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'November' WHEN 12 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'December' END FROM A2IDC21_J But no updates take place. UPDATE is a statement, not an expression. It cannot appear nested in another statement. You want UPDATE A2IDC21_J SET DATE_OF_BIRTH = CASE (CAST(DATE_OF_BIRTH / 100 AS INTEGER) - CAST(DATE_OF_BIRTH / 1 AS INTEGER) * 100) WHEN 1 THEN 'January' WHEN 2 THEN 'February' ... END Also, the expression in the CASE can be simplified to CAST(DATE_OF_BIRTH AS INTEGER) / 100 % 100 Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Looking for equivalent syntax
P Kishor <[EMAIL PROTECTED]> wrote: What is wrong with your original statement? You never mentioned whether that worked on not... did you try it? (listed again below) Well, have _you_ tried it? SQLite doesn't support this syntax. You could update it to the more standard-ish syntax like so UPDATE C1_credDerivEvent SET a.CDEvent = a.CDEvent || ',' || b.CDEvent FROM C1_credDerivEvent a JOIN C1_tmp_credDerivEvent b ON a.CDId = b.CDId JOIN tmp_events c ON b.CDEvent = c.CDEvent SQLite doesn't support this syntax either. FROM clause is not part of UPDATE statement. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Looking for equivalent syntax
Anderson, James H (IT) wrote: I'm trying to convert the following statement in Sybase syntax into the equivalent sqlite syntax: update C1_credDerivEvent set a.CDEvent = a.CDEvent || ',' || b.CDEvent from C1_credDerivEvent a, C1_tmp_credDerivEvent b, tmp_eventsc where a.CDId= b.CDId and b.CDEvent = c.CDEvent update C1_credDerivEvent set CDEvent = CDEvent || ',' || (select b.CDEvent from C1_tmp_credDerivEvent b where C1_credDerivEvent.CDId = b.CDId) where exists ( select * from C1_tmp_credDerivEvent b, tmp_events c where C1_credDerivEvent.CDId = b.CDId and b.CDEvent=c.CDEvent ) Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Looking for equivalent syntax
Igor Tandetnik <[EMAIL PROTECTED]> wrote: update C1_credDerivEvent set CDEvent = CDEvent || ',' || (select b.CDEvent from C1_tmp_credDerivEvent b where C1_credDerivEvent.CDId = b.CDId) where exists ( select * from C1_tmp_credDerivEvent b, tmp_events c where C1_credDerivEvent.CDId = b.CDId and b.CDEvent=c.CDEvent ) This one is shorter, and requires only one subselect per row: update C1_credDerivEvent set CDEvent = ifnull( CDEvent || ',' || (select b.CDEvent from C1_tmp_credDerivEvent b, tmp_events c where C1_credDerivEvent.CDId = b.CDId and b.CDEvent=c.CDEvent), CDEvent) If the nested select produces an empty set, it will be treated as NULL, which will force the concatenation to be NULL, and the update will degenerate into a no-op (SET CDEvent=CDEvent). It's not necessarily faster though. If SQLite can use indexes to satisfy the WHERE clause in the first statement, it doesn't need to look at every record. If only a small portion of all records actually needs updating, the first query may run faster even though a second lookup is necessary for those records that do get updated after all. The second query requires a linear scan of C1_credDerivEvent table, and a lookup for every record. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: What query?
erw2 <[EMAIL PROTECTED]> wrote: I have a table with a following data: IdNo1 No2 11001 11 21002 11 31003 12 41004 12 51004 12 61005 12 71006 13 81007 13 91008 14 ... ... Now, I would like to select only the rows when No2 change. So the result of such query should look like: IdNo1 No2 11001 11 31003 12 71006 13 91008 14 ... ... select min(Id), min(No1), No2 from TableName group by No2; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Looking for equivalent syntax
Anderson, James H (IT) wrote: In order to improve my understanding, I'd like to ask 2 questions re the sql, below. 1. what is the relationship between the "select * from where" within the "where exists" and the "select yadayadayada from where" within the set? No direct relationship. However, the conditions in the two selects are designed to be similar enough, so that when EXISTS test succeeds by finding a suitable row, the select in SET would extract a field from that same row. 2. why is it not necessary to include the "b.CDEvent=c.CDEvent" which is present in the "where exists" in the "where" within the set? Now that I think of it, it might be necessary to join to tmp_events in the SET clause after all. Suppse the data looks like this: select CDId from C1_credDerivEvent; 1 select CDId, CDEvent from C1_tmp_credDerivEvent; 110 120 select CDEvent from tmp_events; 20 Here a select that uses all three tables would produce 20. A select that omits tmp_events would produce two records with the values 10 and 20, from which SQLite would just pick the first one. This could be 10, giving a wrong answer. So to be on the safe side, make it update C1_credDerivEvent set CDEvent = CDEvent || ',' || (select b.CDEvent from C1_tmp_credDerivEvent b, tmp_events c where C1_credDerivEvent.CDId = b.CDId and b.CDEvent=c.CDEvent) where exists ( select * from C1_tmp_credDerivEvent b, tmp_events c where C1_credDerivEvent.CDId = b.CDId and b.CDEvent=c.CDEvent ) Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Unexpected Query Results
Rich Shepard <[EMAIL PROTECTED]> wrote: The table has 180 rows and 31 columns. What I need to do is extract the records and group them by two columns (one as a sub-group of the other). However, even one 'group by' retrieves only three records, the last one for each group: Yes, that's what GROUP BY does. One representative for each group. What I expected was all 180 records, with 60 in each of the 'cat' fields (second field above). select * from voting order by cat; Ultimately, I need to group them by the second field (name == 'cat') and within each of those, sub-group them by the third field (name == 'pos'). select * from voting order by cat, pos; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Using AVG() Correctly
Rich Shepard <[EMAIL PROTECTED]> wrote: I have a table, 'voting,' with 31 columns. For each of 28 REAL columns I need to calculate averages both by groups and total. I tried: sqlite> select AVG(pos) from voting where cat = 'eco'; and 0.0 was returned. The query looks good. What's the data in the pos column? Could it be that the average is indeed zero? What I need to do with the data from table 'voting' is (in Python using psqlite2) is to calculate the average of each of 28 numeric columns for each of the three 'pos' column values within each of the three 'cat' column values. select cat, pos, avg(col1), avg(col2), ... from voting group by cat, pos; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: how to get field names of empty tables ?
Stef Mientki <[EMAIL PROTECTED]> wrote: is there an SQL statement to get the field-names of empty tables ? PRAGMA table_info(tableName); Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: how to get field names of empty tables ?
Stef Mientki <[EMAIL PROTECTED]> wrote: Igor Tandetnik wrote: Stef Mientki <[EMAIL PROTECTED]> wrote: is there an SQL statement to get the field-names of empty tables ? PRAGMA table_info(tableName); I had seen that command, but I wrote something about that these commands could only be run from the command line. No. You can run it just like you run any statement, and it produces a resultset with one row for each column in the tableName table. sqlite3 command line application is not black magic. It uses public SQLite API only. You can look at the source code and see for yourself, if you are so inclined. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: trigger with conditions
anis chaaba <[EMAIL PROTECTED]> wrote: Can you tell how can i trigger with conditions such as: create trigger foo begin if (NEW.VALUE = 'something') insert into tables values.. endif END; http://sqlite.org/lang_createtrigger.html create trigger foo after update on someTable when new.value = 'something' begin ... end; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Quotes in SQLite ?
Stef Mientki <[EMAIL PROTECTED]> wrote: This might be a stupid question, but how do you store quotes in textstrings ? Is it really so that you have to replace, both single and double quotes, each time you read or write something ? You need to escape single quotes if you insist on putting string literals directly into queries. No other characters need to be escaped. See also sqlite3_mprintf. However, I recommend using parameterized queries and binding your strings to parameters. Then you don't need to worry about escaping at all. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Quotes in SQLite ?
Stef Mientki <[EMAIL PROTECTED]> wrote: Igor Tandetnik wrote: You need to escape single quotes if you insist on putting string literals directly into queries. No other characters need to be escaped. I've to translate doublequotes too, possibly because I'm using double quotes around textfields ??, Is that wrong ? You should not be using double quotes around string literals in the first place. It is not valid SQL. SQLite allows it as an extension, but it might lead to unexpected results (if the string literal enclosed in double quotes just accidentally happens to be the same as a column name, it will be interpreted as a column reference). Just don't do it. See also sqlite3_mprintf. where can I find that (looked in the SQL wiki, but couldn't find it) http://sqlite.org/capi3ref.html#sqlite3_mprintf However, I recommend using parameterized queries and binding your strings to parameters. Then you don't need to worry about escaping at all. Sorry don't know what "parameterized queries" and "binding strings to parametrs" are, See sqlite3_bind* can you give me link where I can find some more information. http://sqlite.org/capi3ref.html Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: How to change or add fields to a table ?
Stef Mientki <[EMAIL PROTECTED]> wrote: Are there SQL commands to add or change the fields of an existing table, or should the table completely be rebuild ? Some limited changes may be done with ALTER TABLE statement: http://www.sqlite.org/lang_altertable.html For anything else, you would need to create a new table and migrate data. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: How to change or add fields to a table ?
Stef Mientki <[EMAIL PROTECTED]> wrote: thanks Igor, for the fast answer. (should have found that myself ;-) But there's a strange thing here I don't understand: somewhere on the ALTER TABLE documentation it says: "to rename or add a new column ..." It should be parsed this way: ... allows the user to rename, or add a new column to, an existing table. That is, you can rename an existing table, or add a new column to an existing table. On the other hand I fear that I can only change the name of the Table. If the later is true, why isn't possible to change the name of a column (shouldn't be difficult to implement) ? Please feel free to submit a patch. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Insert
Christian POMPIER <[EMAIL PROTECTED]> wrote: Could i make to insert 10 000 row in my table with a loop ? Yes. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Insert
Christian POMPIER <[EMAIL PROTECTED]> wrote: De : Igor Tandetnik [mailto:[EMAIL PROTECTED] Christian POMPIER <[EMAIL PROTECTED]> wrote: Could i make to insert 10 000 row in my table with a loop ? Yes. But what is the good syntax ? The syntax of the INSERT statement is described here: http://sqlite.org/lang_insert.html The syntax for creating a loop can be found in a manual for your programming language of choice. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Performance problem
Stephen Toney wrote: select count(*) from keyword a, keyword b where a.key=b.key and a.value='music' and b.value='history'; 0|0|TABLE keyword AS a WITH INDEX value 1|1|TABLE keyword AS b WITH INDEX value 4,318 records have value='music' and 27,058 have value='history'. Try running ANALYZE statement. The optimizer might be able to choose better plan after that. If this doesn't help, try this query: select count(*) from keyword a, keyword b where a.key=b.key and a.value='music' and b.value||''='history'; Using an expression in place of b.value prevents the optimizer from using an index on it, at which point it hopefully would use one on b.key. This would result in O(M log N) performance, where M=4318 (the number of records with value='music') and N is the total number of records. The query plan used now results in O(M*M') where M=4318 and M'=27058 - a much worse complexity. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: C
Lloyd wrote: How can I make an array of bit fields? something like, using the 16 bits of a short as an array of bits You can't. But, if you can use C++ rather than C, there's std::bitset class that does just that. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Performance problem
[EMAIL PROTECTED] wrote: Stephen Toney <[EMAIL PROTECTED]> wrote: select count(*) from keyword a, keyword b where a.key=b.key and a.value='music' and b.value='history'; 4,318 records have value='music' and 27,058 have value='history'. The keys are 12-byte strings. That doesn't seem like an extreme case to me. The result should be 116,836,444. A faster approach would be: SELECT (SELECT count(*) FROM keyword WHERE value='music')* (SELECT count(*) FROM keyword WHERE value='history'); You seem to be overlooking a.key=b.key condition. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: subselect
kokenge <[EMAIL PROTECTED]> wrote: This is such a simple SQL statement. So sorry for the question, but I can't get it to work. I'm trying to get a list of employees and the last time they worked on a job. FIles are. employee file : with empl_num = employee number job_history file : with empl_num, job_num, and last_date = last date the employee worked on a job Each employee has worked many jobs during his employment . so employee to job_history is 1 to many The sql is very simple and for some reason I keep getting a error saying the it can't reference stuff in the subselect to the file in the Select? I have it working in all my other databases. - SELECT * FROM employee JOIN job_history ON job_history.empl_num = employee.empl_num AND job_history.last_date = (SELECT max(j1.last_date) FROM job_history as j1 WHERE j1.empl_num = employee.empl_num) - Just to simple - so what am I doing wrong I don't get any syntax errors for this statement. The problem must be in something you don't show. Quote the exact error message. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: subselect
kokenge <[EMAIL PROTECTED]> wrote: Here is my exact sql as executed: --- This works SELECT employee.empl_num FROM employee JOIN job_history ON job_history.empl_num = employee.empl_num This does not work SELECT employee.empl_num FROM employee JOIN job_history ON job_history.empl_num = employee.empl_num AND job_history.last_date = ( SELECT MAX(j1.last_date) FROM job_history AS j1 WHERE j1.empl_num = employee.empl_num ) - Here is my error message Error : SQL logic error or missing database no such column: employee.empl_num - Here is my table layout # # Table structure for table: employee # CREATE TABLE employee ( empl_num INT(10) NOT NULL PRIMARY KEY, dept_id VARCHAR(4) NOT NULL DEFAULT 'WMKR', username VARCHAR(16) NOT NULL, first_name VARCHAR(22), middle_name VARCHAR(22), last_name VARCHAR(22), addr1 VARCHAR(22), addr2 VARCHAR(22), city VARCHAR(22), state CHAR(3), zip VARCHAR(10), country VARCHAR(4) NOT NULL DEFAULT 'USA', phone VARCHAR(17), e_mail VARCHAR(40), password VARCHAR(16) NOT NULL, password_hint VARCHAR(50) NOT NULL, dial_log_id VARCHAR(100) NOT NULL, pass_id VARCHAR(20) NOT NULL ); # # Table structure for table: job_history # CREATE TABLE job_history ( job_num INT, empl_num INT , last_date DATE ); # Everything just works for me. I've just copied and pasted all statements as shown into sqlite3 session. I'd check the code for typos, very carefully. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Any way to know the numbers of rows affected by a cmd?
Anderson, James H (IT) wrote: For a select, the number of rows selected. Just count them as you step through them. For an update, the number of rows updates. For a delete the number of rows deleted. sqlite3_changes, sqlite3_total_changes Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Custom collate - on field or index or both?
jp wrote: So, is creating an index with "collate" useless if you didn't specify the collate at the table level? Not entirely useless: the index may still be used to satisfy "ORDER BY field COLLATE collation" clause. Also, Dr. Hipp appears to be working on allowing syntax like "WHERE field = expr COLLATE collation" to expicitly specify collation for individual comparisons. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: sqlite3_total_changes() and multiple connections
Ron Stevens wrote: I have multiple database connections opened against the same database and I'm having problems with sqlite3_total_changes(). The docs state: "This function returns the total number of database rows that have be modified, inserted, or deleted since the database connection was created using sqlite3_open()." but it seems like only changes made through the connection I call the function on are counted. This is correct. Is there any way to get the total number of changes made through all opened connections? Get the numbers for each connection, and add them up. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Newbie question about LIKE and ESCAPE
A.J.Millan <[EMAIL PROTECTED]> wrote: I need a simple search, say: SELECT someField IN someTable WHERE name LIKE '%xyzetc%'; After some search in this list, I'm a bit more confused that before. For example after reading literally: The escape mechanism for LIKE has never been implemented in SQLite. I don't know where you are reading this. In any case, this information is obsolete. ESCAPE clause works since SQLite 3.1.0 Can someone unveil me the correct syntax for that query? For what query? You only need ESCAPE if you want to look for strings that themselves contain % or _ characters. Do you? What exactly are you trying to achieve? For example, this query retrieves all rows where someField contains '%' character: SELECT * FROM someTable WHERE someField LIKE '%\%%' ESCAPE '\'; Can I use some like: SELECT someField IN someTable WHERE name LIKE 'xyzetc' ESCAPE ; No. ESCAPE should be followed by a string consisting of exactly one character. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Why it does not work properly?
woj <[EMAIL PROTECTED]> wrote: Now, when I run a query: SELECT Mieszalnia.IdMat, Mieszalnia.Partia, Mieszalnia.Kont, Mieszalnia.Uk, Max(Mieszalnia.Data) FROM Mieszalnia; I always get: IdMat Partia KontUk Data 6 3 3 sl1 1172135769 In this result there is indeed max from Data field but rest of the fields fit not... When a SELECT statement involves aggregate functions, all column references in the SELECT must be either parameters to some aggregate functions, or else be also mentioned in GROUP BY clause. SQLite allows, as an extension, departure from this rule, but the row from which values for columns that are neither aggregated nor grouped by are taken is random and unpredictable. Specifically, in the query you show, there's no guarantee that values for IdMat, Partia and so on would be taken from the same row from which Max(Data) comes. Even if SQLite really wanted to help you out here, it is impossible in general. Consider: SELECT IdMat, Max(Data), Min(Data) from Mieszalnia; Which value of IdMat would you expect to see in response to such a query? Should it come from the row with the largest value of Data, with the smallest, or some other? There are many ways to formulate the query you seem to want. E.g. select IdMat, Data from Mieszalnia order by Data desc limit 1; select IdMat, Data from Mieszalnia where Data = (select max(Data) from Mieszalnia); Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Why it does not work properly?
woj <[EMAIL PROTECTED]> wrote: Thank's for your reply. I didnt realize this, but of course it is logical. So, now I think how to select not only one row (what seems to be easy) but set of rows from previously mentioned data where only these rows are picked up with largest Data value for each IdMat, so correct version of querry: SELECT Mieszalnia.IdMat, Mieszalnia.Partia, Mieszalnia.Kont, Mieszalnia.Uk, Max(Mieszalnia.Data) FROM Mieszalnia GROUPED BY Mieszalnia.IdMat; I'm not sure if this was meant as a question or a statement. In case it was in fact a question, consider this: select IdMat, ..., Data from Mieszalnia m1 where not exists ( select * from Mieszalnia m2 where m2.IdMat = m1.IdMat and m2.Data > m1.Data ); Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Sqlite3_prepare() question
Dennis Volodomanov wrote: I'm converting sqlite3_mprintf() into sqlite3_prepare_v2() and the SQL for that was like this: "SELECT *, Table1.ID AS _ID FROM Table1 LEFT JOIN Table2 ON Table2.ID=Table1.ID %s" and I was putting a "WHERE _ID=1", for example, in the %s Now, I'm trying to do the same: "SELECT *, Table1.ID AS _ID FROM Table1 LEFT JOIN Table2 ON Table2.ID=Table1.ID ?1" and then sqlite3_bind_text No, you can't do that. You can only use a parameter in place of, say, an integer constant or a string literal. You can't replace whole SQL fragments. My question is, can I do it like this or do I have to something like: "SELECT *, Table1.ID AS _ID FROM Table1 LEFT JOIN Table2 ON Table2.ID=Table1.ID WHERE _ID=?1" and bind only the _ID parameter in this case? This would work. The issue is that I don't know beforehand what can be in that WHERE statement and how many parameters it might have - it's formed elsewhere. Parameters cannot change a structure of the query. If you need to do this, then you have to build the query in a string, with sprintf or similar, then prepare. Of course you can still use parameters in place of any literals you may need in the query. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: What is wrong with this simple query (offset)?
RB Smissaert <[EMAIL PROTECTED]> wrote: Why does this query give a syntax error near offset? SELECT Name FROM SQLITE_MASTER WHERE TYPE = 'table' ORDER BY 1 ASC offset 2 The syntax doesn't allow OFFSET on its own, but only together with LIMIT. Make it LIMIT -1 OFFSET 2 -- or LIMIT 2, -1 -1 means no limit. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: What is wrong with this simple query (offset)?
RB Smissaert <[EMAIL PROTECTED]> wrote: I take it there is no way to make it not return the field name. I'm not sure what you mean by "not return the field name". My wild guess is you are using sqlite3 command line utility. In this case, type .header OFF before running the query. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Re: What is wrong with this simple query (offset)?
RB Smissaert <[EMAIL PROTECTED]> wrote: I am not using the sqlite3 command line utility, but a VB adapted version of the regular sqlite3.dll. I take it should be no problem to do the same with that. Is there a pragma for this? Nothing in SQLite API forces any kind of field names to be part of query resultset. If you see such a header, it must have been produced by whatever wrapper or adapter you are using. Consult the documentation or contact the authors of the same. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: A few (probably) simple questions ...
Stef Mientki <[EMAIL PROTECTED]> wrote: Q1: What's the difference between ON and WHERE, the 2 statements below return exactly the same ? SELECT Patient_text.*, Opnamen.* FROM Patient as P INNER JOIN Patient_text, Opnamen ON P.PatNr = Patient_text.PatNr WHERE P.PatNr = '1' SELECT Patient_text.*, Opnamen.* FROM Patient INNER JOIN Patient_text, Opnamen WHERE Patient.PatNr = Patient_text.PatNr AND Patient.PatNr = '1' These two queries are equivalent. The difference between ON and WHERE becomes important when the query involves outer joins. Q2: Why isn't ALIAS supported in the JOIN-line, or am I doing something wrong ? SELECT Patient_text.*, Opnamen.* FROM Patient INNER JOIN Patient_text, Opnamen AS O WHERE Patient.PatNr = Patient_text.PatNr ANDPatient.PatNr = '1' What exactly do you believe is not supported? Do you get an error with this statement? It looks good to me. Q3: In the SQL help on the web, I read: "/join-op/ ::= *, *|* *[*NATURAL*]* *[*LEFT *|* RIGHT *|* FULL*]* *[*OUTER *|* INNER *|* CROSS*]* JOIN*" But when I try a RIGHT JOIN, I get an error message ??? SQLite doesn't support right outer joins at this time, only left joins. By the way, SQL syntax supported by SQLite is documented here http://www.sqlite.org/lang.html And here are the limitations: http://www.sqlite.org/omitted.html Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: A few (probably) simple questions ...
Stef Mientki <[EMAIL PROTECTED]> wrote: Q2: Why isn't ALIAS supported in the JOIN-line, or am I doing something wrong ? SELECT Patient_text.*, Opnamen.* FROM Patient INNER JOIN Patient_text, Opnamen AS O WHERE Patient.PatNr = Patient_text.PatNr ANDPatient.PatNr = '1' What exactly do you believe is not supported? Do you get an error with this statement? It looks good to me. I get the following error ":: no such table: Opnamen" (running from Delphi) Well, a logical follow up question is, do you actually have a table named Opnamen in your database? Also, I'm somewhat surprised you don't mention Opnamen in the WHERE clause. Do you really want a full cartesian product? Yes, and the above quote is from http://www.sqlite.org/lang_select.html where nothing is said about not supporting "RIGHT", That's why I pointed you to http://www.sqlite.org/omitted.html. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Re: A few (probably) simple questions ...
Stef Mientki <[EMAIL PROTECTED]> wrote: I get the following error ":: no such table: Opnamen" (running from Delphi) Well, a logical follow up question is, do you actually have a table named Opnamen in your database? Yes, and "INNER JOIN Patient_text, Opnamen works as expected. Can't reproduce. Can you show a small complete sample - a series of CREATE TABLE statements followed by the query that reports syntax error? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Case Insensitive Equality Searches
[EMAIL PROTECTED] wrote: If I want to do "SELECT * FROM table WHERE field = value", how can I do this matching text only and not case? If you always need case insensitive comparison, the easiest way is to assign NOCASE collation to the field when creating the table: create table mytable (field char collate NOCASE, ...) If you need only this comparison to be case insensitive, you can do SELECT * FROM table WHERE upper(field) = upper(value); Reportedly, the latest CVS code for SQLite also supports SELECT * FROM table WHERE field=value collate NOCASE; This will probably make it into the next release. Note that NOCASE collation in SQLite only recognizes letters A through Z as being equal to a through z. It doesn't support any other characters, e.g. accented Latin characters or characters from other scripts. If you need any kind of linguistically correct collation, you need to provide one yourself (luckily SQLite supports custom collations). Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Argh, this must be a very stupid question ...
Stef Mientki <[EMAIL PROTECTED]> wrote: In the (windows) commandline version of sqlite: how do I open / connect an existing database ? Run it with database file name on the command line: sqlite3.exe mydb.db Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: How do I know what DBs I have attached?
jose isaias cabrera <[EMAIL PROTECTED]> wrote: What I would like to know is, how do I know if I have a db attached already? Realize that a set of attached DBs is a property of a connection (a sqlite3* handle), not some kind of persistent state of the database. You seem to be under impression that when two processes open the same DB file, and one process attaches another DB file, the other process can somehow query SQLite and find out that this happened. This is not the case. So, if you want to know if _you_ have attached a DB, just keep track of ATTACH DATABASE commands you have issued on your connection. If you want to know if someone else attached a DB to one you also happen to have open, SQLite can't help you there. Also, can different clients ATTACH to the same DB and REPLACE unique RECORDS without any problem? Well, only one connection can modify a particular database file at the same time. It doesn't matter if you open the file directly, or attach it to an existing connection. If two processes attempt the modifying operation at the same time, one of them will proceed and the other will be locked out. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Is there an inverse for .import?
Anderson, James H (IT) wrote: I need to "export" a table to a file in the same format as used by .import, but I don't see any such cmd. .dump Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: are nested joins possible ?
Stef Mientki <[EMAIL PROTECTED]> wrote: I thought this would work, SELECT * FROM Patient_Text INNER JOIN ( SELECT * FROM Patient INNER JOIN Opnamen ON Patient.PatNr = Opnamen.PatNr ) ON Patient.PatNr = Patient_Text.PatNr But I get an error on the second use of Patient.PatNr. The whole subselect is treated as a single table, you can't address individual tables that went into it anymore. You can give the subselect an alias: SELECT * FROM Patient_Text INNER JOIN ( SELECT * FROM Patient INNER JOIN Opnamen ON Patient.PatNr = Opnamen.PatNr ) AS Patient ON Patient.PatNr = Patient_Text.PatNr However, in this particular case you'll have a problem, since the result of subselect contains two columns named PatNr (one from Patient and one from Opnamen). So Patient.PatNr will be ambiguous. You could explicitly name columns in the subselect and give them aliases, then refer to them as SubselectAlias.ColumnAlias. But this way you won't be able to use * notation. And of course, this particular query can be rewritten as SELECT * FROM Patient_Text JOIN Patient ON Patient.PatNr = Patient_Text.PatNr JOIN Opnamen ON Patient.PatNr = Opnamen.PatNr; It will also likely be much more efficient: all these nested subselects pretty much disable SQLite optimizer. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: What is wrong with this UPDATE?
RB Smissaert <[EMAIL PROTECTED]> wrote: UPDATE A3SQLADC_J SET ADDED_DATE = '' WHERE ADDED_DATE = 0 OR ADDED_DATE IS NULL, START_DATE = '' WHERE START_DATE = 0 OR START_DATE IS NULL near ",": syntax error Make it UPDATE A3SQLADC_J SET ADDED_DATE = (case when ADDED_DATE = 0 OR ADDED_DATE IS NULL then '' else ADDED_DATE end), START_DATE = (case when START_DATE = 0 OR START_DATE IS NULL then '' else START_DATE end); Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: How do I know what DBs I have attached?
jose isaias cabrera <[EMAIL PROTECTED]> wrote: Perhaps, this should be the case. It would not be so hard to keep a table of connections that are attached or open with a table. You don't want an otherwise read-only connection have to write to the database. This will harm concurrency. Well, only one connection can modify a particular database file at the same time. It doesn't matter if you open the file directly, or attach it to an existing connection. If two processes attempt the modifying operation at the same time, one of them will proceed and the other will be locked out. Is there a way of telling the DB to UPDATE records after the actual connection has completed its processing? Something like UPDATE queue? I'm not sure I understand the question. Update records after which of possibly multiple connections has completed its processing? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Re: How do I know what DBs I have attached?
jose isaias cabrera <[EMAIL PROTECTED]> wrote: jose isaias cabrera <[EMAIL PROTECTED]> wrote: Perhaps, this should be the case. It would not be so hard to keep a table of connections that are attached or open with a table. You don't want an otherwise read-only connection have to write to the database. This will harm concurrency. Oh, I agree. I'm confused. Didn't you just say that it would be a good idea for every connection to be recorded in some table in the database? Wouldn't that require every connection to write to the database as part of opening or attaching? The reason why I want to do it, is to not attach, if there one attached, already. For the exact reason that you point out. Since I have a few users that will update this DB at any moment, meaning that could do it at the same time, I want to have a catch for that problem. As soon as you try to update, if another update is already in progress, you'll get an error from SQLite. Wouldn't that be sufficient to "catch the problem"? Let us say that I have a few users that would connect to this DB to update it at any moment. Say user one connects to do an update to his data. At millisecond later, user2 connects to do an update to his data, and here is the question, can user say say something like, UPDATE TAble after database is not busy. Not quite, but you can retry again some time later. See also sqlite3_busy_handler, sqlite3_busy_timeout. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Meta Information: How to retrieve the column names of a table ?
Marten Feldtmann wrote: How can I get all the names of a table without doing a query against the table ? PRAGMA table_info(table-name); I need all the names of columns within tables/views, the column index within the raw table. What's "column index" and "raw table"? I'm not familiar with the terms. With that information I may use sqlite3_column_meta_data to get additional information about the coumns ... What's sqlite3_column_meta_data? It doesn't seem to be mentioned in documentation. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: results from a prepared select statement
Rafi Cohen <[EMAIL PROTECTED]> wrote: Now my question is how do I know, after sqlite3_step, if I got results at all. sqlite3_step returns SQLITE_ROW if you got a row of results, SQLITE_DONE if no more rows, and SQLITE_ERROR in case of error. If resultset is empty, you'll just get SQLITE_DONE on the first call. I would like my application to have this information before trying to retrieve the results by using sqlite3_column* functions. You can only use sqlite3_column* functions if the previous call to sqlite3_step returned SQLITE_ROW. From the other side, if I retrieve those results in a loop, I would like to now how many rows I need to retrieve? You can't know that until you retrieve them all. SQLite engine itself doesn't know that. Just keep going until you get SQLITE_DONE. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Meta Information: How to retrieve the column names of a table ?
Stef Mientki <[EMAIL PROTECTED]> wrote: PRAGMA table_info(table-name); And it also works for views, as just found out by trial and error ;-) Is this standard SQL behavior ? PRAGMA statement is not part of standard SQL. I don't believe there is a standardized way to access metadata. Every DBMS provides its own syntax. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: to quote or not ?
Stef Mientki <[EMAIL PROTECTED]> wrote: So I would expect that I now can always double quote the selection fields, but unfortunately that doesn't seem to be true. From a graphical design, I get for instance: SELECT "Opnamen.PatNr", "Opnamen.Datum" Opnamen, PatNr and Datum are three separate identifiers, and have to be quoted separatedly (if at all), as in "Opnamen"."PatNr". "Opnamen.PatNr" is a single identifier, distinct from Opnamen.PatNr (which is two identifiers separated by period). To illustrate, consider these valid SQL statements: create table Opnamen (PatNr, "Opnamen.PatNr"); insert into Opnamen values (1, 2); select Opnamen.PatNr, "Opnamen"."PatNr", "Opnamen.PatNr", Opnamen."Opnamen.PatNr" from Opnamen; The last query should return a single row with values (1, 1, 2, 2) Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Adding columns of records and updating the result to a record
jose isaias cabrera <[EMAIL PROTECTED]> wrote: I have a system that links two or more records to one head record using a column called ProjID, where ProjID has the unique recNO of the head record. Anyway, what I would like to do is something like this... Imagine, recNo,ProjID,Invoice,Fund 1,1,, 2,1,10.00,30.00 3,1,20.00,60.00 4,1,100.00,600.00 5,5,, 6,5,8.33,20.00 7,5,1.00,5.00 ... ... recNo is unique. What I would like to do is to keep the head record (1 and 5, on this instance) updated as the other children values get updated. Updated means, the Invoice sum of all the child records on the Invoice column of the head record, and the same for the Fund column. This is a very bad design. Relational model doesn't easily incorporate this notion of "special" records. All records in a table should be uniform. I suggest removing these "head" records from the table altogether. You can always calculate them whenever necessary, like this: select ProjId, sum(Invoice), sum(Fund) from tableName group by ProjId; You can make this query into a view if you are so inclined. If, for some reason, you insist on storing these totals in the database persistently, create a separate table containing just those totals records. Use triggers to update these totals whenever something changes in the main table. Again, do not put totals records in the main table. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: 2 questions concerning select statement
Rafi Cohen <[EMAIL PROTECTED]> wrote: 1. When I prepare a select statement for later execution, may I use a question mark instead of a table and later "bind" different table names No. You can only use a parameter where an expression would be valid. 2. In "order by" clause may I use a column which is part of the table but nor part of the result? Yes. In fact, you can use any expression, not just a column name. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: multithread problem
Rafi Cohen <[EMAIL PROTECTED]> wrote: 1. Should I open the database explicitly in the amin part and also in the thread? In my experience, SQLite works best when every thread opens its own connection. 2. should I create the tables in the thread or can I create them in the main thread and modify them in the other? You can create tables on any thread, it doesn't matter. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: data type problem
qinligeng-9Onoh4P/[EMAIL PROTECTED] wrote: if you create a table use following statement (script generated from MS SQL Server 2000) CREATE TABLE [XTollData] ( [DutyID] [char] (32) NOT NULL , [CarNumber] [char] (10) NULL ); SQLite3_Column_decltype will treat DutyID as data type 'char' but not 'char(32)' SQLite ignores length restriction. Any cell may store a string of arbitrary length (or, indeed, any other supported data type). For more details, see http://sqlite.org/datatype3.html Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Raise not working.
Laurent LAVAUD wrote: I have a problem to get < RAISE > function working. When the trigger is triggered by an insert, i only want to execute the < update > and skip the first insert. create trigger checkdrop before INSERT on blocked when (select count(*) from blocked where id=new.id) > 0 BEGIN when What is this 'when' doing here? update blocked set hits = hits + 1 where id = new.id; RAISE (IGNORE) RAISE is a function, not a statement. Make it select RAISE(IGNORE); Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Question about multithreading
Gil Delavous <[EMAIL PROTECTED]>
wrote:
However, what happens when a thread calls a method from the main
thread, this one using its own sqlite connection?
The question doesn't make any sense to me, sorry. Methods don't belong
to threads. Any function in a program can, in principle, be executed by
any thread at any time, including by multiple threads simultaneously.
For example:
Main thread:
void main::query_something() {
// query something using main thread's sqlite connection
}
Print thread:
void print::print_result() {
int value = main->query_something();
}
What makes you think these classes are somehow affine to a particular
thread? They are not. When you call query_something from print_result,
the same thread that executed print_result now executes query_something,
whether it's a "main" thread (whatever that means) or otherwise.
If it's your intention that all methods from class main be called on one
thread, and all methods of print be called on another, it's up to your
program to ensure that. You need some kind of inter-thread communication
mechanism, e.g. a producer/consumer queue.
As my main thread has tons of utility methods called from other
threads
You seem to say "thread" when you mean "class", and this lies at the
heart of your confusion. Realize that the two are entirely different,
largely unrelated concepts.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: Holding sqlite connection
Gil Delavous <[EMAIL PROTECTED]> wrote: I was wondering if its better to open a sqlite database connection when my application launches, use it all along the process life, and closing it when it exits... or if its better to open/close the database connection each time a method has to query/store data, thus leaving the database not open all the time. There is no downside to keeping the database open. On the other hand, when SQLite opens the database it reads and parses its schema, which takes some (short) time, so you usually don't want to do it too often. As my application is multithreaded (each thread open its own, global connection too) I'm not sure if holding connections is the best way to avoid conflicts (I sometimes have SQLITE_MISUSE errors). Just holding a connection open doesn't cause conflicts by itself - you need to actually execute some statement. SQLITE_MISUSE error doesn't indicate a conflict (SQLITE_BUSY does) - it means you are doing something wrong, like trying to execute a statement that was already finalized. In other words, SQLITE_MISUSE signals a bug in your program. Find it and fix it. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: SPAM: Re: Question about multithreading
Voxen <[EMAIL PROTECTED]> wrote: So, from my example, let say thread A created the object "main", and thread B created the object "print". When thread B calls the method from object "main" (as shown by the example), my question was to know if the sqlite connection opened by object "main" can be considered as shared with thread B Of course. much like if I used this connection pointer directly from thread B? There's no "like" - you do use connection pointer directly from thread B. The fact that the piece of code thread B currently executes is a method of an object that happened to be created by thread A is immaterial. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Clarification of bound parameter usage
Ian Frosst <[EMAIL PROTECTED]> wrote: The problem here though, is that I'm doing the prepare/step/finalize each time I want to execute the query, even though the only thing that is going to change are the values. Can I write a loop which prepares the SQL, using named parameters, then in a loop just call sqlite3_bind_*/step/reset, finalizing after all of my calls have been done? Of course. That's precisely what parameters are for. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Re: Question about multithreading
Voxen <[EMAIL PROTECTED]> wrote: There's no "like" - you do use connection pointer directly from thread B. The fact that the piece of code thread B currently executes is a method of an object that happened to be created by thread A is immaterial. That clears things and it shows me I need to open/close the database locally when a method is called by several threads. Well, you can have each thread open a connection, then pass it along as a parameter to whatever function the thread needs to call. This way, the method would always operate on a connection associated with whatever thread were calling it, and you won't have to keep opening and closing connections all the time. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Missing tables in sqlite_master
Christian Mattar <[EMAIL PROTECTED]> wrote:
I've been having trouble with SQLite in PHP. Basically I want to
iterate over all table of a database. I use the following query:
$handle = sqlite_open("db.sqlite");
$result = sqlite_query($handle, "SELECT name FROM sqlite_master WHERE
type='table' ORDER BY name");
$tables = sqlite_fetch_array($result);
var_dump($tables);
Unfortunately, it only returns the first table name in the result.
http://www.phpbuilder.com/manual/en/function.sqlite-fetch-array.php
"Fetches the next row from the given result handle. If there are no more
rows, returns FALSE, otherwise returns an associative array representing
the row data."
You need to call it repeatedly to retrieve all the rows.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: Store and retreive 0D0A (CRLF) in string field
fangles <[EMAIL PROTECTED]> wrote: When I have text pasted into an sqlite string field, it is stored okay but when I retrieve a string, it is truncated at the first CR (0D). SQLite doesn't truncate anything. Whatever you put in you get out. If you see a truncation, it is either done by whatever wrapper you use on top of SQLite, or simply an artifact of the way you inspect the data (e.g. you look at the string in a debugger, and the debugger just happens to show only the first line). Use sqlite3_column_bytes[16] to convince yourself that SQLite gives you the complete string. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: The IN keyword
Jonas Sandman <[EMAIL PROTECTED]> wrote: Still, can this be done without knowing how many extensions there are on beforehand? Something like this perhaps: SELECT * FROM Files WHERE :extension like '%!' || extension || '!%'; and build extension list like !mp3!avi!ogg! Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Index usage for inequalities and GROUP BY
Brownie <[EMAIL PROTECTED]> wrote: I have a table and indices as follows; CREATE TABLE employee(name TEXT,salary INTEGER,job TEXT); CREATE INDEX idx_emp_salary_job ON employee(salary,job); CREATE INDEX idx_emp_job ON employee(job); When I use the following query, SQLite seems to use idx_emp_salary_job for both WHERE and GROUP BY. SELECT job, COUNT(name) FROM employee WHERE salary=100 GROUP BY job; 0|0|TABLE employee WITH INDEX emp_salary_job ORDER BY But when modify this query as follows, SQLite seems to use idx_emp_salary_job for WHERE only. Not used for GROUP BY. SELECT job, COUNT(name) FROM employee WHERE salary>=100 GROUP BY job; 0|0|TABLE employee WITH INDEX emp_salary_job Because index on (salary, jobs) is not helpful here. This index lists rows ordered by salary, and for each distinct value of salary rows are ordered by job. Your first query narrowed down to a single value of salary, at which point the index gave you rows ordered by job and group by was trivial. But now that you have multiple salary values, SQLite has to sort on job anyway. See if this query gets better performance: SELECT job, COUNT(name) FROM employee WHERE +salary>=100 GROUP BY job; Unary plus would suppress the index involving salary, so one on (job) should be used. Another thing to try is to create an index on (job, salary) rather than (salary, job). So, I modify it by using WHERE EXISTS and subqueries. SELECT job, COUNT(name) FROM employee WHERE EXISTS (SELECT * FROM employee WHERE salary>=100) GROUP BY job; This statement doesn't make any sense. The subselect doesn't depend on the outer select in any way, so it would be executed once and EXISTS clause will either produce 1 or 0. Assuming there are indeed records with salary >= 100, this query is equivalent to SELECT job, COUNT(name) FROM employee GROUP BY job; which is quite different from the original query. 0|0|TABLE employee WITH INDEX emp_job ORDER BY 0|0|TABLE employee WITH INDEX emp_salary_job It seems to use indices for both WHERE EXISTS and GROUP BY. Why not? They are separate, largely unrelated queries. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: what's the fastest way to get the record count of a table?
qinligeng-9Onoh4P/[EMAIL PROTECTED] wrote: I want to check the record count of a table every 5 seconds. It seems there's only one way to get a table's record count: select coun(*) from ATable but if the table gets big(1,000,000 rows), it will get slow. Any good idea? Store record count in a separate table, use triggers to keep it up-to-date as rows get added and deleted in the main table. In fact, instead of polling, you can use the same triggers to notify you of changes, e.g. by having them call a custom function. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Python-Sqlite Unicode characters
Kostas5904 wrote:
But...
When I ask a value from the user and I try to store it into the same
table,
I run the following code
item=dialog.GetValue()
table="names1"
cols="id,descr,type"
values=("null",item,3)
a="insert into %s (%s) values %s" % (table,cols,values)
cursor.execute(a)
and I get the error:
pysqlite2.dbapi2.OperationalError: near
"'\u03b1\u03bd\u03c4\u03ce\u03bd\u03b7\u03c2'": syntax error
Inspect the value of 'a' variable right before execute() call. You'll
find it's all wrong. The values must be in parentheses, string literals
must be quoted. While we are at it, in SQL string literals should be
quoted with single quotes. SQLite allows double quotes as an extension,
but it is bad practice to rely on that.
Better still, use parameterized query and bind your values to
parameters. Your approach is wide open to SQL injection attack. Consider
what happens if I type the following into the dialog field:
sometext", 3); delete from names1; --
See what kind of statement you are going to construct and run given this
input.
Even if I'm not being malicious, consider what happens if I innocently
put text containing quotes into the description field.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: create or update question
Eric S. Johansson <[EMAIL PROTECTED]> wrote: I'm trying to figure out how to do do the following: if the record exists Update record with calculation ( a = a + v) else insert record with default values It seems like the "insert or replace into" capabilities is close to what I need but I can't figure out how to update with a default on the first record and update with a calculation on existing records. I can't think of any way to avoid two requests. Run the update statement, use sqlite3_changes to check whether any row has actually been updated. If not, run the insert. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: SQL help
Clark Christensen wrote: I have a table, as described below, where I need to find out if the tech_id in question has at least some modules in a particular collection (coll_id), and they're all complete. select coalesce(min(is_complete), 0) from tech_modules where tech_id=? and coll_id=?; This returns 0 if there's at least one record with is_complete=0 (min will select it), or if there are no matching records at all (min will produce NULL, coalesce will convert it to 0). Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Novice help
Raju Penmetsa wrote: I downloaded all the Windows binary files of SQlite on the download page of sqlite.org. I extracted all of them into separate folders. Now, when I open sqlite3.exe in a DOS window and type - "sqlite3 new.db", it should create a new db if there is none already existing, right? It is not creating anything for me Or I am not able to find where it is creating one. It delays actually creating the file until you create the first table. Run a valid CREATE TABLE statement. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: What query should I use?
erw2 <[EMAIL PROTECTED]> wrote: I have a table with a following data: IdText1 Text2 Text3 Text4 --- 11001 11 test03 test13 21002 11 test01 test11 31003 12 test04 test12 41004 12 test02 test34 51004 12test06 test56 61005 11test17 test67 71005 12test07 test57 81006 13 test05 test98 91007 13 test02 test93 10 1008 14 test01 test03 ... ..... .. Now, I would like to select only the rows when Text2 change. So the result of such query should look like: IdText1 Text2 Text3 Text4 --- 11001 11 test03 test13 31003 12 test04 test12 61005 11 test17 test67 71005 12 test07 test57 81006 13 test05 test98 10 1008 14 test01 test03 ... ..... .. How should this query look like? select * from tableName t1 where t1.Text2 != ( select t2.Text2 from tableName t2 where t2.Id < t1.Id order by t2.Id desc limit 1 ); Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Math functions
Nathan Biggs <[EMAIL PROTECTED]> wrote: Does anyone know if there is a floor function in sqlite, or of a way to implement it. cast(expr as integer) truncates towards zero, in case it helps. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: What query should I use?
woj <[EMAIL PROTECTED]> wrote: Thanks for your reply, the query you have suggested works fine, but there is missing the first row, so the result look: IdText1 Text2 Text3 Text4 --- 31003 12 test04 test12 61005 11 test17 test67 71005 12 test07 test57 81006 13 test05 test98 10 1008 14 test01 test03 ... ..... .. but of course there is an EXACT answer for my question - there are selected only the rows when Text2 changes... How to get also always the first row from original data set? select * from tableName t1 where coalesce( t1.Text2 != (select t2.Text2 from tableName t2 where t2.Id < t1.Id order by t2.Id desc limit 1), 1); Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Math functions
Brad Stiles <[EMAIL PROTECTED]> wrote: On 4/4/07, Nathan Biggs <[EMAIL PROTECTED]> wrote: Does anyone know if there is a floor function in sqlite, or of a way to implement it. Well, the suggestions about extending SQLite are probably quicker, but if a SQL solution is required for portability, you can try something like this: select case when @float >= 0.0 then cast(@float as int) else cast(@float-1 as int) end This would give wrong answer if @float is negative and whole. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Currency Formatting within SQLite
Mitchell Vincent <[EMAIL PROTECTED]> wrote: I know it's a long shot but is it possible to use the Windows API GetCurrencyFormat() function to format currency strings? I need a layer between my database and report generator to properly format currency (money) strings for various countries. I assume you want to have SQLite format values for you (though you are talking about a layer above the database which seems to suggest otherwise). You can install a custom function - see sqlite3_create_function[16]. This function can do whatever you want, including calling GetCurrencyFormat. Then you can do something like select FormatCurrency(currencyValue) from ...; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Currency Formatting within SQLite
Mitchell Vincent <[EMAIL PROTECTED]> wrote: Yes, I know about creating a function but I'm wondering if I can hook into the already-existing Windows API function for it. Currency formatting is quite complicated (much more than just storing a currency "symbol") and Windows has already done the leg work - I just need to see gain access to the API function for it. I don't understand. You can install a custom function into SQLite engine - the function that you implement. This function can then be used in any SQL statement. Within implementation of that function, you can happily use GetCurrencyFormat or any other API. What again seems to be the problem? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: sqlite3.exe .import command
[EMAIL PROTECTED] wrote: I get a message that 13 fields were expected but only one found. The file I am importing is comma delimited text with one line per record. I could find no documentation on format expectations of the .import command. I believe it expects TABs by default, but you can change it with .separator , I would appreciate help on this particular problem and also a URL for any documentation of the sqlite3.exe program. There's a built-in help accessible with ".help" command. Not very extensive, but usually sufficient. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Re: Currency Formatting within SQLite
Mitchell Vincent <[EMAIL PROTECTED]> wrote: The only problem is my lack of understanding on how implementing custom functions in SQLite works. I'm sure your idea is sound I just don't know the details of implementing it. For instance, is this something that has to be compiled into the DLL, or is it database file specific? It can be compiled into any piece of code that can call sqlite3_create_function[16] API. Custom functions are not permanent, they exist within a particular database connection, the one whose handle you pass as the first parameter to sqlite3_create_function. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Nested SELECT : Alternative syntax please ?
Jaime Castells <[EMAIL PROTECTED]> wrote: Actually, Jay, I've bumped into cases where you couldn't avoid a nested query. Here's an example that works in SQLite: SELECT COUNT(funky_values) FROM (SELECT substr(locations.code, 5,9) AS funky_values FROM locations WHERE locations.code LIKE 'B%') WHERE funky_values LIKE '1%' ; select count(*) from locations where locations.code like 'B___1%'; -- or select count(*) from locations where locations.code like 'B%' and substr(locations.code, 5, 9) like '1%'; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Nested SELECT : Alternative syntax please ?
Jaime Castells <[EMAIL PROTECTED]> wrote: Hmm, I think the only time you really can't avoid a nested query is when you want to do a grouping function on the result of a grouping function. Like count the number of customers who have more than a certain number of orders. Something like: SELECT COUNT(cust.id) FROM (SELECT cust.id, COUNT(order.id) AS order_count FROM cust, order WHERE order.cust_id=cust.id GROUP BY cust.id) WHERE order_count>10 ; select count(cust.id) from cust where ( select count(*) from order where order.cust_id = cust.id) > 10; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Nested SELECT : Alternative syntax please ?
Jay Sprenkle <[EMAIL PROTECTED]> wrote: On 4/8/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote: Jaime Castells <[EMAIL PROTECTED]> wrote: Hmm, I think the only time you really can't avoid a nested query is when you want to do a grouping function on the result of a grouping function. Like count the number of customers who have more than a certain number of orders. Something like: SELECT COUNT(cust.id) FROM (SELECT cust.id, COUNT(order.id) AS order_count FROM cust, order WHERE order.cust_id=cust.id GROUP BY cust.id) WHERE order_count>10 ; select count(cust.id) from cust where ( select count(*) from order where order.cust_id = cust.id) > 10; I think you could also do that with this: select cust.id, count(*) from order inner join order on order.cust_id = cust.id group by cust.id having count(*) > 10 This returns a list of customers that have more than 10 orders. Jaime wanted a count of such customers, not a list of them. His original query produces a single number. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: SQLite and nested transactions
Dennis Cote <[EMAIL PROTECTED]> wrote:
Darren Duncan wrote:
I will clarify that child transactions are just an elegant way of
partitioning a larger task, and that parent transactions always
overrule children; even if a child transaction commits successfully,
a rollback of its parent means there are no lasting changes.
Because of this, and the fact that a transaction is basically a
guarantee that all or none of the enclosed statements are executed, it
is much simpler to implement nested transactions using a counter and
the existing transaction API in a set of wrapper functions. There is
no
need to maintain all the intermediate state information.
Support for nested transactions should allow one to roll back the child
transaction to the point where it has started, but still commit the
overall transaction. Consider:
BEGIN parent;
insert into t values ('a');
BEGIN child;
insert into t values ('b');
ROLLBACK child;
insert into t values ('c');
COMMIT parent;
As a result of this sequence, the table should have two new rows with
values 'a' and 'c', but not 'b'. As far as I can tell, this is
impossible to implement in terms of existing SQLite API.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: SQLite and nested transactions
Griggs, Donald <[EMAIL PROTECTED]> wrote: Regarding: "...As Igor pointed out this does not resemble a full implementation of transactions, as nested transactions can be committed and rolled back independently of the outer parent transaction." Nonetheless, it would seem, just from the couple of pages below, that some DB vendors find the less-than-full implementation of nested transactions to be useful for at least some purposes. (I.e., an implementation in which inner transactions do little more than adjust counters. If anything is rolled back, then entire outer transaction is rolled back.) I make no claim to being an expert here -- I'm just a googler. ;-) [quotes from documentation that only deal with COMMIT snipped] The interesting question is not what happens on commit of a nested transaction (the answer is "nothing much" for all reasonable DBMS), but what happens on _rollback_. A full implementation would roll back to the state at which nested transaction originated, and keep the enclosing transaction(s) open. A simple implementation would roll back all outstanding transactions and discard all changes. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
