Re: [sqlite] Need help with SQL query

[Kees Nuyt]

On Fri, 22 Sep 2017 10:54:21 +0100, John G
 wrote:

>I know this is an older thread, but shouldn't that reference be on the ITEM
>table ?  So ...
>
>CREATE TABLE ATTRIBUTES (
>ITEM_ID INTEGER REFERENCES ITEM(ID) ON DELETE CASCADE,
>   KEY   TEXT,
>VALUE TEXT,
>PRIMARY KEY (ITEM_ID,KEY)
>  ) WITHOUT ROWID;
>
>John G

Yes, you are right. I didn't pay enough attention typing that
code. My apologies for any confusion that may have caused.

-- 
Regards,
Kees Nuyt



>On 11 September 2017 at 13:11, Kees Nuyt  wrote:
>
>> On Mon, 11 Sep 2017 14:05:25 +0200, Kees Nuyt 
>> wrote:
>>
>> > CREATE TABLE ATTRIBUTES (
>> >  ITEM_ID INTEGER REFERENCES ATTRIBUTES(ID) ON DELETE CASCADE,
>> >   KEY   TEXT,
>> >   VALUE TEXT,
>> >   PRIMARY KEY (ITEM_ID,KEY)
>> > ) WITHOUT ROWID;
>> > CREATE INDEX attr_item_id ON ATTRIBUTES(ITEM_ID);
>>
>> Correction:
>> In this construct, it makes no sense to create the index
>> attr_item_id, because the ITEM_ID is the first column
>> of the primary key (which is indexed implicitly).
>>
>> So, you can leave out the CREATE INDEX attr_item_id
>> statement in this case.
>>
>> --
>> Regards,
>>
>> Kees Nuyt
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help with SQL query

[John G]

I know this is an older thread, but shouldn't that reference be on the ITEM
table ?  So ...

CREATE TABLE ATTRIBUTES (
ITEM_ID INTEGER REFERENCES ITEM(ID) ON DELETE CASCADE,
   KEY   TEXT,
VALUE TEXT,
PRIMARY KEY (ITEM_ID,KEY)
  ) WITHOUT ROWID;

John G

On 11 September 2017 at 13:11, Kees Nuyt  wrote:

> On Mon, 11 Sep 2017 14:05:25 +0200, Kees Nuyt 
> wrote:
>
> > CREATE TABLE ATTRIBUTES (
> >  ITEM_ID INTEGER REFERENCES ATTRIBUTES(ID) ON DELETE CASCADE,
> >   KEY   TEXT,
> >   VALUE TEXT,
> >   PRIMARY KEY (ITEM_ID,KEY)
> > ) WITHOUT ROWID;
> > CREATE INDEX attr_item_id ON ATTRIBUTES(ITEM_ID);
>
> Correction:
> In this construct, it makes no sense to create the index
> attr_item_id, because the ITEM_ID is the first column
> of the primary key (which is indexed implicitly).
>
> So, you can leave out the CREATE INDEX attr_item_id
> statement in this case.
>
> --
> Regards,
>
> Kees Nuyt
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help with SQL query

[Kees Nuyt]

On Mon, 11 Sep 2017 14:05:25 +0200, Kees Nuyt 
wrote:

> CREATE TABLE ATTRIBUTES (
>  ITEM_ID INTEGER REFERENCES ATTRIBUTES(ID) ON DELETE CASCADE,
>   KEY   TEXT,
>   VALUE TEXT,
>   PRIMARY KEY (ITEM_ID,KEY)
> ) WITHOUT ROWID;
> CREATE INDEX attr_item_id ON ATTRIBUTES(ITEM_ID);

Correction:
In this construct, it makes no sense to create the index
attr_item_id, because the ITEM_ID is the first column
of the primary key (which is indexed implicitly).

So, you can leave out the CREATE INDEX attr_item_id 
statement in this case.

-- 
Regards,

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

Re: [sqlite] Need help with SQL query

[Kees Nuyt]

On Sun, 10 Sep 2017 20:35:16 -0700, Vikas Aditya
 wrote:

> Hi All,
>
> I need some help in figuring our right query syntax for querying
> items from two tables. We have two tables. One of the table has
> list of items. And Second table has additional attributes.

Adding to the suggestions of Ryan Smith, I would suggest a few
table definition optimizations:

> CREATE TABLE ITEM (
> ID INTEGER,
> FIELD0 TEXT
> FIELD1 TEXT,
> FIELD3 TEXT,
> FIELD4 TEXT
> );

By adding 'PRIMARY KEY to the ÍD column, it becomes an alias for
the internal ROWID column, saving space. Also, to reference a
parent table, the reference needs to point to a unique column. 
A primary key fulfills that requirement.

CREATE TABLE ITEM (
ID INTEGER PRIMARY KEY,
FIELD0 TEXT
FIELD1 TEXT,
FIELD3 TEXT,
FIELD4 TEXT
);

> CREATE TABLE ATTRIBUTES (
> ID INTEGER PRIMARY KEY,
> ITEM_ID INTEGER,
> KEY TEXT,
> VALUE TEXT
> );

Do the same for the primary key of the attributes table.
Add a foreign key constraint to formalize the relation
between the parent and child table. This provides 
"referential integrity".
The index will often speed up JOIN operations.

CREATE TABLE ATTRIBUTES (
  ID INTEGER PRIMARY KEY,
  ITEM_ID INTEGER REFERENCES ATTRIBUTES ON DELETE CASCADE,
  KEY   TEXT,
  VALUE TEXT
);
CREATE INDEX attr_item_id ON ATTRIBUTES(ITEM_ID);

Even better, the ID column can be left out, and a different
primary key will enforce that the same KEY can only be used once
for any ITEM_ID:
 
CREATE TABLE ATTRIBUTES (
  ITEM_ID INTEGER REFERENCES ATTRIBUTES(ID) ON DELETE CASCADE,
  KEY   TEXT,
  VALUE TEXT,
  PRIMARY KEY (ITEM_ID,KEY)
) WITHOUT ROWID;
CREATE INDEX attr_item_id ON ATTRIBUTES(ITEM_ID);

Note: 
Foreign key constraints are only enforced when 
   PRAGMA foreign_keys=on;
is executed after opening the database, before any
INSERT/UPDATE/DELETE statement is issued.

HTH

-- 
Regards,

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

Re: [sqlite] Need help with SQL query

[R Smith]

Correction:


On 2017/09/11 6:43 AM, R Smith wrote:


SELECT I.ID, ISNULL(A.VALUE,'[No Value]')
  FROM ITEM AS I
  LEFT JOIN ATTRIBUTES AS A ON A.ITEM_ID = I.ID
 WHERE A.key='abc' OR A.key IS NULL
 ORDER BY A.VALUE;


There is of course no such thing as SORT BY in SQL, it's ORDER BY.
(Forgive me, it's 6am and I need to go to bed still...)



Note 1:  Left join will list all the values from the first table (the 
LEFT table) and add results where possible from the second (RIGHT) 
table, else the values will be NULL for it.
Note 2: ISNULL(x,a) Will output the value of x, unless it is NULL, in 
which case it will show a.
Note 3:  The strings in SQL has single quotes (like 'abc'), only 
identifiers get double quotes.
Note 4: LIMIT and OFFSET is a very bad way to do paging (in case 
that's what you are planning). It's good for limiting the size of a 
query, but offset has to reproduce the entire query every time and 
wait for the offset number of rows to pass before it can jump in and 
start adding rows to the output - it's not really "remembering" where 
it left off. You can do that better with temporary tables containing a 
result set and then stepping through those tables based on a key.


Cheers,
Ryan


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

Re: [sqlite] Need help with SQL query

[R Smith]

On 2017/09/11 5:35 AM, Vikas Aditya wrote:

Hi All,

I need some help in figuring our right query syntax for querying items from two 
tables. We have two tables. One of the table has list of items. And Second 
table has additional attributes.

CREATE TABLE ITEM (
ID INTEGER,
FIELD0 TEXT
FIELD1 TEXT,
FIELD3 TEXT,
FIELD4 TEXT
);

CREATE TABLE ATTRIBUTES (
ID INTEGER,
ITEM_ID INTEGER,
KEY TEXT
VALUE TEXT
);

For a single row in item table, we can have multiple rows in attributes table. 
It is a generic Key/Value attributes for an item. ITEM_ID in 2nd table is the 
ID of an item in 1st table.

We want to select Items sorted by VALUE in the Attribute table.

I have tried following query

SELECT ITEM.ID from ITEM, ATTRIBUTES where ITEM.ID == ATTRIBUTES.ITEM_ID and 
key=“abc” SORT BY VALUE;

This works and items are sorted by value but naturally it only selects items 
that have key/value specified for an item. We also have some items that have 
missing attributes, so key/value row for that item is missing and above query 
will not select those items. We can write a different query that can select all 
items where key is missing. For example:

SELECT ITEM.ID, FIELD0 from ITEM, ATTRIBUTES where ITEM.ID not in (SELECT 
ITEM.ID from ITEM, ATTRIBUTES where ITEM.ID == ATTRIBUTES.ITEM_ID and 
key==“abc”)


But is it possible to write a single query that will present results from both 
queries above?
Our use case is that we are trying to implement sorting functionality in our 
app and for items that don’t have a key/value attribute, we want to treat value 
as NULL or “”  but still return the item. Because results are sorted by VALUE, 
these items with missing key/value will be at beginning of result set. Because 
we also want to add LIMIT and OFFSET, we think a single query is better 
solution versus trying to run two queries and trying to merge two results.


You want LEFT JOIN.

Something like:

SELECT I.ID, ISNULL(A.VALUE,'[No Value]')
  FROM ITEM AS I
  LEFT JOIN ATTRIBUTES AS A ON A.ITEM_ID = I.ID
 WHERE A.key='abc' OR A.key IS NULL
 SORT BY VALUE;


Note 1:  Left join will list all the values from the first table (the 
LEFT table) and add results where possible from the second (RIGHT) 
table, else the values will be NULL for it.
Note 2: ISNULL(x,a) Will output the value of x, unless it is NULL, in 
which case it will show a.
Note 3:  The strings in SQL has single quotes (like 'abc'), only 
identifiers get double quotes.
Note 4: LIMIT and OFFSET is a very bad way to do paging (in case that's 
what you are planning). It's good for limiting the size of a query, but 
offset has to reproduce the entire query every time and wait for the 
offset number of rows to pass before it can jump in and start adding 
rows to the output - it's not really "remembering" where it left off. 
You can do that better with temporary tables containing a result set and 
then stepping through those tables based on a key.


Cheers,
Ryan



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

[sqlite] Need help with SQL query

[Vikas Aditya]

Hi All,

I need some help in figuring our right query syntax for querying items from two 
tables. We have two tables. One of the table has list of items. And Second 
table has additional attributes.

CREATE TABLE ITEM (
ID INTEGER,
FIELD0 TEXT
FIELD1 TEXT,
FIELD3 TEXT,
FIELD4 TEXT
);

CREATE TABLE ATTRIBUTES (
ID INTEGER,
ITEM_ID INTEGER,
KEY TEXT
VALUE TEXT
);

For a single row in item table, we can have multiple rows in attributes table. 
It is a generic Key/Value attributes for an item. ITEM_ID in 2nd table is the 
ID of an item in 1st table.

We want to select Items sorted by VALUE in the Attribute table. 

I have tried following query

SELECT ITEM.ID from ITEM, ATTRIBUTES where ITEM.ID == ATTRIBUTES.ITEM_ID and 
key=“abc” SORT BY VALUE;

This works and items are sorted by value but naturally it only selects items 
that have key/value specified for an item. We also have some items that have 
missing attributes, so key/value row for that item is missing and above query 
will not select those items. We can write a different query that can select all 
items where key is missing. For example:

SELECT ITEM.ID, FIELD0 from ITEM, ATTRIBUTES where ITEM.ID not in (SELECT 
ITEM.ID from ITEM, ATTRIBUTES where ITEM.ID == ATTRIBUTES.ITEM_ID and 
key==“abc”)


But is it possible to write a single query that will present results from both 
queries above? 
Our use case is that we are trying to implement sorting functionality in our 
app and for items that don’t have a key/value attribute, we want to treat value 
as NULL or “”  but still return the item. Because results are sorted by VALUE, 
these items with missing key/value will be at beginning of result set. Because 
we also want to add LIMIT and OFFSET, we think a single query is better 
solution versus trying to run two queries and trying to merge two results.

Thanks,
Vikas

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

Re: [sqlite] need help to implement a VTAB on a already sorted table

[petern]

Richard.  Further to your reply on Mar 31 2017, were those newly worked
code exemplars demonstrating non-trivial sqlite3_index_info from xBestIndex
interaction for a SQLite table backed vtable published?

I am sure there is still great interest for this information considering
the lack of even one expertly worked table backed vtable example to study.
I routinely search this forum's traffic for anything related.

Thanks in advance for your reply.



On Fri, Mar 31, 2017 at 6:06 AM, Richard Hipp  wrote:

> On 3/31/17, aotto  wrote:
> > I already have a working implementation with [a] close [to] empty
> > *xBestIndex and *xFilter.
> >
> > 6) I expect for a primitive WHERE clause like "rep=XXX" some kind of
> access
> >optimization…
>
> Your xBestIndex and xFilter functions must implement the desired
> optimization.
>
> The xBestIndex and xFilter routines work as a team.  xBestIndex is
> passed information about the WHERE clause.  xBestIndex uses that
> information to realize that "rep=XXX" is present, sets fields of
> sqlite3_index_info that cause the XXX value to be passed into xFilter,
> then sets idxNum or idxStr to tell xFilter what algorithm to use.
> xFilter will use the idxNum or idxStr values to recognize that it
> should jump immediately to rep=XXX.
>
> I'll see if I can work up some examples of this later today...
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] need help to implement a VTAB on a already sorted table

[jose isaias cabrera]

Some examples of these would be great, Dr. Hipp.  Thanks. 

On 2017-03-31 09:06, Richard Hipp wrote:

> On 3/31/17, aotto  wrote: 
> 
>> I already have a working implementation with [a] close [to] empty
>> *xBestIndex and *xFilter.
>> 
>> 6) I expect for a primitive WHERE clause like "rep=XXX" some kind of access
>> optimization...
> 
> Your xBestIndex and xFilter functions must implement the desired optimization.
> 
> The xBestIndex and xFilter routines work as a team.  xBestIndex is
> passed information about the WHERE clause.  xBestIndex uses that
> information to realize that "rep=XXX" is present, sets fields of
> sqlite3_index_info that cause the XXX value to be passed into xFilter,
> then sets idxNum or idxStr to tell xFilter what algorithm to use.
> xFilter will use the idxNum or idxStr values to recognize that it
> should jump immediately to rep=XXX.
> 
> I'll see if I can work up some examples of this later today...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] need help to implement a VTAB on a already sorted table

[Richard Hipp]

On 3/31/17, aotto  wrote:
> I already have a working implementation with [a] close [to] empty
> *xBestIndex and *xFilter.
>
> 6) I expect for a primitive WHERE clause like "rep=XXX" some kind of access
>optimization…

Your xBestIndex and xFilter functions must implement the desired optimization.

The xBestIndex and xFilter routines work as a team.  xBestIndex is
passed information about the WHERE clause.  xBestIndex uses that
information to realize that "rep=XXX" is present, sets fields of
sqlite3_index_info that cause the XXX value to be passed into xFilter,
then sets idxNum or idxStr to tell xFilter what algorithm to use.
xFilter will use the idxNum or idxStr values to recognize that it
should jump immediately to rep=XXX.

I'll see if I can work up some examples of this later today...

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

[sqlite] need help to implement a VTAB on a already sorted table

[aotto]

Hi…

I'm new to this mailing-list because I have a problem to define a 
"VIRTUAL TABLE"

sqlite3 extension. I already have a working implementation with close empty
*xBestIndex and *xFilter.

1) My data is already in a sorted list with ONE or MORE primary index 
columns
2) Currently the VTAB is always doing a FULL TABLE SCAN even for trivial 
search.

3) my schema define in sqlite3_declare_vtab is

"CREATE TABLE %s (\
  rep   VARCHAR(3),   \
  par   VARCHAR(3),   \
  year  INTEGER,  \
  imp   DOUBLE,   \
  exp   DOUBLE,   \
  PRIMARY KEY(rep,par,year)   \
)"

4) it seems that sqlite does NOT use the "PRIMARY KEY(rep,par,year)" 
information…

5) the key data is already sorted…
6) I expect for a primitive WHERE clause like "rep=XXX" some kind of access
  optimization…

I'm looking for a "starter" implementation with the conditions from above
with working *xBestIndex and *xFilter…

Thanks for your help.

mfg ao

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

Re: [sqlite] Need help with System.Data.SQLite

[Burtsev , Dmitriy]

I try both "SELECT 1,0;" and "SELECT 1, cast(0 as real)"?
The same error:
Exception calling "WriteToServer" with "1" argument(s): "The given value of 
type SqlDecimal from the data source cannot be converted to type decimal of the 
specified target column."

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hick Gunter
Sent: Wednesday, January 11, 2017 11:29 AM
To: 'SQLite mailing list'
Subject: Re: [sqlite] Need help with System.Data.SQLite

What is the result if you exchange your currently executed statement to "SELECT 
1,0;" ? Or to "SELECT 1, cast(0 as real)"?

