[sqlite] sqlite3.dll wrapper to import .csv file

[Rick Guizawa]

Hi All, I am using sqlite3.dll in my c# winform app, I was wondering if
anyone knows how to import .csv file into sqlite db table using c#
sqlite3.dll wrapper function. Thank's for any help I get.
Cheers,
Rick
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] accessing multiple databases

[Marc L. Allen]

Are you sure it's really opening the DB you think it's opening?  I think SQLite 
will create the file if it's not there, and you'll have nothing in it.

Perhaps you should try it and specify the complete path, just in case.  I don't 
know if the PDO object looks for the file before opening it.

Marc

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of jwzumwalt
> Sent: Tuesday, February 28, 2012 5:05 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] accessing multiple databases
> 
> 
> I'm attempting to do a test accessing multiple databases with the code
> listed below.
> It errors on the 2nd db select statement and says that the "table does
> not exist".
> I am using 2 identical db for the test with different names. Any idea
> what I am doing wrong? - Thanks, Jan Zumwalt
> 
>echo '';
>   echo 'Use Multiple Database';
> 
>   $db = new PDO("sqlite:db1.sqlite3"); // db connect
>   $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
> //enhanced error msg
> 
> # query using simple SELECT
>   $result = $db->query("SELECT * FROM 'sales'");
>   $rows   = $result->fetchall(PDO::FETCH_ASSOC);
>   echo ""; print_r($rows); echo "";
> 
> # attach second database
>   $result = $db->query("ATTACH database 'db2.sqlite3' AS lastyear");
> 
> # query 2nd database
>   $result = $db->query('SELECT * FROM lastyear.sales'); // <== ERRORS
>   $rows   = $result->fetchall(PDO::FETCH_ASSOC);
>   echo ""; print_r($rows); echo ""; ?>
> --
> View this message in context: http://old.nabble.com/accessing-multiple-
> databases-tp33410065p33410065.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] accessing multiple databases

[jwzumwalt]

I'm attempting to do a test accessing multiple databases with the code listed
below.
It errors on the 2nd db select statement and says that the "table does not
exist".
I am using 2 identical db for the test with different names. Any idea what I
am
doing wrong? - Thanks, Jan Zumwalt

';
  echo 'Use Multiple Database';
 
  $db = new PDO("sqlite:db1.sqlite3"); // db connect
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //enhanced
error msg
 
# query using simple SELECT
  $result = $db->query("SELECT * FROM 'sales'");
  $rows   = $result->fetchall(PDO::FETCH_ASSOC);
  echo ""; print_r($rows); echo "";

# attach second database
  $result = $db->query("ATTACH database 'db2.sqlite3' AS lastyear");
 
# query 2nd database
  $result = $db->query('SELECT * FROM lastyear.sales'); // <== ERRORS
  $rows   = $result->fetchall(PDO::FETCH_ASSOC);
  echo ""; print_r($rows); echo ""; 
?>
-- 
View this message in context: 
http://old.nabble.com/accessing-multiple-databases-tp33410065p33410065.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] Multiple WHENs in triggers

[Igor Tandetnik]

On 2/28/2012 11:05 AM, Pavel Ivanov wrote:

Or you can also do it like this:

CREATE TRIGGER UpdateSaleItemDescription AFTER INSERT ON SaleItem
BEGIN
UPDATE SaleItem SET SaleItem_Description='This is seven' WHERE
SaleItem_ID=NEW.SaleItem_Id
AND NEW.SaleItem_ID='7';
UPDATE SaleItem SET SaleItem_Description='This is not seven' WHERE
SaleItem_ID=NEW.SaleItem_Id
AND NEW.SaleItem_ID<>'7';
END;



Or like this:

UPDATE SaleItem SET SaleItem_Description=
(case when NEW.SaleItem_ID='7' then 'This is seven' else 'This is not 
seven' end)

WHERE SaleItem_ID=NEW.SaleItem_Id;

--
Igor Tandetnik

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

Re: [sqlite] Multiple WHENs in triggers

[hsymington]

Pavel Ivanov-2 wrote:
> 
>>SQLite allows only one WHEN per trigger but any number of triggers per
>>action (without any defined order of execution). So you can do it like
>>this:
> 

Thanks, Pavel, that's just what I needed to know. I think I'd been got by
other languages only allowing one trigger per type.

Hamish
-- 
View this message in context: 
http://old.nabble.com/Multiple-WHENs-in-triggers-tp33407606p33407758.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] Multiple WHENs in triggers

[Pavel Ivanov]

> I can't
> believe that SQLite only allows only one trigger with only one WHEN per
> action... can it?

SQLite allows only one WHEN per trigger but any number of triggers per
action (without any defined order of execution). So you can do it like
this:

CREATE TRIGGER UpdateSaleItemDescription1 AFTER INSERT ON SaleItem
WHEN NEW.SaleItem_ID='7'
BEGIN
UPDATE SaleItem SET SaleItem_Description='This is seven' WHERE
SaleItem_ID=NEW.SaleItem_Id;
END;

CREATE TRIGGER UpdateSaleItemDescription2 AFTER INSERT ON SaleItem
WHEN NEW.SaleItem_ID<>'7'
BEGIN
UPDATE SaleItem SET SaleItem_Description='This is not seven' WHERE
SaleItem_ID=NEW.SaleItem_Id;
END;


Or you can also do it like this:

CREATE TRIGGER UpdateSaleItemDescription AFTER INSERT ON SaleItem
BEGIN
UPDATE SaleItem SET SaleItem_Description='This is seven' WHERE
SaleItem_ID=NEW.SaleItem_Id
AND NEW.SaleItem_ID='7';
UPDATE SaleItem SET SaleItem_Description='This is not seven' WHERE
SaleItem_ID=NEW.SaleItem_Id
AND NEW.SaleItem_ID<>'7';
END;


Pavel


On Tue, Feb 28, 2012 at 10:53 AM, hsymington  wrote:
>
> Hi all,
>
> I'm trying to put in a series of conditionals into a trigger, along the
> lines of
>
> --
> CREATE TRIGGER UpdateSaleItemDescription AFTER INSERT ON SaleItem
>
> WHEN NEW.SaleItem_ID='7'
> BEGIN
> UPDATE SaleItem SET SaleItem_Description='This is seven' WHERE
> SaleItem_ID=NEW.SaleItem_Id;
> END;
>
> WHEN NEW.SaleItem_ID<>'7'
> BEGIN
> UPDATE SaleItem SET SaleItem_Description='This is not seven' WHERE
> SaleItem_ID=NEW.SaleItem_Id;
> END;
>
>
> INSERT INTO SaleItem(SaleItem_TaxRate) VALUES('0');
> INSERT INTO SaleItem(SaleItem_TaxRate) VALUES('0');
> --
>
> (obviously simplified for ease of description here).
>
> But this isn't working - I get a syntax error near WHEN (the second one). If
> I remove the four lines starting WHEN NEW.SaleItem_ID='8' then it works ok.
>
> Can I use a SELECT CASE statement here? If so, what's the syntax? I can't
> believe that SQLite only allows only one trigger with only one WHEN per
> action... can it?
>
> Thanks in advance,
>
> Hamish
>
> --
> View this message in context: 
> http://old.nabble.com/Multiple-WHENs-in-triggers-tp33407606p33407606.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] Multiple WHENs in triggers

[hsymington]

Hi all,

I'm trying to put in a series of conditionals into a trigger, along the
lines of

--
CREATE TRIGGER UpdateSaleItemDescription AFTER INSERT ON SaleItem

WHEN NEW.SaleItem_ID='7' 
BEGIN
UPDATE SaleItem SET SaleItem_Description='This is seven' WHERE
SaleItem_ID=NEW.SaleItem_Id;
END;

WHEN NEW.SaleItem_ID<>'7' 
BEGIN
UPDATE SaleItem SET SaleItem_Description='This is not seven' WHERE
SaleItem_ID=NEW.SaleItem_Id;
END;


INSERT INTO SaleItem(SaleItem_TaxRate) VALUES('0');
INSERT INTO SaleItem(SaleItem_TaxRate) VALUES('0');
--

(obviously simplified for ease of description here). 

But this isn't working - I get a syntax error near WHEN (the second one). If
I remove the four lines starting WHEN NEW.SaleItem_ID='8' then it works ok. 

Can I use a SELECT CASE statement here? If so, what's the syntax? I can't
believe that SQLite only allows only one trigger with only one WHEN per
action... can it? 

Thanks in advance,

Hamish

-- 
View this message in context: 
http://old.nabble.com/Multiple-WHENs-in-triggers-tp33407606p33407606.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] Is it possible to reduce the number of times sqlite3BTreeMoveToUnpacked is called with an SQLITE C++ application?

[Dan Kennedy]

On 02/28/2012 04:29 PM, Frank Chang wrote:


Good morning, We have a SQLITE C++ application which tries to find the 
intersection between the blobs in separate sqlite database tables(which we call 
subgraphs) ,containing record numbers. When we profile the code below we find 
that the top profiler user is sqlite3BTreeMoveToUnpacked. I have attached the 
profiler outputs whivh we obtained using SQLITE 3.7.10.
 We were wondering if it is possible to reduce the number of times 
sqlite3BTreeMoveToUnpacked is called with an SQLITE C++ application? Thank you.


MoveToUnpacked() is the routine that seeks for a specified
rowid within a b-tree structure. In the code below it will
be called once for each call to sqlite3_blob_open() or
sqlite3_blob_reopen().

Are you working through the blobs in a table in rowid order?
If so, you would be better off with a SELECT statement that
iterates through multiple rows. Otherwise, I think you're stuck
with the seeks, and the overhead they impose.

Dan.






void cIntersectingDedupe::GetSubGraphBlob(sSUBGRAPHINFO *SubGraph_,unsigned 
long *SubGraphBlob_,
int *Size_) {
int Size;



// Grab a BLOB and put it into a unsigned long buffer. As the BLOB contains 
record numbers,
// we will never see a value of zero. Thus, we use 0 to mark the end of the 
array.
//
// Note that we can have BLOBs of size 0, though. If we used DistillSubGraph to
// dedupe the subgraph, some records will be consolidated into others. The
// donor record's BLOB gets zapped because all of it's BLOB was rolled into the
// donee (All your BLOB are belong to us!)

// First time, open the BLOB for real, else we can re-open (faster):
if (SubGraph_->hBlob==0)
sqlite3_blob_open(SubGraph_->Database,"main","AggregatedData","Rows",SubGraph_->IteratorPos+1,0,_->hBlob);
else
sqlite3_blob_reopen(SubGraph_->hBlob,SubGraph_->IteratorPos+1);
Size=sqlite3_blob_bytes(SubGraph_->hBlob)/sizeof(unsigned long);
sqlite3_blob_read(SubGraph_->hBlob,SubGraphBlob_,Size*sizeof(unsigned long),0);
SubGraphBlob_[Size]=0;
if (Size_!=0)
*Size_=Size;
}
void cIntersectingDedupe::IntersectBlobs(sSUBGRAPHINFO *SubGraph_,unsigned long 
*IntersectionBlob_,
unsigned long *SubGraphBlob_) {
int Pos1,Pos2,PosOut;
GetSubGraphBlob(SubGraph_,SubGraphBlob_);
// Perform the intersection. We walk though the two blobs, if the blobs contain 
the same
// value, that value is copied to PosOut in Blob_, else, the blob that is 
'behind' is
// incremented so it can 'catch up' to the other:
Pos1=Pos2=PosOut=0;
while (IntersectionBlob_[Pos1]!=0&&  SubGraphBlob_[Pos2]!=0) {
if (IntersectionBlob_[Pos1]==SubGraphBlob_[Pos2]) {
IntersectionBlob_[PosOut++]=IntersectionBlob_[Pos1++];
Pos2++;
} else if (IntersectionBlob_[Pos1]

[sqlite] ANNOUNCE: Sqlite Root 1.9.7.5 freeware for MS Windows

[Fabio Spadaro]

I am pleased to announce the new version Sqlite Root 1.9.7.5 at
www.sqliteroot.com:


What is Sqlite Root?
=
Sqlite Root is a Gui Admin Tool for manage Sqlite Database

Features:
=
1. Panel that shows in detail the structure of database tables.

2.  Separate panel to indicate the indexes, views, triggers, sql  editor

3.  Backup table

4.  Changing table with insert / edit / delete fields in certain positions
and maintaining the data if possible

5.  Loading two perspectives and can customize the size of the panels

6.  Possibility to minimize the panels

7.  SQL Editors:
a. tips for each character typed
b. Ability to increase the font size

8.  Database design structured like a spreadsheet

9.  Displaying BLOB using external application

10. Form for create trigger,index,view

Mailing list here 

Any feedbak is welcome!

Fabio Spadaro

Try Sqlite Root a GUI Admin Tools for manage Sqlite Database:
www.sqliteroot.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unable to match proper pattern using GLOB keyword

[Richard Hipp]

On Tue, Feb 28, 2012 at 1:47 AM, Jitendra Shrivastava <
jitendra.s...@gmail.com> wrote:

> Hi,
>
> I am using GLOB in Where clause with character '[' like below:
>
> select * from TableName where ColumnName GLOB '*[*';
>
> Table is having data in ColumnName like "ABC[XYZ", but it is not able to
> find match. How to achieve this record using GLOB. For other characters
> this statement is working fine.
>

SELECT * FROM tableName WHERE columnName GLOB '*[[]*';


>
>
> Thanks,
> Jitendra
> ___
> 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

[sqlite] Is it possible to reduce the number of times sqlite3BTreeMoveToUnpacked is called with an SQLITE C++ application?

[Frank Chang]

Good morning, We have a SQLITE C++ application which tries to find the 
intersection between the blobs in separate sqlite database tables(which we call 
subgraphs) ,containing record numbers. When we profile the code below we find 
that the top profiler user is sqlite3BTreeMoveToUnpacked. I have attached the 
profiler outputs whivh we obtained using SQLITE 3.7.10.
We were wondering if it is possible to reduce the number of times 
sqlite3BTreeMoveToUnpacked is called with an SQLITE C++ application? Thank you.
 
 
void cIntersectingDedupe::GetSubGraphBlob(sSUBGRAPHINFO *SubGraph_,unsigned 
long *SubGraphBlob_,
int *Size_) {
int Size;



// Grab a BLOB and put it into a unsigned long buffer. As the BLOB contains 
record numbers,
// we will never see a value of zero. Thus, we use 0 to mark the end of the 
array.
//
// Note that we can have BLOBs of size 0, though. If we used DistillSubGraph to
// dedupe the subgraph, some records will be consolidated into others. The
// donor record's BLOB gets zapped because all of it's BLOB was rolled into the
// donee (All your BLOB are belong to us!)

// First time, open the BLOB for real, else we can re-open (faster):
if (SubGraph_->hBlob==0)
sqlite3_blob_open(SubGraph_->Database,"main","AggregatedData","Rows",SubGraph_->IteratorPos+1,0,_->hBlob);
else
sqlite3_blob_reopen(SubGraph_->hBlob,SubGraph_->IteratorPos+1);
Size=sqlite3_blob_bytes(SubGraph_->hBlob)/sizeof(unsigned long);
sqlite3_blob_read(SubGraph_->hBlob,SubGraphBlob_,Size*sizeof(unsigned long),0);
SubGraphBlob_[Size]=0;
if (Size_!=0)
*Size_=Size;
}
void cIntersectingDedupe::IntersectBlobs(sSUBGRAPHINFO *SubGraph_,unsigned long 
*IntersectionBlob_,
unsigned long *SubGraphBlob_) {
int Pos1,Pos2,PosOut;
GetSubGraphBlob(SubGraph_,SubGraphBlob_);
// Perform the intersection. We walk though the two blobs, if the blobs contain 
the same
// value, that value is copied to PosOut in Blob_, else, the blob that is 
'behind' is
// incremented so it can 'catch up' to the other:
Pos1=Pos2=PosOut=0;
while (IntersectionBlob_[Pos1]!=0 && SubGraphBlob_[Pos2]!=0) {
if (IntersectionBlob_[Pos1]==SubGraphBlob_[Pos2]) {
IntersectionBlob_[PosOut++]=IntersectionBlob_[Pos1++];
Pos2++;
} else if (IntersectionBlob_[Pos1]

[sqlite] Unable to match proper pattern using GLOB keyword

[Jitendra Shrivastava]

Hi,

I am using GLOB in Where clause with character '[' like below:

select * from TableName where ColumnName GLOB '*[*';

Table is having data in ColumnName like "ABC[XYZ", but it is not able to
find match. How to achieve this record using GLOB. For other characters
this statement is working fine.


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

Re: [sqlite] Alter table: number size

[Richard Hipp]

On Tue, Feb 28, 2012 at 7:14 AM, Marco Turco wrote:

> Hi all,
>
> I need to alter a field from smallint(1) to smallint(2),
>

No you don't; not unless your application or the wrapper you are using are
reading the schema or datatypes separately.  SQLite itself makes no
distinction between smallint(1) and smallint(2).  Both operate exactly the
same.


>
> is there to do this using ALTER TABLE ?
>
>
>
> Thanks in advance
>
>
>
> Marco
>
>
>
>
>
> ___
> 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

[sqlite] Alter table: number size

[Marco Turco]

Hi all,

I need to alter a field from smallint(1) to smallint(2),

is there to do this using ALTER TABLE ?

 

Thanks in advance

 

Marco

 

 

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

Re: [sqlite] FTS simple tokenizer

[Matt Young]

Using the _ character to separate words is an informal  language standard,
s in: method_do_this...

On Tue, Feb 28, 2012 at 12:40 AM, Dan Kennedy  wrote:

> On 02/28/2012 12:09 AM, Jos Groot Lipman wrote:
>
>> It was reported before (and not solved)
>> http://www.mail-archive.com/**sqlite-users@sqlite.org/**msg55959.html
>>
>
> The document sources are updated now. So the fix will appear on
> the website next time it is regenerated.
> __**_
> 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] FTS simple tokenizer

[Dan Kennedy]

On 02/28/2012 12:09 AM, Jos Groot Lipman wrote:

It was reported before (and not solved)
http://www.mail-archive.com/sqlite-users@sqlite.org/msg55959.html


The document sources are updated now. So the fix will appear on
the website next time it is regenerated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users