Re: [sqlite] creating a table

[Niall O'Reilly]

On 21 Nov 2016, at 21:55, Igor Korot wrote:

> You are of course correct. It does depend on an application.
> However, I tried to explain the SQLite and its paradigm in terms of
> the dBase/FoxPro.

  You were correct also, Igor, and gave good advice.

  Best regards,

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

Re: [sqlite] creating a table

[Igor Korot]

Hi, Niall,

On Mon, Nov 21, 2016 at 12:52 PM, Niall O'Reilly  wrote:
> On 21 Nov 2016, at 17:29, John R. Sowden wrote:
>
>> First of all, I come from the dBASE/Foxpro world. There is no distinction
>> between a table and a database.  I understand that with Sqlite a database
>> includes tables and other items.  The scenario that I do not understand, is:
>> say I have a log file with about 7 fields totaling about 80 characters per
>> record.  How do I name the database and table.  Currently I say log16 for
>> the year 2016.
>
>
>   There's no one true way to do this.
>
>   I've read Igor Korot's reply, and what he suggests may well be what you
> need.

You are of course correct. It does depend on an application.
However, I tried to explain the SQLite and its paradigm in terms of
the dBase/FoxPro.

Thank you.

>
>   In a previous job, I had an application where it made sense to use a
> different
>   database file for each time period, and always to call the table
> 'LOGENTRY'.
>   This was because I had to deal with hundreds of thousands of records a
> day,
>   and seldom had queries whose scope was broader than a single calendar day.
>
>   I hope this helps a little.
>
>   Best regards,
>   Niall O'Reilly
>
> ___
> 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] Table name syntax

[Richard Hipp]

On 11/21/16, Don V Nielsen  wrote:
>> And since the "*" forms are considered bad style
>
> I have done this, not knowing it is bad style. Can you provide some reasons
> why it is bad?

Years later when somebody does "ALTER TABLE ... ADD COLUMN" your
application will begin doing unnecessary work to extract columns that
or not used (best case) or fail completely when it gets back a
different number of columns from what it expected (worst case).

Or, somebody my use the techniques outlined in
https://www.sqlite.org/lang_altertable.html#otheralter to change the
order of the columns in the table, which would definitely break your
application.

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

Re: [sqlite] Table name syntax

[Don V Nielsen]

> And since the "*" forms are considered bad style

I have done this, not knowing it is bad style. Can you provide some reasons
why it is bad? I can assume, "Applications are supposed to be controlled
environments, and using tbl.* introduces uncertainty outside the
applications control." But are there more specific reasons?

Thanks,dvn


On Mon, Nov 21, 2016 at 3:09 PM, Richard Hipp  wrote:

> On 11/21/16, David Raymond  wrote:
> >
> > Following the nice SQL diagrams it looks like in a select you can only
> have
> > * or table-name.*, whereas in other places you can have
> > schema-name.table-name. Granted, the second version can be made prettier
> and
> > more readable, but I would have assumed the first version would be ok. Is
> > this par for all SQL versions?
>
> I don't know what other database engines do, but you are correct that
> SCHEMA.TABLE.* is not allowed in SQLite.  And since the "*" forms are
> considered bad style (to be used only interactively, and not in
> applications) we are not motivated to change it, lest developers be
> tempted to use "*" in their applications.
>
> --
> 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] Table name syntax

[Richard Hipp]

On 11/21/16, David Raymond  wrote:
>
> Following the nice SQL diagrams it looks like in a select you can only have
> * or table-name.*, whereas in other places you can have
> schema-name.table-name. Granted, the second version can be made prettier and
> more readable, but I would have assumed the first version would be ok. Is
> this par for all SQL versions?

I don't know what other database engines do, but you are correct that
SCHEMA.TABLE.* is not allowed in SQLite.  And since the "*" forms are
considered bad style (to be used only interactively, and not in
applications) we are not motivated to change it, lest developers be
tempted to use "*" in their applications.

-- 
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] Table name syntax

[David Raymond]

Basic syntax question on qualified table names in a select. I've got 2 attached 
databases, say db1 and db2, and I try to run...

