Re: [sqlite] Is there way to modify table structure after initial definition ?

[Larry Brasfield]

Huang Chen Min wrote:

Dear all:
Forgive for asking such stupid question. I use Xbase way to
understand SQLite.
It is allowed to modify dat type of column in clipper. In my personal
thoughts, Creating a brandnew table and copy those data from older version
might be an alternative way. Is there any instrument to meet my need
directly.
Best Regards
--
tom


You need to study the ALTER TABLE statement and its limitations.  See
http://www.sqlite.org/lang_altertable.html .

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

Re: [sqlite] Relative path to sqlite database

[Igor Tandetnik]

Agrawal, Manish  wrote:
> All the sqlite examples I see use absolute paths to the sqlite database in 
> Visual Studio projects, e.g.
> "C:\users\test\apps\db\test.db3". 
> 
> How can I specify the path to the sqlite database relative to the application 
> root folder, e.g. "db\test.db3"?

You use GetModuleFileName Windows API to obtain the location of your EXE, then 
modify this location as necessary to obtain the desired absolute path to the DB 
file.
-- 
Igor Tandetnik

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

Re: [sqlite] SQLite data source in Visual Studio 2010

[Joe Mistachkin]

As of System.Data.SQLite version 1.0.79.0, design-time installer support
is included in the appropriate setup packages.  The instructions on the
blog post you linked to should now be considered obsolete.

NOTE #1: Since Visual Studio itself is a 32-bit process, you will need
to install the 32-bit setup package in order for the design-time
components to install and work properly.

NOTE #2: When installing the setup package, make sure that the option to
install the assemblies into the GAC is checked if you plan on installing
the design-time components.  In the next release, this box will be checked
by default.

--
Joe Mistachkin

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

Re: [sqlite] Relative path to sqlite database

[Agrawal, Manish]

Ah, I should have thought of that.

When I run the application (Ctrl+F5), the path is created in bin/debug. This is 
also the folder where the .exe file is created.

Just a quick note though, when I move the .exe file to the desktop and 
double-click on it, it crashes, which suggests that the bin/debug location is 
only applicable when the application is run from within the IDE.

Thanks
Manish

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Monday, February 20, 2012 9:51 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Relative path to sqlite database


On 21 Feb 2012, at 2:49am, "Agrawal, Manish"  wrote:

> All the sqlite examples I see use absolute paths to the sqlite database in 
> Visual Studio projects, e.g. "C:\users\test\apps\db\test.db3".
> 
> How can I specify the path to the sqlite database relative to the application 
> root folder, e.g. "db\test.db3"?

Have you tried specifying a filename without a path, just to see where the file 
ends up ?

Simon.
___
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] Relative path to sqlite database

[Simon Slavin]

On 21 Feb 2012, at 2:49am, "Agrawal, Manish"  wrote:

> All the sqlite examples I see use absolute paths to the sqlite database in 
> Visual Studio projects, e.g. "C:\users\test\apps\db\test.db3".
> 
> How can I specify the path to the sqlite database relative to the application 
> root folder, e.g. "db\test.db3"?

Have you tried specifying a filename without a path, just to see where the file 
ends up ?

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

Re: [sqlite] SQLite data source in Visual Studio 2010

[Brady Kelly]

On 21 February 2012 04:45, Agrawal, Manish  wrote:

> I am still playing with the sqlite setup in Visual Studio. Previously I
> found success following the instructions at:
>
>
> http://www.tsjensen.com/blog/2011/06/04/SQLite+On+Visual+Studio+2010+Setup+Instructions.aspx
>
>  But I want to create the data source visually and I am not able to bring
> up the sqlite options in the data sources dialogs as I could when I had
> installed the binary.


How serendipitous! I have just joined this list minutes ago, in order to
ask about this very issue.

I first tried just using NuGet, which is supposed to cover everything, and
then I tried the full install package as well, still with no joy. I don't
want to speculate on whether your experience with the setup files will be
different though. I know I could create the connection manually but it not
being available in Server Explorer suggests something is wrong, and I am
not comfortable with that.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Relative path to sqlite database

[Agrawal, Manish]

All the sqlite examples I see use absolute paths to the sqlite database in 
Visual Studio projects, e.g. "C:\users\test\apps\db\test.db3".

How can I specify the path to the sqlite database relative to the application 
root folder, e.g. "db\test.db3"?

This is helpful when I am developing the app on different machines with 
different paths to the application root.

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

[sqlite] SQLite data source in Visual Studio 2010

[Agrawal, Manish]

I am still playing with the sqlite setup in Visual Studio. Previously I found 
success following the instructions at:

