Re: [sqlite] Resources for learning SQLite

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Mike Clark
> Sent: Thursday, March 29, 2018 1:07 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [EXT] [sqlite] Resources for learning SQLite
>
> I suspect there are already threads on this, so apologies for the potential
> duplicate...
>
> I'm a long-time C# developer who has used Sql Server for decades, but I'm
> just getting started with SQLite. Does anyone have any recommendations for
> books or online resources?
>
> I'm particularly interested in resources that use C#.
>
> I've been working with "Using SQLite" by Jay A. Kreibich, and it's very 
> useful,
> but if there's something more advanced I'd love to know about it.
>
> Thanks!
>

Start by downloading the help file from

http://system.data.sqlite.org/

(Don't forget to unblock it) And look at the introduction, especially "Getting 
the best performance out of SQLite."

I use SQLite with C# and get it either with NuGet, or I copy the files myself. 
NuGet is a little easier.

--
Bill Drago
Software Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com



> --
> Mike Clark 
> Twitter: @Cyberherbalist
> Blog: Cyberherbalist's Blog 
> -
> "Free will, though it makes evil possible, is also the only thing that makes
> possible any love or goodness or joy worth having."
> *- C. S. Lewis*
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of 
the intended recipient and may contain material that is proprietary, 
confidential, privileged or otherwise legally protected or restricted under 
applicable government laws. Any review, disclosure, distributing or other use 
without expressed permission of the sender is strictly prohibited. If you are 
not the intended recipient, please contact the sender and delete all copies 
without reading, printing, or saving..

Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of all 
@L-3Com.com email addresses. To ensure delivery of your messages to this 
recipient, please update your records to use william.dr...@l3t.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] String Too Long

[Drago, William @ CSG - NARDA-MITEQ]

> My guess is that it's a limitation of SQLiteCommand a.k.a
> Microsoft.Data.Sqlite .  You might want to ask about the problem in a forum
> on C#, .NET or Microsoft.Data.* .


Or try System.Data.SQLite instead of the Microsoft version. You can download it 
directly or use NuGet.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com
CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of 
the intended recipient and may contain material that is proprietary, 
confidential, privileged or otherwise legally protected or restricted under 
applicable government laws. Any review, disclosure, distributing or other use 
without expressed permission of the sender is strictly prohibited. If you are 
not the intended recipient, please contact the sender and delete all copies 
without reading, printing, or saving..

Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of all 
@L-3Com.com email addresses. To ensure delivery of your messages to this 
recipient, please update your records to use william.dr...@l3t.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Clarification on Storage

[Drago, William @ CSG - NARDA-MITEQ]

> What I'm interested in finding out, without changing my code at this time, is 
> if
> SQLITE3.EXE is handling things differently than what SQLite Expert is.

It absolutely is. I am a heavy user of SQLite Expert and was stumped by weird 
problems in my databases until I realized the SQLite Expert does strange things 
with types behind the scenes. So, I create/manage my databases with SQLite and 
use SQLite Expert only for viewing data.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com
CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of 
the intended recipient and may contain material that is proprietary, 
confidential, privileged or otherwise legally protected or restricted under 
applicable government laws. Any review, disclosure, distributing or other use 
without expressed permission of the sender is strictly prohibited. If you are 
not the intended recipient, please contact the sender and delete all copies 
without reading, printing, or saving..

Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of all 
@L-3Com.com email addresses. To ensure delivery of your messages to this 
recipient, please update your records to use william.dr...@l3t.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Microsoft.Data.SQLite was Vetting SQLite

[Drago, William @ CSG - NARDA-MITEQ]

> Microsoft's own .NET library is Microsoft.Data.SQLite but not all of 
> Microsoft's
> own tools use it since it is part of a long dependency chain which makes
> compiled apps rather large.
>
> Simon.
>

That's very interesting, thanks.

I couldn't find out much about it, though. I can't even tell what version of 
SQLite it uses. Nice to know it exists.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com
CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of 
the intended recipient and may contain material that is proprietary, 
confidential, privileged or otherwise legally protected or restricted under 
applicable government laws. Any review, disclosure, distributing or other use 
without expressed permission of the sender is strictly prohibited. If you are 
not the intended recipient, please contact the sender and delete all copies 
without reading, printing, or saving..

Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of all 
@L-3Com.com email addresses. To ensure delivery of your messages to this 
recipient, please update your records to use william.dr...@l3t.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vetting SQLite

[Drago, William @ CSG - NARDA-MITEQ]

To all that replied, thank you. Open source, not open contribution is a plus, 
so is the wide deployment and well known users (Airbus). There were many other 
good ideas mentioned like examining the source for network calls, etc. All of 
this will help me build a case in favor of SQLite.

No one here is denying the utility and value of open source software. Our IT 
dept. is following corporate mandates designed to protect our networks from 
various threats. It is understandable.

Most of the software we use here, Microsoft and other well-known and paid-for 
products, are validated by corporate before deployment, and there are regular 
scans and updates. When everyone else in the company is using Microsoft SQL 
Server Express and I'm using SQLite instead it raises eyebrows. The last thing 
we need is some rouge engineer (could be me) breaking all our centrifuges with 
"freeware from the internet" when he should have used approved software 
instead. I know SQLite is safe and secure, but the auditors only know what is 
on their lists.

Thanks again for all of your suggestions. I am a regular reader of this group 
because I learn so much.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com
CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of 
the intended recipient and may contain material that is proprietary, 
confidential, privileged or otherwise legally protected or restricted under 
applicable government laws. Any review, disclosure, distributing or other use 
without expressed permission of the sender is strictly prohibited. If you are 
not the intended recipient, please contact the sender and delete all copies 
without reading, printing, or saving..

Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of all 
@L-3Com.com email addresses. To ensure delivery of your messages to this 
recipient, please update your records to use william.dr...@l3t.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Vetting SQLite

[Drago, William @ CSG - NARDA-MITEQ]

All,

I've been using/loving SQLite for years, but the use of open source software is 
highly discouraged where I work, and now I have to prove to our IT dept. that 
SQLite is reliable and secure. The reliable part is easy because there is 
enough information on the SQLite website about testing, but what about 
security? How can I convince the auditors that SQLite is not stealing corporate 
secrets and spreading viruses?

Is there a statement somewhere on the website that guarantees that copies of 
SQLIte downloaded from SQLite.org and System.Data.Sqlite.org are free of all 
forms of spyware/malware/viruses/etc?

Thanks,
--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com

CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of 
the intended recipient and may contain material that is proprietary, 
confidential, privileged or otherwise legally protected or restricted under 
applicable government laws. Any review, disclosure, distributing or other use 
without expressed permission of the sender is strictly prohibited. If you are 
not the intended recipient, please contact the sender and delete all copies 
without reading, printing, or saving..

Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of all 
@L-3Com.com email addresses. To ensure delivery of your messages to this 
recipient, please update your records to use william.dr...@l3t.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Keith Medcalf
> Sent: Wednesday, November 22, 2017 11:33 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Many ML emails going to GMail's SPAM
>
> >> There is nothing wrong with email - but there is an awful lot wrong
> >> with gnail and Google's ideas on how email is done.  (Not to mention
> >> Yahoo, but it seems that MS have the sense to leave the underpinnings
> >> of hotmail as they were.)
>
> >> To put it simply - friends don't let friends use gmail.
>
> >> Cheers,
> >> GaryB-)
>
> >Short of running my own server, what do you recommend? After losing my
> >lifelong email provider (Suffolk.lib.ny.us) and trying a few others, I
> >eventually settled for gmail, which I use with Thunderbird.
> >I don't have any significant issues with that setup.
>
> And that is OK, as long as you realize that it is quite possible for the 
> e-mail
> provider to be the one causing the issues and that they are not inherent in e-
> mail itself.  E-mail adds very little in the way of non-pre-existing 
> conditions
> that did not exist before the advent of e-mail -- the only real difference
> being that one does not need to pay postage to send e-mail.
>
> I run my own e-mail server and have since before there was an "Internet" as
> such and still do.  It has moved locations and data centers many times over
> the last almost four decades but it has always been mine.  I do have trust
> issues with third-parties so perhaps that is part of the reason.  More likely 
> is
> that it was interesting to set up way back in the later part of the 80's and 
> early
> 90's and there was no reason to discontinue using it.  It does cost a few
> dollars a month to maintain the infrastructure and does require care and
> feeding (particularly security feeding), from time to time, however, mostly 
> all
> the running and maintenance is automated, as it should be.  Do something
> manually maybe once or twice.  If it needed doing twice then it should be
> automated so you don't have to do it manually again.  The exceptions
> happen to be major OS or software upgrades which I prefer to do by hand
> since they only occur with relative infrequency and may have/cause "other
> issues".
>
> Periodically I have to re-jig some of the security to counter new tactics
> employed by the variety of miscreants out there in the world, but that does
> not really need to be done that often at this point (which is really 
> interesting
> because as you figure out one method and appropriate countermeasures,
> another technique that was hidden in the noise becomes exposed -- this can
> really be a really entertaining process).  The fact that a few (4 or 5) spam 
> e-
> mail slip through per day, and that the automation tells me that an equal
> number of new miscreants have been banished per day, plus the daily
> automated log audits lets me know that everything is working properly.
>

Thanks for the interesting reply. What you describe is exactly what I envision, 
which is why I never ran my own server (although I have been very tempted). I 
have enough hobbies for now.

-Bill
CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of 
the intended recipient and may contain material that is proprietary, 
confidential, privileged or otherwise legally protected or restricted under 
applicable government laws. Any review, disclosure, distributing or other use 
without expressed permission of the sender is strictly prohibited. If you are 
not the intended recipient, please contact the sender and delete all copies 
without reading, printing, or saving.

Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of all 
@L-3Com.com email addresses. To ensure delivery of your messages to this 
recipient, please update your records to use william.dr...@l3t.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Drago, William @ CSG - NARDA-MITEQ]

> There is nothing wrong with email - but there is an awful lot wrong with gnail
> and Google's ideas on how email is done.  (Not to mention Yahoo, but it
> seems that MS have the sense to leave the underpinnings of hotmail as they
> were.)
>
> To put it simply - friends don't let friends use gmail.
>
> Cheers,
> GaryB-)

Short of running my own server, what do you recommend? After losing my lifelong 
email provider (Suffolk.lib.ny.us) and trying a few others, I eventually 
settled for gmail, which I use with Thunderbird. I don't have any significant 
issues with that setup.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com

CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of 
the intended recipient and may contain material that is proprietary, 
confidential, privileged or otherwise legally protected or restricted under 
applicable government laws. Any review, disclosure, distributing or other use 
without expressed permission of the sender is strictly prohibited. If you are 
not the intended recipient, please contact the sender and delete all copies 
without reading, printing, or saving.

Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of all 
@L-3Com.com email addresses. To ensure delivery of your messages to this 
recipient, please update your records to use william.dr...@l3t.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Drago, William @ CSG - NARDA-MITEQ]

> I really need to come up with an alternative to the mailing list.
> Perhaps some kind of forum system.  Suggestions are welcomed.
> --
> D. Richard Hipp
> d...@sqlite.org

Please, not a forum. The email list is instant, dynamic, and convenient. I 
don't think checking into a forum to stay current with the brisk activity here 
is very practical or appealing.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com
CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of 
the intended recipient and may contain material that is proprietary, 
confidential, privileged or otherwise legally protected or restricted under 
applicable government laws. Any review, disclosure, distributing or other use 
without expressed permission of the sender is strictly prohibited. If you are 
not the intended recipient, please contact the sender and delete all copies 
without reading, printing, or saving.

Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of all 
@L-3Com.com email addresses. To ensure delivery of your messages to this 
recipient, please update your records to use william.dr...@l3t.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best way to develop a GUI front-end

[Drago, William @ CSG - NARDA-MITEQ]

> > This will work with any .NET/Visual Studio language (C#/F#/VB):
>
> Not sure this will work with VBA though.
> VB .NET is very different from VBA (or VB6), so that won't be that easy,
> unless I am missing something.
>
> RBS

It's not that hard to go from VBA to VB .NET. The part I failed to explain is 
that is does require a transition from one to the other.

-Bill
CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of 
the intended recipient and may contain material that is proprietary, 
confidential, privileged or otherwise legally protected or restricted under 
applicable government laws. Any review, disclosure, distributing or other use 
without expressed permission of the sender is strictly prohibited. If you are 
not the intended recipient, please contact the sender and delete all copies 
without reading, printing, or saving.

Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of all 
@L-3Com.com email addresses. To ensure delivery of your messages to this 
recipient, please update your records to use william.dr...@l3t.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best way to develop a GUI front-end

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Bart Smissaert
> Sent: Monday, November 13, 2017 6:52 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Best way to develop a GUI front-end
>
> As you are familiar with VBA I can see two other options:
>
> 1. Use Christian Werner's ODBC driver from Access (or Excel):
> http://www.ch-werner.de/sqliteodbc/
>
> 2. Use Olaf Schmidt's COM dll with Access (or Excel):
> http://www.vbrichclient.com/#/en/About/
>
> RBS

Better yet, use the official SQLite provider for the .NET languages. This will 
work with any .NET/Visual Studio language (C#/F#/VB):

http://system.data.sqlite.org/

Building GUI apps with Visual Studio is very easy, and you can get the 
community edition of Visual Studio for free.

https://www.visualstudio.com/vs/community/

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com
CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of 
the intended recipient and may contain material that is proprietary, 
confidential, privileged or otherwise legally protected or restricted under 
applicable government laws. Any review, disclosure, distributing or other use 
without expressed permission of the sender is strictly prohibited. If you are 
not the intended recipient, please contact the sender and delete all copies 
without reading, printing, or saving.

Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of all 
@L-3Com.com email addresses. To ensure delivery of your messages to this 
recipient, please update your records to use william.dr...@l3t.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Doc typos

[Drago, William @ CSG - NARDA-MITEQ]

The word hold should be holds in the first sentence of section 3. The Rollback 
Journal. In the 3rd sentence give should be given:

http://sqlite.org/fileformat.html

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com

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

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

Re: [sqlite] Could not load file or assembly error

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Paul J. McMillan, Sr.
> Sent: Thursday, June 22, 2017 1:51 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Could not load file or assembly error
>
> Hi,
>
>
>
> I'm new to SQLite.  I want to use it in my C# application.  I downloaded the
> tool SQLite/SQL Server Compact Toolbox.  Under Data Connections, I keep
> getting the error message in red: "Could not load file or assembly
> 'SQLiteScripting".  Does anyone have an idea of why I'm getting this error?
>

Did you add SQLite to your project? The easiest way is with NuGet. If you don't 
need LINQ/EF6 choose System.Data.SQLite Core (x86/x64).

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com


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

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

Re: [sqlite] .DUMP displays floats differently from SELECT

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Simon Slavin
> Sent: Wednesday, May 03, 2017 12:30 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] .DUMP displays floats differently from SELECT
>
>
> On 3 May 2017, at 3:40pm, Scott Robison  wrote:
>
> > On May 3, 2017 8:07 AM, "Tony Papadimitriou"  wrote:
> >
> >> While trying to search/replace some text from an SQLite3 dump I
> >> noticed that, unfortunately, .DUMP does not produce the exact same
> >> numbers as a plain SELECT on the same values.
> >
> > I know all about expected floating point inaccuracies, but I don’t see
> > why it should matter in this case as we have two different places in
> > the same app (SQLite3) where the same number is ‘displayed’ using
> > whatever default format.  IMO, in both places the same number should
> > display exactly the same (i.e., using the same format), both for
> > consistency and easy verification between dump and actual database.
>
> That’s a matter of preference.  There is one possible bug here.  If the 
> results
> of reading the dump file back in produces a different value to the original
> INSERT, that’s a problem.  So I tried it:
>
> SQLite version 3.16.0 2016-11-04 19:09:39 Enter ".help" for usage hints.
> sqlite> create table xxx(x);
> sqlite> insert into xxx values(1.23);
> sqlite> select * from xxx;
> 1.23
> sqlite> .dump
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE xxx(x);
> INSERT INTO "xxx" VALUES(1.23);
> COMMIT;
>
> Hmm.  Cannot reproduce your problem in my version of the command-line
> tool.  You’re using 3.18.0.  I’m using 3.16.0.  Perhaps its a recent change.  
> Or
> perhaps there’s a difference in a library between my platform (macOS) and
> yours.
>
> Simon.

Nor I with 3.14.1 on Windows 7:

SQLite version 3.14.1 2016-08-11 18:53:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table xxx(x);
sqlite> insert into xxx values(1.23);
sqlite> select * from xxx;
1.23
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx(x);
INSERT INTO "xxx" VALUES(1.23);
COMMIT;
sqlite>

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com

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

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

Re: [sqlite] System.data.SQLite documentation

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Mickey Feldman
> Sent: Thursday, February 23, 2017 7:46 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] System.data.SQLite documentation
>
> I am not able to read SQLite.NET.chm, I see only the left hand panel, but not
> the expanded contents.  Is this a known issue, or perhaps an issue with
> Windows 7? Is this documentation available anywhere else or in some other
> format?
>
>