insert into main.foo
select db1.foo.*
from db1.foo left outer join db2.bar
on db1.foo.pk = db2.bar.pk
where db2.bar.pk is null;

and I get "Error: near "*": syntax error"

If I give db1.foo an alias though it treats it as fine.

insert into main.foo
select aliasName.*
from db1.foo as aliasName left outer join db2.bar
on aliasName.pk = db2.bar.pk
where db2.bar.pk is null;

Following the nice SQL diagrams it looks like in a select you can only have * 
or table-name.*, whereas in other places you can have schema-name.table-name. 
Granted, the second version can be made prettier and more readable, but I would 
have assumed the first version would be ok. Is this par for all SQL versions?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When does SQLite open/create files?

[Richard Hipp]

On 11/21/16, Jens Alfke  wrote:
> Does SQLite ever open or create files while a database connection is already
> open?

(1) When you run ATTACH.

(2) The open/create of the original database is deferred until you
actually need to read or write the database, so the open/create might
occur later than you expect.

(3) Some complex queries involve the use of temporary files, unless
you set PRAGMA temp_store=MEMORY or use equivalent compile-time
options.

-- 
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] When does SQLite open/create files?

[Jens Alfke]

Does SQLite ever open or create files while a database connection is already 
open? Or does that only happen while creating the connection?

(I'm using WAL mode, if it makes a difference.)

--Jens [via iPhone]
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Copying from one table to another

[Vikas Aditya]

Hi Ryan,

Thanks for the transaction suggestion. We will do that.
I provided a simplistic example for constraints, but I think I have an answer 
now.

Thanks,
Vikas


On Nov 21, 2016, at 12:24 PM, R Smith  wrote:

> 
> 
> On 2016/11/21 9:57 PM, Vikas Aditya wrote:
>> Hi everyone,
>> 
>> I have a DB migration question. I have a table called "employees" and it has 
>> a UNIQUE constraint on "employee_email". With some new features being 
>> requested, I need to relax the constraint and have a new constraint on 
>> "employee_email" + "employee_number". Since SQLite ALTER table command can't 
>> modify an existing constraint, I have thought about creating a new table 
>> with the new constraint and then copying everything from old table to new 
>> table and afterwards dumping the old table. It does work but my question is 
>> what is the fastest and most memory efficient way to copy data from old 
>> table to new table?
> 
> Firstly, are you sure about this constraint relaxing? Basically by altering 
> the constraint you are saying (in standard English) that you believe there 
> CAN be an entry where two people with different Employee-numbers could have 
> the same e-mail address, or, put another way, you believe that there could be 
> two different people with different e-mail addresses which could have the 
> same employee number... Which doesn't sound like any company I know of - 
> usually any employee within a company must have both a unique e-mail address 
> and unique employee number. (It might be different but valid for you, I'm 
> just suggesting to double-check the premise for that decision).
> 
> 
>> 
>> Currently we are using:
>> 
>> sql = "INSERT INTO {} SELECT * FROM {}".format(totable, fromtable)
>> cur.execute(sql)
>> 
>> So, a single line statement can copy from old to new. But will this work 
>> fine even if I have 100s of thousands of records in old table? Or will this 
>> load pretty much entire data into memory and then copy to new?
>> Looking for other optimal ways to copy data from one table to another if 
>> this is not optimal.
> 
> Your example would work great - SQLite is very good at doing this and it 
> would use memory correctly.
> It is of course unlikely a relaxed constraint would fail when copying from a 
> table with a more strict set, but it is possible, and it is possible 
> something else might go wrong, so just to be sure, start a transaction before 
> renaming the old table, making the new table and copying the data (you can do 
> all of that in one set of instructions), then commit or rollback based on the 
> result from the cur.execute().
> 
> Good luck!
> Ryan
> 
> 
> ___
> 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] Copying from one table to another

[R Smith]

On 2016/11/21 9:57 PM, Vikas Aditya wrote:

Hi everyone,

I have a DB migration question. I have a table called "employees" and it has a UNIQUE constraint on 
"employee_email". With some new features being requested, I need to relax the constraint and have a new 
constraint on "employee_email" + "employee_number". Since SQLite ALTER table command can't modify 
an existing constraint, I have thought about creating a new table with the new constraint and then copying everything 
from old table to new table and afterwards dumping the old table. It does work but my question is what is the fastest 
and most memory efficient way to copy data from old table to new table?