http://www.tsjensen.com/blog/2011/06/04/SQLite+On+Visual+Studio+2010+Setup+Instructions.aspx

But this requires the sqlite packages to be "installed" on the local computer. 
Following the recommended procedure, I uninstalled all SQLite installations, 
and copied the dll files to a project folder and added references to the dll 
files:

http://magrawal.myweb.usf.edu/DataSourceProblem.JPG

I am sure that with these references, I could follow the instructions at the 
following site to write the app:

http://sqlite.phxsoftware.com/forums/p/76/266.aspx

But I want to create the data source visually and I am not able to bring up the 
sqlite options in the data sources dialogs as I could when I had installed the 
binary. My questions are:

1. Do I HAVE to use the setup files to "install" sqlite on the developer 
machine to get the data source dialog options?

2. What is the minimal procedure for installation? 

3. Why is the installation of 1.0.66.0 from SourceForge required to get the 
visual functionality? What changed after this version?

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

[sqlite] Is there way to modify table structure after initial definition ?

[黃楨民]

Dear all:
Forgive for asking such stupid question. I use Xbase way to
understand SQLite.
It is allowed to modify dat type of column in clipper. In my personal
thoughts, Creating a brandnew table and copy those data from older version
might be an alternative way. Is there any instrument to meet my need
directly.
Best Regards
-- 
tom
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reference error

[Joe Mistachkin]

David Horne wrote:
>
> Thanks for the help. If you've any other ideas then please let me know.
>

I'm confused, do you actually mean the "System.Data.SQLite.dll" file
(not sqlite.dll)? 

Judging from the error message, it seems you may have an extraneous
install of System.Data.SQLite somewhere.  Alternatively, there may be
a stray managed-only System.Data.SQLite assembly in the GAC that
attempts to load the 32-bit interop DLL.

The error message itself is described here:

http://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki#q12

However, I cannot easily explain it in this case because it appeared
at first glance that you were using the DLL with the right processor
architecture for your operating system.

The only other explanation I can readily think of is that you are
somehow setting a reference to the wrong System.Data.SQLite assembly
in your project.

--
Joe Mistachkin

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

Re: [sqlite] SQLiteDataAdaptor Missing

[Joe Mistachkin]

Electric Eddy wrote:
> 
> This then popped up an error message dialogue box stating:
> 
> Invalid core file image runtime version.
>

You'll want to copy the "Installer.exe" file to your installation directory
first (where the "SQLite.Designer.dll" file resides).

--
Joe Mistachkin

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

Re: [sqlite] why is count of rows in LEFT JOIN more than the rowsinthe left table

[Igor Tandetnik]

Puneet Kishor  wrote:
> is there are way to force the query to return only the "first" match so the 
> resulting query has only as many rows as the left
> table? 

SELECT COUNT(*) FROM (
SELECT 1 FROM a LEFT JOIN b ON a.col = b.col GROUP BY a.rowid);

-- 
Igor Tandetnik

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

Re: [sqlite] why is count of rows in LEFT JOIN more than the rows inthe left table

[Puneet Kishor]

On Feb 20, 2012, at 12:46 PM, Jay A. Kreibich wrote:

> On Mon, Feb 20, 2012 at 05:01:17PM +, Simon Slavin scratched on the wall:
>> 
>> On 20 Feb 2012, at 4:11pm, Puneet Kishor  wrote:
>> 
>>> is there are way to force the query to return only the "first" match
>>> so the resulting query has only as many rows as the left table?
>> 
>> You have two problems:
>> 
>> A) You are JOINing two columns which contain some null values.  null is
>> interpreted as 'I don't know', so the joins tend to match many
>> different rows.
> 
>  No, that's not how it works.  If you have a join condition like
>  (A.col == B.col), and either A.col or B.col (or both) is NULL, then
>  the result of the comparison itself is NULL ("Are these values
>  equal?"  "I don't know.").  JOIN operation will only match rows where
>  the join condition is TRUE.  NULL is not "good enough" to match rows.
> 
>  Think about NATURAL JOINs where the two columns are reduced to only
>  one column.  This reduction can only take place if the values are
>  exactly the same.
> 
>> B) You are using a LEFT JOIN which is explicitly defined to include
>> many matches, not just one match.  Instead you might try NATURAL JOIN
>> or CROSS JOIN to see what difference they make.
> 
>  That's not the issue.  Or rather, it is, but that isn't going to show
>  us anything we don't already know.  NATURAL JOIN should return the
>  exact same results as example #5: 329743  (assuming "col" is the only
>  column name shared between tables), except it will merge the two
>  "col" columns into a single column.
> 
>  A CROSS JOIN has no join condition and is going to return
>  2,066,471,848 rows.  That tells us nothing.
> 
>> And you have a meta-problem too: the dataset you're experimenting with
>> is so big you can't see why it's doing what it's doing.
> 
>  Databases are designed to work with sets.  If you want to debug the
>  issue, you need to think in sets as well.  Use the database to answer
>  your questions, rather than just scanning the results and doing it in
>  your head.
> 
>  The statement...
> 
>SELECT COUNT(*) as ct, col FROM a GROUP BY 2 HAVING ct > 1
> 
>  ...will tell you which rows have duplicate columns.  You can run this
>  on either table.
> 
>  If you want to have unique values in the JOIN, the best approach is
>  to put a unique index across each "col" column, so that the columns
>  have unique values.
> 
>  Failing that, replace the table reference in example #6 with
>  something like "(SELECT * FROM a GROUP BY col) as a".  That's going
>  to give you random rows (there is no concept of a "first" row), but
>  it will get you the row count you're looking for.