Did you unblock it? Right click on the file, select properties, then check the 
Unblock box.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com

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

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

Re: [sqlite] Problem compiling 3.17.0 in MSVS 12

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Bart Smissaert
> Sent: Tuesday, February 14, 2017 4:24 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Problem compiling 3.17.0 in MSVS 12
>
> I tried compiling from the MSVS IDE with 3.17 as the source on a different
> machine (also Windows 7, same MSVS version) and compiled fine there.
>
> No idea what the problem is.

Maybe the source file is corrupt on the machine with the errors.
--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com


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

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

Re: [sqlite] SQLite3 Pros / Cons

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Clyde Eisenbeis
> Sent: Saturday, February 04, 2017 2:58 PM
> To: SQLite mailing list 
> Subject: [sqlite] SQLite3 Pros / Cons
>
> I'm new to SQLite ... started using it a few months ago.  I was unaware of
> SQLite3 until I joined the SQLite mailing list.
>
> What are the pros / cons of SQLite3?
>
> If I switched from "using System.Data.SQLite" to SQLite3, are all of the
> functions in a .dll I could download and use?

You're probably better off sticking with System.Data.SQLite because it will be 
easier to make your code compatible with other databases. It's also simpler in 
my opinion than using the SQLite3 .dll.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com



> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

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

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

[Drago, William @ CSG - NARDA-MITEQ]

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

Yes, it is.

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

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

Re: [sqlite] problem with sqlite 4

[Drago, William @ CSG - NARDA-MITEQ]

> The current version of SQLite Expert Personal is 4.2.0, available here:
>
> http://www.sqliteexpert.com/download.html
>
> They don’t appear to give any clues as to which version of SQLite the
> application is using.

The SQLite version is displayed just below the ribbon on the right hand side of 
the screen. It is 3.15.2

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

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

Re: [sqlite] Anybody know why mono SQLite uses SqliteConnection but Windows uses SQLiteConnection?

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Jim Henderson
> Sent: Saturday, November 19, 2016 12:16 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Anybody know why mono SQLite uses SqliteConnection
> but Windows uses SQLiteConnection?
>
> Thanks for the suggestion, Bill.
>
> Will this run on a Linux system? The zip file has Install.exe and Test.exe 
> inside
> it.

I've never used the installer or the test program. I don't know what they do. I 
just use the .dll.

I thought Mono was .NET for Linux, so I just assumed that the SQLite 
Precompiled Binaries for Mono was the Linux version of System.Data.SQLite. 
Sorry if I led you in the wrong direction.

Assuming this doesn't work for you, is it possible for you to edit and compile 
the library you are presently using so that it has the correct case?

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Anybody know why mono SQLite uses SqliteConnection but Windows uses SQLiteConnection?

[Drago, William @ CSG - NARDA-MITEQ]

>
> Do any of you know why the two different versions of SQLite have different
> case in the method names?
>
> Thanks,
> Jim
>

Is there a reason why you can't use this one which should have the correct case?

https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki#sqlite-netFx451-binary-Mono-2013

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Drago, William @ CSG - NARDA-MITEQ]

I skimmed through this entire thread and didn't see any mention of 
System.Data.SQLite which is the ADO.NET provider for SQLite and 
written/maintained by the authors of SQLite.

http://system.data.sqlite.org/

Perhaps I missed it or perhaps it's not really what you are looking for. I 
thought I should at least mention it.

I use System.Data.SQLite with VEE and C#, and I use xerial/sqlite-jdbc with 
Java.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of Jim Callahan
> Sent: Thursday, January 07, 2016 7:23 PM
> To: SQLite mailing list 
> Subject: [sqlite] Wish List for 2016: High Level API for Object Oriented
> Interactive Languages
>
> At the command line interface (CLI) in SQLite (and most SQL
> implementations) is an interpreted set at a time language with implicit loops.
>
> Efficient low level languages (such as C) process data a record at a time and
> the existing API is appropriate for them.
>
> Object Oriented Interactive Languages (OOIL ?) can receive a Table, a View or
> a Query all at once as a data set.
> I would count among the OOIL languages: R, Python, Julia Scala,
> MatLab/Octave and APL. In a slightly different category would be Java and C#
> which are object oriented and arguably interpreted, but are not intended to
> be used interactively at a command line with a Read-Evaluate-Print-Loop
> (REPL).
>
> The intent of the higher level API is to improve the reliability of the
> interfaces. The existing SQLite APIs are correct, but hard to use in the sense
> that creating an interface from an OOIL language is more involved than just
> "wrapping" one by one a set of functions. What I am proposing is a second
> set of APIs that when trivially wrapped for use in an OOIL language would
> result in a function that makes sense to an OOIL programmer and interprets
> the SQL statements in a manner consistent with the SQLite CLI (perhaps it
> could even borrow code from the CLI).
>
> I believe R has remarkably good interface packages for SQLite, but that is not
> necessarily the norm across the other OOIL languages.
>
> I am assuming that the higher level API would be hard to use in C because its
> up to the programmer to write the low level code while maintaining a
> complex abstraction in their head (because C is better suited for creating
> abstractions than using them). Header files (.h) would help some but they
> would inflate the size of the code and still be hard for the C programmer to
> keep track of. So, that's why I see the need for a second higher API that
> might be written in C, but would certainly not be used in C!
>
> I am undecided as to whether the higher level API would be useful in Java or
> C#.  Java and C# programmers might not be used to implicit loops and find
> them not worth the trouble; whereas R, Python or Julia programer would
> expect to get an entire table, view or query all at once.
>
> The higher level API would have to be optional, since it would not be
> desirable for a programmer or organization that needs SQLite to run with the
> smallest possible footprint on a phone, tablet or Internet of things
> (IOT) device.
>
> Just a wishlist idea. No rush for me because I am happy in R and will probably
> be moving from SQLite to client server SQL database before I move from R to
> Python, Julia or Java.
>
> Jim Callahan
> Orlando, FL
>
>
>  email?utm_medium=email_source=link_campaign=sig-
> email_content=webmail>
> This
> email has been sent from a virus-free computer protected by Avast.
> www.avast.com
>  email?utm_medium=email_source=link_campaign=sig-
> email_content=webmail>
> <#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

Re: [sqlite] sqlite importing csv

[Drago, William @ CSG - NARDA-MITEQ]

After opening your command prompt type:

cd C:\jt65logger

Then start SQLite and try importing your csv file.

I see JT65 and DXCC, so I know this is ham related. Contact me directly if 
you'd like to discuss the ham aspects of your project.

--
Bill Drago - W2KWD
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Alan
> Sent: Thursday, August 25, 2016 3:40 AM
> To: sqlite users 
> Subject: Re: [sqlite] sqlite importing csv
>
> By the way I have a folder  C:\jt65Logger that contains all the databases etc 
> to
> do with the project.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite in VS2015 Server Explorer (Bugs)

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Stephan Beal
> Sent: Wednesday, August 24, 2016 6:37 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] SQLite in VS2015 Server Explorer (Bugs)
>
> On Tue, Aug 23, 2016 at 4:13 PM, Tyler Merle  wrote:
>
> > I believe I’ve uncovered two bugs when using VS2015’s Server Explorer
> > to design a SQLite schema.
> >
> > 1. When adding more than one relationship to a table, the first
> > relationship’s “to” table name is used for all new relationships 2.
> > The right-click -> design option for tables doesn’t open the table
> > design on the first try. Takes two right-click -> design selections to
> > open the designer
> >
>
> Wrong mailing list. This is the list for the sqlite library and its shell 
> program
> (neither of which include a schema design tool). Server Explorer is a 
> 3rd-party
> product.

This is also the list for System.Data.SQLite. System.Data.SQLite supports 
design time components for VS2015, so this may be the correct list for 
questions regarding issues with using SQLite and Visual Studio's server 
explorer.

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

Re: [sqlite] Bad db feature request

[Drago, William @ CSG - NARDA-MITEQ]

> boun...@mailinglists.sqlite.org] On Behalf Of Dominique Devienne
> Sent: Wednesday, June 29, 2016 10:52 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Bad db feature request
>
> On Wed, Jun 29, 2016 at 4:28 PM, Simon Slavin 
> wrote:
>
> I wish for the day SQLite has page checksums to detect any such random
> corruption.
> Yes, I know, it's a format change, and will likely slow things down a little, 
> but
> it's worth it IMHO.

Aren't there things like that already built in to the hard disk controllers 
(CRC, Reed Solomon, etc.)?

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use "cursors" in c#

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of R Smith
>
> As an aside - I've always prescribed generating your own ID's. There is no
> requirement for using AUTOINCREMENT in any DB - It's a lazy method for
> creating an easy sure-to-be-unique ID list for simple inserted rows, and
> brilliant for stuff like log files or such, but I would make effort to avoid 
> it
> where one can manage the IDs another way. Palming that responsibility off
> to the DB engine is surely easier, but less optimal, if only in more complex
> and foreign key designs.
>

I agree 100%. I always generate my own ID's. I have a generic insert() function 
that returns the last inserted row id. I never do anything with the returned 
value. It's just there.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use "cursors" in c#

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of R Smith
> Sent: Tuesday, June 28, 2016 7:33 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] How to use "cursors" in c#
>
>
>
> On 2016/06/28 12:17 PM, William Drago wrote:
> >
> >> I am programming in c# and I have to insert into a .sqlite file a new
> >> row and get back the id of this last inserted row. This file
> >> "unfortunately"
> >> might be located on an internal network and other users might try to
> >> add other rows in the meantime.
> >
> >
> > I can't help with your locking issues, but if you are using the
> > System.Data.SQLite ADO.NET provider in your C# program you can get the
> > ID of the last row inserted with the LastInsertRowID property of your
> > connection like this:
> >
> >
> > long rowID = connection.LastInsertRowId;
> >
> > I use it after I commit the transaction.
>
> Oh that's dangerous (sometimes) - it is possible for a second
> process/application/DB connector to insert a row in between you closing the
> transaction and reading the rowID so that it is no longer the last row id...
> which is fine if you simply use it to record a relationship to the inserted 
> data
> in another place, but problematic if you use it to guess/force the id of the
> next row to be inserted (probably in some next transaction that might be a
> mile away).
>
> Of course, in 99% of applications (as in applied instances, not programs), 
> this
> is unlikely to ever be a bother, but it's that other 1% that causes 80% of the
> fails in real life[1], so it pays being diligent.
>
> Thus, I'll add my voice to those forewarning Martina - it is best to retrieve 
> the
> next row ID to be inserted (or whatever value/field you use for primary
> keying) within a transaction before the next inserts (which should all happen
> inside that same transaction). That way can never[2] fail.
>
>
> Cheers!
> Ryan
>
>
> [1]: Probably not 80% exactly, this figure is intended to illustrate the 
> principle,
> and doesn't claim to be empirical.
> [2]: Again, "never" is used here approximate to the principle. In real life, 
> bugs
> happen. :)
>
>

Ah, good thoughts. My thinking was that the last row id wouldn't be valid until 
the transaction was successful. But I guess if the transaction is not 
successful it doesn't matter what the last row is because now there are 
different problems to deal with.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Correct, best, or generally accepted database structure for groups of things

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of John McKown
> Sent: Friday, June 17, 2016 9:35 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Correct, best, or generally accepted database structure
> for groups of things
>
> On Fri, Jun 17, 2016 at 1:37 AM, Chris Locke 
> wrote:
>
> > I fail to see what any of this has to do with sqlite.  I thought this
> > was a mailing list for sqlite?  Seeing queries (no pun intended) on
> > sql statements is very subjective, especially with the limited data
> > provided by the original poster.
> > Everyone will give helpful advice, but it won't stop there, and as
> > soon as the original poster has another query (no pun intended) which
> > would result in a schema change, this would have to be explained, etc.
> >
> > A specific group on SQL is required.
> >
>
> ​Perhaps so. But such a group would run into problems because it would be a
> case of "whose SQL?" The four "big" ones that I know of are: SQLite,
> PostgreSQL, Oracle, and IBM's DB2. The basics are the same, but each has
> their own peculiarities. ​I don't know what the intent of this forum really 
> is. It
> is only for SQLite related "perculiarities"? Or does it include something like
> the OP's question which is basically "how do I do a SQLite query to get this
> information?" I don't really know. I also monitor the PostgreSQL forums and
> see this "how do I craft an SQL query to ...?" type question quite often. What
> is weird to me, is that someone will post such a question on the _bugs_
> forums, phrasing it as "I did this SQL query and it didn't do what I expected.
> Please fix your product to make it work." And the reason it didn't work was
> because the SQL query is garbage. Ah, the ever requested "do what I need,
> not what I said" fix.
>