Firstly, are you sure about this constraint relaxing? Basically by 
altering the constraint you are saying (in standard English) that you 
believe there CAN be an entry where two people with different 
Employee-numbers could have the same e-mail address, or, put another 
way, you believe that there could be two different people with different 
e-mail addresses which could have the same employee number... Which 
doesn't sound like any company I know of - usually any employee within a 
company must have both a unique e-mail address and unique employee 
number. (It might be different but valid for you, I'm just suggesting to 
double-check the premise for that decision).





Currently we are using:

sql = "INSERT INTO {} SELECT * FROM {}".format(totable, fromtable)
cur.execute(sql)

So, a single line statement can copy from old to new. But will this work fine 
even if I have 100s of thousands of records in old table? Or will this load 
pretty much entire data into memory and then copy to new?
Looking for other optimal ways to copy data from one table to another if this 
is not optimal.


Your example would work great - SQLite is very good at doing this and it 
would use memory correctly.
It is of course unlikely a relaxed constraint would fail when copying 
from a table with a more strict set, but it is possible, and it is 
possible something else might go wrong, so just to be sure, start a 
transaction before renaming the old table, making the new table and 
copying the data (you can do all of that in one set of instructions), 
then commit or rollback based on the result from the cur.execute().


Good luck!
Ryan


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

Re: [sqlite] Copying from one table to another

[Vikas Aditya]

Thank You!

Vikas

On Nov 21, 2016, at 12:09 PM, Richard Hipp  wrote:

> On 11/21/16, Vikas Aditya  wrote:
>> 
>> Currently we are using:
>> 
>> sql = "INSERT INTO {} SELECT * FROM {}".format(totable, fromtable)
>> cur.execute(sql)
>> 
>> So, a single line statement can copy from old to new. But will this work
>> fine even if I have 100s of thousands of records in old table? Or will this
>> load pretty much entire data into memory and then copy to new?
> \
> The case above is highly optimized and should work fine for you, with
> minimal memory usage.
> 
> -- 
> 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] Copying from one table to another

[Richard Hipp]

On 11/21/16, Vikas Aditya  wrote:
>
> Currently we are using:
>
> sql = "INSERT INTO {} SELECT * FROM {}".format(totable, fromtable)
> cur.execute(sql)
>
> So, a single line statement can copy from old to new. But will this work
> fine even if I have 100s of thousands of records in old table? Or will this
> load pretty much entire data into memory and then copy to new?
\
The case above is highly optimized and should work fine for you, with
minimal memory usage.

-- 
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] Copying from one table to another

[Vikas Aditya]

Hi everyone,

I have a DB migration question. I have a table called "employees" and it has a 
UNIQUE constraint on "employee_email". With some new features being requested, 
I need to relax the constraint and have a new constraint on "employee_email" + 
"employee_number". Since SQLite ALTER table command can't modify an existing 
constraint, I have thought about creating a new table with the new constraint 
and then copying everything from old table to new table and afterwards dumping 
the old table. It does work but my question is what is the fastest and most 
memory efficient way to copy data from old table to new table?

Currently we are using:

sql = "INSERT INTO {} SELECT * FROM {}".format(totable, fromtable)
cur.execute(sql) 

So, a single line statement can copy from old to new. But will this work fine 
even if I have 100s of thousands of records in old table? Or will this load 
pretty much entire data into memory and then copy to new?
Looking for other optimal ways to copy data from one table to another if this 
is not optimal.

Thanks,
Vikas

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

Re: [sqlite] creating a table

[Jens Alfke]

> On Nov 21, 2016, at 10:08 AM, John R. Sowden  
> wrote:
> 
> Thank you all for your answers and direction for further information.  
> Hopefully, I will not bring these subjects up again. :)

Some of what you’re asking applies to any SQL database. The SQLite docs do 
include a reference to its SQL syntax (since no two databases speak exactly the 
same dialect of SQL) but they're not intended to teach you how to use SQL, or 
relational databases. So if you’re coming from a very different type of 
database like dBase, there’s a lot of basic stuff to [re]learn that the SQLite 
website sort of assumes you already know!