Thanks Jay... brilliant explanation. I ended up finding the dupes in table "b" 
and re-encoding the column so it had only unique values. You are right, "first" 
doesn't mean much in this case... I was referring to "the first matching row, 
and to hell with the subsequent matches." Fortunately, the problem was not too 
laborious to correct manually.


> 
>  You might need to do it with both tables, depending on which table(s)
>  has the duplicate "col" values.

Yup, fortunately I know that; it was table "b". Table "a" has geographic 
features, and neither do I want to "create new" nor "lose existing" features. 
Table "b" has attributes to categorize features in table "a". Cleaning up table 
"b" cleared the problem.



--
Puneet Kishor

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

Re: [sqlite] why is count of rows in LEFT JOIN more than the rows inthe left table

[Jay A. Kreibich]

On Mon, Feb 20, 2012 at 05:01:17PM +, Simon Slavin scratched on the wall:
> 
> On 20 Feb 2012, at 4:11pm, Puneet Kishor  wrote:
> 
> > is there are way to force the query to return only the "first" match
> > so the resulting query has only as many rows as the left table?
> 
> You have two problems:
> 
> A) You are JOINing two columns which contain some null values.  null is
> interpreted as 'I don't know', so the joins tend to match many
> different rows.

  No, that's not how it works.  If you have a join condition like
  (A.col == B.col), and either A.col or B.col (or both) is NULL, then
  the result of the comparison itself is NULL ("Are these values
  equal?"  "I don't know.").  JOIN operation will only match rows where
  the join condition is TRUE.  NULL is not "good enough" to match rows.

  Think about NATURAL JOINs where the two columns are reduced to only
  one column.  This reduction can only take place if the values are
  exactly the same.

> B) You are using a LEFT JOIN which is explicitly defined to include
> many matches, not just one match.  Instead you might try NATURAL JOIN
> or CROSS JOIN to see what difference they make.

  That's not the issue.  Or rather, it is, but that isn't going to show
  us anything we don't already know.  NATURAL JOIN should return the
  exact same results as example #5: 329743  (assuming "col" is the only
  column name shared between tables), except it will merge the two
  "col" columns into a single column.

  A CROSS JOIN has no join condition and is going to return
  2,066,471,848 rows.  That tells us nothing.

> And you have a meta-problem too: the dataset you're experimenting with
> is so big you can't see why it's doing what it's doing.

  Databases are designed to work with sets.  If you want to debug the
  issue, you need to think in sets as well.  Use the database to answer
  your questions, rather than just scanning the results and doing it in
  your head.

  The statement...

SELECT COUNT(*) as ct, col FROM a GROUP BY 2 HAVING ct > 1

  ...will tell you which rows have duplicate columns.  You can run this
  on either table.

  If you want to have unique values in the JOIN, the best approach is
  to put a unique index across each "col" column, so that the columns
  have unique values.

  Failing that, replace the table reference in example #6 with
  something like "(SELECT * FROM a GROUP BY col) as a".  That's going
  to give you random rows (there is no concept of a "first" row), but
  it will get you the row count you're looking for.

  You might need to do it with both tables, depending on which table(s)
  has the duplicate "col" values.


   -j

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

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reference error

[Larry Brasfield]

David Horne wrote:

Thanks for the link. I ran the tool on sqlite.dll and got the following
error:

IESHIMS.DLL Error opening file. The system cannot find the file specified
(2).

Warning: At least one delay-load dependency module was not found.
Warning: At least one module has an unresolved import due to a missing
export function in a delay-load dependent module.