The effect of declaring a NUMERIC(5,5) field in SQLite is that it's affinity 
becomes NUMERIC. The value .0 thus becomes the integer 0. The other legal 
values (.1 to .9) are all converted to real (double) floating point 
numbers, each to the closest possible approximation (or, if the approximation 
is not "close enough", as text !!!).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Burtsev, Dmitriy
Gesendet: Mittwoch, 11. Jänner 2017 15:48
An: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
Betreff: Re: [sqlite] Need help with System.Data.SQLite

I didn't assign any value directly.

This is what I did:

1.   On SQLite create source table.
CREATE TABLE FromNum (
id INT,
Num NUMERIC(5,5) NULL);
INSERT INTO FromNum (id, Num) VALUES (1, .0);

2.  On MS SQL Server 2014 (SP2) create empty destination table.

CREATE TABLE dbo.ToNum (
id INT NOT NULL ,
Num NUMERIC(5,5) NULL);

I can't copy my PowerShell script in this e-mail. Got error "The message's 
content type was not explicitly allowed". I will try pseudocode.

Add-Type -Path '\\SERVER01\SQL\SQLite\netFx46\System.Data.SQLite.dll'

Create System.Data.SQLite.SQLiteConnection

Connection CreateCommand()

CommandText = "SELECT * FROM FromNum"

System.Data.SQLite.SQLiteDataReader = Command.ExecuteReader()

Create System.Data.SqlClient.SqlConnection
Create System.Data.SqlClient.SqlBulkCopy SqlConnection, 
[System.Data.SqlClient.SqlBulkCopyOptions]::TableLock
SqlbulkCopy.DestinationTableName = "ToNum"
Sqlbulkcopy.EnableStreaming = true

SqlbulkCopy.WriteToServer(SQLiteDataReader) <-- Got error here

This code is working with NUMERIC(11,0), NUMERIC(3,2) , NUMERIC(5,3) but not 
working with NUMERIC(5,5).

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of GB
Sent: Wednesday, January 11, 2017 2:25 AM
To: SQLite mailing list
Subject: Re: [sqlite] Need help with System.Data.SQLite

SQLite does not really care about precision and scale. See 
https://sqlite.org/datatype3.html for more information.

