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.
Igo
maybe it's also supported.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
maybe it's also supported.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
OM 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 pro
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]
-
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]
-
See sqlite3_changes, sqlite3_total_changes
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
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.
qlite3_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]
-
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 Tan
w.sqlite.org/datatype3.html
In particular the notion of column type affinity.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
.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.
Igo
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]
-
rker. 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 manipula
a separate DROP TABLE query for each table name.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
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]
-
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
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]
-
lauses. 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]
-
ndently, to
the same or different values.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
'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
ATE 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 IN
tax 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 s
erivEvent b, tmp_events c
where C1_credDerivEvent.CDId = b.CDId and b.CDEvent=c.CDEvent
)
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
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_e
e 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 Tan
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]
-
d 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]
-
mn 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]
-
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]
-
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
e
when new.value = 'something'
begin
...
end;
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
utting 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.
Igo
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 a
would need to create a new table and migrate
data.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
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]
-
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]
-
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 de
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]
-
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
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'
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
qlite3 session.
I'd check the code for typos, very carefully.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
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
syntax like "WHERE field = expr COLLATE collation" to expicitly
specify collation for individual comparisons.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
ection 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
T 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]
-
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);
Igo
r 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]
-
ill use parameters in place
of any literals you may need in the query.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-- or
LIMIT 2, -1
-1 means no limit.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
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 run
art 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
uot;
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 li
uot;RIGHT",
That's why I pointed you to http://www.sqlite.org/omitted.html.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
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]
-
ation, you need to provide
one yourself (luckily SQLite supports custom collations).
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
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 Tan
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.
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]
-
amen 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]
-
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]
-
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 process
s 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_
t;? 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 menti
rieve 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_D
data. Every DBMS provides its own syntax.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
ider these valid SQL statements:
create table Opnamen (PatNr, "Opnamen.PatNr");
insert into Opnamen values (1, 2);
select
Opnamen.PatNr, "Opnamen"."PatNr",
"Opnamen.PatNr", Opnamen."Opname
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
---
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]
-
ad 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]
-
7; 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
=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
-
ead 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
---
xecute 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 an
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]
-
n 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]
-
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]
-
lt 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]
-
her 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 str
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 !mp
? They are separate, largely unrelated queries.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
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
t.
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]
-
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.
ll_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 u
y 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
--
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
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
--
ys
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
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
-
ormat. Then you can do something like
select FormatCurrency(currencyValue) from ...;
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
tion, 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]
-
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.
o 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_
ocations.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%' a
ething 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
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 groupi
7;);
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
impossi
ble 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 discar
1 - 100 of 3828 matches
Mail list logo