That "error" is common and harmless.  Your depends.exe run shows that 
the problem lies elsewhere.  I expect the error message containing "An 
attempt was made to load a program with an incorrect format." is most 
diagnostic here.


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

Re: [sqlite] Reference error

[David Horne]

Thanks for the link. I ran the tool on sqlite.dll and got the following
error:

IESHIMS.DLL Error opening file. The system cannot find the file specified
(2).

Warning: At least one delay-load dependency module was not found.
Warning: At least one module has an unresolved import due to a missing
export function in a delay-load dependent module.

Unfortunately this means nothing to me. I can tell you that there are two
copies of the file on this PC and one of them -
'C:\Windows\winsxs\amd64-microsoft-ie-ieshims...' presumably gets
called on a 64 bit PC.

Thanks for the help. If you've any other ideas then please let me know.


On 20 February 2012 12:24, Joe Mistachkin  wrote:

>
> David Horne wrote:
> >
> > I get the following runtime error -
> >
> > System.InvalidOperationException was unhandled
> >   Message=An error occurred creating the form. See
> Exception.InnerException
> > for details.  The error is:
> >
> > Could not load file or assembly 'System.Data.SQLite, Version=1.0.79.0,
> > Culture=neutral,
> >
> > PublicKeyToken=db937bc2d44ff139' or one of its dependencies. An attempt
> was
> > made to load a program with
> >
> > an incorrect format.
> >   Source=TestApp
> >
> >
> > I used this download - sqlite-netfx40-setup-bundle-x64-2010-1.0.79.0 -
> and
> > installed it without difficulty on a 64 bit Windows 7 PC.
> >
>
> Can you run this tool on the installed System.Data.SQLite.dll and see if it
> complains about any missing dependencies?
>
> http://www.dependencywalker.com/
>
> --
> 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] why is count of rows in LEFT JOIN more than the rows inthe left table

[Simon Slavin]

On 20 Feb 2012, at 4:11pm, Puneet Kishor  wrote:

> is there are way to force the query to return only the "first" match so the 
> resulting query has only as many rows as the left table?

You have two problems:

A) You are JOINing two columns which contain some null values.  null is 
interpreted as 'I don't know', so the joins tend to match many different rows.

B) You are using a LEFT JOIN which is explicitly defined to include many 
matches, not just one match.  Instead you might try NATURAL JOIN or CROSS JOIN 
to see what difference they make.

And you have a meta-problem too: the dataset you're experimenting with is so 
big you can't see why it's doing what it's doing.  It might be useful for you 
to make up a little test database with perhaps 5 rows in each table, just so 
you can look through every row and see what's going on.

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

Re: [sqlite] why is count of rows in LEFT JOIN more than the rows inthe left table

[Puneet Kishor]

On Feb 20, 2012, at 9:34 AM, Igor Tandetnik wrote:

> Puneet Kishor  wrote:
>> But, why is #5 329743 and not 329686? Where are the extra 57 rows coming 
>> from? I thought a LEFT JOIN was supposed to include
>> *all* the rows from the left table with NULLs for the columns of the right 
>> table where there was no match. So, at most, the
>> resulting table would have as many rows as the rows in the left table. 
>> Certainly not more.  
> 
> The conclusion doesn't follow from the premise. Yes, a row from the left 
> table is included in the resultset even if there are no matching rows in the 
> right table. Which means that the resultset would contain *at least* as many 
> rows as there are in the left table - but it certainly may contain more. For 
> each row in the left table that has N matching rows in the right table (with 
> N > 0), there will be N rows in the resultset.

Thanks for the great explanation. Now, the follow up question --

is there are way to force the query to return only the "first" match so the 
resulting query has only as many rows as the left table?



--
Puneet Kishor

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

Re: [sqlite] SQLiteDataAdaptor Missing

[Electric Eddy]

VS is installed on an x64 system so when I build using VS IDE I normally
specify x86.

I don't normally build using MSBuild however, I inserted the line:
x86
in the project file and changed references to Any CPU to x86 and then
rebuilt the installer but get the same error when I run the command.

I tried building it in VS IDE and set the target to Debug x86 but still get
the error.

I've rechecked out the files so it's back to where it was before building.

Is there something specific I need to include on the MSBuild command line?

Patrick


On Mon, Feb 20, 2012 at 3:41 PM, Rob Richardson wrote:

> In another library, I had to specify "x86" processor because it wasn't
> designed for 64-bit machines.  Might you need to do that?
>
> RobR
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Electric Eddy
> Sent: Monday, February 20, 2012 10:26 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLiteDataAdaptor Missing
>
> I performed the following steps:
>
> 1) Checked out the latest local copy using the command 'fossil update'
> 2) I opened a Visual Studio command prompt, changed into the tools
> installer folder and executed the command MSBuild Installer.2008.csproj
> 3) I changed into the root/bin/2008/Debug/bin folder and executed the
> following one line command:
>
> Installer.exe -install true -wow64 true -installFlags All -tracePriority
> Lowest -verbose true -noCompact true -noNetFx40 true -noVs2008 true
> -noVs2010 true -whatIf false -confirm true
>
> This then popped up an error message dialogue box stating:
>
> Invalid core file image runtime version.
>
>
>
>
> On Mon, Feb 20, 2012 at 1:59 PM, Joe Mistachkin  >wrote:
>
> >
> > Electric Eddy wrote:
> > >
> > > Maybe not going as far as including updates in the installer for
> > > support for 2005 but at least a manual process for getting it
> > > working in 2005
> > would
> > > be very helpful.
> > >
> >
> > I've just checked-in preliminary support to the design-time component
> > installer for Visual Studio 2005; however, I currently have no way to
> > test it.
> >
> > If you wish to use and/or test it, here is the command line you will
> > need after building "Installer.exe" (which can be built using MSBuild
> > on the command
> > line):
> >
> >Installer.exe -install true -wow64 true -installFlags All
> >-tracePriority Lowest -verbose true -noCompact true -noNetFx40
> true
> >-noVs2008 true -noVs2010 true -whatIf false -confirm true
> >
> > All of that needs to be on one line.
> >
> > --
> > 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
> ___
> 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] Help request for a query...

[Igor Tandetnik]

Igor Tandetnik  wrote:
> Roger Andersson  wrote:
>> This seems to work but I do not fully understand why ;-)
>> sqlite> .header on
>> sqlite> CREATE TABLE IF NOT EXISTS History (Path TEXT UNIQUE NOT NULL,
>> Hits INTEGER);
>> sqlite> insert or replace into History (Path, Hits) VALUES ('c:\',
>> (select count(*)+ifnull(Hits,1) from History where path='c:\'));
> 
> Since Path is unique, count(*) is either 0 or 1. If it's zero, then there's 
> no corresponding record, so Hits is null, so
> ifnull(Hits, 1) is 1, so the result of subselect is 1. If count(*) is 1, then 
> Hits has a value, and the result of subselect is
> Hits + 1.  
> 
> The same can be achieved in a less convoluted manner:
> 
> insert or replace into History (Path, Hits) VALUES ('c:\',
> (select ifnull(Hits, 0) + 1 from History where path='c:\'));

On second thought, make it

insert or replace into History (Path, Hits) VALUES ('c:\',
ifnull((select Hits from History where path='c:\'), 0) + 1);

I don't think my first version works.
-- 
Igor Tandetnik

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

Re: [sqlite] SQLiteDataAdaptor Missing

[Rob Richardson]

In another library, I had to specify "x86" processor because it wasn't designed 
for 64-bit machines.  Might you need to do that?

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Electric Eddy
Sent: Monday, February 20, 2012 10:26 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLiteDataAdaptor Missing

I performed the following steps:

1) Checked out the latest local copy using the command 'fossil update'
2) I opened a Visual Studio command prompt, changed into the tools installer 
folder and executed the command MSBuild Installer.2008.csproj
3) I changed into the root/bin/2008/Debug/bin folder and executed the following 
one line command:

Installer.exe -install true -wow64 true -installFlags All -tracePriority Lowest 
-verbose true -noCompact true -noNetFx40 true -noVs2008 true
-noVs2010 true -whatIf false -confirm true

This then popped up an error message dialogue box stating:

Invalid core file image runtime version.




On Mon, Feb 20, 2012 at 1:59 PM, Joe Mistachkin wrote:

>
> Electric Eddy wrote:
> >
> > Maybe not going as far as including updates in the installer for 
> > support for 2005 but at least a manual process for getting it 
> > working in 2005
> would
> > be very helpful.
> >
>
> I've just checked-in preliminary support to the design-time component 
> installer for Visual Studio 2005; however, I currently have no way to 
> test it.
>
> If you wish to use and/or test it, here is the command line you will 
> need after building "Installer.exe" (which can be built using MSBuild 
> on the command
> line):
>
>Installer.exe -install true -wow64 true -installFlags All
>-tracePriority Lowest -verbose true -noCompact true -noNetFx40 true
>-noVs2008 true -noVs2010 true -whatIf false -confirm true
>
> All of that needs to be on one line.
>
> --
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] why is count of rows in LEFT JOIN more than the rows inthe left table

[Igor Tandetnik]

Puneet Kishor  wrote:
> But, why is #5 329743 and not 329686? Where are the extra 57 rows coming 
> from? I thought a LEFT JOIN was supposed to include
> *all* the rows from the left table with NULLs for the columns of the right 
> table where there was no match. So, at most, the
> resulting table would have as many rows as the rows in the left table. 
> Certainly not more.  

The conclusion doesn't follow from the premise. Yes, a row from the left table 
is included in the resultset even if there are no matching rows in the right 
table. Which means that the resultset would contain *at least* as many rows as 
there are in the left table - but it certainly may contain more. For each row 
in the left table that has N matching rows in the right table (with N > 0), 
there will be N rows in the resultset.
-- 
Igor Tandetnik

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

Re: [sqlite] SQLiteDataAdaptor Missing

[Electric Eddy]

I performed the following steps:

1) Checked out the latest local copy using the command 'fossil update'
2) I opened a Visual Studio command prompt, changed into the tools
installer folder and executed the command MSBuild Installer.2008.csproj
3) I changed into the root/bin/2008/Debug/bin folder and executed the
following one line command:

Installer.exe -install true -wow64 true -installFlags All -tracePriority
Lowest -verbose true -noCompact true -noNetFx40 true -noVs2008 true
-noVs2010 true -whatIf false -confirm true

This then popped up an error message dialogue box stating:

Invalid core file image runtime version.




On Mon, Feb 20, 2012 at 1:59 PM, Joe Mistachkin wrote:

>
> Electric Eddy wrote:
> >
> > Maybe not going as far as including updates in the installer for support
> > for 2005 but at least a manual process for getting it working in 2005
> would
> > be very helpful.
> >
>
> I've just checked-in preliminary support to the design-time component
> installer
> for Visual Studio 2005; however, I currently have no way to test it.
>
> If you wish to use and/or test it, here is the command line you will need
> after
> building "Installer.exe" (which can be built using MSBuild on the command
> line):
>
>Installer.exe -install true -wow64 true -installFlags All
>-tracePriority Lowest -verbose true -noCompact true -noNetFx40 true
>-noVs2008 true -noVs2010 true -whatIf false -confirm true
>
> All of that needs to be on one line.
>
> --
> 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] why is count of rows in LEFT JOIN more than the rows in the left table

[Dan Kennedy]

On 02/20/2012 10:16 PM, Puneet Kishor wrote:

Could someone please shed some light on this mystery for me?

1.  SELECT Count(*) FROM a;
329686

2.  SELECT Count(*) FROM a WHERE a.col IS NULL;
3

3.  SELECT Count(*) FROM b;
6268

4.  SELECT Count(*) FROM b WHERE b.col IS NULL;
0

5.  SELECT Count(*) FROM a LEFT JOIN b ON a.col = b.col;
329743

6.  SELECT Count(*) FROM a LEFT JOIN b ON a.col = b.col WHERE a.col 
IS NOT NULL;
329740

Of course, given #5, #6 is as expected because of #3.

But, why is #5 329743 and not 329686? Where are the extra 57 rows coming from? 
I thought a LEFT JOIN was supposed to include *all* the rows from the left 
table with NULLs for the columns of the right table where there was no match. 
So, at most, the resulting table would have as many rows as the rows in the 
left table. Certainly not more.

Seems like my understanding is wrong.


Perhaps there exist some a.col values for which there is
more than one row in b where (a.col=b.col).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] why is count of rows in LEFT JOIN more than the rows in the left table

[Puneet Kishor]

Could someone please shed some light on this mystery for me?

1.  SELECT Count(*) FROM a;
329686

2.  SELECT Count(*) FROM a WHERE a.col IS NULL;
3

3.  SELECT Count(*) FROM b;
6268

4.  SELECT Count(*) FROM b WHERE b.col IS NULL;
0

5.  SELECT Count(*) FROM a LEFT JOIN b ON a.col = b.col;
329743

6.  SELECT Count(*) FROM a LEFT JOIN b ON a.col = b.col WHERE a.col 
IS NOT NULL;
329740

Of course, given #5, #6 is as expected because of #3.

But, why is #5 329743 and not 329686? Where are the extra 57 rows coming from? 
I thought a LEFT JOIN was supposed to include *all* the rows from the left 
table with NULLs for the columns of the right table where there was no match. 
So, at most, the resulting table would have as many rows as the rows in the 
left table. Certainly not more.

Seems like my understanding is wrong.