The culprit lies in the use of SqlDecimal which cannot be used by 
System.Data.SQLite directly and has no implicit conversion to something 
understandable by System.Data.SQLite (for more information about SqlDecimal see 
https://msdn.microsoft.com/en-us/library/xaktx377(v=vs.110).aspx).

However, an explicit conversion exists, so an explicit cast to System.Decimal 
would work. Maybe you could try this at the Point where you assign the Value 
(more information about the explicit operator can be found here: 
https://msdn.microsoft.com/en-us/library/xhbhezf4.aspx).

Also have a close look at what is being fed into the other numeric columns. I 
doubt these are SqlDecimals too.

Burtsev, Dmitriy schrieb am 10.01.2017 um 15:33:
> Thank you for response.
>
> It looks like we are on different pages here. Let me start from the beginning.
>
> We are moving data between SQLite database and Microsoft SQL Server. At this 
> time we are using Excel files but we run into some Excel limitation.
> I am trying to change Excel files to SQLite database files.
>
> The test table has several NUMERIC type columns. We have NUMERIC(11,0), 
> NUMERIC(3,2) , NUMERIC(5,3). Our code works fine until we add NUMERIC(5,5) 
> column.
> It looks like the problem is not with NUMERIC type in general, but only when 
> precision is equal to scale.
>
> Exception calling "WriteToServer" with "1" argument(s): "The given value of 
> type SqlDecimal from the data source cannot be converted to type decimal of 
> the specified target column."
>
>
> -----Original Message-
> From: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of GB
> Sent: Tuesday, January 10, 2017 2:08 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Need help with System.Data.SQLite
>
> System.Data.SqlTypes.SqlDecimal is specific to the SQL Server provider and 
> thus the SQLite provider doesn't know how to handle it. Try using 
> System.Decimal as a more generic approach. If you 

Re: [sqlite] Need help with System.Data.SQLite

[Hick Gunter]

What is the result if you exchange your currently executed statement to "SELECT 
1,0;" ? Or to "SELECT 1, cast(0 as real)"?

The effect of declaring a NUMERIC(5,5) field in SQLite is that it's affinity 
becomes NUMERIC. The value .0 thus becomes the integer 0. The other legal 
values (.1 to .9) are all converted to real (double) floating point 
numbers, each to the closest possible approximation (or, if the approximation 
is not "close enough", as text !!!).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Burtsev, Dmitriy
Gesendet: Mittwoch, 11. Jänner 2017 15:48
An: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
Betreff: Re: [sqlite] Need help with System.Data.SQLite

I didn't assign any value directly.

This is what I did:

1.   On SQLite create source table.
CREATE TABLE FromNum (
id INT,
Num NUMERIC(5,5) NULL);
INSERT INTO FromNum (id, Num) VALUES (1, .0);

2.  On MS SQL Server 2014 (SP2) create empty destination table.

CREATE TABLE dbo.ToNum (
id INT NOT NULL ,
Num NUMERIC(5,5) NULL);

I can't copy my PowerShell script in this e-mail. Got error "The message's 
content type was not explicitly allowed". I will try pseudocode.

Add-Type -Path '\\SERVER01\SQL\SQLite\netFx46\System.Data.SQLite.dll'

Create System.Data.SQLite.SQLiteConnection

Connection CreateCommand()

CommandText = "SELECT * FROM FromNum"

System.Data.SQLite.SQLiteDataReader = Command.ExecuteReader()

Create System.Data.SqlClient.SqlConnection
Create System.Data.SqlClient.SqlBulkCopy SqlConnection, 
[System.Data.SqlClient.SqlBulkCopyOptions]::TableLock
SqlbulkCopy.DestinationTableName = "ToNum"
Sqlbulkcopy.EnableStreaming = true

SqlbulkCopy.WriteToServer(SQLiteDataReader) <-- Got error here

This code is working with NUMERIC(11,0), NUMERIC(3,2) , NUMERIC(5,3) but not 
working with NUMERIC(5,5).

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of GB
Sent: Wednesday, January 11, 2017 2:25 AM
To: SQLite mailing list
Subject: Re: [sqlite] Need help with System.Data.SQLite

SQLite does not really care about precision and scale. See 
https://sqlite.org/datatype3.html for more information.

The culprit lies in the use of SqlDecimal which cannot be used by 
System.Data.SQLite directly and has no implicit conversion to something 
understandable by System.Data.SQLite (for more information about SqlDecimal see 
https://msdn.microsoft.com/en-us/library/xaktx377(v=vs.110).aspx).

However, an explicit conversion exists, so an explicit cast to System.Decimal 
would work. Maybe you could try this at the Point where you assign the Value 
(more information about the explicit operator can be found here: 
https://msdn.microsoft.com/en-us/library/xhbhezf4.aspx).

Also have a close look at what is being fed into the other numeric columns. I 
doubt these are SqlDecimals too.

Burtsev, Dmitriy schrieb am 10.01.2017 um 15:33:
> Thank you for response.
>
> It looks like we are on different pages here. Let me start from the beginning.
>
> We are moving data between SQLite database and Microsoft SQL Server. At this 
> time we are using Excel files but we run into some Excel limitation.
> I am trying to change Excel files to SQLite database files.
>
> The test table has several NUMERIC type columns. We have NUMERIC(11,0), 
> NUMERIC(3,2) , NUMERIC(5,3). Our code works fine until we add NUMERIC(5,5) 
> column.
> It looks like the problem is not with NUMERIC type in general, but only when 
> precision is equal to scale.
>
> Exception calling "WriteToServer" with "1" argument(s): "The given value of 
> type SqlDecimal from the data source cannot be converted to type decimal of 
> the specified target column."
>
>
> -Original Message-
> From: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of GB
> Sent: Tuesday, January 10, 2017 2:08 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Need help with System.Data.SQLite
>
> System.Data.SqlTypes.SqlDecimal is specific to the SQL Server provider and 
> thus the SQLite provider doesn't know how to handle it. Try using 
> System.Decimal as a more generic approach. If you need to be portable across 
> providers, you will be better off using classes from System.Data.Common 
> anyway.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> This message, and any of its attachments, is for the intended recipient(s) 
> only, and it may contain information that is privileged, confidential, and/or 
> proprietary and subject to important terms and conditions available at 
> http

Re: [sqlite] Need help with System.Data.SQLite

[Burtsev , Dmitriy]

I didn't assign any value directly.

This is what I did:

1.   On SQLite create source table.
CREATE TABLE FromNum (
id INT,
Num NUMERIC(5,5) NULL);
INSERT INTO FromNum (id, Num) VALUES (1, .0);

2.  On MS SQL Server 2014 (SP2) create empty destination table.

CREATE TABLE dbo.ToNum (
id INT NOT NULL ,
Num NUMERIC(5,5) NULL);

I can't copy my PowerShell script in this e-mail. Got error "The message's 
content type was not explicitly allowed". I will try pseudocode.

Add-Type -Path '\\SERVER01\SQL\SQLite\netFx46\System.Data.SQLite.dll'

Create System.Data.SQLite.SQLiteConnection

Connection CreateCommand()

CommandText = "SELECT * FROM FromNum"

System.Data.SQLite.SQLiteDataReader = Command.ExecuteReader()

Create System.Data.SqlClient.SqlConnection
Create System.Data.SqlClient.SqlBulkCopy SqlConnection, 
[System.Data.SqlClient.SqlBulkCopyOptions]::TableLock
SqlbulkCopy.DestinationTableName = "ToNum"
Sqlbulkcopy.EnableStreaming = true

SqlbulkCopy.WriteToServer(SQLiteDataReader) <-- Got error here

This code is working with NUMERIC(11,0), NUMERIC(3,2) , NUMERIC(5,3) but not 
working with NUMERIC(5,5).

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of GB
Sent: Wednesday, January 11, 2017 2:25 AM
To: SQLite mailing list
Subject: Re: [sqlite] Need help with System.Data.SQLite

SQLite does not really care about precision and scale. See 
https://sqlite.org/datatype3.html for more information.

The culprit lies in the use of SqlDecimal which cannot be used by 
System.Data.SQLite directly and has no implicit conversion to something 
understandable by System.Data.SQLite (for more information about SqlDecimal see 
https://msdn.microsoft.com/en-us/library/xaktx377(v=vs.110).aspx).

However, an explicit conversion exists, so an explicit cast to System.Decimal 
would work. Maybe you could try this at the Point where you assign the Value 
(more information about the explicit operator can be found here: 
https://msdn.microsoft.com/en-us/library/xhbhezf4.aspx).

Also have a close look at what is being fed into the other numeric columns. I 
doubt these are SqlDecimals too.

Burtsev, Dmitriy schrieb am 10.01.2017 um 15:33:
> Thank you for response.
>
> It looks like we are on different pages here. Let me start from the beginning.
>
> We are moving data between SQLite database and Microsoft SQL Server. At this 
> time we are using Excel files but we run into some Excel limitation.
> I am trying to change Excel files to SQLite database files.
>
> The test table has several NUMERIC type columns. We have NUMERIC(11,0), 
> NUMERIC(3,2) , NUMERIC(5,3). Our code works fine until we add NUMERIC(5,5) 
> column.
> It looks like the problem is not with NUMERIC type in general, but only when 
> precision is equal to scale.
>
> Exception calling "WriteToServer" with "1" argument(s): "The given value of 
> type SqlDecimal from the data source cannot be converted to type decimal of 
> the specified target column."
>   
>   
> -Original Message-
> From: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of GB
> Sent: Tuesday, January 10, 2017 2:08 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Need help with System.Data.SQLite
>
> System.Data.SqlTypes.SqlDecimal is specific to the SQL Server provider and 
> thus the SQLite provider doesn't know how to handle it. Try using 
> System.Decimal as a more generic approach. If you need to be portable across 
> providers, you will be better off using classes from System.Data.Common 
> anyway.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> This message, and any of its attachments, is for the intended recipient(s) 
> only, and it may contain information that is privileged, confidential, and/or 
> proprietary and subject to important terms and conditions available at 
> http://www.stifel.com/disclosures/emaildisclaimers/. If you are not the 
> intended recipient, please delete this message and immediately notify the 
> sender. No confidentiality, privilege, or property rights are waived or lost 
> by any errors in transmission.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Need help with System.Data.SQLite

[GB]

SQLite does not really care about precision and scale. See 
https://sqlite.org/datatype3.html for more information.


The culprit lies in the use of SqlDecimal which cannot be used by 
System.Data.SQLite directly and has no implicit conversion to something 
understandable by System.Data.SQLite (for more information about 
SqlDecimal see 
https://msdn.microsoft.com/en-us/library/xaktx377(v=vs.110).aspx).


However, an explicit conversion exists, so an explicit cast to 
System.Decimal would work. Maybe you could try this at the Point where 
you assign the Value (more information about the explicit operator can 
be found here: https://msdn.microsoft.com/en-us/library/xhbhezf4.aspx).


Also have a close look at what is being fed into the other numeric 
columns. I doubt these are SqlDecimals too.


Burtsev, Dmitriy schrieb am 10.01.2017 um 15:33:

Thank you for response.

It looks like we are on different pages here. Let me start from the beginning.

We are moving data between SQLite database and Microsoft SQL Server. At this 
time we are using Excel files but we run into some Excel limitation.
I am trying to change Excel files to SQLite database files.

The test table has several NUMERIC type columns. We have NUMERIC(11,0), 
NUMERIC(3,2) , NUMERIC(5,3). Our code works fine until we add NUMERIC(5,5) 
column.
It looks like the problem is not with NUMERIC type in general, but only when 
precision is equal to scale.

Exception calling "WriteToServer" with "1" argument(s): "The given value of type 
SqlDecimal from the data source cannot be converted to type decimal of the specified target column."
  
  
-Original Message-

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of GB
Sent: Tuesday, January 10, 2017 2:08 AM
To: SQLite mailing list
Subject: Re: [sqlite] Need help with System.Data.SQLite

System.Data.SqlTypes.SqlDecimal is specific to the SQL Server provider and thus 
the SQLite provider doesn't know how to handle it. Try using System.Decimal as 
a more generic approach. If you need to be portable across providers, you will 
be better off using classes from System.Data.Common anyway.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
This message, and any of its attachments, is for the intended recipient(s) 
only, and it may contain information that is privileged, confidential, and/or 
proprietary and subject to important terms and conditions available at 
http://www.stifel.com/disclosures/emaildisclaimers/. If you are not the 
intended recipient, please delete this message and immediately notify the 
sender. No confidentiality, privilege, or property rights are waived or lost by 
any errors in transmission.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Need help with System.Data.SQLite

[Burtsev , Dmitriy]

Thank you for response. 

It looks like we are on different pages here. Let me start from the beginning.

We are moving data between SQLite database and Microsoft SQL Server. At this 
time we are using Excel files but we run into some Excel limitation. 
I am trying to change Excel files to SQLite database files.

The test table has several NUMERIC type columns. We have NUMERIC(11,0), 
NUMERIC(3,2) , NUMERIC(5,3). Our code works fine until we add NUMERIC(5,5) 
column.
It looks like the problem is not with NUMERIC type in general, but only when 
precision is equal to scale.

Exception calling "WriteToServer" with "1" argument(s): "The given value of 
type SqlDecimal from the data source cannot be converted to type decimal of the 
specified target column."
 
 
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of GB
Sent: Tuesday, January 10, 2017 2:08 AM
To: SQLite mailing list
Subject: Re: [sqlite] Need help with System.Data.SQLite

System.Data.SqlTypes.SqlDecimal is specific to the SQL Server provider and thus 
the SQLite provider doesn't know how to handle it. Try using System.Decimal as 
a more generic approach. If you need to be portable across providers, you will 
be better off using classes from System.Data.Common anyway.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
This message, and any of its attachments, is for the intended recipient(s) 
only, and it may contain information that is privileged, confidential, and/or 
proprietary and subject to important terms and conditions available at 
http://www.stifel.com/disclosures/emaildisclaimers/. If you are not the 
intended recipient, please delete this message and immediately notify the 
sender. No confidentiality, privilege, or property rights are waived or lost by 
any errors in transmission.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help with System.Data.SQLite

[GB]

System.Data.SqlTypes.SqlDecimal is specific to the SQL Server provider 
and thus the SQLite provider doesn't know how to handle it. Try using 
System.Decimal as a more generic approach. If you need to be portable 
across providers, you will be better off using classes from 
System.Data.Common anyway.

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

Re: [sqlite] Need help with System.Data.SQLite

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Burtsev, Dmitriy
> Sent: Monday, January 09, 2017 10:10 AM
> To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
> Subject: [sqlite] Need help with System.Data.SQLite
>
> Is this a right mail group for  System.Data.SQLite ?
>

Yes, it is.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

Effective immediately my new email address is william.dr...@l3t.com. Please 
update your records.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Need help with System.Data.SQLite

[Burtsev , Dmitriy]

Is this a right mail group for  System.Data.SQLite ?

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Burtsev, Dmitriy 
Sent: Thursday, January 05, 2017 9:08 AM
To: 'sqlite-users@mailinglists.sqlite.org'
Subject: [sqlite] Need help with System.Data.SQLite.SQLiteDataReader and 
NUMERIC(5, 5) column

Hello

I got this error:
Exception calling "WriteToServer" with "1" argument(s): "The given value of 
type SqlDecimal from the data source cannot be converted to type decimal of the 
specified target column."

How to reproduce.

1.   On SQLite execute this statements.
CREATE TABLE FromNum (
id INT,
Num NUMERIC(5,5) NULL);
INSERT INTO FromNum (id, Num) VALUES (1, .0);

2.  On MS SQL Server 2014 (SP2) execute this statement


CREATE TABLE dbo.ToNum (
id INT NOT NULL ,
Num NUMERIC(5,5) NULL);



I can't copy my PowerShell script in this e-mail. Got error "The message's 
content type was not explicitly allowed". I will try pseudocode.



Add-Type -Path '\\SERVER01\SQL\SQLite\netFx46\System.Data.SQLite.dll'

Create System.Data.SQLite.SQLiteConnection

Connection CreateCommand()

CommandText = "SELECT * FROM FromNum"

System.Data.SQLite.SQLiteDataReader = Command.ExecuteReader()

Create System.Data.SqlClient.SqlConnection
Create System.Data.SqlClient.SqlBulkCopy with 
[System.Data.SqlClient.SqlBulkCopyOptions]::TableLock
bulkCopy.BatchSize = 5000
bulkCopy.DestinationTableName = "ToNum"
bulkcopy.EnableStreaming = true

bulkCopy.WriteToServer(SQLiteDataReader) <-- Got error here

Note: I did run it with System.Data.SqlClient.SqlDataReader as a source. It 
works with NUMERIC(5,5) column.
I did run it with System.Data.SQLite.SQLiteDataReader but without NUMERIC(5,5) 
column. It works.
The problem appears only when I run SQLiteDataReader as a sourse and table have 
NUMERIC(5,5) column.

Powershell version 5, 64 bit
System.Data.SQLite.dll version 1.0.103.0  64 bit .NET Framework 4.6.1

Thank you
Dmitriy Burtsev

This message, and any of its attachments, is for the intended recipient(s) 
only, and it may contain information that is privileged, confidential, and/or 
proprietary and subject to important terms and conditions available at 
http://www.stifel.com/disclosures/emaildisclaimers/. If you are not the 
intended recipient, please delete this message and immediately notify the 
sender. No confidentiality, privilege, or property rights are waived or lost by 
any errors in transmission.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Need help with System.Data.SQLite.SQLiteDataReader and NUMERIC(5, 5) column

[Burtsev , Dmitriy]

Hello

I got this error:
Exception calling "WriteToServer" with "1" argument(s): "The given value of 
type SqlDecimal from the data source cannot be converted to type decimal of the 
specified target column."

How to reproduce.

1.   On SQLite execute this statements.
CREATE TABLE FromNum (
id INT,
Num NUMERIC(5,5) NULL);
INSERT INTO FromNum (id, Num) VALUES (1, .0);

2.  On MS SQL Server 2014 (SP2) execute this statement


CREATE TABLE dbo.ToNum (
id INT NOT NULL ,
Num NUMERIC(5,5) NULL);



I can't copy my PowerShell script in this e-mail. Got error "The message's 
content type was not explicitly allowed". I will try pseudocode.



Add-Type -Path '\\SERVER01\SQL\SQLite\netFx46\System.Data.SQLite.dll'

Create System.Data.SQLite.SQLiteConnection

Connection CreateCommand()

CommandText = "SELECT * FROM FromNum"

System.Data.SQLite.SQLiteDataReader = Command.ExecuteReader()

Create System.Data.SqlClient.SqlConnection
Create System.Data.SqlClient.SqlBulkCopy with 
[System.Data.SqlClient.SqlBulkCopyOptions]::TableLock
bulkCopy.BatchSize = 5000
bulkCopy.DestinationTableName = "ToNum"
bulkcopy.EnableStreaming = true

bulkCopy.WriteToServer(SQLiteDataReader) <-- Got error here

Note: I did run it with System.Data.SqlClient.SqlDataReader as a source. It 
works with NUMERIC(5,5) column.
I did run it with System.Data.SQLite.SQLiteDataReader but without NUMERIC(5,5) 
column. It works.
The problem appears only when I run SQLiteDataReader as a sourse and table have 
NUMERIC(5,5) column.

Powershell version 5, 64 bit
System.Data.SQLite.dll version 1.0.103.0  64 bit
.NET Framework 4.6.1

Thank you
Dmitriy Burtsev

This message, and any of its attachments, is for the intended recipient(s) 
only, and it may contain information that is privileged, confidential, and/or 
proprietary and subject to important terms and conditions available at 
http://www.stifel.com/disclosures/emaildisclaimers/. If you are not the 
intended recipient, please delete this message and immediately notify the 
sender. No confidentiality, privilege, or property rights are waived or lost by 
any errors in transmission.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help on SQLite In-Memory Mode

[Kevin Benson]

On Mon, Aug 11, 2014 at 9:19 PM, Nick Bao  wrote:

> Thanks, Joe!
>
> But it did not work for me.
>
> Still got the error:
> System.ArgumentException: Invalid ConnectionString format for parameter
> "FullUri"
>
>
>
Are you using System.Data.SQLite version > *1.0.81.0 ?*

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help on SQLite In-Memory Mode

[Nick Bao]

Thanks, Joe!

But it did not work for me.

Still got the error:
System.ArgumentException: Invalid ConnectionString format for parameter 
"FullUri"


Nick Bao
DL_DEV_4/DL_DEV_19, VP, Dalian Office - AvePoint, Inc.
P: +86.411.8473.6866 | F: 159.0496.1680 | nick@avepoint.com
Follow us on Facebook, Twitter and LinkedIn!
  



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Joe Mistachkin
Sent: Saturday, August 09, 2014 9:54 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Need help on SQLite In-Memory Mode


Try this:

SQLiteConnection connection = new SQLiteConnection(
"FullUri=file::memory:?cache=shared;"); 

--
Joe Mistachkin

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

Re: [sqlite] Need help on SQLite In-Memory Mode

[Joe Mistachkin]

Try this:

SQLiteConnection connection = new SQLiteConnection(
"FullUri=file::memory:?cache=shared;"); 

--
Joe Mistachkin

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

[sqlite] Need help on SQLite In-Memory Mode

[№⑥ 沨]

‍‍
Hi dear sqlite users,
 
I am using SQLite In-Memory Database in my application.
 
My application is written in C#.
 
I am trying to create an In-Memory Database that can be opened by multiple 
connections as descripted in this link.
 
What it is the right format of connection string in this case?
 
I have tried 
 
SQLiteConnection connection = new SQLiteConnection("Data 
Source=file::memory:?cache=shared"); 
 
SQLiteConnection connection = new SQLiteConnection("Data 
Source=file://:memory:;cache=shared");
 
None of these worked.
 
Thanks in advance!
 
 
 
 
 
 ‍
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Need Help

[Vijay Mekala]

Hi,
I am new to sqlite and my objective is to run the sqlite tests on build
where sqlite got integrated and have to use my own ZFS for my test.

I have downloaded the source where tests are available but need help on how
to take them and run against sqlite which is integrated in our code.

Can some one can help on how to do it ?

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

Re: [sqlite] Need help with query

[Michael Black]

You're structure is bad for future growth (i.e. multiple languages) as the
query gets really nasty really quickly.

You should normalize this data and your query will never change.


pragma foreign_keys=ON;
CREATE TABLE buttons (
ID integer primary key autoincrement,
Key1 varchar not null,
Key2 varchar not null
);
insert into buttons(key1,key2) values('FORM1','SAVE_BUTTON');
insert into buttons(key1,key2) values('FORM1','HELP_BUTTON');

CREATE TABLE masterlanguages(
ID integer primary key autoincrement,
ISOCode varchar not null
);
insert into masterlanguages (ISOCode) values ('ENG');
insert into masterlanguages(ISOCode) values ('DEU');

CREATE TABLE buttontext (
ID integer primary key autoincrement,
Description varchar not null,
masterlanguage integer,
button integer,
foreign key(masterlanguage) references masterlanguages(id),
foreign key(button) references buttons(id)
);

insert into buttontext (Description,masterlanguage,button)
values('Save',1,1);
insert into buttontext (Description,masterlanguage,button)
values('Help',1,2);
insert into buttontext (Description,masterlanguage,button)
values('Speichern',2,1);
insert into buttontext (Description,masterlanguage,button)
values('Hilfe',2,2);

All you need to do0 is specify what language you want in the query.

sqlite> select * from buttons b1 join buttontext b2 on b1.id=b2.button and
b2.masterlanguage=(select id from masterlanguages where ISOCode='ENG');
ID|Key1|Key2|ID|Description|masterlanguage|button
1|FORM1|SAVE_BUTTON|1|Save|1|1
2|FORM1|HELP_BUTTON|2|Help|1|2
sqlite> select * from buttons b1 join buttontext b2 on b1.id=b2.button and
b2.masterlanguage=(select id from masterlanguages where ISOCode='DEU');
ID|Key1|Key2|ID|Description|masterlanguage|button
1|FORM1|SAVE_BUTTON|3|Speichern|2|1
2|FORM1|HELP_BUTTON|4|Hilfe|2|2

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kai Peters
Sent: Monday, January 14, 2013 11:50 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Need help with query

Hi all,

given

CREATE TABLE masterlanguages (
  ID  integer primary key autoincrement,
  Key1varchar not null, 
  Key2varchar not null,
  ISOCode varchar not null,
  Description varchar not null,
  MaxCharsinteger default 0
);


insert into masterlanguages values (null, 'FORM1', 'SAVE_BUTTON', 'ENG',
'Save', 0);
insert into masterlanguages values (null, 'FORM1', 'HELP_BUTTON', 'ENG',
'Help', 0);
insert into masterlanguages values (null, 'FORM1', 'SAVE_BUTTON', 'DEU',
'Speichern', 0);
insert into masterlanguages values (null, 'FORM1', 'HELP_BUTTON', 'DEU',
'Hilfe', 0);



In addition to the data from 

SELECT * FROM MASTERLANGUAGES WHERE ISOCode = 'DEU' 

I also need the Description field for the corresponding record (based on
Key1 + Key2) in English so 
that I can display the original English description as well as its German
translation.


How can I achieve this?

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

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

Re: [sqlite] Need help with query

[Kai Peters]

On Tue, 15 Jan 2013 14:57:42 +0900, Yongil Jang wrote:
> SELECT t1.key1, t1.key2, t2.description FROM MASTERLANGUAGES as t1, 
> MASTERLANGUAGES as t2 WHERE
> t1.ISOCode = 'DEU' and t2.ISOCode = 'ENG' and t1.key1 = t2.key1 and t1.key2 = 
> t2.key2;

that is very close - just needed to add t1.description

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

Re: [sqlite] Need help with query

[Yongil Jang]

like this?

sqlite> SELECT t1.key1, t1.key2, t2.description FROM MASTERLANGUAGES as t1,
MASTERLANGUAGES as t2 WHERE t1.ISOCode = 'DEU' and t2.ISOCode = 'ENG' and
t1.key1 = t2.key1 and t1.key2 = t2.key2;
FORM1|SAVE_BUTTON|Save
FORM1|HELP_BUTTON|Help

Sorry if my try is wrong.


2013/1/15 Kai Peters 

> Hi all,
>
> given
>
> CREATE TABLE masterlanguages (
>   ID  integer primary key autoincrement,
>   Key1varchar not null,
>   Key2varchar not null,
>   ISOCode varchar not null,
>   Description varchar not null,
>   MaxCharsinteger default 0
> );
>
>
> insert into masterlanguages values (null, 'FORM1', 'SAVE_BUTTON', 'ENG',
> 'Save', 0);
> insert into masterlanguages values (null, 'FORM1', 'HELP_BUTTON', 'ENG',
> 'Help', 0);
> insert into masterlanguages values (null, 'FORM1', 'SAVE_BUTTON', 'DEU',
> 'Speichern', 0);
> insert into masterlanguages values (null, 'FORM1', 'HELP_BUTTON', 'DEU',
> 'Hilfe', 0);
>
>
>
> In addition to the data from
>
> SELECT * FROM MASTERLANGUAGES WHERE ISOCode = 'DEU'
>
> I also need the Description field for the corresponding record (based on
> Key1 + Key2) in English so
> that I can display the original English description as well as its German
> translation.
>
>
> How can I achieve this?
>
> TIA,
> Kai
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Need help with query

[Kai Peters]

Hi all,

given

CREATE TABLE masterlanguages (
  ID  integer primary key autoincrement,
  Key1varchar not null, 
  Key2varchar not null,
  ISOCode varchar not null,
  Description varchar not null,
  MaxCharsinteger default 0
);


insert into masterlanguages values (null, 'FORM1', 'SAVE_BUTTON', 'ENG', 
'Save', 0);
insert into masterlanguages values (null, 'FORM1', 'HELP_BUTTON', 'ENG', 
'Help', 0);
insert into masterlanguages values (null, 'FORM1', 'SAVE_BUTTON', 'DEU', 
'Speichern', 0);
insert into masterlanguages values (null, 'FORM1', 'HELP_BUTTON', 'DEU', 
'Hilfe', 0);



In addition to the data from 

SELECT * FROM MASTERLANGUAGES WHERE ISOCode = 'DEU' 

I also need the Description field for the corresponding record (based on Key1 + 
Key2) in English so 
that I can display the original English description as well as its German 
translation.


How can I achieve this?

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

[sqlite] Need help with adding Update Callback for node-sqlite

[Ashwini Jadhav]

I am not sure if this is the correct mailing list to ask this question. If
not, please redirect me to the correct forum.

 

I am using Node js with node-sqlite extension. I tried to use the commented
out caode in node-sqlite to add an update callback. On running the code I am
getting a segmentation fault. What should I do?

 

Thanks! 

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

Re: [sqlite] Need Help! -- SQlite database on server

[Antonio Maniero]

I'm reading about:

http://www.atensoft.net/dev/sqlite3.php
http://www.sqliteconcepts.org/CS_index.html
http://www.sqlitening.com
http://www.codeproject.com/KB/database/SQLite_Server.aspx
http://www.realsoftware.com/realserver/
http://sqliteserver.xhost.ro/index.html
http://users.libero.it/irwin/
http://www.thecommon.net/8.html
http://sqlrelay.sourceforge.net/
http://sqlitedbms.sourceforge.net/index.htm
http://www.it77.de/sqlite/sqlite_sockets.htm
http://www.sqlite.org/cvstrac/wiki?p=ClientServer

What the impressions of experienced users/Sqlite implementers about the
concepts applied to above softwares?

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

Re: [sqlite] Need Help! -- SQlite database on server

[Marco Bambini]

Please take a look at cubeSQL:
http://www.sqlabs.com/cubesql.php

--
Marco Bambini
http://www.sqlabs.com








On Aug 4, 2011, at 7:15 PM, Vinoth raj wrote:

> Dear SQLite users,
> 
> I have been using SQlite database since three years. Suddenly I have a
> requirement for client/server support for my project.
> So, the requirement is to save sqlite database on a server from a C++
> application.
> I explored the SQLite API with no success. Even numeours queries on the
> google did not yield any result.
> It would be a great help if you can shed some light on my problem. Is it
> possible at all to save SQLite database on a server?
> 
> Eagerly looking forward to your valuable advice.
> 
> Thanks and Regards,
> Vinoth
> New Delhi, India
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Need Help! -- SQlite database on server

[Wiktor Adamski]

http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need Help! -- SQlite database on server

[Alexey Pechnikov]

2011/8/4 Vinoth raj :
> So, the requirement is to save sqlite database on a server from a C++
> application.

There are a lot of ways to copy file to server. SQLite database is single file.
And you can send SQL dump. And you can send diff of SQL dump.
And you can export your data and send RDF or other data format.
I don't see your problem. May be you want any different?..

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need Help! -- SQlite database on server

[Tim Butterfield]

On Thu, Aug 4, 2011 at 12:52 PM, Stephan Beal  wrote:
> On Thu, Aug 4, 2011 at 7:50 PM, Stephan Beal  wrote:
>
>> http://www.sqlite.org/whentouse.html
>>
>
> Specifically: 2nd section, 1st list item.

It may depend on the usage.  After reading about the possible issues,
I have just implemented something like this for use across multiple
networks, which also had a requirement for using a file share and not
a DB server.  This usage is to distribute versioned data between
different environments, whether on different networks in the same
building or between different cities/countries.  The usage is
non-constant.  No SQLite files remain open for more than the one
action being performed.  There is periodic polling/reading by a
service along with some occasional user initiated read/write action.
There is a relatively small number of total SQLite connections,
roughly three per end point.  The main db is relatively small with the
versioned data package/bundle each being in separate SQLite files.
The smaller files are to make opening the file happen more quickly.
To reduce risk of the mentioned issues, I implemented a separate file
locking mechanism around the SQLite open/close to make each access
exclusive.

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

Re: [sqlite] Need Help! -- SQlite database on server

[Kevin Martin]

On 4 Aug 2011, at 18:15, Vinoth raj wrote:

> I have been using SQlite database since three years. Suddenly I have a
> requirement for client/server support for my project.
> So, the requirement is to save sqlite database on a server from a C++
> application.

We use sqlite in a client/server situation. However, the main focus of  
the server is data analysis, it just happens to store the data/results  
in sqlite database. The client contains minimal code and just allows  
us to submit data and export analysis results.

The set up is essentially:

Server Side:
Database is a file on server
RPCServer runs on server on localhost:35790
Server runs sshd.

Client Side
Client runs ssh and establishes tunnel to 35790 on the server
RPCClient connects to localhost:35790

The RPC Client/Server are built with boost iostreams and boost  
serialization. However, if you take this approach it may be better to  
use something like Ice (http://zeroc.com/) if the license suits you.

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

Re: [sqlite] Need Help! -- SQlite database on server

[Stephan Beal]

On Thu, Aug 4, 2011 at 7:50 PM, Stephan Beal  wrote:

> http://www.sqlite.org/whentouse.html
>

Specifically: 2nd section, 1st list item.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need Help! -- SQlite database on server

[Stephan Beal]

On Thu, Aug 4, 2011 at 7:15 PM, Vinoth raj  wrote:

> It would be a great help if you can shed some light on my problem. Is it
> possible at all to save SQLite database on a server?
>

http://www.sqlite.org/whentouse.html


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Need Help! -- SQlite database on server

[Vinoth raj]

Dear SQLite users,

I have been using SQlite database since three years. Suddenly I have a
requirement for client/server support for my project.
So, the requirement is to save sqlite database on a server from a C++
application.
I explored the SQLite API with no success. Even numeours queries on the
google did not yield any result.
It would be a great help if you can shed some light on my problem. Is it
possible at all to save SQLite database on a server?

Eagerly looking forward to your valuable advice.

Thanks and Regards,
Vinoth
New Delhi, India
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] need help with a query using datetime

[Igor Tandetnik]

Black, Michael (IS)  wrote:
> sqlite> create table t(d date);
> sqlite> insert into t values('2011-12-31 09:00');
> sqlite> insert into t values('2011-12-31 12:15');
> sqlite> select d,substr(datetime(d,'-12 hours'),1,16) from t;
> 2011-12-31 09:00|2011-12-30 21:00
> 2011-12-31 12:15|2011-12-31 00:15

In place of datetime and substr, consider

strftime('%Y-%m-%d %H:%M', d, '-12 hours')

-- 
Igor Tandetnik

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

Re: [sqlite] need help with a query using datetime

[Ruth Ivimey-Cook]

On 17/06/2011 12:10, looki wrote:
> First column holds the givin datetime from my table and the second column
> should show the datetime from first row but 12 hours before. for example:
>
> '2011-12-31 09:00' '2011-12-30 21:00'
> '2011-12-31 12:15' '2011-12-30 00:15'
> ...
>
> looks simple but datetime was not sufficient for this in my research.

For MySQL, the adddate() operator will do the trick:
  select theDate, adddate(theDate,interval -12 hour) from table;

Does Sqlite have this too?
Ruth
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] need help with a query using datetime

[Black, Michael (IS)]

sqlite> create table t(d date);
sqlite> insert into t values('2011-12-31 09:00');
sqlite> insert into t values('2011-12-31 12:15');
sqlite> select d,substr(datetime(d,'-12 hours'),1,16) from t;
2011-12-31 09:00|2011-12-30 21:00
2011-12-31 12:15|2011-12-31 00:15



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of looki [looki1...@yahoo.com]
Sent: Friday, June 17, 2011 6:10 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] need help with a query using datetime


Hi,
i have a table which holds dates like
'2011-12-31 09:00' and i now want to write a query which gives me 2 columns.

First column holds the givin datetime from my table and the second column
should show the datetime from first row but 12 hours before. for example:

'2011-12-31 09:00' '2011-12-30 21:00'
'2011-12-31 12:15' '2011-12-30 00:15'
...

looks simple but datetime was not sufficient for this in my research.

Very thanks for your help.

Greetz looki



--
View this message in context: 
http://old.nabble.com/need-help-with-a-query-using-datetime-tp31868064p31868064.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] need help with a query using datetime

[looki]

Hi,
i have a table which holds dates like
'2011-12-31 09:00' and i now want to write a query which gives me 2 columns.

First column holds the givin datetime from my table and the second column
should show the datetime from first row but 12 hours before. for example:

'2011-12-31 09:00' '2011-12-30 21:00'
'2011-12-31 12:15' '2011-12-30 00:15'
...

looks simple but datetime was not sufficient for this in my research. 

Very thanks for your help.

Greetz looki



-- 
View this message in context: 
http://old.nabble.com/need-help-with-a-query-using-datetime-tp31868064p31868064.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Need help constructing some simple commands

[Igor Tandetnik]

don wish  wrote:
> Thus, if the "Subject" column does not contain the string "last", I
> want to replace the "Value" with an integer, say "5".
> 
> Also, if the "Subject" column contains the string "current", I want
> to replace the "Value" witn an integer, say "3".

What should happen in the (presumably quite likely) case where both conditions 
are true - Subject does not contain 'last' but does contain 'current'?

Assuming you meant the first condition to read "Subject contains 'last' ", you 
want something like this:

update m set Value = case
when Subject like '%last%' then 5
when Subject like '%current%' then 3
else Value end;

-- 
Igor Tandetnik

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

[sqlite] Need help constructing some simple commands

[don wish]

   To start, I am not an SQLite user. i haven't used a database for
   many years and have never used an SQL database.

   I am on a MS Windows system. I have an application that uses SQLite as a
   database for storing its information.

   The name of the file containing the database is "a.sqlite". The database
   contains a single table called "m".

   The application uses SQLite Manager to manage the database.

   When I open Manager, it opens the database file. I can see the table
   entries1

   For each entry (row) in the database there are columns. The two
   columns of interest are "Subject" - a string - and "Value" - an
   integer.

   Ex: id  Subject    Value  ... Other columns

   7 Example C current  9
   8 Example A last 1

   I can hand-edit the values in the columns using the window
   interface, but this is slow and onerous.
   
   There is a tab in the Manager window called "Execute SQL", which I
   assume will take SQL commands and execute them against the opened
   table.

   I have some 2000 entries in the table. I would like to change all of the
   values of the "Value" column to a specific integer based on the content
   of the "Subject" column.

   Thus, if the "Subject" column does not contain the string "last", I
   want to replace the "Value" with an integer, say "5".

   Also, if the "Subject" column contains the string "current", I want
   to replace the "Value" witn an integer, say "3".

   So, I need to "Select" and "Replace" the entries conditionally.

   I am sure there are a very small number of commands I need to complete
   this change for all 2000 rows, but I don't know what they are and
   wouldn't know the specific syntax needed. I haven't found help in the
   "Doucments" provided on the SQLite site.

   If I had the commands, I would enter these commands using the "Execute
   SQL" box.

   I will only need to do this once, which is why I am asking for help. If
   I needed to manipulate SQLlite databases as part of my work, I would
   take the time to learn SQL commands and syntax.

   Can someone send me the exact commands I need to finish the task?

   (The application developer says they aren't responsible for telling
   thier users how to use SQL Manager. :-( )

   Of course, I will try the commands on a copy of the file before
   changing the real file.

   Please e-mail the answer to: don_u_w...@yahoo.com
   as I am not a member of this mailing list.

   TIA

   Don Wish

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

Re: [sqlite] Need help understanding how to post to this list

[Mr. Puneet Kishor]

On May 3, 2011, at 6:04 PM, Rolf Marsh wrote:

> Hello.. I just joined today and can't seem to figure out how to start a 
> new thread... Can someone please enlighten me?


You just did.

Just post a question with the subject line indicating clearly what is bothering 
you and take a seat. The doctor will be with you shortly.


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

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

[sqlite] Need help understanding how to post to this list

[Rolf Marsh]

Hello.. I just joined today and can't seem to figure out how to start a 
new thread... Can someone please enlighten me?

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

Re: [sqlite] Need help

[Oliver Peters]

Igor Tandetnik  writes:

> 
> Balasubramani Vivekkanandan  wrote:
> >I am very new database development and sqlite. I have a requirement
> > where I want to restrict the size of my integer primary key to 3 bytes.
> > In my database, entries will be added and removed very frequently. So If the
> > primary key limit of 3 bytes is reached, it should try to reuse the deleted
> > entries.
> 
> What purpose exactly is this, rather bizarre, requirement supposed to serve,
if you don't mind me asking?
> 
> > Is this possible with sqlite?
> 
> Not automatically. You can implement it in your application code.

sorry that I cant agree but

with an extra table to store the latest id, a little TRIGGER gaming and DELETE
and REPLACE the job could be done (I believe) but it isn't worth to waste time
with this because using the application is much more comfortable.

greetings
oliver

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

Re: [sqlite] Need help

[Oliver Peters]

Igor Tandetnik  writes:

> 
> Balasubramani Vivekkanandan  wrote:
> >I am very new database development and sqlite. I have a requirement
> > where I want to restrict the size of my integer primary key to 3 bytes.
> > In my database, entries will be added and removed very frequently. So If the
> > primary key limit of 3 bytes is reached, it should try to reuse the deleted
> > entries.
> 
> What purpose exactly is this, rather bizarre, requirement supposed to serve,
if you don't mind me asking?
> 
> > Is this possible with sqlite?
> 
> Not automatically. You can implement it in your application code.


sorry that I've to contradict but I believe(!) that it should be possible
this way:

1. create table using INTEGER PRIMARY KEY (without autoincrement)
2. create an extra table with an 1 column
3. INSERT a 0 (zero) in that extra table
4. create a TRIGGER that fires if the id reaches the max(id) + 1, checks the
Integer in the extra table and INSERTS integer + 1 in main table (REPLACE),
INSERTS the new id in extra table by REPLACING the old one (DELETE and INSERT)

just a few thopughts without having tested

greetings
oliver



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

Re: [sqlite] Need help

[Igor Tandetnik]

Balasubramani Vivekkanandan  wrote:
>I am very new database development and sqlite. I have a requirement
> where I want to restrict the size of my integer primary key to 3 bytes.
> In my database, entries will be added and removed very frequently. So If the
> primary key limit of 3 bytes is reached, it should try to reuse the deleted
> entries.

What purpose exactly is this, rather bizarre, requirement supposed to serve, if 
you don't mind me asking?

> Is this possible with sqlite?

Not automatically. You can implement it in your application code.
-- 
Igor Tandetnik

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

[sqlite] Need help

[Balasubramani Vivekkanandan]

Hello,
I am very new database development and sqlite. I have a requirement
where I want to restrict the size of my integer primary key to 3 bytes.
In my database, entries will be added and removed very frequently. So If the
primary key limit of 3 bytes is reached, it should try to reuse the deleted
entries. Is this possible with sqlite? Please help me.

Thanks in Advance,

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

Re: [sqlite] Need help to confirm my understanding on Foreign Key indexes

[Simon Slavin]

On 28 Feb 2011, at 9:41am, rf rf wrote:

> Looking at the documentation for the latest version I think I understand that 
> I must define indexes for all foreign keys, manually, with UNIQUE as 
> described in the documentation.  If I don't then the database will give me an 
> immediate or deferred error.  These Foreign Key indexes are NOT generated 
> automatically by the database when I define a FOREIGN KEY... REFERENCES... ON 
> DELETE.. constraint.

The relevant text is at



I believe the requirement is that some index has to exist which ensures that 
the key has a UNIQUE constraint.  You don't have to make one index per FOREIGN 
KEY, or an index which exactly matches the terms used in the FOREIGN KEY 
clause, as long as something on the table ensures appropriate UNIQUEness.

Of course, making a UNIQUE index which exactly matches the FOREIGN KEY clause 
will, under most circumstances, not only ensure UNIQUEness, but also provide a 
fast and efficient way for SQLite to check the constraint.  So it might be 
worth doing things that way as a standard.

> I want to double check before I screw up my (sizable) database.  I seem to 
> remember that most DBMS (eg Oracle) would create these indexes automatically.

Right.  SQLite doesn't.  Partly because it's possible to create one index which 
takes care of many requirements and that will save database space, and SQLite 
has to worry more about space.  But you can check to see whether you already 
have all that's required: insert a record in the child database.  If it works, 
SQLite has all that's needed.  If it doesn't you don't, so create one.  If you 
created one and decide you don't need it, you can always 'DROP INDEX ...'.

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

[sqlite] Need help to confirm my understanding on Foreign Key indexes

[rf rf]

Looking at the documentation for the latest version I think I understand that I 
must define indexes for all foreign keys, manually, with UNIQUE as described in 
the documentation.  If I don't then the database will give me an immediate or 
deferred error.  These Foreign Key indexes are NOT generated automatically by 
the database when I define a FOREIGN KEY... REFERENCES... ON DELETE.. 
constraint.
 
I want to double check before I screw up my (sizable) database.  I seem to 
remember that most DBMS (eg Oracle) would create these indexes automatically.
 
Thank you!


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

Re: [sqlite] need help on escaping the ']' character in the identifier

[Richard Hipp]

On Tue, Dec 7, 2010 at 3:49 AM, 곽현미  wrote:

> Anyone please give me an advise,
>
> when i try the query below,
>
> CREATE TABLE "Test]" (no INTEGER),
>
> sqlite gives me the 'unrecognized token: "]" ' result.
>

It works when I try it.  What version of SQLite are you using?  Are you
using the sqlite3.exe command-line shell, or some third-party product?



>
> Is there a way to escape the ']' character in the identifier?
>
> Thanks,
> hyunmi.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] need help on escaping the ']' character in the identifier

[Drake Wilson]

Quoth Philip Graham Willoughby , on 
2010-12-07 10:57:45 +:
> Use the right quotes, single not double; this works for me:
> 
> create table 'test]' (no integer);

Yagh!  Please don't call those the 'right' quotes in this case.

Quoth http://sqlite.org/lang_keywords.html:
| For resilience when confronted with historical SQL statements,
| SQLite will sometimes bend the quoting rules above:
|
|   * If a keyword in single quotes (ex: 'key' or 'glob') is used in a
| context where an identifier is allowed but where a string
| literal is not allowed, then the token is understood to be an
| identifier instead of a string literal.
|
|   * If a keyword in double quotes (ex: "key" or "glob") is used in a
| context where it cannot be resolved to an identifier but where a
| string literal is allowed, then the token is understood to be a
| string literal instead of an identifier.
|
| Programmers are cautioned not to use the two exceptions described in
| the previous bullets. We emphasize that they exist only so that old
| and ill-formed SQL statements will run correctly. Future versions of
| SQLite might change to raise errors instead of accepting the malformed
| statements covered by the exceptions above.

(I suspect the real answer is "don't do that", but I'm not entirely
confident.)

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

Re: [sqlite] need help on escaping the ']' character in the identifier

[Philip Graham Willoughby]

On 7 Dec 2010, at 08:49, 곽현미 wrote:

> Anyone please give me an advise,
> 
> when i try the query below,
> 
> CREATE TABLE "Test]" (no INTEGER),
> 
> sqlite gives me the 'unrecognized token: "]" ' result.
> 
> Is there a way to escape the ']' character in the identifier?

Use the right quotes, single not double; this works for me:

create table 'test]' (no integer);

Best Regards,

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

StrawberryCat Limited is registered in England and Wales with Company No. 
7234809.

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

[sqlite] need help on escaping the ']' character in the identifier

[곽현미]

Anyone please give me an advise,

when i try the query below,

CREATE TABLE "Test]" (no INTEGER),

sqlite gives me the 'unrecognized token: "]" ' result.

Is there a way to escape the ']' character in the identifier?

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

Re: [sqlite] Need help with self-join (I think)

[Kristoffer Danielsson]

Nice :)
 
> To: sqlite-users@sqlite.org
> From: itandet...@mvps.org
> Date: Sat, 25 Sep 2010 10:21:18 -0400
> Subject: Re: [sqlite] Need help with self-join (I think)
> 
> Kristoffer Danielsson <kristoffer.daniels...@live.se> wrote:
> >> From: oliver@web.de
> >> a little mistake - here's the correction:
> >> 
> >> SELECT DISTINCT t.Year, b.Name,
> >> (SELECT SomeValue FROM Test INNER JOIN Test2 WHERE Year = t.Year AND Name =
> >> b.Name) AS SomeValue
> >> FROM Test t
> >> CROSS JOIN (SELECT DISTINCT Name FROM Test) b
> >> ;
> >
> > Ah, this one's easier to follow.
> 
> See how you like this one:
> 
> select *
> from (select distinct year from Test) as AllYears
> join (select distinct name from Test) as AllNames
> left join Test t on (t.year=AllYears.Year and t.name=AllNames.name)
> left join Test2 t2 on (t.TestId = t2.TestId);
> 
> -- 
> Igor Tandetnik
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help with self-join (I think)

[Oliver Peters]

Igor Tandetnik  writes:

[..]
> 
> See how you like this one:
> 
> select *
> from (select distinct year from Test) as AllYears
> join (select distinct name from Test) as AllNames
> left join Test t on (t.year=AllYears.Year and t.name=AllNames.name)
> left join Test2 t2 on (t.TestId = t2.TestId);
> 


Hey Igor, that was my victim ;-); but nevertheless - you're the greatest
and your solution seems very "clean" (only looking not analyzing) compared to my

Oliver

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

Re: [sqlite] Need help with self-join (I think)

[Igor Tandetnik]

Kristoffer Danielsson  wrote:
>> From: oliver@web.de
>> a little mistake - here's the correction:
>> 
>> SELECT DISTINCT t.Year, b.Name,
>> (SELECT SomeValue FROM Test INNER JOIN Test2 WHERE Year = t.Year AND Name =
>> b.Name) AS SomeValue
>> FROM Test t
>> CROSS JOIN (SELECT DISTINCT Name FROM Test) b
>> ;
>
> Ah, this one's easier to follow.

See how you like this one:

select *
from (select distinct year from Test) as AllYears
join (select distinct name from Test) as AllNames
left join Test t on (t.year=AllYears.Year and t.name=AllNames.name)
left join Test2 t2 on (t.TestId = t2.TestId);

-- 
Igor Tandetnik


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

Re: [sqlite] Need help with self-join (I think)

[Kristoffer Danielsson]

Well, let me explain :)

 

My problem originates from a chart generator which needs all data to be 
explicitly set - including "none" values. Think of this simple example:

2001|B|123

2002|C|234

 

How would you GROUP and COMPARE A and B in a bar chart? Two columns in each 
category...

2001: B=123, C=0

2002: B=0, C=234

 

Either you make the SQL query return those empty parts or you perform these 
extra check afterwards. You effectively helped me with the first approach :)

 

 

Regarding NULL values I tend to avoid them at all costs. NULL values make 
NATURAL JOINs "fail", which increases the chances of doing mistakes.


 
> Date: Sat, 25 Sep 2010 02:47:05 +0200
> From: oliver@web.de
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Need help with self-join (I think)
> 
> Am 25.09.2010 01:47, schrieb Kristoffer Danielsson:
> 
> [...]
> 
> >
> > Because, given a certain algorithm, generating statistics will become a lot 
> > easier if each value combination is represented in the returned row set.
> >
> 
> really? NULL means there are no values present or there are unknown 
> values - statistics with NULL should be without consequences
> 
> if you think you should calculate instead of NULL with 0 (the number) it 
> still isn't correct (try it with an average, ie sales figures: you 
> assume that NULL = 0 but that assumption is a mistake)
> 
> 
> [...]
> 
> Oliver
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help with self-join (I think)

[Oliver Peters]

Am 25.09.2010 01:47, schrieb Kristoffer Danielsson:

[...]

>
> Because, given a certain algorithm, generating statistics will become a lot 
> easier if each value combination is represented in the returned row set.
>

really? NULL means there are no values present or there are unknown 
values - statistics with NULL should be without consequences

if you think you should calculate instead of NULL with 0 (the number) it 
still isn't correct (try it with an average, ie sales figures: you 
assume that NULL = 0 but that assumption is a mistake)


[...]

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

Re: [sqlite] Need help with self-join (I think)

[Kristoffer Danielsson]

Yeah. USING is good to have :)

 

And yes, 123 might very well be a random number. I should have made that clear!
 
> To: sqlite-users@sqlite.org
> From: oliver@web.de
> Date: Sat, 25 Sep 2010 00:19:44 +
> Subject: Re: [sqlite] Need help with self-join (I think)
> 
> Kristoffer Danielsson <kristoffer.daniels...@...> writes:
> 
> > 
> > 
> > Ah, this one's easier to follow. What do you mean by "in this case"? What 
> > was
> the condition that made it
> 
> the point was not to remove something but to take into consideration that 
> there
> has to be a JOIN on the TestIDs (in this case results doesn't differ because 
> you
> only have 123 as SomeValue) - and because it's too late/early here is my 
> assumed
> last correction (see the USING I forgot, if you forget this you will get a
> Cartesian Product)
> 
> SELECT DISTINCT t.Year, b.Name,
> (SELECT SomeValue FROM Test INNER JOIN Test2 USING(TestID) WHERE Year = t.Year
> AND Name =
> b.Name) AS SomeValue
> FROM Test t
> CROSS JOIN (SELECT DISTINCT Name FROM Test) b
> ;
> 
> 
> 
> 
> > possible to remove the rest?
> > 
> > Again, thanks!
> > 
> > 
> > > To: sqlite-us...@...
> > > From: oliver@...
> > > Date: Sat, 25 Sep 2010 00:05:11 +
> > > Subject: Re: [sqlite] Need help with self-join (I think)
> > > 
> > > sry,
> > > 
> > > a little mistake - here's the correction:
> > > 
> > > SELECT DISTINCT t.Year, b.Name,
> > > (SELECT SomeValue FROM Test INNER JOIN Test2 WHERE Year = t.Year AND Name 
> > > =
> > > b.Name) AS SomeValue
> > > FROM Test t
> > > CROSS JOIN (SELECT DISTINCT Name FROM Test) b
> > > ;
> > > 
> > > 
> > > (results don't differ - in this case!)
> > > 
> > > Oliver
> > > 
> > > ___
> > > sqlite-users mailing list
> > > sqlite-us...@...
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > 
> > ___
> > sqlite-users mailing list
> > sqlite-us...@...
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > 
> > 
> 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help with self-join (I think)

[Oliver Peters]

Kristoffer Danielsson <kristoffer.daniels...@...> writes:

> 
> 
> Ah, this one's easier to follow. What do you mean by "in this case"? What was
the condition that made it

the point was not to remove something but to take into consideration that there
has to be a JOIN on the TestIDs (in this case results doesn't differ because you
only have 123 as SomeValue) - and because it's too late/early here is my assumed
last correction (see the USING I forgot, if you forget this you will get a
Cartesian Product)

SELECT DISTINCT t.Year, b.Name,
(SELECT SomeValue FROM Test INNER JOIN Test2 USING(TestID) WHERE Year = t.Year
AND Name =
b.Name) AS SomeValue
FROM Test t
CROSS JOIN (SELECT DISTINCT Name FROM Test) b
;




> possible to remove the rest?
> 
> Again, thanks!
> 
>  
> > To: sqlite-us...@...
> > From: oliver@...
> > Date: Sat, 25 Sep 2010 00:05:11 +
> > Subject: Re: [sqlite] Need help with self-join (I think)
> > 
> > sry,
> > 
> > a little mistake - here's the correction:
> > 
> > SELECT DISTINCT t.Year, b.Name,
> > (SELECT SomeValue FROM Test INNER JOIN Test2 WHERE Year = t.Year AND Name =
> > b.Name) AS SomeValue
> > FROM Test t
> > CROSS JOIN (SELECT DISTINCT Name FROM Test) b
> > ;
> > 
> > 
> > (results don't differ - in this case!)
> > 
> > Oliver
> > 
> > ___
> > sqlite-users mailing list
> > sqlite-us...@...
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-us...@...
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 




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

Re: [sqlite] Need help with self-join (I think)

[Kristoffer Danielsson]

Ah, this one's easier to follow. What do you mean by "in this case"? What was 
the condition that made it possible to remove the rest?


Again, thanks!

 
> To: sqlite-users@sqlite.org
> From: oliver@web.de
> Date: Sat, 25 Sep 2010 00:05:11 +0000
> Subject: Re: [sqlite] Need help with self-join (I think)
> 
> sry,
> 
> a little mistake - here's the correction:
> 
> SELECT DISTINCT t.Year, b.Name,
> (SELECT SomeValue FROM Test INNER JOIN Test2 WHERE Year = t.Year AND Name =
> b.Name) AS SomeValue
> FROM Test t
> CROSS JOIN (SELECT DISTINCT Name FROM Test) b
> ;
> 
> 
> (results don't differ - in this case!)
> 
> Oliver
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help with self-join (I think)

[Oliver Peters]

sry,

a little mistake - here's the correction:

SELECT DISTINCT t.Year, b.Name,
(SELECT SomeValue FROM Test INNER JOIN Test2 WHERE Year = t.Year AND Name =
b.Name) AS SomeValue
FROM Test t
CROSS JOIN (SELECT DISTINCT Name FROM Test) b
;


(results don't differ - in this case!)

Oliver

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

Re: [sqlite] Need help with self-join (I think)

[Kristoffer Danielsson]

Neat! Many thanks :)

 

Putting this logic together with my original query will be an interesting 
challenge, hehe. Do you believe this is the best solution?

 

Chris
 
> To: sqlite-users@sqlite.org
> From: oliver@web.de
> Date: Fri, 24 Sep 2010 23:47:59 +
> Subject: Re: [sqlite] Need help with self-join (I think)
> 
> Kristoffer Danielsson <kristoffer.daniels...@...> writes:
> 
> SELECT DISTINCT t.Year, b.Name,
> CASE
> WHEN (SELECT TestID FROM Test WHERE Year = t.Year AND Name = b.Name) IS NULL
> THEN NULL
> ELSE (SELECT SomeValue FROM Test2)
> END AS SomeValue
> FROM Test t
> CROSS JOIN (SELECT DISTINCT Name FROM Test) b
> ;
> 
> Oliver
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help with self-join (I think)

[Oliver Peters]

Kristoffer Danielsson  writes:

SELECT DISTINCT t.Year, b.Name,
CASE
WHEN (SELECT TestID FROM Test WHERE Year = t.Year AND Name = b.Name) IS NULL
THEN NULL
ELSE (SELECT SomeValue FROM Test2)
END AS SomeValue
FROM Test t
CROSS JOIN (SELECT DISTINCT Name FROM Test) b
;

Oliver

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

Re: [sqlite] Need help with self-join (I think)

[Kristoffer Danielsson]

Hi,

 

Thanks for your reply. You're right, it's an outer-join I'm looking for. 
Unfortunately, your suggestion does not do the trick.

 

"You didn't have a Test1 row for (2007, 'C'), so why would you get 2007|C|NULL?"

Because, given a certain algorithm, generating statistics will become a lot 
easier if each value combination is represented in the returned row set.

 

Perhaps a UNION is needed for this type of query?

 

> Date: Fri, 24 Sep 2010 18:17:51 -0500
> From: nicolas.willi...@oracle.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Need help with self-join (I think)
> 
> On Sat, Sep 25, 2010 at 01:01:36AM +0200, Kristoffer Danielsson wrote:
> > CREATE TABLE Test (TestID INTEGER PRIMARY KEY, Year INT NOT NULL, Name TEXT 
> > NOT NULL);
> > INSERT INTO Test (Year, Name) VALUES (2007, 'A');
> > INSERT INTO Test (Year, Name) VALUES (2007, 'B');
> > INSERT INTO Test (Year, Name) VALUES (2008, 'B');
> > INSERT INTO Test (Year, Name) VALUES (2009, 'A');
> > INSERT INTO Test (Year, Name) VALUES (2009, 'B');
> > INSERT INTO Test (Year, Name) VALUES (2009, 'C');
> > CREATE TABLE Test2 (TestID INTEGER NOT NULL UNIQUE, SomeValue INT NOT NULL);
> > INSERT INTO Test2 (TestID, SomeValue) VALUES (1, 123);
> > INSERT INTO Test2 (TestID, SomeValue) VALUES (2, 123);
> > INSERT INTO Test2 (TestID, SomeValue) VALUES (3, 123);
> > INSERT INTO Test2 (TestID, SomeValue) VALUES (4, 123);
> > INSERT INTO Test2 (TestID, SomeValue) VALUES (5, 123);
> > INSERT INTO Test2 (TestID, SomeValue) VALUES (6, 123);
> > 
> > [...]
> > My problem is that I want each combination of Year+Name to be represented - 
> > with zero/NULL for SomeValue. How do I accomplish this?
> > 
> > 2007|A|123
> > 2007|B|123
> > 2007|C|NULL
> 
> You didn't have a Test1 row for (2007, 'C'), so why would you get
> 2007|C|NULL? You also had one (and just one) row in Test2 for every
> TestID in Test, so there are no NULLs that could appear as you request.
> 
> But, if you did:
> 
> INSERT INTO Test (Year, Name) VALUES (2007, 'C');
> 
> without a corresponding row in Test2, then your SELECT would not return
> 2007|C|NULL. Try this:
> 
> SELECT Year, Name, SomeValue
> FROM Test LEFT OUTER JOIN Test2 ON (Test.TestID = Test2.TestID)
> ORDER BY Year, Name;
> 
> (Self-join is when both sides of the JOIN use the same table. That's
> not the case here. What you were looking for here is an OUTER JOIN
> instead of an INNER JOIN; NATURAL JOINs are a type of INNER JOIN.)
> 
> Nico
> -- 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Need help with self-join (I think)

[Nicolas Williams]

On Sat, Sep 25, 2010 at 01:01:36AM +0200, Kristoffer Danielsson wrote:
> CREATE TABLE Test (TestID INTEGER PRIMARY KEY, Year INT NOT NULL, Name TEXT 
> NOT NULL);
> INSERT INTO Test (Year, Name) VALUES (2007, 'A');
> INSERT INTO Test (Year, Name) VALUES (2007, 'B');
> INSERT INTO Test (Year, Name) VALUES (2008, 'B');
> INSERT INTO Test (Year, Name) VALUES (2009, 'A');
> INSERT INTO Test (Year, Name) VALUES (2009, 'B');
> INSERT INTO Test (Year, Name) VALUES (2009, 'C');
> CREATE TABLE Test2 (TestID INTEGER NOT NULL UNIQUE, SomeValue INT NOT NULL);
> INSERT INTO Test2 (TestID, SomeValue) VALUES (1, 123);
> INSERT INTO Test2 (TestID, SomeValue) VALUES (2, 123);
> INSERT INTO Test2 (TestID, SomeValue) VALUES (3, 123);
> INSERT INTO Test2 (TestID, SomeValue) VALUES (4, 123);
> INSERT INTO Test2 (TestID, SomeValue) VALUES (5, 123);
> INSERT INTO Test2 (TestID, SomeValue) VALUES (6, 123);
>  
> [...]
> My problem is that I want each combination of Year+Name to be represented - 
> with zero/NULL for SomeValue. How do I accomplish this?
> 
> 2007|A|123
> 2007|B|123
> 2007|C|NULL

You didn't have a Test1 row for (2007, 'C'), so why would you get
2007|C|NULL?  You also had one (and just one) row in Test2 for every
TestID in Test, so there are no NULLs that could appear as you request.

But, if you did:

INSERT INTO Test (Year, Name) VALUES (2007, 'C');

without a corresponding row in Test2, then your SELECT would not return
2007|C|NULL.  Try this:

SELECT Year, Name, SomeValue
FROM Test LEFT OUTER JOIN Test2 ON (Test.TestID = Test2.TestID)
ORDER BY Year, Name;

(Self-join is when both sides of the JOIN use the same table.  That's
not the case here.  What you were looking for here is an OUTER JOIN
instead of an INNER JOIN; NATURAL JOINs are a type of INNER JOIN.)

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

[sqlite] Need help with self-join (I think)

[Kristoffer Danielsson]

Hi,

 

I have an interesting SQL problem where I want certain rows to always be 
represented. It feels like a self-join, but I'm not sure. Please help!

 

Create a database as follows:

 



CREATE TABLE Test (TestID INTEGER PRIMARY KEY, Year INT NOT NULL, Name TEXT NOT 
NULL);

INSERT INTO Test (Year, Name) VALUES (2007, 'A');
INSERT INTO Test (Year, Name) VALUES (2007, 'B');
INSERT INTO Test (Year, Name) VALUES (2008, 'B');
INSERT INTO Test (Year, Name) VALUES (2009, 'A');
INSERT INTO Test (Year, Name) VALUES (2009, 'B');
INSERT INTO Test (Year, Name) VALUES (2009, 'C');

 

CREATE TABLE Test2 (TestID INTEGER NOT NULL UNIQUE, SomeValue INT NOT NULL);

INSERT INTO Test2 (TestID, SomeValue) VALUES (1, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (2, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (3, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (4, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (5, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (6, 123);



 

SELECT Year, Name, SomeValue FROM Test NATURAL JOIN Test2 ORDER BY Year, Name;

 

2007|A|123
2007|B|123
2008|B|123
2009|A|123
2009|B|123
2009|C|123

 

My problem is that I want each combination of Year+Name to be represented - 
with zero/NULL for SomeValue. How do I accomplish this?

 

2007|A|123
2007|B|123
2007|C|NULL
2008|A|NULL
2008|B|123
2008|C|NULL
2009|A|123
2009|B|123
2009|C|123

 

If there is an "easy" solution it would be great, as the original query is 
quite complex... :P

 

Thank you for your help!
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help understanding the basic of C++/sqlite

[Raoul]

Thanks all for your input.

Thanks to your feedback I have been able to advance quite a long way in my
studies... And yes my big problem is in fact understanding C++ (still in
learning phase).

4) I will check if this option is avaialable too on the express edition.

5) I did try to install System.Data.SQLite last week but it doesn't seem to
be supported in the express edition of the Visual product.

I never came across the Wiki link before even though I spent sometime on
sqlite.org so thanks again for this.

Regards,

Fabou

2010/1/19 Simon Davies 

> 2010/1/19 Fabrice NA :
>  > Hi all,
> >
> > I am trying to understand Sqlite and to make thing worse I am also
> learning
> > C++. You will not be surprised that I find it really hard to understand
> the
> > C++ example on the web. Can someone guide me to digest this? If you can
> > explain please do so by extrapolating since I am a total newbie.
> >
> > I have managed to compile the code example into a file called testdb.exe
> and
> > have created a database named Cars.db containing 7 rows. (notice that I
> have
> > removed some part of that code that I don't need help for)
> >
> > #include 
> > #include 
> >
> > static int callback(void *NotUsed, int argc, char **argv, char
> **azColName){
> >  int i;
> >  for(i=0; i >printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
> >  }
> >  printf("\n");
> >  return 0;
> > }
> >
> > int main(int argc, char **argv){
> >  sqlite3 *db;
> >  char *zErrMsg = 0;
> >  int rc;
> >  if( argc!=3 ){
> >fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
> >exit(1);
> >  }
> >
> >  rc = sqlite3_open(argv[1], );
> >  rc = sqlite3_exec(db, argv[2], callback, 0, );
> >
> >  sqlite3_close(db);
> >
> >  return 0;
> >
> > }
> >
> > Now in command line when I execute testdb.exe cars.db "select * from
> cars"
> > (the table is called cars too) everything works fine and I get an output
> > with all my seven rows. But how come this work? Is this some kind of
> magic?
>
> No
>
> >
> > Even after reading the doc again and again I don't understand (probably
> > because I am still learning some basic C++ concepts). Here my questions;
>
> Your problems appear to be mostly understanding C/C++.
>
> >
> > 1)   What is the purpose of doing "sqlite3  *db"  are we just
> creating a
> > pointer of type sqlite3 named db here?
>
> Yes. It is essentially a handle to the database that is returned by
> the database open call, which then needs to be passed to other sqlite
> library routines.
>
> >
> > 2)   At first I though that for the main function the first
> > parameter  "cars.db"
> > was represented by the variable argc and the second "select * from cars"
> by
> > argv. Well at the end, it looks like that "cars.db" is argv[1] and that
> the
> > select statement is argv[2]. What is argc then?
>
> argc is the count of passed in arguments
> argv is the vector of passed in arguments
> argv[0] is always the name of the executable that is executing
>
> > Seems like it's the number
> > of rows returned by the query (when looking at function callback) but how
> > the program find this out? How come we have a line "  if( argc!=3 )" and
> see
> > this same argc variable in the callback function?
>
> argc/argv in main and argc/argv in callback have nothing to do with each
> other.
>
> callback is a routine that needs to be provided for an sqlite_exec()
> call. In callback argc would be better named numColumns, and argv
> better named columnData.
>
> >
> > 3)   I don't understand the third argument from the query " rc =
> > sqlite3_exec(db, argv[2], callback, 0, );" my problem is
> > understanding the callback function inside the sqlite3_exec function.
> > Reading this link http://www.sqlite.org/c3ref/exec.html didn't help too
> much
> > even though it has been written in plain English.
>
> As sqlite_exec() retrieves each row of data, it calls the callback
> routine that is provided via the third argument (in your case also
> named 'callback'). You can then do what YOU want with the data by
> coding the callback appropriately. In the case you have shown, the
> values are simply being printed out.
>
> >
> > 4)   I am using VC++ Express from Microsoft  on WinXP and would like
> to
> > know if it's possible to pass parameter when debugging i.e. tell the
> > debugger to use cars.db for file and use "select * from cars" as a
> statement
> > (this would allow me to see what's hapening witout replacing variables by
> > their real values).
>
> Don't know about VC++ Express, but in Visual Studio there is a
> debugging tab on the project properties page that allows command line
> arguments to be specified.
>
> >
> > 5)   It's really hard to find some simple example on internet about
> C++
> > working with sqlite. Can any of you provide with simple sample codes that
> > shows how you can do and what you can do with sqlite and C++?
>
> SQLite is a C 

Re: [sqlite] Need help understanding the basic of C++/sqlite

[Simon Davies]

2010/1/19 Fabrice NA :
> Hi all,
>
> I am trying to understand Sqlite and to make thing worse I am also learning
> C++. You will not be surprised that I find it really hard to understand the
> C++ example on the web. Can someone guide me to digest this? If you can
> explain please do so by extrapolating since I am a total newbie.
>
> I have managed to compile the code example into a file called testdb.exe and
> have created a database named Cars.db containing 7 rows. (notice that I have
> removed some part of that code that I don't need help for)
>
> #include 
> #include 
>
> static int callback(void *NotUsed, int argc, char **argv, char **azColName){
>  int i;
>  for(i=0; i    printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
>  }
>  printf("\n");
>  return 0;
> }
>
> int main(int argc, char **argv){
>  sqlite3 *db;
>  char *zErrMsg = 0;
>  int rc;
>  if( argc!=3 ){
>    fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
>    exit(1);
>  }
>
>  rc = sqlite3_open(argv[1], );
>  rc = sqlite3_exec(db, argv[2], callback, 0, );
>
>  sqlite3_close(db);
>
>  return 0;
>
> }
>
> Now in command line when I execute testdb.exe cars.db "select * from cars"
> (the table is called cars too) everything works fine and I get an output
> with all my seven rows. But how come this work? Is this some kind of magic?

No

>
> Even after reading the doc again and again I don't understand (probably
> because I am still learning some basic C++ concepts). Here my questions;

Your problems appear to be mostly understanding C/C++.

>
> 1)       What is the purpose of doing "sqlite3  *db"  are we just creating a
> pointer of type sqlite3 named db here?

Yes. It is essentially a handle to the database that is returned by
the database open call, which then needs to be passed to other sqlite
library routines.

>
> 2)       At first I though that for the main function the first
> parameter  "cars.db"
> was represented by the variable argc and the second "select * from cars" by
> argv. Well at the end, it looks like that "cars.db" is argv[1] and that the
> select statement is argv[2]. What is argc then?