I don't know what the actual rules are for this group either. Almost any 
database related topic seems to be tolerated if not enthusiastically embraced.
Chris Locke has only been active here since May of this year (and maybe he 
won't be back now that his problem is solved), so maybe he's unaware of some of 
(off) topics that have made the rounds. In any case maybe he has a good point 
in keeping the mailing list strictly on topic. I personally don't mind the 
occasional detour into other realms, especially on a low activity group like 
this one, and I usually learn a thing or two along the way as well, but that is 
just my opinion.

-Bill

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Correct, best, or generally accepted database structure for groups of things

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of Chris Locke
> Sent: Friday, June 17, 2016 2:37 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Correct, best, or generally accepted database structure
> for groups of things
>
> I fail to see what any of this has to do with sqlite.  I thought this was a 
> mailing
> list for sqlite?

Sorry for the off topic post. I've been a member of this group for a few years 
and I've seen generic SQL questions posted here from time to time without 
complaints from the mods. I thought it was ok. I do appreciate the value of a 
high signal-to-noise ratio group, so I'll take this discussion elsewhere.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com


CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Correct, best, or generally accepted database structure for groups of things

[Drago, William @ CSG - NARDA-MITEQ]

All,

Say I had a table of apples:

CREATE TABLE Apples (
ID INTEGER PRIMARY KEY,
Color TEXT COLLATE NOCASE, --Could be Red, Green, or Yellow
Height REAL, --Measured in cm
Width REAL --Measured in cm
Weight REAL --Measured in grams
);

And say I had a function that looks at the Apples table and finds groups of 4 
apples that match in color, dimensions, and weight.
Should that function insert its results into a table that looks like the one 
below, or is there a better way?

CREATE TABLE Groups (
ID INTEGER PRIMARY KEY,
AppleID1 INTEGER
AppleID2 INTEGER
AppleID3 INTEGER
AppleID4 INTEGER
);

These apples are part of a communication system and there will always be 
exactly 4 in each group.

My concern is, from the Groups table how to do I find the number of groups of 
red apples, or groups of green apples that weight approx. 80 grams and are 
approx. 10cm tall without including redundant information from the Apples table?

Any advice is appreciated.

Thanks,
--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com


CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble coding conditional UNIQUE

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of James K. Lowden
> Sent: Tuesday, June 14, 2016 8:55 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Trouble coding conditional UNIQUE
>
> On Tue, 14 Jun 2016 16:27:29 +0000
> "Drago, William @ CSG - NARDA-MITEQ" <william.dr...@l-3com.com>
> wrote:
>
> > Once the part has been grouped into a set (Matched=1) it receives a
> > unique permanent serial number and the temporary serial number can be
> > reused, so (Model, TemporarySerialNumber) doesn't have to be unique
> > anymore. Information about what parts belong to what set is stored in
> > a different table.
> >
> > So is using a conditional constraint in this case okay, or is there a
> > better way?
>
> It's OK, Bill; it was OK before I posted.  I'm only suggesting an alternative 
> you
> might like better.
>
> You pasted your particulars into my generic description, and they fit
> perfectly.  You have two sets:
>
> 1.  Parts with a unique, permanent serial number.
> 2.  Parts with a temporary, reusable serial number.
>
> You could remove the latter set to a new table, perhaps "UnmatchedParts",
> having the characteristics you want, namely a primary key or unique
> constraint on (Model, TemporarySerialNumber).  Then you don't need the
> Matched bolean column in either table, solving your original problem (a
> conditional constraint).  Then you have a view,
>
>   create view vParts as
>   select 1 as Matched, * from Parts
>   UNION
>   select 0, * from UnmatchedParts
>   ;
>
> I think there's a natural tendency to put similar things in one table when 
> they
> share common properties, sometimes by adding a discriminator column. I've
> seen lots of tables like that, and designed some myself upon a time.  SQL
> encourages it, because that way you can write just one INSERT, etc., and just
> set the flag right.
>
> When you push hard on such a table, though, by trying to do things right, you
> wind up with little conundrums (conundra?) like the one you posted. They
> grow out of the fact that the things aren't the same.
> They're just a smidgen different, so they need to be tweaked just so, and
> before you know it you either have to lean on some oddball feature of the
> DBMS, or punt.  If you separate them, the you might have more query text,
> but each one will be simpler and easier to understand.
>
> HTH.

This helps a lot. Thank you. I would have never thought of this on my own.
I tried it, it works, and I like it.

Regards,
-Bill


CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble coding conditional UNIQUE

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of James K. Lowden
> Sent: Tuesday, June 14, 2016 9:48 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Trouble coding conditional UNIQUE
>
> On Mon, 13 Jun 2016 19:11:29 +0000
> "Drago, William @ CSG - NARDA-MITEQ" <william.dr...@l-3com.com>
> wrote:
>
> > I need UNIQUE(B, C) only when E=0.
>
> A conditional constraint is evidence that you have two kinds of things
> represented in one table: those E=0 types that are identified by {B,C}, and
> the rest.  They're represented in a single table because they seem to have
> the same columns, although the E=0 types don't need an E column.
>
> A better solution might be to separate the two types into to two tables, each
> with its own constraints, and use a UNION to represent them as one.

I'm grouping parts with temporary, reusable serial numbers into matched sets of 
4.
The temporary serial numbers are stick-on labels with alphanumeric text like 
red5, blu7, grn2.
There are duplicates within this pool and the colors don't mean anything.
Before the parts are matched I can't allow more than one part to have the same 
temporary serial number, so as long as Matched=0 (Model, TemporarySerialNumber) 
must be unique.
Once the part has been grouped into a set (Matched=1) it receives a unique 
permanent serial number and the temporary serial number can be reused, so 
(Model, TemporarySerialNumber) doesn't have to be unique anymore.
Information about what parts belong to what set is stored in a different table.

So is using a conditional constraint in this case okay, or is there a better 
way?

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

Re: [sqlite] Trouble coding conditional UNIQUE

[Drago, William @ CSG - NARDA-MITEQ]

Wow! Thanks for the quick reply and link to documentation. When I searched on 
line for conditional unique constraint I found all sorts of complicated 
examples that sent me off in the wrong direction.

Thank you!
--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of R Smith
> Sent: Monday, June 13, 2016 3:22 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Trouble coding conditional UNIQUE
>
> Apologies Bill, I hit send before linking the actual documentation, here
> goes:
>
> https://www.sqlite.org/partialindex.html
>
> See especially section 2.1
>
>
> Good luck!
> Ryan
>
>
> On 2016/06/13 9:16 PM, R Smith wrote:
> >
> >
> > On 2016/06/13 9:11 PM, Drago, William @ CSG - NARDA-MITEQ wrote:
> >> All,
> >>
> >> I am having trouble figuring out how to implement a conditional
> >> UNIQUE constraint.
> >>
> >> Assume the following table:
> >>
> >> CREATE TABLE myTable (
> >> A INTEGER PRIMARY KEY,
> >> B TEXT NOT NULL COLLATE NOCASE,
> >> C TEXT NOT NULL COLLATE NOCASE,
> >> D TEXT NOT NULL COLLATE NOCASE,
> >> E INTEGER,
> >> F TEXT COLLATE NOCASE,
> >> G TEXT COLLATE NOCASE,
> >> H TEXT COLLATE NOCASE
> >> );
> >>
> >> I need UNIQUE(B, C) only when E=0. I've searched all the usual
> >> places, but I can't find anything close enough to what I'm doing to
> >> be useful.
> >
> > CREATE UNIQUE INDEX Idx_myTable_BC ON myTable (B,C) WHERE E=0;
> >
> >
> >
> > ___
> > 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
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Trouble coding conditional UNIQUE

[Drago, William @ CSG - NARDA-MITEQ]

All,

I am having trouble figuring out how to implement a conditional UNIQUE 
constraint.

Assume the following table:

CREATE TABLE myTable (
A INTEGER PRIMARY KEY,
B TEXT NOT NULL COLLATE NOCASE,
C TEXT NOT NULL COLLATE NOCASE,
D TEXT NOT NULL COLLATE NOCASE,
E INTEGER,
F TEXT COLLATE NOCASE,
G TEXT COLLATE NOCASE,
H TEXT COLLATE NOCASE
);

I need UNIQUE(B, C) only when E=0. I've searched all the usual places, but I 
can't find anything close enough to what I'm doing to be useful.

Thanks,
--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (Solved) How to get 1 row with no null columns

[Drago, William @ CSG - NARDA-MITEQ]

To all who replied, thanks for all the help.

Naming the columns in not a problem. It's easy enough to get the column names 
and build the appropriate select statement.

Thanks again,
--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Scott Robison
> Sent: Wednesday, May 11, 2016 2:17 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] How to get 1 row with no null columns
>
> I also cannot think of a way to do this without naming columns. If this is
> something you have to do frequently from multiple locations, it might be
> worth creating a view that does the hard work in one place.
>
> On Wed, May 11, 2016 at 10:29 AM, Simon Slavin 
> wrote:
>
> >
> > On 11 May 2016, at 11:26am, William Drago  wrote:
> >
> > > Is there a simple way to find a row in a table where none of columns
> > contain a null value? For example:
> > >
> > > SELECT * FROM AnyTable WHERE (all columns IS NOT NULL) LIMIT 1;
> >
> > The coalesce(a,b,c, ...) function returns the first of its arguments
> > which isn't NULL.  If they're all NULL it returns NULL.  So it sort-of
> > does what you want, but you will have to list the column names.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Scott Robison
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Fastest way to backup/copy database?

[Drago, William @ CSG - NARDA-MITEQ]

Rob,

I have a continuously running application that simply writes the same data to 
two different databases; one in the primary location and the other in the 
backup location. This is obviously not a perfect solution (a man with two 
watches never really knows what time it is) but it's good enough for us and is 
simple.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com

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

[sqlite] How to register System.Data.SQLite as a data source?

[Drago, William @ CSG - NARDA-MITEQ]

> Drago, William wrote:
> >
> > I always reference System.Data.SQLite directly and have no idea how to
> > register it as a data source and what the implications of doing so are
> > (will it break applications that reference the .NET assy directly)?
> > Can anyone advise?
> >
>
> A data source for what?  The .NET Framework itself?  Visual Studio?
> The tool used by the official setup packages for this purpose is
> here:
>
>   http://urn.to/r/r
>
> --
> Joe Mistachkin

Sorry for the poorly worded question. My follow-up might be just as bad; 
apologies in advance.

I did look at Installer.cs, but I'm not knowledgeable enough to understand what 
it's doing. I noticed "what-if" mode and maybe now I'm not so averse to running 
the setups.

Back to my question... Some Windows applications claim to work with any 
database as long as you choose the appropriate "provider" from the drop down 
list. On my computer the providers listed are:

Odbc Data Provider
OleDb Data Provider
OracleClient Data Provider
SqlClient Data Provider
Microsoft SQL Server Compact Data Provider 4.0
Microsoft SQL Server Compact Data Provider

How do I get SQLite/System.Data.SQLite to appear in that list?

Thank you,
-Bill Drago


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

[sqlite] How to register System.Data.SQLite as a data source?

[Drago, William @ CSG - NARDA-MITEQ]

All,

I always reference System.Data.SQLite directly and have no idea how to register 
it as a data source and what the implications of doing so are (will it break 
applications that reference the .NET assy directly)? Can anyone advise?

Thanks,
--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com

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

[sqlite] CTE for a noob

[Drago, William @ CSG - NARDA-MITEQ]

Have you seen this tutorial?

https://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Stephen Chrzanowski
> Sent: Monday, March 07, 2016 9:47 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] CTE for a noob
>
> I'd like to learn how to use CTEs by example when working on my own
> project, by mutating it from individual calls into one clean call.  I looked 
> at the
> page (As I said in my previous email/note/post(?)) and just thought I'd put
> this question out there.
>
> Given I have the schema posted here: http://pastebin.com/hA6weV4n
>
> Currently my application makes two queries to the database.  One to get the
> list of projects via [ select ProjectID,Description from Projects order by
> Description ]
>
> It then goes through each record retrieved and then gets another query via [
> select (select count(VideoID) from vViewedVideos where DateViewed is null
> and ProjectID=:P) Unwatched, (select count(VideoID) from vViewedVideos
> where ProjectID=:P) Videos ] where :P is the ProjectID.
>
> I then take the results of ProjectID, Description, Unwatched and Watched
> and format a string to put it into a listbox on the form.
>
> I want to change the order in which this listbox is populated based on a 
> single
> query and the final sort order of [ order by Unwatched=0, Unwatched ] so
> that anything that has no unwatched videos available are at the bottom of
> list, and any videos that have something to watch are sorted ascending at the
> top of the list, 1 to whatever  Maybe even add the flexibility later on down
> the line so that I can change the order based on [ order by Description ] or [
> Unwatched=0, Description ] or whatever, just by changing the final Order By
> clause at runtime.
>
> From what magic I've seen on the CTE page at sqlite.org, this should be easy
> to do, but I've not a clue where to start.
>
> If you'd like to look at the raw database, it is found here:
> https://dl.dropboxusercontent.com/u/1598459/sql/videos.db3
>
> Any hints and thoughts would be appreciated!
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Are BLOB stored inline with the rest of the record fields?

[Drago, William @ CSG - NARDA-MITEQ]

I haven't seen any anyone else mention this yet. Another consideration when 
working with large blobs is to make those columns that last ones in your 
schema. To quote Dr. Hipp:

"make the BLOB columns the last column in your tables.  Or even store the BLOBs 
in a separate table which only has two columns: an integer primary key and the 
blob itself, and then access the BLOB content using a join if you need to. If 
you put various small integer fields after the BLOB, then SQLite has to scan 
through the entire BLOB content (following the linked list of disk pages) to 
get to the integer fields at the end, and that definitely can slow you down."

Found here:

http://sqlite.1065341.n5.nabble.com/Effect-of-blobs-on-performance-td19559.html#a19560

Your blobs are small so this probably doesn't apply to your application, but 
something to keep in mind for future projects.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Eric Grange
> Sent: Thursday, March 03, 2016 3:16 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Are BLOB stored inline with the rest of the record 
> fields?
>
> > All BLOBs are stored inline.  The only complication comes when the
> > total
> row length is longer than a page.
>
> Ok thanks!
>
> That is besides my original question but what is the complication in that 
> case?
> Is the blob partially stored in the row, or is some indirection to other pages
> used, with only some pointer stored in the row?
>
> > Surely you mean big-endian?
>
> Yes, my mistake :)
>
> > Storing Blobs has a few conversions added if you try to use the SQL to
> store it, but it's quite efficient when using the API.
>
> By "use SQL", you mean as a literal blob embedded in the SQL string?
>
> My main use case would be through the API, the actual numbers are fixed
> precision, and so scattered over the range they cannot be displayed to end
> users without using exponents, so some formatting will have to happen.
> Besides size, using a blob rather than base32/base64 would simplify the
> encoding/decoding, and for debugging, blobs usually display in an
> hexadecimal form here, so a big-endian blob would be directly "readable".
>
>
>
> On Wed, Mar 2, 2016 at 11:07 PM, Doug Currie 
> wrote:
>
> > On Wed, Mar 2, 2016 at 4:42 PM, R Smith  wrote:
> > >
> > >
> > > Personally, unless your space is constrained, I would simply save
> > > the numbers as strings, perhaps Hex or BCD with leading chars and
> > > convert as needed. This would sort correctly without tricks and not
> > > do much worse
> > for
> > > space. (Base64 would be even better space-wise but won't sort correct).
> > >
> >
> > There is an encoding defined in RFC 4648 "Base-N Encodings" that does
> > preserve sort order; it is called Base 32 Encoding with Extended Hex
> > Alphabet. I would think the Base64 alphabet could be rearranged to
> > have the same property.
> >
> > e
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] s::[i] Why SQLite does not use a web-based forum?