I suggest looking for a book or a tutorial website to learn the basics of 
relational databases and SQL. If creating tables was a roadblock, I guarantee 
you’re going to be perplexed by things like joins. I learned SQLite ten+ years 
ago via an O’Reilly book, which might still be in print. I’m sure there are 
others.

If you use a general relational-database book, be aware that the main 
difference between SQLite and other SQL databases is that it largely ignores 
column types and widths: you can put any type of value into any column (sort of 
like how in Python or JavaScript you can put any type of value into any 
variable), and you don’t need to specify a column width because SQLite can 
store any length of string into a column (again, like strings in most modern 
languages.)

Oh, also, if you don’t already know about it, the `sqlite3` command-line tool 
is invaluable for learning to use SQLite, since it lets you try out commands 
and queries interactively.

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

Re: [sqlite] creating a table

[John R. Sowden]

Thank you all for your answers and direction for further information.  
Hopefully, I will not bring these subjects up again.  :)


John


On 11/21/2016 09:29 AM, John R. Sowden wrote:
First of all, I come from the dBASE/Foxpro world. There is no 
distinction between a table and a database.  I understand that with 
Sqlite a database includes tables and other items.  The scenario that 
I do not understand, is: say I have a log file with about 7 fields 
totaling about 80 characters per record.  How do I name the database 
and table.  Currently I say log16 for the year 2016.


Secondly, I have 2 "front ends" for Sqlite on my Ubuntu 16.04 
computer.  Neither one allows me to set the length of the text fields 
in the table creation process.  How does the Sqlite know how long each 
record should be, same with integers.


No help found in the documentation on the Sqlite web site.

John



___
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] I keep getting seg faults building my database using python sqlite3

[Kevin O'Gorman]

On Mon, Nov 21, 2016 at 9:41 AM, Roger Binns  wrote:

> On 19/11/16 08:08, Kevin O'Gorman wrote:
> > System with problems: Running Xubuntu Linux 16.04.1, Python 3.5.2.
> [...]
> > System without this problem: Running Ubuntu Linux 14.04.5, Python 3.4.3.
>
> You are good on Python versions then.  My remaining recommendation is to
> make the process that does SQLite be a child process (ie no making its
> own children).  That will eliminate an entire class of potential
> problems, although it appears unlikely you are experiencing any of them.
>
> The final option is to run the process under valgrind.  That will
> definitively show the cause.  Do note however that you may want to
> change some of the default options since you have nice big systems.  For
> example I like to set --freelist-vol and related to very big numbers
> (several gigabytes) which ensures that freed memory is not reused for a
> long time.  You could also set the valgrind option so that only one
> thread is allowed - it will catch inadvertent threading you may note be
> aware of.
>
> Roger
>

Thanks for that.  I may do the valgrind thing -- it sounds useful.  But
just to add
to my annoyance about this whole things, I've been having both systems
running
for a couple of days now with no problems or interruptions.  Remember, the
i7 system was failing after 2 hours at most.  I did tweak the code a
little, but
the only thing that seems likely to have stopped the problem is that I put
in
code to do a commit after every 10,000 INSERT statements.  The two systems
are running identical Python code on the same inputs.  I had intended this
to
verify that one fails and the other does not.  What I got is something
different,
but on balance I like it best when my processes do not fail out.  Maybe this
time the code will finish (at this rate it will be at least a week, maybe
three.

-- 
#define QUESTION ((bb) || (!bb)) /* Shakespeare */
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] creating a table

[Niall O'Reilly]

On 21 Nov 2016, at 17:29, John R. Sowden wrote:

First of all, I come from the dBASE/Foxpro world. There is no 
distinction between a table and a database.  I understand that with 
Sqlite a database includes tables and other items.  The scenario that 
I do not understand, is: say I have a log file with about 7 fields 
totaling about 80 characters per record.  How do I name the database 
and table.  Currently I say log16 for the year 2016.


  There's no one true way to do this.

  I've read Igor Korot's reply, and what he suggests may well be what 
you need.


  In a previous job, I had an application where it made sense to use a 
different
  database file for each time period, and always to call the table 
'LOGENTRY'.
  This was because I had to deal with hundreds of thousands of records 
a day,
  and seldom had queries whose scope was broader than a single calendar 
day.


  I hope this helps a little.

  Best regards,
  Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] creating a table

[Igor Korot]

Hi, John,

On Mon, Nov 21, 2016 at 12:29 PM, John R. Sowden
 wrote:
> First of all, I come from the dBASE/Foxpro world. There is no distinction
> between a table and a database.  I understand that with Sqlite a database
> includes tables and other items.  The scenario that I do not understand, is:
> say I have a log file with about 7 fields totaling about 80 characters per
> record.  How do I name the database and table.  Currently I say log16 for
> the year 2016.

If I may:
When you reference the SQLite DB you have a one big database file which contains
all the information: tables, indexes, data, etc.
If you look at it from the dBase/FoxPro POV, the database is the
directory where all you
tables are. However, it is just a 1 file.
A table in the SQLite DB is what it is - a table.

You can call the DB "logs.db" and inside create a table with the name "log16".
Then 1January 2017 will come and you will create a table in that DB
called "log17".

>
> Secondly, I have 2 "front ends" for Sqlite on my Ubuntu 16.04 computer.
> Neither one allows me to set the length of the text fields in the table
> creation process.  How does the Sqlite know how long each record should be,
> same with integers.

SQLite doesn't use that standard.
Try googling for "SQLite create table" and "SQLite affinity".

Hopefully then everything will be clearer.

Thank you.

>
> No help found in the documentation on the Sqlite web site.
>
> John
>
>
>
> ___
> 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] I keep getting seg faults building my database using python sqlite3

