[sqlite] Run sqlite from a batch file
Hi All, I am trying to execute MS DOS batch file from C# code using
system.diagnostics to run a sequence of sqlite3 commands to output query
results in a '.csv' file.
MyBatch.bat contains...
sqlite3 mydb.db a.txt
echo test c:\test.txt
a.txt contains...
.mode csv
.separator ,
.output c:\out.csv
select * from data limit 10;
.exit
my C# code...
using System.Diagnostics;
void test()
{
Process.Start(@C:\MyBatch.bat);
}
I have found that the above codes did not produce the 'c:\out.csv' at all
but the 'echo test c:\test.txt' command produced the file 'c:\test.txt'
containing test. There was no error message, there was only a command
prompt window flashing for a second. Also, I modified MyBatch.bat to change
the DOS input redirection into 'sqlite3 mydb.db .read a.txt' but still
did not work as expected.
What I tried that seemed to work were...
1. Double-click the unmodified 'c:\MyBatch.bat' file returned the expected
file 'c:\out.csv' containing the query results as well as the
'c:\test.txt'.
2. Using the C# code below:
private void test2()
{
string target = @c:\sqlite3;
string arg1 = @c:\\mydb.db;
string arg2 = .read + @c:\\a.sql;
using (Process proc = new Process())
{
proc.StartInfo.FileName = target;
proc.StartInfo.Arguments = string.Format({0} \{1}\,
arg1, arg2);
proc.StartInfo.RedirectStandardError = true;
proc.StartInfo.RedirectStandardOutput = true;
proc.StartInfo.UseShellExecute = false;
proc.Start();
proc.WaitForExit();
textBox1.Text = proc.StandardError.ReadToEnd();
proc.WaitForExit();
textBox2.Text = proc.StandardOutput.ReadToEnd();
proc.WaitForExit();
}
}
where 'c:\a.sql' contains...
select * from data limit 10;
finally, would fill the textBox2.Text with the query results.
However, I need to be able to execute a batch file from my C# code to run a
series of sqlite3 commands to yield query results in .csv file. So, if
someone from this forum could help me, I would really appreciate it. Thank
you in advance.
Cheers,
Rick
On Thu, Feb 28, 2013 at 1:00 AM, sqlite-users-requ...@sqlite.org wrote:
Send sqlite-users mailing list submissions to
sqlite-users@sqlite.org
To subscribe or unsubscribe via the World Wide Web, visit
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
or, via email, send a message with subject or body 'help' to
sqlite-users-requ...@sqlite.org
You can reach the person managing the list at
sqlite-users-ow...@sqlite.org
When replying, please edit your Subject line so it is more specific
than Re: Contents of sqlite-users digest...
Today's Topics:
1. Re: locked database returning SQLITE_IOERR, not SQLITE_BUSY
(Greg Jan?e)
2. Re: locked database returning SQLITE_IOERR, not SQLITE_BUSY
(Dan Kennedy)
3. Re: locked database returning SQLITE_IOERR, not SQLITE_BUSY
(Greg Jan?e)
4. Re: [SQLite.ADO.Net] Upgrading XP to SQLite version?
(Kevin Benson)
5. Re: locked database returning SQLITE_IOERR, not SQLITE_BUSY
(Simon Slavin)
6. Re: locked database returning SQLITE_IOERR, not SQLITE_BUSY
(Michael Black)
7. Re: locked database returning SQLITE_IOERR, not SQLITE_BUSY
(Dan Kennedy)
8. Re: SQLite equivalent to Oracle's MERGE INTO (anydacdev anydacdev)
9. Re: SQLite equivalent to Oracle's MERGE INTO (Igor Tandetnik)
10. Re: [SQLite.ADO.Net] Upgrading XP to SQLite version?
(Gilles Ganault)
11. Re: [SQLite.ADO.Net] Upgrading XP to SQLite version?
(Kevin Benson)
12. Re: [SQLite.ADO.Net] Upgrading XP to SQLite version?
(Gilles Ganault)
13. Re: [SQLite.ADO.Net] Upgrading XP to SQLite version?
(Kevin Benson)
14. Re: Memory DB - Query does not return all records after
Update (Pavel Ivanov)
15. Re: SQLite equivalent to Oracle's MERGE INTO (Pavel Ivanov)
16. SQLite4 UPDATE performance (Rob Turpin)
17. Re: like query (Dominique Devienne)
18. Re: like query (Clemens Ladisch)
19. Re: like query (Dominique Devienne)
20. Re: SQLite4 UPDATE performance (Richard Hipp)
21. Re: SQLite equivalent to Oracle's MERGE INTO (James K. Lowden)
22. Re: like query (Igor Tandetnik)
23. Re: [SQLite.ADO.Net] Upgrading XP to SQLite version?
(Gilles Ganault)
24. SQLite2010 Pro ODBC Driver (Paul Mathieu)
25. Re: like query (Dominique Devienne)
26. Re: Memory DB - Query does not return all records after
Update (mike.akers)
27. Sqlite on windows ce 5.0 Emulator (Anand Shah)
28. Re: Memory DB - Query does not return all records after
Update (mike.akers)
29. Tool to import tables and its data from sqlserver to sqlite (levi)
30. Re: SQLite4 UPDATE performance (Rob Turpin)
31.
[sqlite] unique combination of concatenated column query
Hi, All. Could you, please, help me with sql query ? For example, if I have a table1 like the following (fields/values separated by commas): component, bin, prd 2.1, 1, 217 6.5, 4, 217 7.1 ,3, 217 7.6 ,5, 217 7.7,5, 217 1.3 ,2, 217 1.1,1, 298 3.1 ,1, 298 6.2 ,2, 298 7.3 ,5, 298 8.1 ,3, 298 8.4 ,4, 298 1.1 ,5, 298 and I want to produce a table2 below: prd ,combo 217,2.1|1.3|7.1|6.5|7.6 217 ,2.1|1.3|7.1|6.5|7.7 298 ,1.1|6.2|8.1|8.4|1.1 298 ,1.1|6.2|8.1|8.4|7.3 298 ,3.1|6.2|8.1|8.4|1.1 298,3.1|6.2|8.1|8.4|7.3 whereas column 'combo' contains all possible combinations of components in table1 written in ascending order of table1 'bin' values. Thank you in advance as your help is very much appreciated. Cheers, ric ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Re; Subrank query
Dear Friends, please help with sqlite query, i have a table like: score| rank | game 98| 1 |1615 98| 1 |1615 92| 2 |1615 87| 3 |1615 87| 3 |1615 87| 3 |1615 112 | 1 |1616 94| 2 |1616 94| 2 |1616 I want to have a query to produce : score | rank | subrank | game 98 | 1 | 1 | 1615 98 |1| 2 | 1615 92 |2| 1 | 1615 87 | 3 | 1 | 1615 87 | 3 | 2 | 1615 87 | 3 | 3 | 1615 112 | 1 |1 | 1616 94| 2 | 1 | 1616 94| 2 |2 | 1616 Thank's in advance for your help. Ricky ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User Defined Function using P/Invoke SQLite wrappers
Hi All, I have an assignment to do statistical processes in C#. I like SQLite stand-alone feature but I need to be able to do the assignment using something like SQL Server stored-procedure. I was wondering if someone could give me a simple straight forward example of creating user defined function in c# - sqlite application. I have collected basic ( probably incomplete) P/Invoke SQLite wrapper functions but I don't know how to include the user defined function in the wrapper. Thank you very much for any help you could give me. Cheers, Rick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Table inserts take a long time to complete.
Hi All, I would very much appreciate if someone could help me speed up my
database table insertion process as what I have in the followings took a
long time to complete even when using sqlite3 shell command.
Thank you in advance for your help.
Cheers,
Rick
CREATE TABLE [BuyPattern] (
[ID] INTEGER NOT NULL,
[Year] INTEGER NOT NULL,
[Item] TEXT NOT NULL,
[Skips] INTEGER NOT NULL,
[Frequency] INTEGER NOT NULL,
[LastBuyDate] DATE NOT NULL,
[NextBuyDate] DATE NOT NULL,
[Match] TEXT NOT NULL
)
CREATE TABLE [Purchases] (
[RecNumber] INTEGER NOT NULL,
[BuyDate] DATE NOT NULL,
[Item] TEXT NOT NULL
)
CREATE INDEX [IDX_Purchases_1] ON [Purchases](
[RecNumber] ASC,
[Item] ASC
)
CREATE INDEX [IDX_BuyPattern_1] ON [SkipnHit](
[Item] ASC,
[Skips] ASC,
[Frequency] ASC,
[NextBuyDate] ASC,
[Hit] ASC,
[Year] ASC,
[LastBuyDate] ASC
)
begin;
insert into BuyPattern select 1 AS 'ID',
2004 AS 'Year',
x.Item AS 'ITEM',
CAST((CAST(strftime('%J', y.BuyDate) AS INTEGER) - CAST(strftime('%J',
x.BuyDate) AS INTEGER))/7 AS INTEGER) AS 'SKIPS',
COUNT(x.Item) AS 'FREQUENCY',
strftime('%Y-%m-%d', (select max(BuyDate) from Purchases where
strftime('%Y', BuyDate) = '2004' and Item = x.Item group by Item)) AS
'LastBuyDate',
strftime('%Y-%m-%d', CAST(strftime('%J', y.BuyDate) AS INTEGER) -
CAST(strftime('%J', x.BuyDate) AS INTEGER) + 1 + CAST(strftime('%J',
(select max(BuyDate) from Item where strftime('%Y', BuyDate) = '2004'
and Item = x.Item group by Item)) AS INTEGER)) AS 'NextBuyDate' ,
Case when (SELECT count(*) from Item where Item = x.Item and
strftime('%Y-%m-%d', BuyDate) = strftime('%Y-%m-%d', CAST(strftime('%J',
y.BuyDate) AS INTEGER) - CAST(strftime('%J', x.BuyDate) AS INTEGER) + 1 +
CAST(strftime('%J', (select max(BuyDate) from Purchases where
strftime('%Y', BuyDate) = '2004' and Item = x.Item group by Item)) AS
INTEGER)))0 THEN 'Match' Else 'Miss' End AS 'HIT/MISS'
FROM Purchases x, Purchases y where x.Item = y.Item and
(CAST(strftime('%J', y.BuyDate) AS INTEGER) - CAST(strftime('%J',
x.BuyDate) AS INTEGER)) 0 and CAST((CAST(strftime('%J', y.BuyDate) AS
INTEGER) - CAST(strftime('%J', x.BuyDate) AS INTEGER))/7 AS INTEGER) =
52and strftime('%Y', x.BuyDate) = '2004' GROUP BY x.Item,
CAST((CAST(strftime('%J', y.BuyDate) AS INTEGER) - CAST(strftime('%J',
x.BuyDate) AS INTEGER))/7 AS INTEGER);
end;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3.dll wrapper to import .csv file
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
[sqlite] Can we access table column using field and index instead of name?
Hi, would it be possible to reference a column in a table using other than its name? For example, if I have : create table X ( a TEXT NOT NULL); would it be possible to access x.a with something similar as x.field[0]? Thank's. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Random Sampling
Hi All, I need help with speeding up my samples collection which is
processed using C# codes and SQLite. I am doing a random sampling
experiment using sqlite to find the most dominant types of lab. findings.
We have about 26 years of lab experiments data provided in a table such as:
CREATE TABLE [Data] (
[ExperimentNumber] INTEGER NOT NULL,
[ExperimentDate] DATE NOT NULL,
[Type1] INTEGER NOT NULL,
[Type2] INTEGER NOT NULL,
[Type3] INTEGER NOT NULL
)
Actually we have 22 'Type' fields but for simplicity, we assume to have
only 3 'Type' fields. Moreover, the types found in each experiment are
separated as well as combined and sorted in the field of CType in table
Types below.
CREATE TABLE [Types] (
[ExperimentNumber] INTEGER NOT NULL,
[ExperimentDate] DATE NOT NULL,
[CType] TEXT NOT NULL
)
For example, if we have in table Data as:
insert into Data values(12, '1989-02-29', 88, 25, 10);
then we have in table Types precisely as
insert into Types values(12. '1989-02-29', 88);
insert into Types values(12. '1989-02-29', 25);
insert into Types values(12. '1989-02-29', 10);
insert into Types values(12. '1989-02-29', 10|25|);
insert into Types values(12. '1989-02-29', 10|88);
insert into Types values(12. '1989-02-29', 25|88);
insert into Types values(12. '1989-02-29', 10|25|88);
To find the most dominant 'Types', sample experiments are selected randomly
from a set of timeframes which is also defined randomly. Most frequent
'Types' found from the selected samples are then recorded and compared to a
randomly selected experiment number whose experiment date is later than any
experiment date of the selected samples. Matched 'Type' are recorded into a
database table for further analysis. So, I created the following tables:
CREATE TABLE [SampleDetects] (
[ExperimentRanges] TEXT NOT NULL,
[ExperimentNumToMatch] TEXT NOT NULL
)
CREATE TABLE [SampleRanges] (
[Samplerangeid] INTEGER NOT NULL,
[start] INTEGER NOT NULL,
[end] INTEGER NOT NULL
)
CREATE TABLE [TopSample] (
[CType] TEXT NOT NULL,
[frequency] INTEGER NOT NULL,
[Samplerangeid] INTEGER NOT NULL
)
CREATE TABLE [TopSampleMatches] (
[matchedCType] TEXT NOT NULL,
[Samplerangeid] INTEGER NOT NULL,
[Experimentno] INTEGER NOT NULL
)
While the sqlite3.dll wrapper functions are not displayed here, but the
following C# code should be comprehensive :
CODE
void Sampling(string database, int samplesize, int MaxTimeframes,
int noofExpToMatch)
{
random = new Random();
Classes.clsSQLite3DLL sqlite3 = new Classes.clsSQLite3DLL();
//sqlite3.dll wrapper
int count = 0; //counter to increment
int minexpno = sqlite3.getMinMaxExpNo(database, Min);
//Obtain min. experiment no.
int maxexpno = sqlite3.getMinMaxExpNo(database, Max);
//...and max. experiment no.
int Samplerangeid = sqlite3.getLastSampleRangeID(database);
//get the last samplerangeid in the database
while (count samplesize)
{
//new cycle initialization
starts---
//the whole experiment population is divided into no of
time frames
int nooftimeframes = random.Next(1, MaxTimeframes);
//define the number of timeframes
//each timeframe has an equal interval
//minus 20 is placed to ensure availability of later
experiments to compare with
int interval = Convert.ToInt32((maxexpno - minexpno - 20) /
nooftimeframes);
int[] start = new int[nooftimeframes]; //to store the
lowest experiment no. in each timeframe
int[] end = new int[nooftimeframes]; //to store the highest
experiment no. in each timeframe
int _minexpno = minexpno; //the lowest experiment number
found in the database
int maxnum = 0; //the maximum exepriment number of all the
randomly selected samples
string Expnumbers = string.Empty; //to store the starts
and the ends of experiment numbers of each intervals.
//This string will be inserted into table
SampleDetects to ensure only unique values collected.
int matchcount = 0; //represents the number of experiments
to compare with for incremental purpose
int ExpNo_tomatch = 0; //to store random experiment number
to compare with
string sqlmid = string.Empty;
string sqlhead = string.Empty;
string sqltail = group by CType order by count(*) desc;;
string sql = string.Empty;
//initialization
ends-
//select range of experiment numbers for each timeframe
for (int j = 0; j nooftimeframes; ++j)
{
start[j] =
[sqlite] SQLite Random number generator
Hi All, how do you generate a random number between two numbers in your query using the random() function? Thank's. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Table insert takes very long time, please help..
Hi all, I have the following tables:
CREATE TABLE [a] (
[ItemType] VARCHAR(10) NOT NULL,
[Item] VARCHAR(60) NOT NULL,
[LastDate] DATE NOT NULL,
[NextDate] DATE NOT NULL,
[Probability] FLOAT NOT NULL,
[Frequency] INTEGER NOT NULL,
[TotalFrequency] INTEGER NOT NULL
)
CREATE TABLE [b] (
[ID] INTEGER NOT NULL,
[TheDate] DATE NOT NULL,
[Item] VARCHAR(35) NOT NULL
)
CREATE TABLE [c] (
[ItemType] VARCHAR(10) NOT NULL,
[Item] VARCHAR(60) NOT NULL,
[SkippedWeeks] INTEGER NOT NULL,
[Frequency] INTEGER NOT NULL
)
My insert is as follow:
INSERT INTO a
SELECT 'Item',
x.Item,
(SELECT strftime('%Y-%m-%d',TheDate) FROM b WHERE TheDate = (SELECT
MAX(TheDate) FROM b WHERE Item = x.Item)),
strftime('%Y-%m-%d', (y.skippedweeks * 7 + (SELECT strftime('%J',
TheDate) from b WHERE TheDate = (SELECT MAX(TheDate) from b WHERE
Item = x.Item,
ROUND((CAST (y.frequency AS REAL)/CAST ((SELECT SUM(Frequency) from c
WHERE item = x.Item) AS REAL) * 100) , 4),
y.frequency,
(SELECT SUM(Frequency) from c WHERE item = x.Item)
FROM b x, c y
WHERE x.Item = y.Item AND
y.Frequency = (SELECT MAX(Frequency) FROM c WHERE item = x.Item) GROUP
BY x.Item;
Using shell sqlite3.exe to process the insert took a long time to
complete. Table b has about 5 rows and table c has about 8
rows of data. How can I speed up my insert? Please, really appreciate
any help on this. Thank you.
Cheers,
Ric
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table insert takes very long time, please help..
edit: I need to mention that BEGIN and COMMIT were already included in
my insert.
On Fri, Dec 30, 2011 at 10:53 PM, Rick Guizawa guizaw...@gmail.com wrote:
Hi all, I have the following tables:
CREATE TABLE [a] (
[ItemType] VARCHAR(10) NOT NULL,
[Item] VARCHAR(60) NOT NULL,
[LastDate] DATE NOT NULL,
[NextDate] DATE NOT NULL,
[Probability] FLOAT NOT NULL,
[Frequency] INTEGER NOT NULL,
[TotalFrequency] INTEGER NOT NULL
)
CREATE TABLE [b] (
[ID] INTEGER NOT NULL,
[TheDate] DATE NOT NULL,
[Item] VARCHAR(35) NOT NULL
)
CREATE TABLE [c] (
[ItemType] VARCHAR(10) NOT NULL,
[Item] VARCHAR(60) NOT NULL,
[SkippedWeeks] INTEGER NOT NULL,
[Frequency] INTEGER NOT NULL
)
My insert is as follow:
INSERT INTO a
SELECT 'Item',
x.Item,
(SELECT strftime('%Y-%m-%d',TheDate) FROM b WHERE TheDate = (SELECT
MAX(TheDate) FROM b WHERE Item = x.Item)),
strftime('%Y-%m-%d', (y.skippedweeks * 7 + (SELECT strftime('%J',
TheDate) from b WHERE TheDate = (SELECT MAX(TheDate) from b WHERE
Item = x.Item,
ROUND((CAST (y.frequency AS REAL)/CAST ((SELECT SUM(Frequency) from c
WHERE item = x.Item) AS REAL) * 100) , 4),
y.frequency,
(SELECT SUM(Frequency) from c WHERE item = x.Item)
FROM b x, c y
WHERE x.Item = y.Item AND
y.Frequency = (SELECT MAX(Frequency) FROM c WHERE item = x.Item) GROUP
BY x.Item;
Using shell sqlite3.exe to process the insert took a long time to
complete. Table b has about 5 rows and table c has about 8
rows of data. How can I speed up my insert? Please, really appreciate
any help on this. Thank you.
Cheers,
Ric
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