[Drago, William @ CSG - NARDA-MITEQ]

Send your question to a mailing list and a large number of people see it right 
away. Discussion follows as quickly as email can be sent and rec'd. You get the 
answer to your question in the least possible time. Post your question on a 
web-based forum and it will take much longer for the same number of people to 
see it, and most of those people are there trying to solve their own problems 
not help you with yours.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of admin at shuling.net
> Sent: Sunday, February 14, 2016 8:19 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: s::[i] [sqlite] Why SQLite does not use a web-based forum?
> Importance: High
> Sensitivity: Confidential
>
> Hi,
>
> Why SQLite does not utilize a web-based forum for all users discuss
> problems? I think that will be more convenient and can help more people.
>
> Thanks
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] No Sqlite.dll for VS 2015 C# console application

[Drago, William @ CSG - NARDA-MITEQ]

Is System.Data.SQLite.dll in your bin folder along with the x86, x64 folders 
and corresponding SQLite.Interop.dll files?

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Nils Westerberg
> Sent: Friday, December 18, 2015 3:51 AM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] No Sqlite.dll for VS 2015 C# console application
>
> Hello,
> I have a Universal app with SQLite using sqlite-net and referencing
> SQLite for Universal App Platform that works. I am using VS 2015 update
> 1, Windows 10.
> I am trying to make a console application using sqlite-net but I cannot
> find a valid reference to SQLite. I have installed SQLite for Universal
> Platform, for Windows Runtime and for Windows Runtime (8.1) via Visual
> Studio Tools.Extensions and Updates... The References.Add Reference...
> does not show any SQLite Package as it does for the Universal app.
> I used References.Manage NuGet Packages to install System.Data.SQLite
> which updated the references. Running the application gives: "Unable to
> load DLL sqlite3: The specified module could not be found."I have tried
> to reference SQLite in other downloads from ??System.Data.SQLite but I
> get the same error.
>
> Adding sqlite3.dll to the load path gives: "Unable to load DLL sqlite3:
> This operation is only valid in the context of an app container."
> What can I do?  I have asked the question on Microsoft Developer
> Network as I thought to be more a VS problem but a moderator (Fred Bao)
> suggested that I ask the SQLite forum.
>
> Nils
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] The Simplest SQLite Common Table Expression Tutorial

[Drago, William @ CSG - NARDA-MITEQ]

David,

Thank you for sharing that. I found it very helpful.

Regards,

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of David Barrett
> Sent: Friday, September 25, 2015 4:17 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] The Simplest SQLite Common Table Expression Tutorial
>
> Hey all, just wanted to share this in case anybody is also looking for
> a very simple tutorial for CTE's in sqlite:
>
> http://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-
> expression-tutorial/
>
> The Simplest SQLite Common Table Expression Tutorial
>
> I?ve been trying to wrap my head aroundCommon Table Expressions
>  for a while, and all the
> tutorials I?ve read started out with ?simple? examples that were way
> too advanced for me to follow. Here?s my attempt to write a tutorial
> that starts as simple as possible.
>
> First, let?s start with the simplest query:
>
> sqlite> SELECT 1;
> 1
> sqlite>
>
> All this does is return a result set containing a row. Next, consider
> the simplest subquery:
>
> sqlite> SELECT * FROM ( SELECT 1 );
> 1
> sqlite>
>
> This just selects all the results from the subquery ? which in this
> case, is just a single row. A ?Common Table Expression? is basically
> the same as a subquery, except assigned a name and defined prior to the
> query in which it?s referenced. Accordingly, the simplest CTE version
> of the above query would be like:
>
> sqlite> WITH one AS ( SELECT 1 )
> SELECT * FROM one;
> 1
> sqlite>
>
> Breaking that down a bit further:
>
>- We?ve defined a common table expression named ?one?
>- We?ve ?filled? it with the output of SELECT 1, which is just 1 row
>- Then we selected everything from ?one?
>- Such that the final result is a single value: 1
>
> But a CTE can have multiple columns, too, and those columns can be
> assigned
> names:
>
> sqlite> WITH twoCol( a, b ) AS ( SELECT 1, 2 )
> SELECT a, b FROM twoCol;
> 1|2
> sqlite>
>
> Similarly, a CTE can query other tables:
>
> sqlite> CREATE TABLE foo ( bar INTEGER ); INSERT INTO foo VALUES(1);
> sqlite> INSERT INTO foo VALUES(2); SELECT * FROM foo;
> 1
> 2
> sqlite> WITH fooCTE AS (SELECT * FROM foo)
> SELECT * FROM fooCTE;
> 1
> 2
> sqlite>
>
> Additionally, you can define as many CTEs as you want in a single
> query:
>
> sqlite> WITH aCTE AS (SELECT 'a'),
>  bCTE AS (SELECT 'b')
> SELECT * FROM aCTE, bCTE;
> a|b
> sqlite>
>
> So, common table expressions can be used to restructure a query to make
> it more readable, by moving the subqueries out in front. But the real
> power of common table expressions is when you define an expression that
> recursively selects itself. They key to this is using a ?Compound
> Select Statements?, such as the UNION ALL operator. This just combines
> two result sets into one (so long as they have the same number of
> columns):
>
> sqlite> SELECT 1, 2
> UNION ALL
> SELECT 3, 4;
> 1|2
> 3|4
> sqlite>
>
> Take this example:
>
> sqlite> WITH RECURSIVE infinite AS (
> SELECT 1
> UNION ALL
> SELECT * FROM infinite
> )
> SELECT * FROM infinite;
> ^CError: interrupted
> sqlite>
>
> Let?s break down why that query will never finish:
>
>- ?WITH RECURSIVE infinite? defines a common table expression named
>?infinite?
>- ?SELECT 1? seeds that CTE?s output with a single row ? containing
> ?1?
>- Next the ?UNION ALL? says ?combine the output of what?s on the
> left,
>with the output of what?s on the right
>- And on the right we do ?SELECT * FROM infinite? ? meaning, select
>everything currently in the table.
>- The result is we?re defining a common table expression named
>?infinite? to be the union of ?a single row? and ?all other rows?.
>- Because no ?cap? has been placed on this (via a WHERE or LIMIT),
> this
>means we?ve defined an infinitely recurring CTE. Fun!
>
> So we can ?cap? that CTE by writing a query like:
>
> sqlite> WITH RECURSIVE finite AS (
> SELECT 1
> UNION ALL
> SELECT * FROM finite LIMIT 2
> )
> SELECT * FROM finite;
> 1
> 1
> sqlite>
>
> This does the same basic thing, but we?ve limited the number of
> possible results to only be 2. Ok, so that?s all well and good, but
> what is this good for? It turns out, a lot. Say you wanted to generate
> a table on the fly containing the numbers one through ten:
>
> sqlite> WITH RECURSIVE ten(x) AS (
> SELECT 1
> UNION ALL
> SELECT x+1 FROM ten WHERE x<10
> )
> SELECT * FROM ten;
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> 10
> sqlite>
>
> To do this, we?ve defined a 

[sqlite] sqlite3 file as database

[Drago, William @ CSG - NARDA-MITEQ]

Why do people use .db3 for sqlite database files? In my experience .db3 is the 
file extension for dBase III database files.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin
> Sent: Saturday, September 12, 2015 11:50 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite3 file as database
>
>
> On 12 Sep 2015, at 1:19pm, s.movaseghi at eramtec.ir wrote:
>
> > I have a database file as database.db3 but I have to use
> database.sqlite3 .
> > How can I convert the db3 file to sqlite3 file?
>
> If it is actually a SQLite database already then just rename the file.
> SQLite does not care what the file is called.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] index for OR clause

[Drago, William @ CSG - NARDA-MITEQ]

If case is not important would adding COLLATE NOCASE to column c improve 
performance?

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of R.Smith
> Sent: Monday, July 27, 2015 2:27 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] index for OR clause
>
>
>
> On 2015-07-27 08:09 PM, Simon Slavin wrote:
> > On 27 Jul 2015, at 6:58pm, Sylvain Pointeau
>  wrote:
> >
> >> create table TEST (
> >> a TEXT NOT NULL,
> >> a2 TEXT NOT NULL,
> >> b TEXT NOT NULL,
> >> c TEXT NOT NULL
> >> );
> >>
> >> create index IDX_TEST_1 on TEST(a,a2,b,c);
> >>
> >> insert into TEST(a,a2,b,c) values ('123','1234','456','PP'); insert
> >> into TEST(a,a2,b,c) values ('234','2345','456','PP'); insert into
> >> TEST(a,a2,b,c) values ('456','4567','456','PP'); insert into
> >> TEST(a,a2,b,c) values ('0123','10456','678','DP');
> >>
> >> sqlite> explain query plan select * from TEST where (a = '123' or
> >> a2='1234') and b = '456' and c='PP';
> >>
> >> 0|0|0|SCAN TABLE TEST
> >>
> >> whereas
> >>
> >> sqlite> explain query plan select * from TEST where a = '123' and b
> = '456'
> >> and c='PP';
> >>
> >> 0|0|0|SEARCH TABLE TEST USING COVERING INDEX IDX_TEST_1 (a=?)
> >>
> >> how to make this query using an index? is this possible?
> > That is a good job of investigation you have done there.  What you
> didn't take into account is that the order of columns in an index
> matters.
> >
> > Your example has known precise values for b and c, and sometimes but
> not always knows a and a2.  So b and c should be up front in your
> index:
> >
> > create index IDX_TEST_2 on TEST(b,c,a,a2);
>
> Quite, but there is also another consideration: Cardinality.  Now it
> might be that this data extract is not representative of the whole
> table at all, but from the looks of it, those columns b and c offer
> severely low cardinality, perhaps in the order of more than 30% of the
> table in duplications.
>
> If this is the case, an index on c or b will amount to pretty much a
> table-scan still. You really want to hit those high-cardinality columns
> in your leading indexer (which is why Richard rightly suggested the
> double index).
>
> I'd almost think having the less-expensive set of these:
> create index IDX_TEST_1 on TEST(a);
> create index IDX_TEST_2 on TEST(a2);
> would produce very near the same speeds for that query if the
> cardinality of columns b and c remain low for populated data.
>
> If however the cardinality scales up pretty well, then Richard's
> suggestion is much better, and if the cardinality will be even higher
> in b and c than in the a's, then Simon's suggestion is best.
>
> Tool for the job etc.
>
> Cheers,
> Ryan
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Broken link on System.Data.SQLite homepage

[Drago, William @ CSG - NARDA-MITEQ]

I'm getting a 404 on the link to sqlite.phxsoftware.com.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com

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

[sqlite] why does SQLite support VARCHAR(X) when the doc says it ignores it?

[Drago, William @ CSG - NARDA-MITEQ]

I am one of those people. I use SQLite and MS SQL Server and it is very 
convenient to have this cross compatibility.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com

> Lots of people take SQL code originally intended for other SQL engines,
> start up SQLite and want it to work first time.  So it does.  It won't
> perfectly copy the behaviour of engines which respect the '255' but
> it's good enough to assure the programmer that SQLite is worth using.
>
> Simon.
> ___
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Slow real world performance - Any suggestions please (warning long)

[Drago, William @ CSG - NARDA-MITEQ]

Keith & Simon,

Excellent explanations. Thank you.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin
> Sent: Saturday, July 04, 2015 1:14 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Slow real world performance - Any suggestions
> please (warning long)
>
>
> On 4 Jul 2015, at 5:46pm, William Drago 
> wrote:
>
> > Clearly, in this case, using COLLATE NOCASE in the table definition
> is
> > the right thing to do. Under what conditions would using it in the
> index instead be the right thing to do?
>
> It's rare.  Sometimes you have a column where case normally does
> matter, but occasionally want to search case-insensitive.  This might
> happen in a field where acronyms and initialisms are used a lot and you
> need to distinguish between them.  For example, an English Language
> dataset might normally need to distinguish between 'ACID' and 'acid'
> but you might want to enable a fast searching facility, without your
> users having to be fussy about typing capital letters.
>
> It's also possible to do it the other way: define the column as COLLATE
> NOCASE but have an index include the column COLLATE BINARY.  Or even
> COLLATE RTRIM.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Reader.GetBytes() - when is a byte not a byte?

[Drago, William @ CSG - NARDA-MITEQ]

Lots of good replies. My problem is solved, thank you.

My assumption was that GetBytes() could be used to read the bytes of any column 
regardless of type. What difference should column type or affinity make if I'm 
asking for bytes? If I'm using GetBytes() on a column with REAL affinity, it 
should put 8 bytes in the read buffer, but it doesn't. If I send text to a BLOB 
column it's typeof() changes to TEXT which confuses GetBytes(). I think this is 
pretty silly because bytes are bytes, right? Why bother verifying type when the 
method name already tells you what you are getting and bytes can be obtained 
from any data type? (Rhetorical questions, but feel free to respond anyway.)

Anyway, the problem is solved. Thank you all.

( cast('x y z' as blob) ) is especially convenient. Thank you for that 
excellent suggestion, Jean (and thanks for the Stack Overflow link, that helped 
too).

And thanks to R. Smith for posting the informative examples that also helped me 
understand what was happening.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Jean Chevalier
> Sent: Thursday, July 02, 2015 1:40 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Reader.GetBytes() - when is a byte not a byte?
>
> It's not mandatory to use x'' notation to insert into a blob, when
> one can use cast.
>
> The following should return blob content correctly without explicitly
> lying it down as Hex:
>
> sqlite> create table T (c blob check(typeof(c) = 'blob'));
>
> sqlite> insert into T values ( cast('x y z' as blob) );
>
> sqlite> select c from T;
>
> x y z
>
> Please check whether your calling program consumes the above as text or
> blob, I'd expect blob.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Mozilla wiki 'avoid SQLite'

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of david at andl.org
> Sent: Monday, June 15, 2015 2:28 AM
> To: 'General Discussion of SQLite Database'
> Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'
>
> I won't abuse the patience of our hosts by prolonging this debate, but
> I disagree strongly with this theme.
>
> I have almost certainly written more C/C++ code than you or most of the
> people on this list, and I never choose it first. I am personally at
> least 3 times as productive in C# as I am in C (slightly narrower
> margin in C++), and computers are far cheaper than brains.

If productivity is important is there any reason why you're not working in F#? 
I don't know that language myself, but I've seen some impressive demos.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