[Roger Binns]

On 19/11/16 08:08, Kevin O'Gorman wrote:
> System with problems: Running Xubuntu Linux 16.04.1, Python 3.5.2.
[...]
> System without this problem: Running Ubuntu Linux 14.04.5, Python 3.4.3.

You are good on Python versions then.  My remaining recommendation is to
make the process that does SQLite be a child process (ie no making its
own children).  That will eliminate an entire class of potential
problems, although it appears unlikely you are experiencing any of them.

The final option is to run the process under valgrind.  That will
definitively show the cause.  Do note however that you may want to
change some of the default options since you have nice big systems.  For
example I like to set --freelist-vol and related to very big numbers
(several gigabytes) which ensures that freed memory is not reused for a
long time.  You could also set the valgrind option so that only one
thread is allowed - it will catch inadvertent threading you may note be
aware of.

Roger




signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] creating a table

[Rob Willett]

John,

There is a lot of documentation on the SQLite website.

Here's the 'official' docs on creating a table 
https://www.sqlite.org/lang_createtable.html


A Sqlite database consists of many tables. I am unsure if there is an 
upper limit, if there is, its more tables than I have ever created. You 
probably need to read up a bit more on SQL in general and SQLite in 
particular. Its a great SQL database for many uses, though not for every 
use. Here's a summary https://sqlite.org/whentouse.


SQLite does not set limits on text fields. Thats a great strength (some 
people may disagree), see here https://www.sqlite.org/datatype3.html


You can create a table and specify the text length but its there for 
compatibility and is ignored. You want to drop 20, 200 or 2000 chars in 
your field, go ahead and do it.


SQLite is very flexible and very fast, there's a ton of help and docs 
out there, the support is direct from the people who write it.


Rob


On 21 Nov 2016, at 17:29, John R. Sowden wrote:

First of all, I come from the dBASE/Foxpro world. There is no 
distinction between a table and a database.  I understand that with 
Sqlite a database includes tables and other items.  The scenario that 
I do not understand, is: say I have a log file with about 7 fields 
totaling about 80 characters per record.  How do I name the database 
and table.  Currently I say log16 for the year 2016.


Secondly, I have 2 "front ends" for Sqlite on my Ubuntu 16.04 
computer.  Neither one allows me to set the length of the text fields 
in the table creation process.  How does the Sqlite know how long each 
record should be, same with integers.


No help found in the documentation on the Sqlite web site.

John

___
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] creating a table

[John R. Sowden]

