Re: [sqlite] Need help with System.Data.SQLite
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...
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( trackid INTEGER, trackname TEXT,
Re: [sqlite] Foreign key error...
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
> -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
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...
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
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