>
> This theme is strongly reminiscent of arguments over moving from
> assembly language, and it's basically wrong. The best tool is the one
> that gets the required job done with maximal speed at minimal cost.
>
> And just for the record, C# does not compile into byte code. I suggest
> you check your facts.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of
> Aleksey Tulinov
> Sent: Monday, 15 June 2015 10:32 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'
>
> On 15/06/15 01:00, Simon Slavin wrote:
>
> Simon,
>
> > Perhaps the next generation of computer languages will be designed by
> computer, to let us speak to them in an efficient manner.
> >
>
> I'm sure computer would insist on C, if not, then it's apparently a
> software bug.
>
> On a serious note, i think it's rather question of programming
> computers or programming another programs. As you've mentioned, higher
> level languages often compiles into byte-code which is then interpreted
> by virtual machine.
> So you don't speak to machine, you speak to mediator who speak to
> machine.
> This by definition an overhead, with growing complexity of the program,
> overhead will grow accordingly, this is unavoidable.
>
> I think it's also fair to say that SQL is not for programming machines
> (no offense), it's for programming SQLite and other database
> implementations.
> Even if something is called "virtual machine", VM always behaves
> somehow differently from The Machine, thus programmer'
> efforts has mediated effect on latter.
>
> Of course VM could do a good job in a specific domain, but each VM
> limits the liberty of expressing yourself to the machine and vice
> versa.
>
> In my opinion best database language would reflect the way in which
> database works and best computer programming language would reflect the
> way in which computer works, as close as reasonably possible.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] CTE/Sudoku talk (was User-defined types -- in Andl)

[Drago, William @ CSG - NARDA-MITEQ]

I'm in NY, but just by coincidence I'll be very close to Charlotte for a 
graduation on the same darn day as your CTE/Sudoku talk. So close, yet so far! 
I am also going to miss your Git: Just say "No!" talk which I would have loved 
to see.

So, if these talks are permanently available on YouTube (not just live 
streamed) please let us know.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Richard Hipp
> Sent: Wednesday, June 10, 2015 12:01 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] User-defined types -- in Andl
>
> On 6/9/15, david at andl.org  wrote:
> > I don't remember the last time I saw SQL like this. Understanding it
> > might be the challenge...
>
> I'll be giving a talk on CTEs this Saturday at the Southeastern
> Linuxfest (http://www.southeastlinuxfest.org/) during which I will
> explain and demonstrate how to write a simple CTE that solves a sudoku
> puzzle.  If you cannot attend in person, I'm told that the talk will be
> streamed live to youtube.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] How to get length of all columns in a table

[Drago, William @ CSG - NARDA-MITEQ]

All,

I just want to make sure everyone understands that I know how to get the length 
of a column and that I was only asking for a wildcard technique to get the 
lengths of all columns in a table without having to list all the columns (e.g. 
SELECT Length(*) FROM myTable). Obviously that doesn't work, but that's the 
kind of thing I was looking for.

I already wrote a little code snippet to automatically generate a "SELECT 
Length(col_1), Length(col_2...)" string from the table def, and I'm happy with 
that solution.

I am using this on BLOBs and Length() is returning the number of bytes, which 
is exactly what I need.

All is well. Thanks for all your input,

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com



> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Richard Hipp
> Sent: Tuesday, June 02, 2015 9:15 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] How to get length of all columns in a table
>
> On 6/2/15, Igor Tandetnik  wrote:
> > On 6/2/2015 2:28 AM, Hick Gunter wrote:
> >> Sqlite3_column_bytes will convert numeric values to strings and
> >> return the length of that "string representation" (excluding the
> >> terminating \0), not the byte size required to store the numeric
> value itself.
> >
> > So will length() SQL function, I'm pretty sure.
>
> Yes, both length() and sqlite3_column_bytes() will convert numbers to
> strings before computing the length.  However, they are not equivalent.
>
> The length() SQL function returns the number of *characters*.  The
> sqlite3_column_bytes() C function returns the number of *bytes*.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] How to get length of all columns in a table - Solved

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of J Decker
> Sent: Monday, June 01, 2015 2:48 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] How to get length of all columns in a table
>
> you get the length in the result set?
>
> sqlite3_column_bytes( statement, idx );

I'm using System.Data.SQLite, so the techniques are different from the SQLite C 
Interface, but the bottom line is there is no wildcard (*) for returning the 
lengths of all columns.

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

[sqlite] How to get length of all columns in a table

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin
> Sent: Monday, June 01, 2015 12:59 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] How to get length of all columns in a table
>
>
> On 1 Jun 2015, at 4:45pm, Drago, William @ CSG - NARDA-MITEQ
>  wrote:
>
> Just to confirm, the following is the only practical way to get the
> length of each column in a table:
> >
> > Select Length(col_1)
> > ,Length(col_2)
> > ,Length(col_3)
> > .
> > .
> > .
> > ,Length(col_n)
>
> Martin's other answer will probably give you the answer faster:
>
> SELECT length(col_1||col2||col_3 ... col_n) FROM MyTable

That returns the sum of the column lengths. I was looking for the length of 
each individual column.

Thanks anyway,

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

[sqlite] How to get length of all columns in a table

[Drago, William @ CSG - NARDA-MITEQ]

Thank you for the reply, Martin.

I wasn't looking for the sum of all lengths, just the length of each individual 
column, but I think you answered my question anyway--that it can't be done 
without naming each column.

That's fine, I just wanted to make sure that I wasn't naming the columns 
needlessly.

Just to confirm, the following is the only practical way to get the length of 
each column in a table:

Select Length(col_1)
,Length(col_2)
,Length(col_3)
. 
. 
. 
,Length(col_n)
FROM myTable;

Correct?

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Martin Engelschalk
> Sent: Monday, June 01, 2015 11:23 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] How to get length of all columns in a table
>
> Hi,
>
> the length funktion takes an expression as an argument, for example a
> column name like
>
> select length(MyColumn) from MyTable;
>
> Offhand, I can think of no way to calculate the sum of the lengths of
> the contents of all columns of a table without naming all the columns.
> There are two ways:
>
> select length(MyColumn1 || MyColumn2 || MyColumn3) from MyTable; or
> select length(MyColumn1) + length (MyColumn2) + length (MyColumn3) from
> MyTable;
>
> Also, you will probably want to watch for columns with null values. So
> this will work:
>
> select coalesce(length(MyColumn1),0) + coalesce(length (MyColumn2),0) +
> coalesce(length (MyColumn3),0) from MyTable;
>
> HTH
> Martin
>
>
> Am 01.06.2015 um 17:12 schrieb Drago, William @ CSG - NARDA-MITEQ:
> > All,
> >
> > What is the correct syntax for getting the length of all columns in a
> > table? I tried
> >
> > SELECT Length(*) FROM myTable;
> >
> > and
> >
> > Length(SELECT * FROM myTable);
> >
> > Neither one of those works, and I can't find anything on line.
> >
> > Thanks,
> > --
> > Bill Drago
> > Senior Engineer
> > L3 Narda-MITEQ<http://www.nardamicrowave.com/>
> > 435 Moreland Road
> > Hauppauge, NY 11788
> > 631-272-5947 /
> > William.Drago at L-3COM.com<mailto:William.Drago at L-3COM.com>
> >
> >
> > CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and
> any attachments are solely for the use of the addressee and may contain
> information that is privileged or confidential. Any disclosure, use or
> distribution of the information contained herein is prohibited. In the
> event this e-mail contains technical data within the definition of the
> International Traffic in Arms Regulations or Export Administration
> Regulations, it is subject to the export control laws of the
> U.S.Government. The recipient should check this e-mail and any
> attachments for the presence of viruses as L-3 does not accept any
> liability associated with the transmission of this e-mail. If you have
> received this communication in error, please notify the sender by reply
> e-mail and immediately delete this message and any attachments.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] How to get length of all columns in a table

[Drago, William @ CSG - NARDA-MITEQ]

All,

What is the correct syntax for getting the length of all columns in a table? I 
tried

SELECT Length(*) FROM myTable;

and

Length(SELECT * FROM myTable);

Neither one of those works, and I can't find anything on line.

Thanks,
--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


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

[sqlite] Please confirm what I THINK I know about blobs

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Eduardo Morras
> Sent: Monday, May 11, 2015 11:02 AM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Please confirm what I THINK I know about blobs
>
> On Mon, 11 May 2015 14:00:41 +0000
> "Drago, William @ CSG - NARDA-MITEQ"  wrote:
>
>
> > Correct me if I'm wrong, but the only way to read comments is by
> > reading back the entire CREATE TABLE string, correct?
>
> Yes, you need to parse it. You search for '--' and '\n'. The chars
> between them are the comment.
>
> I thought you need it only for documentation purpouse and not
> automatic/dynamic programming.


I should have included more information in my original post, but I'm always 
afraid of boring people with too much information.

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com

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

[sqlite] Please confirm what I THINK I know about blobs

[Drago, William @ CSG - NARDA-MITEQ]

All good suggestions, folks. To summarize:

Dr. Hipp uses an additional column.
Eduardo Morras recommends comments.
Simon Slavin recommends comments or in some cases a table dedicated to metadata.
Darren Duncan confirms the need for metadata.
Scott Doctor recommends putting the info in the column name.
Eric Hill suggests taking advantage of SQLite's type flexibility and adding the 
info to the column type.

I liked the idea of using comments, but while they are great for human 
consumption, they are not easy to read programmatically. Since I want to build 
VEE* structs automatically from SQLite tables this is important.

Correct me if I'm wrong, but the only way to read comments is by reading back 
the entire CREATE TABLE string, correct?

* http://en.wikipedia.org/wiki/Agilent_VEE

Thanks,
--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Eric Hill
> Sent: Saturday, May 09, 2015 9:55 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Please confirm what I THINK I know about blobs
>
> This approach:
>
> CREATE TABLE blob_table (
>   ModelNo TEXT,
>   SerialNo TEXT,
>   VSWR BLOB_DOUBLE
> )
>
> involves comments?  I don't see how.  Nothing wrong with the comments
> approach, but this is an approach that just takes advantage of the fact
> that SQLite does not have fixed data types.
>
> Eric
>
> From: Simon Slavin<mailto:slavins at bigfraud.org>
> Sent: ?Saturday?, ?May? ?9?, ?2015 ?3?:?52? ?PM
> To: General Discussion of SQLite Database<mailto:sqlite-
> users at mailinglists.sqlite.org>
>
>
> On 9 May 2015, at 8:12pm, Drago, William @ CSG - NARDA-MITEQ
>  wrote:
>
> > Best idea yet! Anyone see any issues with this?
>
> It's actually a comment, and SQLite provides ways of putting proper
> comments in table definitions:
>
> CREATE TABLE blob_table (
>  ModelNo TEXT, -- new-style models as used from 2006 onwards  SerialNo
> TEXT,  VSWR BLOB -- array of ten double-length floats
> )
>
> These comments can be found if you look at the table definition in
> sqlite_master.
>
> I've also seen SQL databases where the designer created an otherwise
> unused table to hold comments on every column, something like this:
>
> CREATE TABLE _structure (
>  tableName TEXT,
>  columnName TEXT,
>  introduced TEXT,
>  variableType TEXT,
>  theComments TEXT
> )
>
> 'introduced' was the edit of their program which first used the column
> (equivalent to 'checkin' as used by the SQLite development team).
> 'variableType' was not the SQL type but the type of variable in the
> programming language they were using the database with.  This helped
> because the language had numerous variable types and subtle bugs could
> be introduced if you, for example, stored a value from an unsigned
> integer then did maths on the value in a long integer.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Please confirm what I THINK I know about blobs

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Eric Hill
> Sent: Saturday, May 09, 2015 2:14 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Please confirm what I THINK I know about blobs
>
> The comment approach could work, I guess, but why not just encode the
> type into the column's declared type?
>
> CREATE TABLE blob_table (
>   ModelNo TEXT,
>   SerialNo TEXT,
>   VSWR BLOB_DOUBLE
> )
>
> That's what I do with numeric columns that I need to identify as
> actually containing dates.  As I understand it, SQLite only scans those
> type names to choose a column affinity, so you are free to be as
> creative with them as you want.  sqlite3_column_decltype() will return
> whatever you put as the type.
>

Best idea yet! Anyone see any issues with this?

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com

> HTH,
>
> Eric

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

[sqlite] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome

[Drago, William @ CSG - NARDA-MITEQ]

Under SQLite Admin Tools what is the difference between major and minor?

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com



> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Gerald Bauer
> Sent: Tuesday, May 05, 2015 7:05 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Awesome SQLite List - Collection of SQLite
> Goodies Started - Contributions Welcome
>
> Hello,
> >   It seems to me that it would be useful to include them, flagged as
> > "commercial only".
>
>   Due to popular demand I started a "commercial only" awesome sqlite
> list [1]. For now entries are free (as in beer)  ;-)  Cheers.
>
> [1] https://github.com/planetopendata/awesome-
> sqlite/blob/master/COMMERCIAL.md
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] System.Data.SQLite, Need alternate way to get field length

[Drago, William @ CSG - NARDA-MITEQ]

Thanks for the reply, Clemens. The problem is now solved.


--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Clemens Ladisch
> Sent: Saturday, May 02, 2015 3:11 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] System.Data.SQLite, Need alternate way to get
> field length
>
> William Drago wrote:
> > I am trying to determine the number of bytes in a blob. According to
> the help file under SQLiteDataReader.GetBytes Method in the Remarks
> section:
> >
> > "To determine the number of bytes in the column, pass a null value
> for the buffer. The total length will be returned."
> >
> > I'm working in VEE and VEE doesn't have a null keyword, so I tried
> System.DBNull.Value and I get an error (signatures don't match).
>
> If VEE (whatever that is) does not allow you to specify something that
> ends up as a null reference, the you cannot use this mechanism.
>
> > So, are there any other ways to get the number of bytes in a blob?
>
> SELECT length(MyLittleBlob) FROM ...
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome

[Drago, William @ CSG - NARDA-MITEQ]

Here's very good JDBC driver for SQLite:

https://bitbucket.org/xerial/sqlite-jdbc

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com



> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Jim Callahan
> Sent: Monday, May 04, 2015 10:14 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Awesome SQLite List - Collection of SQLite
> Goodies Started - Contributions Welcome
>
> ?Readers on GitHub might also be interested in ODBC and JDBC drivers for
> SQLite:
>
> ODBC
> http://www.ch-werner.de/sqliteodbc/
>
> JDBC
> http://www.ch-werner.de/javasqlite/index.html?
>
> Also, a few weeks ago there was a discussion on this list of how to
> call SQLite from C# and an add-in for Microsoft Visual Studio.
>
> Also there is a (very high quality!)  RSQLite interface from the R
> statistical language to SQLite.
>
> RSQLite (on CRAN)
> http://cran.r-project.org/web/packages/RSQLite/index.html
> as well as
>
> sqldf (on CRAN)
> http://cran.r-project.org/web/packages/sqldf/sqldf.pdf
>
> sqldf (on GitHub)
> https://github.com/ggrothendieck/sqldf
>
>
> As I understand there are interfaces from a variety of other languages
> including Python and PHP.
>
> Jim Callahan
> Orlando, FL
>
>
> On Mon, May 4, 2015 at 8:55 AM, Gerald Bauer 
> wrote:
>
> > Hello,
> >   Thanks I've updated the awesome-sqlite list [1] and now includes a
> > new section on SpatiaLite and more admin tools. Cheers.
> >
> > [1] https://github.com/planetopendata/awesome-sqlite
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Does column order matter for performance?

[Drago, William @ CSG - NARDA-MITEQ]

Read Mr. Hipp's reply to me when I asked a similar question:


"Two things to be aware of:

(1) When reading a row, SQLite reads from beginning to end.  So if you have
some small integer or boolean fields, it is better to put them first in the
table. Otherwise, SQLite has to read past the big BLOBs in order to get to
the smaller fields, even if the BLOBs themselves are not used.

(2) When changing any column of a row, the entire row is rewritten,
including the unchanged columns.  So if you have some smaller fields
(integers and booleans) that change frequently and also some large BLOBs
that change infrequently, you might consider factoring the BLOBs out into a
separate table just so they don't have to be rewritten every time a boolean
in the same row changes.

Both points above a purely performance considerations.  You should always
get the correct answer either way."


Here's the entire discussion:
http://sqlite.1065341.n5.nabble.com/BLOBs-and-NULLs-td75201.html

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Pol-Online
> Sent: Thursday, April 30, 2015 12:45 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] Does column order matter for performance?
>
> Hi,
>
> I wasn?t able to find the answer to this question online: does the
> column order matter for SQLite performance? E.g. should you put fixed
> width columns like INTEGER before TEXT or BLOB?
>
>
> -Pol
>
> 
> Pol-Online
> info at pol-online.net (mailto:info at pol-online.net)
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Regarding testing