First of all, I come from the dBASE/Foxpro world. There is no 
distinction between a table and a database.  I understand that with 
Sqlite a database includes tables and other items.  The scenario that I 
do not understand, is: say I have a log file with about 7 fields 
totaling about 80 characters per record.  How do I name the database and 
table.  Currently I say log16 for the year 2016.


Secondly, I have 2 "front ends" for Sqlite on my Ubuntu 16.04 computer.  
Neither one allows me to set the length of the text fields in the table 
creation process.  How does the Sqlite know how long each record should 
be, same with integers.


No help found in the documentation on the Sqlite web site.

John



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

Re: [sqlite] SQLite binary with Math Functions for OS-X?

[David Goldwich]

On Mon, Nov 21, 2016 at 1:12 PM, Ronald Gombach  wrote:
>
> Is there a pre-c binary of SQLIte available for down load that includes a 
> math library. I particularly need the “median” function.
>
> If not, can someone point me to instructions on compilation command line to 
> include the math library (OS-X).

Homebrew can do that conveniently:

$ brew update && brew install --with-functions sqlite
$ /usr/local/opt/sqlite/bin/sqlite3
sqlite> select 
load_extension('/usr/local/opt/sqlite/lib/libsqlitefunctions.dylib’);
sqlite> select pi();
pi()
3.14159265358979

The “math library” here being extension-functions.c from
https://www.sqlite.org/contrib/.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Setting up SQLite for VB6

[J Trahair]

Thank you, it's working now.

Regards

Jonathan

On 21/11/2016 09:19, Bart Smissaert wrote:

I think you will need this ODBC driver:

http://www.ch-werner.de/sqliteodbc/

RBS

On Mon, Nov 21, 2016 at 8:12 AM, J Trahair 
wrote:


Hi everyone

I am trying to link a VB6 project to SQLite. (I can do this fine in
VB.Net.) I am using System.Data.SQLite.dll file version 1.0.66.0, and the
following connection properties:

Option Explicit
Public grsUtilities As ADODB.Recordset
Public gconn As ADODB.Connection
Public mstrSQL As String

Private Sub Command1_Click()

 Set gconn = New ADODB.Connection
 gconn.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=C:\My
Documents\Access VideoOCR\My_database.db;Version=3;"
 'or
 'gconn.ConnectionString = "ODBC CONNECT TO [SQLite3
Datasource;Database=C:\My Documents\Access VideoOCR\My_database.db;];"

 gconn.Open

 Set grsUtilities = New ADODB.Recordset
 grsUtilities.CursorLocation = adUseServer
 grsUtilities.Open mstrSQL, gconn, adOpenForwardOnly, adLockReadOnly

End Sub

I have set a reference to Microsoft ActiveX Data Objects 2.8 Library.

The SQLite dll is in the folder C:\My Documents\Access VideoOCR along with
the database.

The error message is: [Microsoft][ODBC Driver manager] Data source name
not found and no default driver specified (for both connection strings).

Please can you let me know what I am missing.

Thank you.
___
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] SQLite binary with Math Functions for OS-X?

[Roman Fleysher]

Can't you count how many rows there are and then sort by the variable of 
interest, limiting output to half the count, all within SQL?

Roman



Sent from my T-Mobile 4G LTE Device


 Original message 
From: Ronald Gombach 
Date: 11/21/16 7:12 AM (GMT-05:00)
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] SQLite binary with Math Functions for OS-X?

Is there a pre-c binary of SQLIte available for down load that includes a math 
library. I particularly need the “median” function.

If not, can someone point me to instructions on compilation command line to 
include the math library (OS-X).

Thanks for any info you can share.

Ron Gombach
ron...@gombach.com 
The Gombach Group
Morrisville, PA
215-295-6555

___
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] ANN: SQLite Maestro 16.11 released

[SQL Maestro Group]

Hi!

SQL Maestro Group announces the release of SQLite Maestro 16.11, a complete 
Windows GUI solution for SQLite database management. The new version is 
immediately available at

http://www.sqlmaestro.com/products/sqlite/maestro/

Top 10 new features
=