--
Puneet Kishor


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

Re: [sqlite] replacing several nested queries and UNION ALLs with one query

[Gerry Snyder]

a2 != '' seems redundant when a1 = a2 and a1 != ''
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLiteDataAdaptor Missing

[Joe Mistachkin]

Electric Eddy wrote:
> 
> Maybe not going as far as including updates in the installer for support
> for 2005 but at least a manual process for getting it working in 2005
would
> be very helpful.
> 

I've just checked-in preliminary support to the design-time component
installer
for Visual Studio 2005; however, I currently have no way to test it.

If you wish to use and/or test it, here is the command line you will need
after
building "Installer.exe" (which can be built using MSBuild on the command
line):

Installer.exe -install true -wow64 true -installFlags All
-tracePriority Lowest -verbose true -noCompact true -noNetFx40 true
-noVs2008 true -noVs2010 true -whatIf false -confirm true

All of that needs to be on one line.

--
Joe Mistachkin

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

Re: [sqlite] SQLiteDataAdaptor Missing

[Electric Eddy]

Just the SQLiteDataAdapter component to work in 2005. I've no immediate
need to compile SQLite from source as the downloadable Setup for .NET is
enabling me build my projects successfully.  The problem is I initially
used the designer to create a number of SQLiteDataAdapters and related
components and I have modified the database schema and now need to
regenerate the adapters related SLQ commands.   To include the new fields I
simply delete the old SQLiteDataAdapters and drag a new one in from the
Toolbox and use the SQL query builder to create the new SQL commands.  It's
easy enough for me to build these commands programatically however, the
designer was used extensively throughout the project so it would be very
time consuming to remove the designer generated code and replace it with
normal code.

Patrick

On Mon, Feb 20, 2012 at 12:58 PM, Joe Mistachkin wrote:

>
> Electric Eddy wrote:
> >
> > Maybe not going as far as including updates in the installer for support
> > for 2005 but at least a manual process for getting it working in 2005
> would
> > be very helpful.
> >
>
> Out of curiosity, what do you need more, the design-time component
> installer
> to work with Visual Studio 2005 or the actual project files and build
> system
> to work with Visual Studio 2005?
>
> --
> 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] SQLiteDataAdaptor Missing

[Joe Mistachkin]

Electric Eddy wrote:
> 
> Maybe not going as far as including updates in the installer for support
> for 2005 but at least a manual process for getting it working in 2005
would
> be very helpful.
> 

Out of curiosity, what do you need more, the design-time component installer
to work with Visual Studio 2005 or the actual project files and build system
to work with Visual Studio 2005?

--
Joe Mistachkin

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

Re: [sqlite] SQLiteDataAdaptor Missing

[Electric Eddy]

I was just going on the basis of what I paid for the professional version
the last time.  Although 2005 is an old version there are a huge number of
developers maintaining legacy systems on this platform, so I would consider
it worthwhile keeping them in the loop, and would be willing to setup a
remotely accessible VS 2005 environment for someone to explore the issue
further.

Maybe not going as far as including updates in the installer for support
for 2005 but at least a manual process for getting it working in 2005 would
be very helpful.

Patrick.

On Mon, Feb 20, 2012 at 4:38 AM, Joe Mistachkin wrote:

>
> Electric Eddy wrote:
> >
> > That's a pity, my problem is for the type of development I do, spending a
> > few grand on upgrading to 2008 for the sake of the SQLite designer
> > capability would be hard to justify.
> >
>
> Actually, I managed to find Visual Studio 2008 Standard for sale on the
> Sears web site for less than $300.
>
> >
> > My workaround for the moment is I designed it in a trial version of 2008
> > and manually copy the relevant files back to 2005.
> >
>
> The design-time components are the only feature of System.Data.SQLite that
> have to support specific versions of Visual Studio and their use is
> completely
> optional.  Yes, they make it easier to design tables, etc; however,
> everything
> they do can easily be done manually as well.
>
> >
> > I'll probably need to port to MS SQL to allow for future maintenance of
> the
> > legacy application I'm maintaining.
> >
>
> That might be a valid option; however, even if you use the "Express"
> edition,
> which is the only free option, you still have to install it and that can be
> quite challenging.
>
> --
> 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] Reference error

[Joe Mistachkin]