argc is the count of passed in arguments
argv is the vector of passed in arguments
argv[0] is always the name of the executable that is executing

> Seems like it's the number
> of rows returned by the query (when looking at function callback) but how
> the program find this out? How come we have a line "  if( argc!=3 )" and see
> this same argc variable in the callback function?

argc/argv in main and argc/argv in callback have nothing to do with each other.

callback is a routine that needs to be provided for an sqlite_exec()
call. In callback argc would be better named numColumns, and argv
better named columnData.

>
> 3)       I don't understand the third argument from the query " rc =
> sqlite3_exec(db, argv[2], callback, 0, );" my problem is
> understanding the callback function inside the sqlite3_exec function.
> Reading this link http://www.sqlite.org/c3ref/exec.html didn't help too much
> even though it has been written in plain English.

As sqlite_exec() retrieves each row of data, it calls the callback
routine that is provided via the third argument (in your case also
named 'callback'). You can then do what YOU want with the data by
coding the callback appropriately. In the case you have shown, the
values are simply being printed out.

>
> 4)       I am using VC++ Express from Microsoft  on WinXP and would like to
> know if it's possible to pass parameter when debugging i.e. tell the
> debugger to use cars.db for file and use "select * from cars" as a statement
> (this would allow me to see what's hapening witout replacing variables by
> their real values).