1. Support for the FTS5 extension.
2. Support for indexes on expressions.
3. A number of new encryption algorithms in data grids.
4. Custom SQL scripts for secondary database connections.
5. Improved Data Input Forms.
6. Support for Adobe Reader DC in BLOB Editor.
7. Ability to import blank values as empty strings.
8. Default application-level Data Import settings.
9. A number of new SQL Editor options.
10. Some performance and usability improvements.

Full press-release (with explaining screenshots) is available at:
http://www.sqlmaestro.com/news/company/sqlite_maestro_16_11_released/

Background information:

SQL Maestro Group offers complete database admin and management tools for 
MySQL, Oracle, MS SQL Server, PostgreSQL, SQLite, DB2, Firebird, SQL 
Anywhere and MaxDB providing the highest performance, scalability and 
reliability to meet the requirements of today's database applications.


Sincerely yours,
The SQL Maestro Group Team
http://www.sqlmaestro.com 


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

[sqlite] SQLite binary with Math Functions for OS-X?

[Ronald Gombach]

Is there a pre-c binary of SQLIte available for down load that includes a math 
library. I particularly need the “median” function.

If not, can someone point me to instructions on compilation command line to 
include the math library (OS-X).

Thanks for any info you can share.

Ron Gombach
ron...@gombach.com 
The Gombach Group
Morrisville, PA 
215-295-6555

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

Re: [sqlite] Setting up SQLite for VB6

[Bart Smissaert]

I think you will need this ODBC driver:

http://www.ch-werner.de/sqliteodbc/

RBS

On Mon, Nov 21, 2016 at 8:12 AM, J Trahair 
wrote:

> Hi everyone
>
> I am trying to link a VB6 project to SQLite. (I can do this fine in
> VB.Net.) I am using System.Data.SQLite.dll file version 1.0.66.0, and the
> following connection properties:
>
> Option Explicit
> Public grsUtilities As ADODB.Recordset
> Public gconn As ADODB.Connection
> Public mstrSQL As String
>
> Private Sub Command1_Click()
>
> Set gconn = New ADODB.Connection
> gconn.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=C:\My
> Documents\Access VideoOCR\My_database.db;Version=3;"
> 'or
> 'gconn.ConnectionString = "ODBC CONNECT TO [SQLite3
> Datasource;Database=C:\My Documents\Access VideoOCR\My_database.db;];"
>
> gconn.Open
>
> Set grsUtilities = New ADODB.Recordset
> grsUtilities.CursorLocation = adUseServer
> grsUtilities.Open mstrSQL, gconn, adOpenForwardOnly, adLockReadOnly
>
> End Sub
>
> I have set a reference to Microsoft ActiveX Data Objects 2.8 Library.
>
> The SQLite dll is in the folder C:\My Documents\Access VideoOCR along with
> the database.
>
> The error message is: [Microsoft][ODBC Driver manager] Data source name
> not found and no default driver specified (for both connection strings).
>
> Please can you let me know what I am missing.
>
> Thank you.
> ___
> 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] Setting up SQLite for VB6

[J Trahair]

Hi everyone

I am trying to link a VB6 project to SQLite. (I can do this fine in 
VB.Net.) I am using System.Data.SQLite.dll file version 1.0.66.0, and 
the following connection properties:


Option Explicit
Public grsUtilities As ADODB.Recordset
Public gconn As ADODB.Connection
Public mstrSQL As String

Private Sub Command1_Click()

Set gconn = New ADODB.Connection
gconn.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=C:\My 
Documents\Access VideoOCR\My_database.db;Version=3;"

'or
'gconn.ConnectionString = "ODBC CONNECT TO [SQLite3 
Datasource;Database=C:\My Documents\Access VideoOCR\My_database.db;];"


gconn.Open

Set grsUtilities = New ADODB.Recordset
grsUtilities.CursorLocation = adUseServer
grsUtilities.Open mstrSQL, gconn, adOpenForwardOnly, adLockReadOnly

End Sub

I have set a reference to Microsoft ActiveX Data Objects 2.8 Library.

The SQLite dll is in the folder C:\My Documents\Access VideoOCR along 
with the database.


The error message is: [Microsoft][ODBC Driver manager] Data source name 
not found and no default driver specified (for both connection strings).


Please can you let me know what I am missing.

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