David Horne wrote:
> 
> I get the following runtime error -
> 
> System.InvalidOperationException was unhandled
>   Message=An error occurred creating the form. See
Exception.InnerException
> for details.  The error is:
> 
> Could not load file or assembly 'System.Data.SQLite, Version=1.0.79.0,
> Culture=neutral,
> 
> PublicKeyToken=db937bc2d44ff139' or one of its dependencies. An attempt
was
> made to load a program with
> 
> an incorrect format.
>   Source=TestApp
> 
> 
> I used this download - sqlite-netfx40-setup-bundle-x64-2010-1.0.79.0 - and
> installed it without difficulty on a 64 bit Windows 7 PC.
> 

Can you run this tool on the installed System.Data.SQLite.dll and see if it
complains about any missing dependencies?

http://www.dependencywalker.com/

--
Joe Mistachkin

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

Re: [sqlite] Data base locked.

[Simon Slavin]

On 20 Feb 2012, at 8:09am, bhaskarReddy  wrote:

>   While running Data base with sqlite, i am getting error as Data
> base locked.
> 
> How to resolve from this error.

Don't lock the database.

Bhaskar, have you ever read any of the documentation or is your first thought 
when encountering a problem to post to this mailing list ?

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

Re: [sqlite] sqlite3 interactive shell

[Simon Slavin]

On 20 Feb 2012, at 7:42am, Kf Lee  wrote:

> New to sqlite3.  Install 3.7.10 with --prefix=/usr/local  and source in
> /usr/locsl/sqlite3-src.  Can run sqlite3 test.db when attached to the
> source directory but would get sqlite header and source mismatch if try to
> start sqlite3 from any other directory!  Help appreciated.

There's a very good chance that another copy of sqlite3 comes pre-installed 
with your operating system.  You don't say which OS you're using but you 
mention an installer, and sqlite doesn't use one, so I assume you're getting 
your copy from somewhere other than

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

By the way, the sqlite3 executable program has nothing at all to do with the 
source code.  The executable program is not a library of any kind, it's a 
utility program for messing with databases.  The source code you've downloaded 
doesn't depend on any library files: it has everything it needs in the source.  
The two don't have to be of precisely the same version number.

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

[sqlite] Reference error

[David Horne]

As a new sqlite user, I've written a simple program in VB (Visual Basic
2010 Express), adding a reference to sqlite.dll in the project file. The
following code builds without errors.

Imports System.Data.SQLite

Public Class Form1

Private dbConn As SQLiteConnection

Public Sub New()

' This call is required by the designer.
InitializeComponent()

'configure & open data connection
dbConn = New SQLiteConnection("Data
Source=C:\Users\David\testJnl;Version=3;")

End Sub

I get the following runtime error -

System.InvalidOperationException was unhandled
  Message=An error occurred creating the form. See Exception.InnerException
for details.  The error is:

Could not load file or assembly 'System.Data.SQLite, Version=1.0.79.0,
Culture=neutral,

PublicKeyToken=db937bc2d44ff139' or one of its dependencies. An attempt was
made to load a program with

an incorrect format.
  Source=TestApp


I used this download - sqlite-netfx40-setup-bundle-x64-2010-1.0.79.0 - and
installed it without difficulty on a 64 bit Windows 7 PC.


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

Re: [sqlite] BUG REPORT using refreshschema on SQLiteCommandBuilder

[ITS Smoynihan]

Joe 

Here is an MS link that describes using/manipulating the command texts , but
I had misinterpreted the instructions .

http://msdn.microsoft.com/en-us/library/tf579hcz.aspx

so , you are correct , no bug , but the behaviour with SqlCommandBuilder is
a bit different which does not null the InsertCommand text ( maybe it
should)

many thanks for prompt responses


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin
Sent: 20 February 2012 04:30
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] BUG REPORT using refreshschema on SQLiteCommandBuilder


ITS Smoynihan wrote:
> 
> The MS note in
>
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuil
> der.aspx  would not make sense otherwise, if it is going to null out the
> texts 
> 

That page of documentation is specific to the provider for SQL Server.

None of the DbCommandBuilder derived classes appear, at least publically, to
store any text to be used for later reconstructing the commands disposed by
the RefreshSchema method.  

The RefreshSchema method is implemented by the DbCommandBuilder class itself
(part of the .NET Framework), not System.Data.SQLite and the
DbCommandBuilder
class does not null out any texts because there are none stored to null out.

What problem you are reporting here?  Where does any documentation on the
DbCommandBuilder class or the SQLiteCommandBuilder class directly mention
storing command text (via the public interface)?

--
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

[sqlite] Data base locked.

[bhaskarReddy]

Hi Friends,

   While running Data base with sqlite, i am getting error as Data
base locked.

How to resolve from this error.

Regards,
Bhaskar.
-- 
View this message in context: 
http://old.nabble.com/Data-base-locked.-tp33355225p33355225.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