Don't know about VC++ Express, but in Visual Studio there is a
debugging tab on the project properties page that allows command line
arguments to be specified.

>
> 5)       It's really hard to find some simple example on internet about C++
> working with sqlite. Can any of you provide with simple sample codes that
> shows how you can do and what you can do with sqlite and C++?

SQLite is a C library, not C++. You can however link C++ code against
C object, and the SQLite library even provides appropriate 'extern "C"
{}' wrapping around things so that it can be compiled with a C++
compiler; but that does not make it C++ code.

There are C++ wrappers, notably System.Data.SQLite
(http://sqlite.phxsoftware.com)
(sorry anybody else)

further things to take a look at:
http://www.sqlite.org/cintro.html

The code you show above is based on sqlite3_exec(). This is
deprecated; better to use sqlite3_prepare_v2(), sqlite3_step,
sqlite3_reset()/sqlite3_finalize():
http://www.sqlite.org/cvstrac/wiki?p=SimpleCode

>
> I hope I didn't offended anyone with my lack of knowledge and I thank in
> advance the courageous ones who managed to read this email until the end and
> probably got answers to my questions.
>
> Fabou

Regards,

Re: [sqlite] Need help understanding the basic of C++/sqlite

[a1rex]

1. Complete c program for beginners  is here:

http://manishtech.wordpress.com/2009/03/30/sqlite-with-c/


2. sqlite3_exec with callback is an obsolete concept from sqlite2

Use sqlite3_prepare_v2 with sqlite3_step  as it is linear,  more effective and 
giving more control approach.
 
I hope it helps,
Samuel




From: noel frankinet <noel.franki...@skynet.be>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Tue, January 19, 2010 9:18:06 AM
Subject: Re: [sqlite] Need help understanding the basic of C++/sqlite

Fabrice NA a écrit :

Hi,

In sqlite3_exec, you pass a function pointer (callback).
Sqlite call that function with each row of data

Best wishes

Noël
> Hi all,
>
>
>
> I am trying to understand Sqlite and to make thing worse I am also learning
> C++. You will not be surprised that I find it really hard to understand the
> C++ example on the web. Can someone guide me to digest this? If you can
> explain please do so by extrapolating since I am a total newbie.
>
>
>
> I have managed to compile the code example into a file called testdb.exe and
> have created a database named Cars.db containing 7 rows. (notice that I have
> removed some part of that code that I don't need help for)
>
>
>
> #include 
>
> #include 
>
>
>
> static int callback(void *NotUsed, int argc, char **argv, char **azColName){
>
>   int i;
>
>   for(i=0; i<argc; i++){
>
> printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
>
>   }
>
>   printf("\n");
>
>   return 0;
>
> }
>
>
>
> int main(int argc, char **argv){
>
>   sqlite3 *db;
>
>   char *zErrMsg = 0;
>
>   int rc;
>
>
>
>   if( argc!=3 ){
>
> fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
>
> exit(1);
>
>   }
>
>   rc = sqlite3_open(argv[1], );
>
>
>
>   rc = sqlite3_exec(db, argv[2], callback, 0, );
>
>
>
>   sqlite3_close(db);
>
>
>
>   return 0;
>
> }
>
>
>
> Now in command line when I execute testdb.exe cars.db "select * from cars"
> (the table is called cars too) everything works fine and I get an output
> with all my seven rows. But how come this work? Is this some kind of magic?
>
>
>
> Even after reading the doc again and again I don't understand (probably
> because I am still learning some basic C++ concepts). Here my questions;
>
>
>
> 1)   What is the purpose of doing "sqlite3  *db"  are we just creating a
> pointer of type sqlite3 named db here?
>
>
>
> 2)   At first I though that for the main function the first
> parameter  "cars.db"
> was represented by the variable argc and the second "select * from cars" by
> argv. Well at the end, it looks like that "cars.db" is argv[1] and that the
> select statement is argv[2]. What is argc then?  Seems like it's the number
> of rows returned by the query (when looking at function callback) but how
> the program find this out? How come we have a line "  if( argc!=3 )" and see
> this same argc variable in the callback function?
>
>
>
> 3)   I don't understand the third argument from the query " rc =
> sqlite3_exec(db, argv[2], callback, 0, );" my problem is
> understanding the callback function inside the sqlite3_exec function.
> Reading this link http://www.sqlite.org/c3ref/exec.html didn't help too much
> even though it has been written in plain English.
>
>
>
> 4)   I am using VC++ Express from Microsoft  on WinXP and would like to
> know if it's possible to pass parameter when debugging i.e. tell the
> debugger to use cars.db for file and use "select * from cars" as a statement
> (this would allow me to see what's hapening witout replacing variables by
> their real values).
>
>
>
> 5)   It's really hard to find some simple example on internet about C++
> working with sqlite. Can any of you provide with simple sample codes that
> shows how you can do and what you can do with sqlite and C++?
>
>
>
> I hope I didn't offended anyone with my lack of knowledge and I thank in
> advance the courageous ones who managed to read this email until the end and
> probably got answers to my questions.
>
>
>
> Fabou
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>  

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



  __
