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

[GB]

System.Data.SqlTypes.SqlDecimal is specific to the SQL Server provider 
and thus the SQLite provider doesn't know how to handle it. Try using 
System.Decimal as a more generic approach. If you need to be portable 
across providers, you will be better off using classes from 
System.Data.Common anyway.

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

Re: [sqlite] Foreign key error...

[Ken Wagner]

Keith,

"this does not allow the same track on multiple albums" with the same trackno, 
but a different trackno seems to work. Thus results cannot be guaranteed valid?

Ken


On 01/08/2017 06:57 AM, Keith Medcalf wrote:

On Sunday, 8 January, 2017 05:05, Ken Wagner  wrote:


Keith,

Ahh, better to be specific and avoid simplistic assumptions.

For foreign keys which is better: 'trackerartist' or 'artistid' in the
track file? Does it matter? Is it personal preference?

It is a matter of personal preference.  Personally, I use the same name for the 
same data, and do not add useless prefaces, and usually do not preface the id 
with the table name.  For example:

create table artists (
  id integer primary key,
  name text collate nocase unique
);
create table albums (
  id integer primary key,
  name text collate nocase unique,
  artistid integer references artists
);
create table tracks (
  id integer primary key,
  seq integer,
  name text collate nocase,
  artistid integer references artists,
  albumid integer references albums,
  unique (albumid, seq)
);

select albums.name as albumname,
albumartists.name as albumartist,
tracks.seq as trackno,
tracks.name as trackname,
trackartists.name as trackartist
   from albums, tracks, artists as albumartists, artists as trackartists
  where tracks.artistid = trackartists.id
and tracks.albumid = albums.id
and albums.artistid = albumartists.id;

Of course, this does not allow the same track on multiple albums.  For that you 
need another table to do the N:M mapping:

create table artists (
  id integer primary key,
  name text collate nocase unique
);
create table albums (
  id integer primary key,
  name text collate nocase unique,
  artistid integer references artists
);
create table tracks (
  id integer primary key,
  name text collate nocase,
  artistid integer references artists,
);
create table albumtracks (
  id integer primary key,
  albumid integer references albums,
  trackid integer references tracks,
  seq integer,
  unique (albumid, seq),
  unique (albumid, trackid),
  unique (trackid, albumid)
);

select albums.name as albumname,
albumartists.name as akbumartist,
albumtracks.seq as trackno,
tracks.name as trackname,
trackartists.name as trackartist
   from albums, albumtracks, tracks, artists as albumartists, artists as 
trackartists
  where tracks.artistid = trackartists.id
and albumtracks.albumid = albums.id
and albumtracks.trackid = tracks.id
and albums.artistid = albumartists.id;


On 01/08/2017 05:46 AM, Keith Medcalf wrote:

... join ... using (column) has nothing whatever to do with foreign

keys.

"FROM a JOIN b USING (c) is "syntactic sugar" (meaning that it is

sweetening and full of calories, but has no nutritional value) for the
expression "FROM a, b WHERE a.c = b.c"

This is so, for example, if you use really long stupid names it saves

considerable space and typing:

SELECT * FROM TheFirstTableToBeJoined JOIN TheSecondTableToBeJoined

USING (TheCommonColumnNameBetweenTheFirstAndTheSecondTable);

-vs-
SELECT * FROM TheFirstTableToBeJoined, TheSecondTableToBeJoined WHERE

TheFirstTableToBeJoined.TheCommonColumnNameBetweenTheFirstAndTheSecondTabl
e =
TheSecondTableToBeJoined.TheCommonColumnNameBetweenTheFirstAndTheSecondTab
le;



-Original Message-
From: sqlite-users [mailto:sqlite-users-

boun...@mailinglists.sqlite.org]

On Behalf Of Ken Wagner
Sent: Sunday, 8 January, 2017 04:04
To: SQLite mailing list
Subject: Re: [sqlite] Foreign key error...

Thanks, Kees,

The message is helpful as a warning.

select artistname, trackname from artist inner join track on
trackartist = artistid;  works just fine.

But isn't the efficiency of 'using (artistid)' more desirable?

Is the use of a 'trackerartist' as the foreign key used because it is
more informative?

I.e., wherever it is seen it shows the track-artist link? But is more
demanding when coding:

   'on trackerartist = artistid' vs 'using (artistid)'

Best or preferred SQLite3 practice for using which foreign reference
style 'trackartist' vs 'artistid'?

Thanks,
Ken


On 01/08/2017 04:47 AM, Kees Nuyt wrote:

On Sun, 8 Jan 2017 04:21:16 -0600, Ken Wagner
 wrote:


Hello SQLusers,

The error below occurs even though the

CREATE TABLE track(

 trackid INTEGER,
 trackname   TEXT,
 trackartist INTEGER,
 *FOREIGN KEY(trackartist) REFERENCES artist(artistid)*
);

statement at https://sqlite.org/foreignkeys.html was observed.

It appears that 'trackerartist' should be named 'artistid'.

SQLite3 CLI results using version SQLite 3.15.2 2016-11-28 19:13:37
bbd85d235f7037c6a033a9690534391ffeacecc8

sqlite> select artistname, trackname from artist inner join track

using