[Drago, William @ CSG - NARDA-MITEQ]

Never saw this before. LMAO. Will put it too good use...

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com



> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin
> Sent: Monday, April 27, 2015 7:58 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Regarding testing
>
>
> On 27 Apr 2015, at 12:54pm, Sairam Gaddam 
> wrote:
>
> > How SQLite is tested and can I get those test cases?
>
>  es%3F>
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] SQLite and Scientific Computing: Arrays and Complex Numbers

[Drago, William @ CSG - NARDA-MITEQ]

All,

I've been enjoying this discussion and have learned a thing or two from all the 
suggestions.

My particular problem is indeed solved. Adding a simple blob to my original 
table is the best solution in this case. If you think about the trace data as 
simply a screenshot of the analyzer display then I think it is easier to 
understand why I don't want to bother with more complex solutions. Even though 
they may be technically more correct, they are overkill for what I'm trying to 
accomplish. I'm just saving a screenshot, minus the graticule and all the other 
decorations on the display. There's no need to make it any more complicated 
than that. I don't even have to save the X-axis elements as those are derived 
from other parameters that are already known. Someone asked if the trace data 
was time stamped. It is not. Date and time of the test are stored along with 
all the other relevant information.

If I ever have to deal with complex numbers as my core data I have some pretty 
good ideas on how to handle them now. BTW, the languages I use most, VEE & C#, 
both support complex data types. Given the amount of use database programs see 
in the scientific world I'm surprised there is no native complex data type (I 
can appreciate the difficulty in implementing such a thing). Array support for 
each type would be nice too. We have this in VEE and it is so easy to use. But 
for now every programmer working in science or engineering has to re-invent the 
wheel.

For the curious here are the analyzers I'm working with:
http://www.keysight.com/en/pc-101745%3Aepsg%3Apgr/pna-network-analyzers-300-khz-to-11-thz?cc=US=eng

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com



> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Jim Callahan
> Sent: Sunday, April 26, 2015 9:56 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] SQLite and Scientific Computing: Arrays and Complex
> Numbers
>
> The original thread asking about an array of complex numbers has been
> marked as "solved." The requester has decided to serialize the complex
> numbers and store them in a blob. Earlier, Keith had suggested storing
> complex numbers as a pair of real numbers and a separate box table. I
> extended Keith's suggestion with two or three tables, elements, arrays
> and optionally coordinates.
>
> There is some literature on storing arrays in SQL databases. In
> addition complex numbers seem to be the orphan stepchild of programming
> languages (let alone databases). Although FORTRAN IV had complex
> numbers they were not added to the C standard until C99.
>
> Language / Standard / Library
> 
> C / C99/ complex.h
> http://en.wikibooks.org/wiki/C_Programming/C_Reference/complex.h
> C# / 4.0 / System.Numerics.Complex
> https://msdn.microsoft.com/en-
> us/library/system.numerics.complex(v=vs.110).aspx
>
> Java /?/ Apache Commons
> Python/2.6.5/ cmath
> https://docs.python.org/2/library/cmath.html
>
> http://en.wikipedia.org/wiki/Complex_data_type
>
> So, I suppose if a company wanted to sponsor it, complex numbers could
> be supported through an addin library similar to FTS3 and FTS4 for full
> text searches.
> http://sqlite.org/fts3.html
>
> Here for example, is a discussion on IBM DeveloperWorks concerning the
> Informix database.
> https://www.ibm.com/developerworks/community/blogs/gbowerman/entry/sql_
> and_the_complex_plane?lang=en
>
> Some databases have Abstract Defined Type (Oracle) or User Defined
> Types (Microsoft SQL Server) that could be used for complex numbers.
>
> Ironically, the scientific data format NetCDF did not have provision
> for complex numbers (it was designed for weather data).
> https://www.unidata.ucar.edu/mailing_lists/archives/netcdfgroup/2011/ms
> g00027.html
>
> There are some discusssions of scientific versions of SQL (such as
> SciQL):
>
> "A Query Language for Multidimensional Arrays:
> Design, Implementation, and Optimization Techniques"
> http://homepages.inf.ed.ac.uk/libkin/papers/sigmod96a.pdf
>
> "Requirements for Science Data Bases and SciDB"
> http://www-db.cs.wisc.edu/cidr/cidr2009/Paper_26.pdf
>
> "SciQL, A Query Language for Science Applications"
> http://homepages.cwi.nl/~zhang/papers/arraydb11.pdf
>
> Jim
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the 

[sqlite] building SQLite DLL with Visual C++

[Drago, William @ CSG - NARDA-MITEQ]

I'm afraid I can't help you with that, Jay. I don't use data bindings and all 
that other built-in visual studio db stuff because performance is slow and it's 
hard to maintain. I just read from the db into a data table. From there you can 
do whatever you want with the data.

Search on line for VB.NET examples and I'm sure you'll find some examples.

Good luck,
--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Jay Smith
> Sent: Sunday, April 26, 2015 3:44 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] building SQLite DLL with Visual C++
>
> Thanks Bill
>
> That worked
>
> BUT
>
> In my form when I go to "DataBindings" I get a Pop-up to "Add Project
> Datasource" then the 2 screens to choose Datasource Type( I select
> Database) then I choose DataSet. This is where it breaks down."Choose
> Data Connection there are 4 choices "Access" and 3 server types, then
> there is  which I select. And that leaves me with a single "Data
> provider"
> Microsoft OLE DB simple provider  This connection works, but is it
> correct?
> The connection is MSDAOSP
>
> Then I get to the screen "Choose your database objects" and an ERROR
>
> "Selecting Objects of type 'Table' is not supported.
>
> Then we get to the next part of the problem. I am in my late 70's and
> have not done any SQL for over 15 years ( I was pretty good at one
> time.) Where do I go from here.
>
> Jay
>
> On Sat, Apr 25, 2015 at 5:12 PM, Drago, William @ CSG - NARDA-MITEQ <
> William.Drago at l-3com.com> wrote:
>
> > I don't know what error you're getting so I can't offer specific
> help.
> >
> > This is what works for us:
> >
> > Open your project in Visual Studio.
> >
> > Click Tools > NuGet Package Manager > Manage NuGet Packages for
> Solution...
> >
> > Select Online > nugget.org
> >
> > In the search box enter system.data.sqlite
> >
> > A list of packages should be displayed after a short wait.
> >
> > Select System.Data.SQLite Core (x86/x64) or, if you need LINQ and
> > Entity Framework support, select System.Data.SQLite (x86/x64), then
> > click Install (we use Core here)
> >
> > After installation you should see a little green checkmark next to
> the
> > package you selected.
> >
> > Click Close and you're done. Check on line for VB.NET code samples
> > using SQLITE.
> >
> > Good luck,
> >
> > --
> > Bill Drago
> > Senior Engineer
> > L3 Narda-MITEQ
> > 435 Moreland Road
> > Hauppauge, NY 11788
> > 631-272-5947 / William.Drago at L-3COM.com
> >
> >
> >
> > > -Original Message-
> > > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> > > users-bounces at mailinglists.sqlite.org] On Behalf Of Jay Smith
> > > Sent: Saturday, April 25, 2015 3:50 PM
> > > To: General Discussion of SQLite Database
> > > Subject: Re: [sqlite] building SQLite DLL with Visual C++
> > >
> > > Hi Bill
> > >
> > > I have been trying for 2 days and I am totally lost.
> > >
> > > I have NuGet Package Manager. I don't know how to use it.
> > >
> > > From the  PM>   System.Data.SQLiteI get an error.
> > >
> > > Can you be a little more specific.
> > >
> > > Jay
> > >
> > > On Thu, Apr 23, 2015 at 4:08 PM, Drago, William @ CSG - NARDA-MITEQ
> > > < William.Drago at l-3com.com> wrote:
> > >
> > > > > -Original Message-
> > > > > From: sqlite-users-bounces at mailinglists.sqlite.org
> > > > > [mailto:sqlite- users-bounces at mailinglists.sqlite.org] On
> Behalf
> > > > > Of Jay Smith
> > > > > Sent: Thursday, April 23, 2015 3:47 PM
> > > > > To: General Discussion of SQLite Database
> > > > > Subject: Re: [sqlite] building SQLite DLL with Visual C++
> > > > >
> > > > > Thank you Adam for responding to my post. I have the windows
> > > > > binaries downloaded.
> > > > >
> > > > > At this point I am just following the instructions in the book.
> > > > > And I really am not sure what I need the dll for.
> > > > >
> > > > > Here's the scenario. I have cre

[sqlite] building SQLite DLL with Visual C++

[Drago, William @ CSG - NARDA-MITEQ]

I don't know what error you're getting so I can't offer specific help.

This is what works for us:

Open your project in Visual Studio.

Click Tools > NuGet Package Manager > Manage NuGet Packages for Solution...

Select Online > nugget.org

In the search box enter system.data.sqlite

A list of packages should be displayed after a short wait.

Select System.Data.SQLite Core (x86/x64) or, if you need LINQ and Entity 
Framework support, select System.Data.SQLite (x86/x64), then click Install
(we use Core here)

After installation you should see a little green checkmark next to the package 
you selected.

Click Close and you're done. Check on line for VB.NET code samples using SQLITE.

Good luck,

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com



> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Jay Smith
> Sent: Saturday, April 25, 2015 3:50 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] building SQLite DLL with Visual C++
>
> Hi Bill
>
> I have been trying for 2 days and I am totally lost.
>
> I have NuGet Package Manager. I don't know how to use it.
>
> From the  PM>   System.Data.SQLiteI get an error.
>
> Can you be a little more specific.
>
> Jay
>
> On Thu, Apr 23, 2015 at 4:08 PM, Drago, William @ CSG - NARDA-MITEQ <
> William.Drago at l-3com.com> wrote:
>
> > > -Original Message-
> > > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> > > users-bounces at mailinglists.sqlite.org] On Behalf Of Jay Smith
> > > Sent: Thursday, April 23, 2015 3:47 PM
> > > To: General Discussion of SQLite Database
> > > Subject: Re: [sqlite] building SQLite DLL with Visual C++
> > >
> > > Thank you Adam for responding to my post. I have the windows
> > > binaries downloaded.
> > >
> > > At this point I am just following the instructions in the book. And
> > > I really am not sure what I need the dll for.
> > >
> > > Here's the scenario. I have created a program in vb2012. The
> program
> > > stores less than 20 fields of data. I am currently saving the data
> > > in a html format. I just recently discovered SQLite. I am now in
> the
> > > process of changing over to a database to store data. I studied SQL
> > > and Oracle
> > > 10 years ago. I have almost completed the database for the project.
> > > My problem is how to integrate the SQL db into my VB program.
> >
> > Your subject line says C++, but the line above says VB, so is your
> > program written in C++ or VB?
> > If it's VB just get SQLite with NuGet (Tools > NuGet Package Manager,
> > then search for System.Data.SQLite Core).
> >
> > -Bill
> >
> >
> > CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and
> > any attachments are solely for the use of the addressee and may
> > contain information that is privileged or confidential. Any
> > disclosure, use or distribution of the information contained herein
> is
> > prohibited. In the event this e-mail contains technical data within
> > the definition of the International Traffic in Arms Regulations or
> > Export Administration Regulations, it is subject to the export
> control
> > laws of the U.S.Government. The recipient should check this e-mail
> and
> > any attachments for the presence of viruses as L-3 does not accept
> any
> > liability associated with the transmission of this e-mail. If you
> have
> > received this communication in error, please notify the sender by
> > reply e-mail and immediately delete this message and any attachments.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Thoughts on storing arrays of complex numbers (Solved)

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Scott Hess
> Sent: Friday, April 24, 2015 3:19 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Thoughts on storing arrays of complex numbers
> (Solved)
>
> On Fri, Apr 24, 2015 at 12:01 PM, Drago, William @ CSG - NARDA-MITEQ
>  wrote:
> > Since the data is received from the analyzer as an array of
> > real/imaginary pairs (R,I,R,I,R,I,R,I...), 3202 elements total,
> that's
> > how I will blob and store it. This is the simplest way to add it to
> > the database. It's just one more field along with all the other data.
> > If I ever need to operate on that trace data again it's a simple
> > matter of pulling out of the database and un-blobbing it.
>
> In a case like this, I don't think I've ever come to regret suggesting
> the use of a serialization library, like protobuf (or cap'n proto or
> third or avro or ...).  When you make your ad-hoc serialization
> strategy, it works swell for six months, then a new requirement comes
> downstream and you have to figure out a new format plus how to convert
> all the old data.  If that happens two or three times, you start to get
> a combinatoric problem which makes it hard to reason about how a change
> is going to affect existing installs.  Most such requirements are for
> an additional field per array index, which many serialization libraries
> can support pretty transparently.

So, serialize the complex array data then store it in SQLite as a blob? I'm 
working in C# which has built-in support for serialization, do I still need a 
third party library? Other than writing some objects to disk in Java quite a 
few years ago, I have little experience with serialization, sorry for the 
ignorance.

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com



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

[sqlite] Thoughts on storing arrays of complex numbers

[Drago, William @ CSG - NARDA-MITEQ]