Looking for the perfect gift? Give the gift of Flickr! 

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

Re: [sqlite] Need help understanding the basic of C++/sqlite

[noel frankinet]

Fabrice NA a écrit :

Hi,

In sqlite3_exec, you pass a function pointer (callback).
Sqlite call that function with each row of data

Best wishes

Noël
> Hi all,
>
>
>
> I am trying to understand Sqlite and to make thing worse I am also learning
> C++. You will not be surprised that I find it really hard to understand the
> C++ example on the web. Can someone guide me to digest this? If you can
> explain please do so by extrapolating since I am a total newbie.
>
>
>
> I have managed to compile the code example into a file called testdb.exe and
> have created a database named Cars.db containing 7 rows. (notice that I have
> removed some part of that code that I don't need help for)
>
>
>
> #include 
>
> #include 
>
>
>
> static int callback(void *NotUsed, int argc, char **argv, char **azColName){
>
>   int i;
>
>   for(i=0; i
> printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
>
>   }
>
>   printf("\n");
>
>   return 0;
>
> }
>
>
>
> int main(int argc, char **argv){
>
>   sqlite3 *db;
>
>   char *zErrMsg = 0;
>
>   int rc;
>
>
>
>   if( argc!=3 ){
>
> fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
>
> exit(1);
>
>   }
>
>   rc = sqlite3_open(argv[1], );
>
>
>
>   rc = sqlite3_exec(db, argv[2], callback, 0, );
>
>
>
>   sqlite3_close(db);
>
>
>
>   return 0;
>
> }
>
>
>
> Now in command line when I execute testdb.exe cars.db "select * from cars"
> (the table is called cars too) everything works fine and I get an output
> with all my seven rows. But how come this work? Is this some kind of magic?
>
>
>
> Even after reading the doc again and again I don't understand (probably
> because I am still learning some basic C++ concepts). Here my questions;
>
>
>
> 1)   What is the purpose of doing "sqlite3  *db"  are we just creating a
> pointer of type sqlite3 named db here?
>
>
>
> 2)   At first I though that for the main function the first
> parameter  "cars.db"
> was represented by the variable argc and the second "select * from cars" by
> argv. Well at the end, it looks like that "cars.db" is argv[1] and that the
> select statement is argv[2]. What is argc then?  Seems like it's the number
> of rows returned by the query (when looking at function callback) but how
> the program find this out? How come we have a line "  if( argc!=3 )" and see
> this same argc variable in the callback function?
>
>
>
> 3)   I don't understand the third argument from the query " rc =
> sqlite3_exec(db, argv[2], callback, 0, );" my problem is
> understanding the callback function inside the sqlite3_exec function.
> Reading this link http://www.sqlite.org/c3ref/exec.html didn't help too much
> even though it has been written in plain English.
>
>
>
> 4)   I am using VC++ Express from Microsoft  on WinXP and would like to
> know if it's possible to pass parameter when debugging i.e. tell the
> debugger to use cars.db for file and use "select * from cars" as a statement
> (this would allow me to see what's hapening witout replacing variables by
> their real values).
>
>
>
> 5)   It's really hard to find some simple example on internet about C++
> working with sqlite. Can any of you provide with simple sample codes that
> shows how you can do and what you can do with sqlite and C++?
>
>
>
> I hope I didn't offended anyone with my lack of knowledge and I thank in
> advance the courageous ones who managed to read this email until the end and
> probably got answers to my questions.
>
>
>
> Fabou
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   

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

[sqlite] Need help understanding the basic of C++/sqlite

[Fabrice NA]

Hi all,



I am trying to understand Sqlite and to make thing worse I am also learning
C++. You will not be surprised that I find it really hard to understand the
C++ example on the web. Can someone guide me to digest this? If you can
explain please do so by extrapolating since I am a total newbie.