(trackartist);
Error:\ cannot join using column trackartist - column not present in
both tables
sqlite> .tables track
track
sqlite> .schema track
CREATE TABLE track(
 

Re: [sqlite] Foreign key error...

[Ken Wagner]

Yes, thanks.

The 'left join on'  or 'inner join on ' removes the chance of an 
erroneous key linkage.


Also makes sense to pay close attention as to which table is left and 
right.


Ken

On 01/09/2017 06:46 AM, Dominique Devienne wrote:

On Sun, Jan 8, 2017 at 12:46 PM, Keith Medcalf  wrote:


... join ... using (column) has nothing whatever to do with foreign keys.

"FROM a JOIN b USING (c) is "syntactic sugar" ([...]) for the expression
"FROM a, b WHERE a.c = b.c"


Or "FROM a JOIN b ON a.c = b.c".
Or "FROM a INNER JOIN b ON a.c = b.c".

Syntax does matter (to some?) for readability and comprehension of a query
IMHO.

I prefer to keep my WHERE clauses for single-table "filtering",
and rely on JOIN-ON for how two tables "connect" during a join.
(which columns to "thread the needle through" to form/constitute
a "multi-table row" is my personal mental image of a join).

My $0.02. --DD

PS: I myself consider "FROM a, b WHERE a.c = b.c" to be the "legacy"
syntax, best avoided :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] 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

[sqlite] Need help with System.Data.SQLite

[Burtsev , Dmitriy]

Is this a right mail group for  System.Data.SQLite ?

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Burtsev, Dmitriy 
Sent: Thursday, January 05, 2017 9:08 AM
To: 'sqlite-users@mailinglists.sqlite.org'
Subject: [sqlite] Need help with System.Data.SQLite.SQLiteDataReader and 
NUMERIC(5, 5) column

Hello

I got this error:
Exception calling "WriteToServer" with "1" argument(s): "The given value of 
type SqlDecimal from the data source cannot be converted to type decimal of the 
specified target column."

How to reproduce.

1.   On SQLite execute this statements.
CREATE TABLE FromNum (
id INT,
Num NUMERIC(5,5) NULL);
INSERT INTO FromNum (id, Num) VALUES (1, .0);

2.  On MS SQL Server 2014 (SP2) execute this statement


CREATE TABLE dbo.ToNum (
id INT NOT NULL ,
Num NUMERIC(5,5) NULL);



I can't copy my PowerShell script in this e-mail. Got error "The message's 
content type was not explicitly allowed". I will try pseudocode.



Add-Type -Path '\\SERVER01\SQL\SQLite\netFx46\System.Data.SQLite.dll'

Create System.Data.SQLite.SQLiteConnection

Connection CreateCommand()

CommandText = "SELECT * FROM FromNum"

System.Data.SQLite.SQLiteDataReader = Command.ExecuteReader()

Create System.Data.SqlClient.SqlConnection
Create System.Data.SqlClient.SqlBulkCopy with 
[System.Data.SqlClient.SqlBulkCopyOptions]::TableLock
bulkCopy.BatchSize = 5000
bulkCopy.DestinationTableName = "ToNum"
bulkcopy.EnableStreaming = true

bulkCopy.WriteToServer(SQLiteDataReader) <-- Got error here

Note: I did run it with System.Data.SqlClient.SqlDataReader as a source. It 
works with NUMERIC(5,5) column.
I did run it with System.Data.SQLite.SQLiteDataReader but without NUMERIC(5,5) 
column. It works.
The problem appears only when I run SQLiteDataReader as a sourse and table have 
NUMERIC(5,5) column.

Powershell version 5, 64 bit
System.Data.SQLite.dll version 1.0.103.0  64 bit .NET Framework 4.6.1

Thank you
Dmitriy Burtsev

This message, and any of its attachments, is for the intended recipient(s) 
only, and it may contain information that is privileged, confidential, and/or 
proprietary and subject to important terms and conditions available at 
http://www.stifel.com/disclosures/emaildisclaimers/. If you are not the 
intended recipient, please delete this message and immediately notify the 
sender. No confidentiality, privilege, or property rights are waived or lost by 
any errors in transmission.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key error...

[Dominique Devienne]

On Sun, Jan 8, 2017 at 12:46 PM, Keith Medcalf  wrote:

>
> ... join ... using (column) has nothing whatever to do with foreign keys.
>
> "FROM a JOIN b USING (c) is "syntactic sugar" ([...]) for the expression
> "FROM a, b WHERE a.c = b.c"
>

Or "FROM a JOIN b ON a.c = b.c".
Or "FROM a INNER JOIN b ON a.c = b.c".

Syntax does matter (to some?) for readability and comprehension of a query
IMHO.

I prefer to keep my WHERE clauses for single-table "filtering",
and rely on JOIN-ON for how two tables "connect" during a join.
(which columns to "thread the needle through" to form/constitute
a "multi-table row" is my personal mental image of a join).

My $0.02. --DD

PS: I myself consider "FROM a, b WHERE a.c = b.c" to be the "legacy"
syntax, best avoided :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE and the like and SIMilarity

[Clemens Ladisch]

Anony Mous wrote:
> In SqLite, LIKE works backwards. It's not case-sensitive, although it
> should be.

The SQL standard specifies that LIKE does comparisons using the
collation of the string values.

SQLite uses NOCASE by default.  You could override the like() function
(which is what PRAGMA case_sensitive_like does), but it is not possible
to get a string's collation from inside a user-defined function.

> I suggest ... that SqLite implement:
> SIM and ISIM

This is too similar to SQL's "SIMILAR TO" operator.


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