This makes perfect sense. Thank you.

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Jim Callahan
> Sent: Saturday, April 25, 2015 1:37 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Thoughts on storing arrays of complex numbers
>
> Keith is close.
>
> I would suggest two tables: Elements and Arrays.
>
> Elements Table
> Since arrays are usually referenced by names, I would include an
> "arrayname" field in the Elements table to indicate membership in a
> particular array. Also, I would not trust the recordID for the internal
> ordering of the array, so I would suggest a SeqNo (sequence number or
> unidimensional index) for the elements of the array.
>
> CREATE TABLE ComplexElements
> (
>ComplexElementID INTEGER PRIMARY KEY,
>ComplexArrayName  CHARACTER NOT NULL DEFAULT 'ComplexArray1',
>SeqNo INTEGER NOT NULL DEFAULT 0,-- zero based
>RealPart REAL NOT NULL DEFAULT 0,
>ImagPart REAL NOT NULL DEFAULT 0
> );
>
> The dimensions of the array are an array property and not an element
> property, therefore the number of elements in each dimensions are
> stored in the ComplexArrays table. SQLite knows nothing about the
> dimensions (they are just data) the higher level language calling
> SQLite coerces the dimensions on the unidimensional list of array
> element pairs provided by SQLite.
>
> I have allowed for up to five dimensions (dim1 through dim5).
>
> Do you want zero or one based arrays?
>
> Do you want row-major or column-major interpretation of element vector?
>
> ?
> CREATE TABLE  ComplexArrays
> (
>   ComplexArrayID INTEGER PRIMARY KEY,
>   ComplexArrayName  CHARACTER NOT NULL DEFAULT 'ComplexArray1',
>   dim1 INTEGER NOT NULL DEFAULT 0,  -- min element 0 in 1 dim
>   dim2 DEFAULT NULL ALIAS y,-- dim is maxsize of dim, not coord
>   dim3 DEFAULT NULL ALIAS z,
>   dim4 DEFAULT NULL,
>   dim5 DEFAULT NULL,
> );
>
> Coordinates, you would probably want to generate the indices for the
> arrays "on the fly" in the higher level language, but if you wanted to
> reference the array indices in SQL you might have a third table
> "Coordinates" which would store the ComplexArrayName, SeqNo, Coord1
> ALIAS ,X Coord2 ALIAS Y,
> Coord3 ALIAS Z, Coord4, Coord5.
>
> Then one might create a SQL VIEW  "ComplexData" which would allow one
> to query the contents of an array (a view is a stored query that acts
> as a virtual table -- you can use the view name as if it were a table
> in a
> query):
>
> SELECT ArrayName, X, Y, Z, RealPart, ImagPart FROM ComplexData WHERE
> ArrayName = 'Array1';
>
> or, to select a single element, specify the coordinates:
>
> SELECT ArrayName, X, Y, Z, RealPart, ImagPart FROM ComplexData WHERE
> ArrayName = 'Array1' AND X = 0 AND Y = 0 AND Z = 0; -- 3D, zero based
> array
>
> Thanks to Keith, he was on the right track.
>
> Warning, my capitalization and names may be inconsistent and my SQL
> might be pseudocode, but the intent is create the structures to support
> the final two queries.
>
> Hope this helps.
>
> Jim Callahan
> Orlando, FL
>
> On Fri, Apr 24, 2015 at 5:52 PM, Keith Medcalf 
> wrote:
>
> >
> > Create table ComplexNumbers
> > (
> >id integer primary key,
> >real real not null default 0,
> >imag real not null default 0
> > );
> >
> > Then, where ever you need to use a complex number you store it in the
> > complex number table and store the id of that number instead.  For
> example:
> >
> > ?
> > create table  Boxes
> > (
> >id integer primary key,
> >length integer references ComplexNumbers,
> >width integer references COmplexNumbers );
> >
> > Or if you need a list then something lije:
> >
> > create table ListHeader
> > (
> >List integer primary key,
> >Name text collate nocase not null unique, );
> >
> > create table ListEntries
> > (
> >List integer not null references ListHeader,
> >member integer not null references ComplexNumber );
> >
> > This is called a Relational Data Model because, well, you relate
> > things to each other.
> >
> > > -Original Message-
> > > From: sqlite-users-bounces at mailinglists.sqlite.org
> > > [mailto:sqlite-users- bounces at mailinglists.sqlite.org] On Behalf Of
> > > Drago, Wil

[sqlite] Thoughts on storing arrays of complex numbers (Solved)

[Drago, William @ CSG - NARDA-MITEQ]

All,

I always learn something from this group even when the answers don't directly 
solve my problem, and for that I am always thankful. Perhaps I should have 
included more information about what I'm trying to accomplish and/or phrased 
the question differently. That might have saved some time for the folks who 
replied. The problem is solved, but there's more info below if anyone is 
interested.

In this case the problem is very simple: All I have to do is store the data. I 
don't have to search it or perform any computations on it, and portability is 
not an issue as this is Windows only in-house code.

The complex data array, which represents a network analyzer trace (similar to 
the trace on an oscilloscope), is processed by my application to extract the 
pertinent information which is stored in a properly normalized database. Once 
all the useful information has been extracted from the array there is no need 
to keep the array, however, as an extra measure of thoroughness I do keep it 
just in case something was missed and we need to look at it again sometime in 
the future.

Since the data is received from the analyzer as an array of real/imaginary 
pairs (R,I,R,I,R,I,R,I...), 3202 elements total, that's how I will blob and 
store it. This is the simplest way to add it to the database. It's just one 
more field along with all the other data. If I ever need to operate on that 
trace data again it's a simple matter of pulling out of the database and 
un-blobbing it.

Thank you for your suggestions,
--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com



> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of James K. Lowden
> Sent: Friday, April 24, 2015 12:42 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Thoughts on storing arrays of complex numbers
>
> On Fri, 24 Apr 2015 13:37:40 +0000
> "Drago, William @ CSG - NARDA-MITEQ"  wrote:
>
> > I'm trying to avoid re-inventing the wheel. Is there a best or
> > generally accept way to store arrays of complex numbers?
>
> A table in First Normal Form has no repeating groups.  That means no
> row has an array of any kind.  Arrays in the relational model are
> represented in columns, one element per row.
>
> A column whose type is nonrepeating is said to be "atomic", but that's
> something of a convention.  An atom is supposed to be indivisible, but
> we can take substrings of string, parts of dates, and exponents of
> floating point numbers.  So nonrepeating datatypes aren't necessarily
> atomic, exactly.  They're just not repeating.  ;-)
>
> The question of your complex array then comes down to two apects: how
> to represent the complex number, and how to represent the array.  The
> case for the array is simple: keep one complex number per row.  The
> case for the "divisible atomic" complex number depends on a choice: how
> you want the DBMS to treat the components of the complex type.
>
> The most general solution -- and therefore probably the best one -- is
> to keep the real and complex component each in its own REAL column.
> That lets you sort and select the complex numbers using SQLite's built-
> in functions without limitation.  For example, if we call those
> components "a" and "b", you could say,
>
>   select * from T where "a" between 1.0 and 2.0
>
> Such a table would be
>
>   create table complex_array
>   ( name TEXT not null
>   , ordinal INTEGER not null
>   , real_part REAL not null
>   , imaginary REAL not null
>   , primary key( name, ordinal )
>   );
>
> That's the textbook solution on a DBMS without user-defined types.
>
> An alternative is to conceive of the complex type as a datatype, and
> represent it in SQLite as BLOB or TEXT.  That severely limits SQLite's
> ability to compare and select the values, although that limitation can
> be somewhat alleviated with user-defined functions e.g.,
>
>   select * from T where "C" = complex(1.0, -0.5)
>
> If 1) you're not interested in letting the DBMS inspect the data, and
> 2) you have some convenient C function to losslessly convert your
> complex type to a string or bit-string, then a single-column
> representation might be more convenient.
>
> --jkl
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and 

[sqlite] Thoughts on storing arrays of complex numbers

[Drago, William @ CSG - NARDA-MITEQ]

All,

I'm trying to avoid re-inventing the wheel. Is there a best or generally accept 
way to store arrays of complex numbers? I'm considering the following:

I could have two blob fields in my table. One for the real parts and one for 
the imaginary. (I don't like this.)
Or, I could use a single blob field and concat the real and imaginary parts 
into one long blob. (I like this.)
Or, I could store pairs in the blob 
(realimaginaryrealimaginaryrealimaginaryrealimaginary). (I like this.)

Or maybe there's a real nifty way to handle complex numbers that I haven't 
thought of.

Thanks,
--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


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

[sqlite] How do non-SQLite DBMS communicate?

[Drago, William @ CSG - NARDA-MITEQ]

James, Howard, and Simon,

Thank you for your replies. They were very helpful and gave me the information 
I needed.

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of James K. Lowden
> Sent: Thursday, April 23, 2015 1:57 AM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] How do non-SQLite DBMS communicate?
>
> On Wed, 22 Apr 2015 22:28:57 +0000
> "Drago, William @ CSG - NARDA-MITEQ"  wrote:
>
> > When using SQLite the application program accesses the SQLite DBMS
> via
> > its .dll file.
>
> The DLL is a function-call library.  A function is a named bit of code.
> To "call a function" is to jump to that named bit of code.  The SQLite
> functions are part of your application.  That means they execute in
> your process's virtual address space.
>
> > When using something like Oracle Express (a local DBMS) the
> > application program is communicating with Oracle Express via some
> sort
> > of network protocol even though there's no network involved.
> > What is that called?
>
> See "loopback" in wikipedia.  Oracle is a separate process from yours;
> you application communicates with it via TCP/IP.  If the Oracle process
> resides on a machine other than the one your process is running on, it
> connects to that machine with TCP/IP (probably using a hostname and a
> port number).  If the Oracle process is running on the same machine as
> yours, your process connects to it, still using TCP/IP, but via the
> loopback network, usually by the name "localhost".
>
> From the point of view of Oracle and your process, there *is* a
> "network involved" either way.  In the case of the loopback address,
> the network is emulated by the operating system.  Many parts are
> missing (wires, for one, at least ones you can see) but the ends look
> the same to both players.
>
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] building SQLite DLL with Visual C++

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Jay Smith
> Sent: Thursday, April 23, 2015 3:47 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] building SQLite DLL with Visual C++
>
> Thank you Adam for responding to my post. I have the windows binaries
> downloaded.
>
> At this point I am just following the instructions in the book. And I
> really am not sure what I need the dll for.
>
> Here's the scenario. I have created a program in vb2012. The program
> stores less than 20 fields of data. I am currently saving the data in a
> html format. I just recently discovered SQLite. I am now in the process
> of changing over to a database to store data. I studied SQL and Oracle
> 10 years ago. I have almost completed the database for the project.  My
> problem is how to integrate the SQL db into my VB program.

Your subject line says C++, but the line above says VB, so is your program 
written in C++ or VB?
If it's VB just get SQLite with NuGet (Tools > NuGet Package Manager, then 
search for System.Data.SQLite Core).

-Bill


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

[sqlite] How do non-SQLite DBMS communicate?

[Drago, William @ CSG - NARDA-MITEQ]

All,

I'm not sure how to ask this question.

When using SQLite the application program accesses the SQLite DBMS via its .dll 
file. When using something like Oracle Express (a local DBMS) the application 
program is communicating with Oracle Express via some sort of network protocol 
even though there's no network involved. What is that called?

My reason for asking is, I'm writing a short white paper describing my use of 
SQLite (to encourage other engineers where I work to use it too) and I'm trying 
to explain the differences between SQLite and other local database systems. So, 
SQLite databases are accessed via .dll where as other local databases run a 
server that is accessed via ???

Can anyone help me fill in those question marks?
Thanks,

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


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

[sqlite] Hot journal file won't go away

[Drago, William @ CSG - NARDA-MITEQ]

Never mind. It was my own stupidity causing the problem.

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com



> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Clemens Ladisch
> Sent: Tuesday, April 21, 2015 4:30 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Hot journal file won't go away
>
> Drago, William @ CSG - NARDA-MITEQ wrote:
> > What is the recommended procedure for clearing a hot journal file?
>
> Just opening the database should be enough.
>
> > I used to do a "SELECT..." but since I upgraded from
> > System.Data.SQLite
> > 1.0.93.0 to 1.0.96.0 that doesn't work anymore.
>
> If the rollback of the interrupted transaction fails, it should not be
> possible to even read from the database.
>
> Are you sure that the journal is still hot?  Might the journal mode be
> something other than "delete"?
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Hot journal file won't go away

[Drago, William @ CSG - NARDA-MITEQ]

All,



What is the recommended procedure for clearing a hot journal file? I used to do 
a "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"

and "SELECT name FROM sqlite_master WHERE type='index' ORDER BY tbl_name" but 
since I upgraded from System.Data.SQLite 1.0.93.0 to 1.0.96.0 that doesn't work 
anymore.



Thanks,

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


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

[sqlite] SQLite to SQL Server

[Drago, William @ CSG - NARDA-MITEQ]

Thanks for the tip. I've used dump before to move data around between SQLite 
databases. I should have realized that I could have used it to move data to SQL 
Server.

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin
> Sent: Monday, April 13, 2015 6:24 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite to SQL Server
>
>
> On 13 Apr 2015, at 10:38pm, Drago, William @ CSG - NARDA-MITEQ
>  wrote:
>
> > Is there a convenient way to transfer data from SQLite to SQL Server?
> I'm presently writing code to do a row by row transfer of all my
> tables, but the thought crossed my mind that maybe there's a better
> way.
>
> If you're just doing it once, use the SQLite shell tool to '.dump' the
> database to a text file of SQL commands, then read the text file into a
> fresh database in SQL Server.
>
> You may have to do some minor editing of the text file to make it
> conform to SQL Server's notion of SQL rather than SQLite's notion of
> SQL.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] SQLite to SQL Server

[Drago, William @ CSG - NARDA-MITEQ]

All,

Is there a convenient way to transfer data from SQLite to SQL Server? I'm 
presently writing code to do a row by row transfer of all my tables, but the 
thought crossed my mind that maybe there's a better way.

Thanks,
--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


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

[sqlite] Transpose selected rows into columns

[Drago, William @ CSG - NARDA-MITEQ]

Jim,

This works quite well. Thank you.

And thanks to all others who replied.

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Jim Morris
> Sent: Tuesday, April 07, 2015 10:07 AM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Transpose selected rows into columns
>
> You might try
>
> select SerialNumber, V0, V5, V5-V0
> from
> (select SerialNumber,
> max(case Stim when 'V0' then Resp else null end) V0,
> max(case Stim when 'V5' then Resp else null end) V5 from MyTable
> group by SerialNumber)
>
>
>
> On 4/7/2015 6:58 AM, Drago, William @ CSG - NARDA-MITEQ wrote:
> > Igor,
> >
> > Your solution works well. What I can't figure out is how to
> efficiently create a column representing V5-V0.
> >
> > SerialNumber |  V0   |  V5  | Vdiff
> > -|---|--|---
> > 123  |  0.2  |  0.6 |  0.4
> >
> >
> > This is what I'm using, but it takes twice as long:
> >
> > select SerialNumber,
> >
> > max(case Stim when 'V0' then Resp else null end) V0,
> > max(case Stim when 'V5' then Resp else null end) V5,
> >
> > (max(case Stim when 'V0' then Resp else null end) -
> > max(case Stim when 'V5' then Resp else null end)) Vdiff
> >
> > from MyTable group by SerialNumber;
> >
> >
> > There must be a more efficient way. (I tried V5-V0 and assigning
> > intermediate values to variables but got nothing but errors.)
> >
> > Thanks,
> > --
> > Bill Drago
> > Senior Engineer
> > L3 Narda-MITEQ
> > 435 Moreland Road
> > Hauppauge, NY 11788
> > 631-272-5947 / William.Drago at L-3COM.com
> >
> >
> >
> >> -Original Message-
> >> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> >> users-bounces at mailinglists.sqlite.org] On Behalf Of Igor Tandetnik
> >> Sent: Friday, March 27, 2015 3:20 PM
> >> To: sqlite-users at mailinglists.sqlite.org
> >> Subject: Re: [sqlite] Transpose selected rows into columns
> >>
> >> On 3/27/2015 11:48 AM, Drago, William @ CSG - NARDA-MITEQ wrote:
> >>> I want the rows containing V0 and V5 to become columns like this:
> >>>
> >>> SerialNumber |  V0   |  V5
> >>> -|---|---
> >>> 123  | 0.136 | 0.599
> >>> 124  | 0.126 | 0.587
> >>> 125  | 0.119 | 0.602
> >> select SerialNumber,
> >> max(case Stim when 'V0' then Resp else null end) V0,
> >> max(case Stim when 'V5' then Resp else null end) V5 from MyTable
> >> group by SerialNumber;
> >>
> >> --
> >> Igor Tandetnik
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users at mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and
> any attachments are solely for the use of the addressee and may contain
> information that is privileged or confidential. Any disclosure, use or
> distribution of the information contained herein is prohibited. In the
> event this e-mail contains technical data within the definition of the
> International Traffic in Arms Regulations or Export Administration
> Regulations, it is subject to the export control laws of the
> U.S.Government. The recipient should check this e-mail and any
> attachments for the presence of viruses as L-3 does not accept any
> liability associated with the transmission of this e-mail. If you have
> received this communication in error, please notify the sender by reply
> e-mail and immediately delete this message and any attachments.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

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