I have managed to compile the code example into a file called testdb.exe and
have created a database named Cars.db containing 7 rows. (notice that I have
removed some part of that code that I don't need help for)



#include 

#include 



static int callback(void *NotUsed, int argc, char **argv, char **azColName){

  int i;

  for(i=0; i

Re: [sqlite] Need help constructing a query

[Igor Tandetnik]

Jörgen Hägglund
 wrote: 
> Hi all!
> I have three tables, one containing IP addresses as integers, lets
> call 
> it 'base'.
> A second table containing IP ranges and the country code the range
> belongs to. Lets call this 'IPtoCC' with the fields IPFrom and IPTo as
> integers and CC as text.
> Then a third, 'Exclude', containing country codes i want to be
> excluded with a single field CC as text.
> What I need is to delete all records in 'base' where base.IP falls
> into 
> a range of IPtoCC.IPFrom to IPtoCC.IPTo and that IPtoCC.CC is in
> Exclude.CC. Is it possible to do this in a single DELETE?

delete from base where exists
(select 1 from IPtoCC join Exclude on (IPtoCC.CC = Exclude.CC)
 where base.IP between IPFrom and IPTo);

Igor Tandetnik

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

Re: [sqlite] Need help constructing a query

[Jay A. Kreibich]

On Wed, Nov 11, 2009 at 09:36:41AM -0600, P Kishor scratched on the wall:
> 2009/11/11 Jörgen Hägglund :
> > Hi all!
> > I have three tables, one containing IP addresses as integers, lets call
> > it 'base'.
> > A second table containing IP ranges and the country code the range
> > belongs to. Lets call this 'IPtoCC' with the fields IPFrom and IPTo as
> > integers and CC as text.
> > Then a third, 'Exclude', containing country codes i want to be excluded
> > with a single field CC as text.
> > What I need is to delete all records in 'base' where base.IP falls into
> > a range of IPtoCC.IPFrom to IPtoCC.IPTo and that IPtoCC.CC is in Exclude.CC.
> > Is it possible to do this in a single DELETE?
> >
> 
> DELETE
> FROM base
> WHERE IP BETWEEN
>   (SELECT IPFrom FROM IPtoCC JOIN Exclude ON IPtoCC.CC = Exclude.CC) AND
>   (SELECT IPTo FROM IPtoCC JOIN Exclude ON IPtoCC.CC = Exclude.CC)

  I think you need to add WHERE clauses to the sub-selects so you pick
  the proper upper and lower bound for that base value.  Something like
  "...WHERE base.ip >= IPtoCC.IPFrom AND base.ip <= IPtoCC.IPTo".
  Otherwise each sub-select may return a whole column of values.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help constructing a query

[P Kishor]

2009/11/11 Jörgen Hägglund :
> Hi all!
> I have three tables, one containing IP addresses as integers, lets call
> it 'base'.
> A second table containing IP ranges and the country code the range
> belongs to. Lets call this 'IPtoCC' with the fields IPFrom and IPTo as
> integers and CC as text.
> Then a third, 'Exclude', containing country codes i want to be excluded
> with a single field CC as text.
> What I need is to delete all records in 'base' where base.IP falls into
> a range of IPtoCC.IPFrom to IPtoCC.IPTo and that IPtoCC.CC is in Exclude.CC.
> Is it possible to do this in a single DELETE?
>

DELETE
FROM base
WHERE IP BETWEEN
  (SELECT IPFrom FROM IPtoCC JOIN Exclude ON IPtoCC.CC = Exclude.CC) AND
  (SELECT IPTo FROM IPtoCC JOIN Exclude ON IPtoCC.CC = Exclude.CC)


Logically the above should work, but it requires two sub-selects.
Others will likely suggest a better way.


> Regards,
> /Jörgen
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, Wisconsin, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Need help constructing a query

[Jörgen Hägglund]

Hi all!
I have three tables, one containing IP addresses as integers, lets call 
it 'base'.
A second table containing IP ranges and the country code the range 
belongs to. Lets call this 'IPtoCC' with the fields IPFrom and IPTo as 
integers and CC as text.
Then a third, 'Exclude', containing country codes i want to be excluded 
with a single field CC as text.
What I need is to delete all records in 'base' where base.IP falls into 
a range of IPtoCC.IPFrom to IPtoCC.IPTo and that IPtoCC.CC is in Exclude.CC.
Is it possible to do this in a single DELETE?

Regards,
/Jörgen
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need Help SQL

[Rick Ratchford]

Thanks! 

Cheers!
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
#>boun...@sqlite.org] On Behalf Of sub sk79
#>Sent: Monday, October 12, 2009 9:35 PM
#>To: General Discussion of SQLite Database
#>Subject: Re: [sqlite] Need Help SQL
#>
#>Hi!,
#>
#>Here is a non-math version using PL/SQL date operators and functions
#>available in StepSqlite (https://www.metatranz.com/stepsqlite/).
#>Hopefully this should be easier to follow.
#>
#>You can compile the below code directly to a win32 dll on the
#>StepSqlite website and then use it in your VB code.
#>
#>Assumes Date column is in '-MM-DD'. If using a different format,
#>just call   DateTime.setDateFormat()   to set proper format.
#>
#>
#>create table items(ID integer, Date date, Price float);
#>PACKAGE BODY MyPackage IS
#>PROCEDURE get_prices (start_month char, start_day char, end_month
#>char, end_day char  ) IS
#>BEGIN
#>-- n_* below are dates normalized to fall in a given year, here I
#>chose year 2000 because its a leap year and has all possible
#>day-numbers for proper normalization.
#>FOR item IN
#>(SELECT id, to_char(date, 'MM') mon, to_char(date, 'DD') day, price
#> FROM (SELECT id, date, price,
#> to_date('2000-' || start_month||'-'||start_day,
#>'-MM-DD') n_start,
#> to_date('2000-' || end_month  ||'-'||end_day,
#>'-MM-DD') n_end,
#> to_date(to_char(date, '2000-MM-DD'), '-MM-DD')
#>n_date
#>FROM items
#>  )
#> WHERE (n_start < n_end AND n_date between n_start and n_end)
#>OR (n_start > n_end AND n_date NOT between n_end and n_start)
#> ORDER BY to_char(date, 'MM-DD')
#> )
#>LOOP
#>DBMS_OUTPUT.put_line(item.id || '  ' || item.mon || '
#>'||item.day||'  '||item.price);
#>END LOOP;
#>END;
#>
#>BEGIN
#>insert into items(id, date, price) values(1,'2004-01-01',  1.1);
#>insert into items(id, date, price) values(2,'2004-02-01',  1.1);
#>insert into items(id, date, price) values(3,'2004-02-16',  1.1);
#>insert into items(id, date, price) values(4,'2004-10-01',  1.1);
#>insert into items(id, date, price) values(5,'2004-10-22',  1.1);
#>
#>insert into items(id, date, price) values(51,'2005-01-01',  5.1);
#>insert into items(id, date, price) values(52,'2005-02-01',  5.1);
#>insert into items(id, date, price) values(53,'2005-02-16',  5.1);
#>insert into items(id, date, price) values(54,'2005-10-01',  5.1);
#>insert into items(id, date, price) values(55,'2005-10-22',  5.1);
#>
#>insert into items(id, date, price) values(61,'2006-01-01',  6.1);
#>insert into items(id, date, price) values(62,'2006-02-01',  6.1);
#>insert into items(id, date, price) values(63,'2006-02-16',  6.1);
#>insert into items(id, date, price) values(64,'2006-10-01',  6.1);
#>insert into items(id, date, price) values(65,'2006-10-22',  6.1);
#>
#>DBMS_OUTPUT.put_line('Price data Range: 02-15 to 10-21');
#>get_prices('02', '15', '10', '21');
#>
#>DBMS_OUTPUT.put_line('Price data Range: 10-21 to 02-15');
#>get_prices('10', '21', '02','15');
#>
#>rollback;
#>END;
#>
#>
#>Result:
#>
#>Price data Range: 02-15 to 10-21
#>3  02  16  1.1
#>53  02  16  5.1
#>63  02  16  6.1
#>4  10  01  1.1
#>54  10  01  5.1
#>64  10  01  6.1
#>Price data Range: 10-21 to 02-15
#>1  01  01  1.1
#>51  01  01  5.1
#>61  01  01  6.1
#>2  02  01  1.1
#>52  02  01  5.1
#>62  02  01  6.1
#>5  10  22  1.1
#>55  10  22  5.1
#>65  10  22  6.1
#>
#>
#>
#>Regards,
#>SK
#>___
#>sqlite-users mailing list
#>sqlite-users@sqlite.org
#>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Need Help SQL

[sub sk79]

Hi!,

Here is a non-math version using PL/SQL date operators and functions
available in StepSqlite (https://www.metatranz.com/stepsqlite/).
Hopefully this should be easier to follow.

You can compile the below code directly to a win32 dll on the
StepSqlite website and then use it in your VB code.

Assumes Date column is in '-MM-DD'. If using a different format,
just call   DateTime.setDateFormat()   to set proper format.


create table items(ID integer, Date date, Price float);
PACKAGE BODY MyPackage IS
PROCEDURE get_prices (start_month char, start_day char, end_month
char, end_day char  ) IS
BEGIN
-- n_* below are dates normalized to fall in a given year, here I
chose year 2000 because its a leap year and has all possible
day-numbers for proper normalization.
FOR item IN
(SELECT id, to_char(date, 'MM') mon, to_char(date, 'DD') day, price
 FROM (SELECT id, date, price,
 to_date('2000-' || start_month||'-'||start_day,
'-MM-DD') n_start,
 to_date('2000-' || end_month  ||'-'||end_day,
'-MM-DD') n_end,
 to_date(to_char(date, '2000-MM-DD'), '-MM-DD')
n_date
FROM items
  )
 WHERE (n_start < n_end AND n_date between n_start and n_end)
OR (n_start > n_end AND n_date NOT between n_end and n_start)
 ORDER BY to_char(date, 'MM-DD')
 )
LOOP
DBMS_OUTPUT.put_line(item.id || '  ' || item.mon || '
'||item.day||'  '||item.price);
END LOOP;
END;

BEGIN
insert into items(id, date, price) values(1,'2004-01-01',  1.1);
insert into items(id, date, price) values(2,'2004-02-01',  1.1);
insert into items(id, date, price) values(3,'2004-02-16',  1.1);
insert into items(id, date, price) values(4,'2004-10-01',  1.1);
insert into items(id, date, price) values(5,'2004-10-22',  1.1);

insert into items(id, date, price) values(51,'2005-01-01',  5.1);
insert into items(id, date, price) values(52,'2005-02-01',  5.1);
insert into items(id, date, price) values(53,'2005-02-16',  5.1);
insert into items(id, date, price) values(54,'2005-10-01',  5.1);
insert into items(id, date, price) values(55,'2005-10-22',  5.1);

insert into items(id, date, price) values(61,'2006-01-01',  6.1);
insert into items(id, date, price) values(62,'2006-02-01',  6.1);
insert into items(id, date, price) values(63,'2006-02-16',  6.1);
insert into items(id, date, price) values(64,'2006-10-01',  6.1);
insert into items(id, date, price) values(65,'2006-10-22',  6.1);

DBMS_OUTPUT.put_line('Price data Range: 02-15 to 10-21');
get_prices('02', '15', '10', '21');

DBMS_OUTPUT.put_line('Price data Range: 10-21 to 02-15');
get_prices('10', '21', '02','15');

rollback;
END;


Result:

Price data Range: 02-15 to 10-21
3  02  16  1.1
53  02  16  5.1
63  02  16  6.1
4  10  01  1.1
54  10  01  5.1
64  10  01  6.1
Price data Range: 10-21 to 02-15
1  01  01  1.1
51  01  01  5.1
61  01  01  6.1
2  02  01  1.1
52  02  01  5.1
62  02  01  6.1
5  10  22  1.1
55  10  22  5.1
65  10  22  6.1



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

Re: [sqlite] Need Help SQL

[Igor Tandetnik]

Rick Ratchford 
wrote: 
> Your code:
> 
> ((:EndMonth - :StartMonth) * 100 + (:EndDay - :StartDay) + 1300) %
> 1300 
> 
> VB:
> 
> (" & lngEndMth - lngStartMth) & ") * 100 + " & ((lngEndDay -
> lngStartDay) + 1300) Mod 1300
> 
> In the VB version, I'm simply doing all the math outside the string
> itself

Not all math: you are doing "*100 + " in SQL. Herein lies the problem.

> and concat it within the string. Just for giggles, I changed
> so the math is done inside the SQL string and changed mod back to %.
> Same results. 
> 
> SELECT Date, Month, Day, Open, High, Low, Close FROM [AUS $, COMP-R
> AN_REV] WHERE ((Month - 8)*100 + (Day - 1) + 1300) % 1300 <= -4 * 100
> + 0 ORDER BY ((Month - 8)*100 + (Day - 1) + 1300) % 1300

This is not equivalent to what I wrote. It lacks "+1300) % 1300) on the right 
hand side. In my statement, both sides of the comparison are always 
non-negative.

> Well, after going through all the above steps explaining what I did to
> convert, it appears that I can SEE what you mean by the above
> statement. 
> 
> Rather than this...
> 
> (" & lngEndMth - lngStartMth & ") * 100 + ((" & lngEndDay -
> lngStartDay & ") + 1300) % 1300 "
> 
> It should have been this...
> 
> ((" & lngEndMth - lngStartMth & ") * 100 + (" & lngEndDay -
> lngStartDay & ") + 1300) % 1300 "

Either that, or 

& ((lngEndMth - lngStartMth) * 100 +  (lngEndDay - lngStartDay) + 1300) Mod 
1300 &

It's OK to do the math on VB side, as long as you actually do the correct math.

Igor Tandetnik


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

Re: [sqlite] Need Help SQL

[RB Smissaert]

> I'm using Olaf Schmidt's VB SQLite binder.

That does use parameterized statements.
Look at the methods and properties of the cCommand object in the object
browser. Also look at the demo code that comes with dhRichClient3.

RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford
Sent: 12 October 2009 20:16
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Need Help SQL

#>Doesn't your VB SQLite binding support parameterized statements? If so,
#>I'd suggest you dump it and find a better one.
#>
#>Igor Tandetnik

I'm not up on the terminology. I'm using Olaf Schmidt's VB SQLite binder.
He'd probably be better to answer this question than I.

:-)
Rick



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


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

Re: [sqlite] Need Help SQL

[Rick Ratchford]

#>> Here is that string from your earlier example:
#>>
#>> sSQL = "SELECT Date, Month, Day, Open, High, Low, Close FROM [" &
#>> gsTableName & "] " & "WHERE ((Month - " & lngStartMth & ")*100 + (Day
#>> - " &
#>> lngStartDay & ") + 1300) % 1300 <= " & lngEndMth - lngStartMth & " *
#>> 100 + " & ((lngEndDay - lngStartDay) + 1300) Mod 1300 & " ORDER BY
#>> ((Month - " &
#>> lngStartMth & ")*100 + (Day - " & lngStartDay & ") + 1300) % 1300"
#>
#>In the the right-hand side of <= operator, you only apply  "mod 1300" to
#>((lngEndDay - lngStartDay) + 1300) part but not to
#>(lngEndMth - lngStartMth)*100 part. Be careful which calculations you
#>perform in the host language, and which you embed in SQL
#>statement. You have a strange mix of the two.
#>
#>Doesn't your VB SQLite binding support parameterized statements? If so,
#>I'd suggest you dump it and find a better one.
#>
#>Igor Tandetnik

The <= replaced the < you had in the original because it would return UP TO
but not including the Ending mm/dd.

Since the problem starts following the WHERE clause, I'm not going to
include the info before it.

I have in my code...

WHERE ((Month - " & lngStartMth & ")*100 + (Day - " & lngStartDay & ") +
1300) % 1300 <= (" & lngEndMth - lngStartMth & ") * 100 + " & ((lngEndDay -
lngStartDay) + 1300) Mod 1300 & " ORDER BY ((Month - " & lngStartMth &
")*100 + (Day - " & lngStartDay & ") + 1300) % 1300"

Your code:

WHERE ((Month - :StartMonth) * 100 + (Day - :StartDay) + 1300) % 1300 <

VB:

WHERE ((Month - " & lngStartMth & ") * 100 + (Day - " & lngStartDay & ") +
1300 % 1300 <=

All that was done is that :StartMonth is replaced with variable lngStartMth
and :StartDay replaced with variable lngStartDay. Both long values.

Your code:

((:EndMonth - :StartMonth) * 100 + (:EndDay - :StartDay) + 1300) % 1300

VB:

(" & lngEndMth - lngStartMth) & ") * 100 + " & ((lngEndDay - lngStartDay) +
1300) Mod 1300

In the VB version, I'm simply doing all the math outside the string itself
and concat it within the string. Just for giggles, I changed so the math is
done inside the SQL string and changed mod back to %. Same results.

SELECT Date, Month, Day, Open, High, Low, Close FROM [AUS $, COMP-R AN_REV]
WHERE ((Month - 8)*100 + (Day - 1) + 1300) % 1300 <= -4 * 100 + 0 ORDER BY
((Month - 8)*100 + (Day - 1) + 1300) % 1300

  
#>In the the right-hand side of <= operator, you only apply  "mod 1300" to
#>((lngEndDay - lngStartDay) + 1300) part but not to
#>(lngEndMth - lngStartMth)*100 part.

Well, after going through all the above steps explaining what I did to
convert, it appears that I can SEE what you mean by the above statement.

Rather than this...

(" & lngEndMth - lngStartMth & ") * 100 + ((" & lngEndDay - lngStartDay & ")
+ 1300) % 1300 "

It should have been this...

((" & lngEndMth - lngStartMth & ") * 100 + (" & lngEndDay - lngStartDay & ")
+ 1300) % 1300 "


I'm HAPPY to say that it WORKS!!!

Thanks for your help and time.

Cheers!

Rick












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

Re: [sqlite] Need Help SQL

[Rick Ratchford]

#>Doesn't your VB SQLite binding support parameterized statements? If so,
#>I'd suggest you dump it and find a better one.
#>
#>Igor Tandetnik

I'm not up on the terminology. I'm using Olaf Schmidt's VB SQLite binder.
He'd probably be better to answer this question than I.

:-)
Rick



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

Re: [sqlite] Need Help SQL

[Igor Tandetnik]

Rich Shepard  wrote:
> On Sun, 11 Oct 2009, Igor Tandetnik wrote:
>
>> I simply combine (month, day) pair into a single number - the same
>> way you combine two digits of a decimal number by multiplying the
>> first by 10 and adding the second. The multiplier doesn't have to be
>> 100 - any number greater than 31 will do.
>
>   Would it not be easier to use the DATE column and STRFTIME()?

It would be possible. Would it be easier? I'm not sure. Why don't you write an 
equivalent statement using this approach, then 
compare and contrast yours and mine?

> Specifying start and end dates as -MM-DD removes the need for
> such manipulations

As far as I can tell, the OP wants to only specify start and end month/day, and 
expects data from all years that fall into the 
specified window. E.g., for the span of 1/1 through 1/31, he wants all entries 
from January regardless of the year.

> and will work within a single year as well as over
> multi-year spans.

I don't quite see how you plan to pull that off. Please enlighten me.

>   I ask because that's the way I would approach the solution to the
> question Rick asked. The MONTH and DAY columns seem repetitive to me
> and a potential souce of loss of integrity. If those columns are
> filled by extracting the appropriate portions of the DATE column, why
> not use the latter itself?

Suppose dates where represented in the database as -MM-DD  - how would that 
help you solve the problem? Again, I suggest you 
show an example.

Igor Tandetnik 


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

Re: [sqlite] Need Help SQL

[Rich Shepard]

On Sun, 11 Oct 2009, Igor Tandetnik wrote:

> I simply combine (month, day) pair into a single number - the same way you
> combine two digits of a decimal number by multiplying the first by 10 and
> adding the second. The multiplier doesn't have to be 100 - any number
> greater than 31 will do.

Igor,

   Would it not be easier to use the DATE column and STRFTIME()? Specifying
start and end dates as -MM-DD removes the need for such manipulations
and will work within a single year as well as over multi-year spans.

   I ask because that's the way I would approach the solution to the question
Rick asked. The MONTH and DAY columns seem repetitive to me and a potential
souce of loss of integrity. If those columns are filled by extracting the
appropriate portions of the DATE column, why not use the latter itself?

Curious minds want to know,

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

Re: [sqlite] Need Help SQL

[Igor Tandetnik]

Rick Ratchford wrote:
> #>There is a slight complication with the case where endDate <
> startDate, #>where you want to wrap around to next year. For that, I
> simply need to #>perform calculations modulo a large number - any
> number greater than the #>representation of 12/31 in my scheme. 1300
> is one such number. #>
> #>Igor Tandetnik
> 
> That slight complication is still there though. It works when the
> start < end, but it fails the other way around.

Precisely how does it fail? Seems to work for me:

create table t(month integer, day integer, price integer);
insert into t values(1, 10, 1);
insert into t values(5, 10, 2);
insert into t values(12, 10, 3);

-- select rows between 10/1 and 3/31
select Month, Day, Price
from t, (select 10 as StartMonth, 1 as StartDay, 3 as EndMonth, 31 as EndDay)
where ((Month - StartMonth)*100 + (Day - StartDay) + 1300) % 1300 <
   ((EndMonth - StartMonth)*100 + (EndDay - StartDay) + 1300) % 1300
order by ((Month - StartMonth)*100 + (Day - StartDay) + 1300) % 1300;
12|10|3
1|10|1

Isn't that what you wanted?

Igor Tandetnik


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

Re: [sqlite] Need Help SQL

[Rick Ratchford]

#>There is a slight complication with the case where endDate < startDate,
#>where you want to wrap around to next year. For that, I simply need to
#>perform calculations modulo a large number - any number greater than the
#>representation of 12/31 in my scheme. 1300 is one such number.
#>
#>Igor Tandetnik

That slight complication is still there though. It works when the start <
end, but it fails the other way around.

Pretty clever with the numbering system though. And thanks for suggestion.

:-)

Rick



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

Re: [sqlite] Need Help SQL

[Rick Ratchford]

Igor,

Okay, I give.

What do the 100 and 1300 values signify? I am completely baffled at how you
arrived at these values and what they do.

Thanks.

Rick 
 
#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
#>boun...@sqlite.org] On Behalf Of Igor Tandetnik
#>Sent: Friday, October 09, 2009 6:46 PM
#>To: sqlite-users@sqlite.org
#>Subject: Re: [sqlite] Need Help SQL
#>
#>Rick Ratchford <r...@amazingaccuracy.com>
#>> Data Fields:  ID, Date, Month, Day, Year, Price
#>>
#>> Problem: When provided the starting Month/Day numbers, and ending
#>> Month/Day numbers, what is the correct way to SQL the database so
#>> that the recordset created returns as follows (assume 4 years of
#>> data):
#>>
#>> In other words, all the 3/12's first, then 3/13's, then 3/14's, etc.
#>> all the way down to the ending Month/Date.
#>>
#>> Where I really get stuck is when the Starting Month number is greater
#>> than the Ending Month number. For example, say I want the starting
#>> Month/Day as 10/22 and the ending Month/Day as 4/16. Simply stating
#>> WHERE Month >= Start Month AND Month <= End Month doesn't seem
#>> correct. Since I want to return all the prices between 10/22 and 4/16
#>> of each year of data I have in the table, no Month number could be
#>> greater than/equal to 10  and also less than/equal to 4.
#>
#>Try something like this:
#>
#>select Month, Day, Price from mytable
#>where ((Month - :StartMonth)*100 + (Day - :StartDay) + 1300) % 1300 <
#>   ((:EndMonth - :StartMonth)*100 + (:EndDay - :StartDay) + 1300)
#>% 1300
#>order by ((Month - :StartMonth)*100 + (Day - :StartDay) + 1300) % 1300;
#>
#>Igor Tandetnik
#>
#>___
#>sqlite-users mailing list
#>sqlite-users@sqlite.org
#>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Need Help SQL

[Rick Ratchford]

Thank you Igor. I will. :-)

Cheers!
 
Rick 
 
#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
#>boun...@sqlite.org] On Behalf Of Igor Tandetnik
#>Sent: Friday, October 09, 2009 6:46 PM
#>To: sqlite-users@sqlite.org
#>Subject: Re: [sqlite] Need Help SQL
#>
#>Rick Ratchford <r...@amazingaccuracy.com>
#>> Data Fields:  ID, Date, Month, Day, Year, Price
#>>
#>> Problem: When provided the starting Month/Day numbers, and ending
#>> Month/Day numbers, what is the correct way to SQL the database so
#>> that the recordset created returns as follows (assume 4 years of
#>> data):
#>>
#>> In other words, all the 3/12's first, then 3/13's, then 3/14's, etc.
#>> all the way down to the ending Month/Date.
#>>
#>> Where I really get stuck is when the Starting Month number is greater
#>> than the Ending Month number. For example, say I want the starting
#>> Month/Day as 10/22 and the ending Month/Day as 4/16. Simply stating
#>> WHERE Month >= Start Month AND Month <= End Month doesn't seem
#>> correct. Since I want to return all the prices between 10/22 and 4/16
#>> of each year of data I have in the table, no Month number could be
#>> greater than/equal to 10  and also less than/equal to 4.
#>
#>Try something like this:
#>
#>select Month, Day, Price from mytable
#>where ((Month - :StartMonth)*100 + (Day - :StartDay) + 1300) % 1300 <
#>   ((:EndMonth - :StartMonth)*100 + (:EndDay - :StartDay) + 1300)
#>% 1300
#>order by ((Month - :StartMonth)*100 + (Day - :StartDay) + 1300) % 1300;
#>
#>Igor Tandetnik
#>
#>___
#>sqlite-users mailing list
#>sqlite-users@sqlite.org
#>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Need Help SQL

[Igor Tandetnik]

Rick Ratchford 
> Data Fields:  ID, Date, Month, Day, Year, Price
> 
> Problem: When provided the starting Month/Day numbers, and ending
> Month/Day numbers, what is the correct way to SQL the database so
> that the recordset created returns as follows (assume 4 years of
> data): 
> 
> In other words, all the 3/12's first, then 3/13's, then 3/14's, etc.
> all the way down to the ending Month/Date.
> 
> Where I really get stuck is when the Starting Month number is greater
> than the Ending Month number. For example, say I want the starting
> Month/Day as 10/22 and the ending Month/Day as 4/16. Simply stating
> WHERE Month >= Start Month AND Month <= End Month doesn't seem
> correct. Since I want to return all the prices between 10/22 and 4/16
> of each year of data I have in the table, no Month number could be
> greater than/equal to 10  and also less than/equal to 4.

Try something like this:

select Month, Day, Price from mytable
where ((Month - :StartMonth)*100 + (Day - :StartDay) + 1300) % 1300 <
   ((:EndMonth - :StartMonth)*100 + (:EndDay - :StartDay) + 1300) % 1300
order by ((Month - :StartMonth)*100 + (Day - :StartDay) + 1300) % 1300;

Igor Tandetnik

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

[sqlite] Need Help SQL

[Rick Ratchford]

I'm trying to correctly word an SQL statement.

Data Fields:  ID, Date, Month, Day, Year, Price

Problem: When provided the starting Month/Day numbers, and ending Month/Day
numbers, what is the correct way to SQL the database so that the recordset
created returns as follows (assume 4 years of data):

Example:

Starting Month = 3
Starting Day = 12

Ending Month = 7
Ending Day = 5

The recordset created needs to return the data in these columns:

Month   Day   Price
===
3  12  24.50
3  12  12.34
3  12  33.01
3  12   8.76
3  13  11.72
3  13  77.55
3  13  12.00
.
.
7   5   99.87
7   56.22
7   5   54.61


In other words, all the 3/12's first, then 3/13's, then 3/14's, etc. all the
way down to the ending Month/Date.

Where I really get stuck is when the Starting Month number is greater than
the Ending Month number. For example, say I want the starting Month/Day as
10/22 and the ending Month/Day as 4/16. Simply stating WHERE Month >= Start
Month AND Month <= End Month doesn't seem correct. Since I want to return
all the prices between 10/22 and 4/16 of each year of data I have in the
table, no Month number could be greater than/equal to 10  and also less
than/equal to 4.

I'm still pretty green on working out these SQL statements. I'm hoping some
suggestions will help.

Thanks.

Rick



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

Re: [sqlite] Need help To Get Started with SQLITE

[jack]

Thanks. Obviously it's going to take awhile to remember my C.

Jack

- Original Message - 
From: "Dan Kennedy" <danielk1...@gmail.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Saturday, October 03, 2009 1:05 PM
Subject: Re: [sqlite] Need help To Get Started with SQLITE


> 
> On Oct 4, 2009, at 12:01 AM, jack wrote:
> 
>> I just setting out to learn how to use sqlite3 (3.6.18).  Obviouly  
>> I'm missing some very important points.
>>
>> The very simple test app below is to  open (and create) an sql  
>> datbase then close it
>>
>> Using windows XP. Using a precompiled .LIB. I confirmed the version  
>> number from the command line.
>>
>> It bombs!
>>
>> #include 
>> #include "sqlite3.h"
>> int main()
>> {
>> using namespace std;
>> std::string dbName = "C:\\SQL DATABASES\\first_try.db";
>> const char * c_dbName = dbName.c_str();
>> sqlite3  **db;
>> int  rc;
>>
>> rc = sqlite3_open( "X", db );
> 
> The above causes SQLite to dereference pointer db, which is  
> uninitialized.
> You want something like:
> 
>   sqlite3 *db;
>   sqlite3_open("X", );
>   ...
>   sqlite3_close(db);
> 
> 
> 
> 
>> sqlite3_close( *db );
>> return 0;
>> }
>>
>>
>> I tracked down in sqlite3.c where it bombed.
>>
>> #if !SQLITE_OS_WINCE && !defined(__CYGWIN__)
>>  int nByte;
>>  void *zConverted;
>>  char *zOut;
>>  UNUSED_PARAMETER(nFull);
>>  zConverted = convertUtf8Filename(zRelative);
>>  if( isNT() ){
>>WCHAR *zTemp;
>>nByte = GetFullPathNameW((WCHAR*)zConverted, 0, 0, 0) + 3;
>>zTemp = malloc( nByte*sizeof(zTemp[0]) );
>>if( zTemp==0 ){
>>  free(zConverted);
>>  return SQLITE_NOMEM;   <<<<<*** GOT TO HERE THEN BOMBED
>>}
>>GetFullPathNameW((WCHAR*)zConverted, nByte, zTemp, 0);
>>free(zConverted);
>>zOut = unicodeToUtf8(zTemp);
>>free(zTemp);
>>
>>
>> Jack
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help To Get Started with SQLITE

[Dan Kennedy]

On Oct 4, 2009, at 12:01 AM, jack wrote:

> I just setting out to learn how to use sqlite3 (3.6.18).  Obviouly  
> I'm missing some very important points.
>
> The very simple test app below is to  open (and create) an sql  
> datbase then close it
>
> Using windows XP. Using a precompiled .LIB. I confirmed the version  
> number from the command line.
>
> It bombs!
>
> #include 
> #include "sqlite3.h"
> int main()
> {
> using namespace std;
> std::string dbName = "C:\\SQL DATABASES\\first_try.db";
> const char * c_dbName = dbName.c_str();
> sqlite3  **db;
> int  rc;
>
> rc = sqlite3_open( "X", db );

The above causes SQLite to dereference pointer db, which is  
uninitialized.
You want something like:

   sqlite3 *db;
   sqlite3_open("X", );
   ...
   sqlite3_close(db);




> sqlite3_close( *db );
> return 0;
> }
>
>
> I tracked down in sqlite3.c where it bombed.
>
> #if !SQLITE_OS_WINCE && !defined(__CYGWIN__)
>  int nByte;
>  void *zConverted;
>  char *zOut;
>  UNUSED_PARAMETER(nFull);
>  zConverted = convertUtf8Filename(zRelative);
>  if( isNT() ){
>WCHAR *zTemp;
>nByte = GetFullPathNameW((WCHAR*)zConverted, 0, 0, 0) + 3;
>zTemp = malloc( nByte*sizeof(zTemp[0]) );
>if( zTemp==0 ){
>  free(zConverted);
>  return SQLITE_NOMEM;   <*** GOT TO HERE THEN BOMBED
>}
>GetFullPathNameW((WCHAR*)zConverted, nByte, zTemp, 0);
>free(zConverted);
>zOut = unicodeToUtf8(zTemp);
>free(zTemp);
>
>
> Jack
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] need help, sqlite produces errors

[leife]

at first let me say: this doesn't run on my pc, but on a Popcorn-Hour A110
Mediaserver. That is a problem, because i can't predict the behaviour of
sqlite or php and i can't get real error message (Just one general Message
which occurs for all things).

i have a function that scans a folder which contains musicfiles, takes their
id3-tag and puts them into a sqlite-database. the main folder contains
subfolders which are alpabetically ordered, which contain subfolders
themselves. there should be about 7 oder 8k files in the main order
('Music').
But there occures a strange problem:
as long as i scan every of the alphabetical folders (e.g 'Music/A') there
occurs no error with the script; the database is build correctly, everything
is inserted fine and so on.
but as soon as i try to scan the main-folder, sqlite produces an error. Why
is that ? Can't sqlite managed a couple of thousend entrys? That would be
surprising, as i read from people who had about 100k files in their
sqlite-db.
i'm pretty shure, that this error is created by sqlite and not by the script
(two reasons: works when there are just a couple hundred files and second, i
checked it about a million times). of course i can display the code if
wished.
has anything like that occured to you folks?
This problem is strange, as sqlite builds the database, but then seems not
to finalize it. i can display for example display things using sqlite_query(
$db, SELECT) but when i try to get _all_ files to be written e.g in a
playlist, it is not possible. I can also see the created sqlite-file (called
files.sqlite). unfortunately thanks to the pch a110 i can't run PRAGMA
Integrity Check.
So thats it more or less. if you have any suggestions what it might be, i
would be happy to hear them.

leife
-- 
View this message in context: 
http://www.nabble.com/need-help%2C-sqlite-produces-errors-tp23416858p23416858.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Need help with the SQL statement.

[Joanne Pham]

Thanks a ton Igor!
It worked. Your help is greatly appreciated.
Thanks,
JP





From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org
Sent: Thursday, April 16, 2009 4:52:28 AM
Subject: Re: [sqlite] Need help with the SQL statement.

"Joanne Pham" <joannekp...@yahoo.com>
wrote in message news:348376.69121...@web90302.mail.mud.yahoo.com
> 20657220 is number of minutes in GMT time zone.
> So we need to convert to second by 20657220 *60.
> select datetime(20657220*60, 'unixepoch','localtime' );
> will be 2009-04-11 00:00:00

In this case, this should work:

strftime('%s', date(startTime*60, 'unixepoch', 'localtime'), 'utc')/60

You convert your UTC timestamp to localtime, strip time portion (by way 
of date() function), then convert the result back to UTC (by way of 
strfrime(..., 'utc'). This way you'll get a UTC timestamp that 
corresponds to midnight local time of the same calendar date.

Igor Tandetnik 



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



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

Re: [sqlite] Need help with the SQL statement.

[Igor Tandetnik]

"Joanne Pham" 
wrote in message news:348376.69121...@web90302.mail.mud.yahoo.com
> 20657220 is number of minutes in GMT time zone.
> So we need to convert to second by 20657220 *60.
> select datetime(20657220*60, 'unixepoch','localtime' );
> will be 2009-04-11 00:00:00

In this case, this should work:

strftime('%s', date(startTime*60, 'unixepoch', 'localtime'), 'utc')/60

You convert your UTC timestamp to localtime, strip time portion (by way 
of date() function), then convert the result back to UTC (by way of 
strfrime(..., 'utc'). This way you'll get a UTC timestamp that 
corresponds to midnight local time of the same calendar date.

Igor Tandetnik 



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

Re: [sqlite] Need help with the SQL statement.

[Joanne Pham]

Hi Igor,
20657220  is number of minutes in GMT time zone.
So we need to convert to second by 20657220 *60.
select datetime(20657220*60, 'unixepoch','localtime' ); 
will be 2009-04-11 00:00:00
Thanks for the hlep Igor
JP





From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org
Sent: Wednesday, April 15, 2009 9:17:09 PM
Subject: Re: [sqlite] Need help with the SQL statement.

"Joanne Pham" <joannekp...@yahoo.com>
wrote in message news:872428.4795...@web90308.mail.mud.yahoo.com
> But the first row (20657220 1 2 101 -- this is 2009-04-11 00:00:00)
> may not be there in the dailyDataTable so min(startTime) won't work
> in this case. Any idea Igor?

I don't quite see how 20657220 can represent midnight (of any day) when 
it's not a multiple of 24*60=1440. What epoch are you counting from? 
This:

select datetime(20657220*60, 'unixepoch');

produces 2009-04-11 07:00:00 for me.

Normally, I'd expect something like "startTime / 1440 * 1440" to work 
(this simply rounds down to nearest multiple of 1440). But I guess I 
don't understand your time representation conventions.

Igor Tandetnik 



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



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