[sqlite] Transpose selected rows into columns

[Drago, William @ CSG - NARDA-MITEQ]

Simon,

Your solution almost works. It returns a lot of duplicate rows and I don't know 
how to fix that. I'm using what Igor posted.

Thanks,
--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com



> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin
> Sent: Friday, March 27, 2015 12:20 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Transpose selected rows into columns
>
>
> > On 27 Mar 2015, at 3:48pm, Drago, William @ CSG - NARDA-MITEQ
>  wrote:
> >
> > SerialNumber | Stim  |  Resp
> > -|---|-
> > .|  .|   .
> > .|  .|   .
> > 123  |  V0   |  0.136
> > 123  |  V1   |  0.201
> > 123  |  V2   |  0.297
> > 123  |  V3   |  0.311
> > 123  |  V4   |  0.408
> > 123  |  V5   |  0.599
> > 124  |  .|   .
> > 124  |  .|   .
> >
> >
> > I want the rows containing V0 and V5 to become columns like this:
> >
> > SerialNumber |  V0   |  V5
> > -|---|---
> > 123  | 0.136 | 0.599
> > 124  | 0.126 | 0.587
> > 125  | 0.119 | 0.602
>
> SELECT T.SerialNumber, J0.Resp, J5.Resp FROM myTable AS T
>   JOIN MyTable AS J0 ON (J0.SerialNumber = T.SerialNumber AND
> J0.Stim = 'V0')
>   JOIN MyTable AS J5 ON (J5.SerialNumber = T.SerialNumber AND
> J5.Stim = 'V5');
>
> This will be much faster if you have an index as follows or some
> equivalent:
>
> CREATE INDEX m_SerStim ON myTable (SerialNumber, Stim);
>
> Note, I have not tried the above code, it's just what I thought of.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Transpose selected rows into columns

[Drago, William @ CSG - NARDA-MITEQ]

Igor,

Your solution works well. What I can't figure out is how to efficiently create 
a column representing V5-V0.

SerialNumber |  V0   |  V5  | Vdiff
-|---|--|---
123  |  0.2  |  0.6 |  0.4


This is what I'm using, but it takes twice as long:

select SerialNumber,

   max(case Stim when 'V0' then Resp else null end) V0,
   max(case Stim when 'V5' then Resp else null end) V5,

   (max(case Stim when 'V0' then Resp else null end) -
   max(case Stim when 'V5' then Resp else null end)) Vdiff

from MyTable group by SerialNumber;


There must be a more efficient way. (I tried V5-V0 and assigning intermediate 
values to variables but got nothing but errors.)

Thanks,
--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com



> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Friday, March 27, 2015 3:20 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Transpose selected rows into columns
>
> On 3/27/2015 11:48 AM, Drago, William @ CSG - NARDA-MITEQ wrote:
> > I want the rows containing V0 and V5 to become columns like this:
> >
> > SerialNumber |  V0   |  V5
> > -|---|---
> > 123  | 0.136 | 0.599
> > 124  | 0.126 | 0.587
> > 125  | 0.119 | 0.602
>
> select SerialNumber,
>max(case Stim when 'V0' then Resp else null end) V0,
>max(case Stim when 'V5' then Resp else null end) V5 from MyTable
> group by SerialNumber;
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Transpose selected rows into columns

[Drago, William @ CSG - NARDA-MITEQ]

All,

Say I have a table that looks like this:


SerialNumber | Stim  |  Resp
-|---|-
.|  .|   .
.|  .|   .
123  |  V0   |  0.136
123  |  V1   |  0.201
123  |  V2   |  0.297
123  |  V3   |  0.311
123  |  V4   |  0.408
123  |  V5   |  0.599
124  |  .|   .
124  |  .|   .


I want the rows containing V0 and V5 to become columns like this:

SerialNumber |  V0   |  V5
-|---|---
123  | 0.136 | 0.599
124  | 0.126 | 0.587
125  | 0.119 | 0.602


I am presently using two SELECT statements as follows:


SELECT SerialNumber, Resp AS V0 WHERE SerialNumber = '123' AND Stim = 'V0'

SELECT SerialNumber, Resp AS V5 WHERE SerialNumber = '123' AND Stim = 'V5'


And then reformatting the results using C# code before outputting to .csv file.

Is there a way to do everything in SQL? I looked this up online and found some 
pretty complicated examples. Is it really that hard?

Thanks,
--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


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

[sqlite] Searching for interfacing info with Liberty Basic

[Drago, William @ CSG - NARDA-MITEQ]

And this:

http://www.libertybasicuniversity.com/lbnews/nl106/SQLite.htm

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com



> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of cmassar
> Sent: Monday, March 16, 2015 4:55 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] Searching for interfacing info with Liberty Basic
>
> I've been using Liberty Basic 4.04 as a hobby language for about twelve
> years now. In that time I never programmed anything that needed a real
> database. Now I have that need and I would like to use sqlite because
> of its many advantages.
>
> I know enough about (relational) databases and how they work but not
> how to use them from within LB.  I can't figure out what the DLL calls
> are like - mostly which parameters, their sequence  and their
> definitions. Can anyone help me to obtain this information? I would
> like to work directly in the sqlite dll but if a wrapper is available
> I'll be satisfied if I can work with indexes and foreign keys.
>
> Thanks all in advance.
>
> Cor
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Searching for interfacing info with Liberty Basic

[Drago, William @ CSG - NARDA-MITEQ]

This is old, but may still be useful to you:

http://libertybasicuniversity.com/lbnews/nl105/sql.htm

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com



> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of cmassar
> Sent: Monday, March 16, 2015 4:55 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] Searching for interfacing info with Liberty Basic
>
> I've been using Liberty Basic 4.04 as a hobby language for about twelve
> years now. In that time I never programmed anything that needed a real
> database. Now I have that need and I would like to use sqlite because
> of its many advantages.
>
> I know enough about (relational) databases and how they work but not
> how to use them from within LB.  I can't figure out what the DLL calls
> are like - mostly which parameters, their sequence  and their
> definitions. Can anyone help me to obtain this information? I would
> like to work directly in the sqlite dll but if a wrapper is available
> I'll be satisfied if I can work with indexes and foreign keys.
>
> Thanks all in advance.
>
> Cor
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Documentation typo

[Drago, William @ CSG - NARDA-MITEQ]

On this page:

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

Under the heading  1.4 Multiple Result Rows  the word "of" is missing between 
the words "instead" and "peaches" in the third sentence.

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


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

[sqlite] When to disambiguate column names in queries?

[Drago, William @ CSG - NARDA-MITEQ]

Ryan & Dave,

Thank you for the replies. It's nice to know my thinking is on the right track.

Regards,
--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of R.Smith
> Sent: Monday, March 16, 2015 1:26 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] When to disambiguate column names in queries?
>
>
> On 2015-03-16 06:16 PM, Drago, William @ CSG - NARDA-MITEQ wrote:
> > All,
> >
> > Some of my Select statements are pretty long and I'm starting to
> think it'd be a good idea to always include table names of columns
> instead of just when they are not unique. This would make the Select
> statements longer, but perhaps easier to understand if the reader knows
> where each column is from.
> >
> > Any thoughts on this? I realize something like this can be highly
> subjective, but I'm wondering if there's a generally accepted practice
> in the SQLite world.
>
> I won't try to imagine this applies to the SQLite or any other SQL
> world, but I've had good success (In terms of always understanding
> queries that needs to be referred back to after some months and
> legibility to others plus zero SQL engine confusion or incorrect
> queries) when using very short aliasing as a standard.
>
> I've started the practice out of necessity using MS SQL where automated
> queries by default append the table-name to every column which made it
> almost illegible and fills up the screen with nonsense text making the
> query hard to read from among the riffraff.  (People using MS Query via
> Excel might be familiar with this example).
>
> A typical Query offered by some designer might start out like this:
>
> SELECT PorMasterHeader.POrder, PorMasterHeader..EntryDate,
> PorMasterHeader.CLientCode, AprClients.ClientName, PorMaster.Lines,
> PorMasterDetail.LineNo, PorMasterDetail.StockCode,
> PorMasterDetail.Price, PorMasterDetail.OrderQty,
> (PorMasterDetail.OrderQty*PorMasterDetail.Price) AS TotCost
> FROM [SAPSystemCompanyAX].[dbo].[PorMasterHeader] PorMasterHeader,
> [SAPSystemCompanyAX].[dbo].[PorMaster] PorMaster,
> [SAPSystemCompanyAX].[dbo].[PorMasterDetail] PorMasterDetail,
> [SAPSystemCompanyAX].[dbo].[AprClients] AprClients
> WHERE PorMasterDetail.POrder=PorMasterHeader.POrder AND
> PorMaster.POrder=PorMasterHeader.POrder  AND
> PorMasterDetail.LineNo>1AND
> AprClients.ClientCode=PorMasterHeader.Client
> ORDER BY AprClients.ClientName, PorMasterHeader.POrder,
> PorMasterDetail.LineNo
>
>
>
> which is a wall-of-text mess...
> Making use of nothing more than short clear aliases and better
> indentation fixes it to read like this:
>
> SELECT PH.POrder, PH..EntryDate, PH.CLientCode, CL.ClientName,
> PM.Lines, PD.LineNo,
> PD.StockCode, PD.Price, PD.OrderQty, (PD.OrderQty*PD.Price)
> AS TotCost
>FROM [SAPSystemCompanyAX].[dbo].[PorMasterHeader] PH,
> [SAPSystemCompanyAX].[dbo].[PorMaster] PM,
> [SAPSystemCompanyAX].[dbo].[PorMasterDetail] PD,
> [SAPSystemCompanyAX].[dbo].[AprClients] CL
>   WHERE PD.POrder=PH.POrder  AND PM.POrder=PH.POrder AND
> PD.LineNo>1
> AND CL.ClientCode=PH.Client
>   ORDER BY CL.ClientName, PM.POrder, PD.LineNo
>
>
> which is legible to all, works always exactly (no column name is left
> to
> ambiguity) and easy to understand and is DB-Engine independent.
>
> The best part being, the more convoluted the query, the larger the
> space-saving.
>
> Note: It also helps to have a bit of convention in place, such as
> ALWAYS aliasing [AprClients] to CL which will make you "see" the
> meaning of a query faster over time - but be careful, it may happen
> that someone else aliases some other table to CL and if you don't check
> it, might end up spending hours chasing obscure bugs.
>
> Good luck,
> Ryan
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subj

[sqlite] When to disambiguate column names in queries?

[Drago, William @ CSG - NARDA-MITEQ]

All,

Some of my Select statements are pretty long and I'm starting to think it'd be 
a good idea to always include table names of columns instead of just when they 
are not unique. This would make the Select statements longer, but perhaps 
easier to understand if the reader knows where each column is from.

Any thoughts on this? I realize something like this can be highly subjective, 
but I'm wondering if there's a generally accepted practice in the SQLite world.

Thanks,
--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


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

[sqlite] Question about ON DELETE CASCADE

[Drago, William @ CSG - NARDA-MITEQ]

Thank you for your replies, Mr. Smith and Mr. Slavin.

I made the changes and all is well. Grateful for the assistance...

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin
> Sent: Monday, March 09, 2015 6:19 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Question about ON DELETE CASCADE
>
>
> On 9 Mar 2015, at 9:11pm, R.Smith  wrote:
>
> >> If so, do I have to create a new database and repopulate it or is
> there a way to edit the existing database from the command shell?
> >
> > There is always a way - but it is dangerous.
>
> Mister Smith's method should work perfectly.  You will, of course, work
> on a copy of the database so you have a copy to fall back on if you
> mess it up.
>
> If editing your schema worries you you might like to do something
> safer.  Another method of changing a database schema is to use the
> shell tool to '.dump' the database to a text file.  You can then edit
> the text file to reflect your desired changes, then use the shell tool
> to create a new blank database and '.read' in the modified text file.
>
> The two methods, if used correctly, will end up with the same result.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Question about ON DELETE CASCADE

[Drago, William @ CSG - NARDA-MITEQ]

All,

I want to modify my database so that deleting a row from a parent table will 
delete all dependant rows in all child tables. According to this page:

https://www.sqlite.org/foreignkeys.html

It looks like I have to add ON DELETE CASCADE to the child keys of all child 
tables. For example:

FROM:
DatasetID INTEGER REFERENCES UUT_Info

TO:
DatasetID INTEGER REFERENCES UUT_Info ON DELETE CASCADE

Is this correct?

If so, do I have to create a new database and repopulate it or is there a way 
to edit the existing database from the command shell?

Thank you,
--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


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

[sqlite] SQLite error(5): database is locked in System.Data.SQLite

[Drago, William @ CSG - NARDA-MITEQ]

There is some info here:

http://sqlite.org/threadsafe.html

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com



> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Hick Gunter
> Sent: Wednesday, March 04, 2015 8:45 AM
> To: 'General Discussion of SQLite Database'
> Subject: Re: [sqlite] SQLite error(5): database is locked in
> System.Data.SQLite
>
> Only one writer may be active at any one time. The other(s) will
> recieve an error return status. You can either wait a while and retry
> this in your application or set a busy timeout to handle the "usual"
> cases for you.
>
> -Urspr?ngliche Nachricht-
> Von: Rohit Savaliya [mailto:rohit.savaliya at matrixcomsec.com]
> Gesendet: Mittwoch, 04. M?rz 2015 07:46
> An: sqlite-users at mailinglists.sqlite.org
> Betreff: [sqlite] SQLite error(5): database is locked in
> System.Data.SQLite
>
> Hi all,
>
> I am using System.Data.SQLite in my project.
> I have two threads which are updating database simultaneously.
> I get SQLite error(5): database is locked, System.Data.SQLite error.
>
> Why is it so!. What could be the reason? Does System.Data.SQLite is not
> thread safe?
> Please help me out.
>
> --
> Regards
> Rohit Savaliya
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the use
> of the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the
> sender by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.