[sqlite] System.Data.SQLite: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column

[Burtsev, Dmitriy]

Windows 7 64bit
SQLite version 3.17.0
sqlite-netFx46-static-binary-x64-2015-1.0.104.0

Note: The code is working correctly with Devart ODBC driver for SQLite 2.1.4
The problem is with System.Data.SQLite

How to reproduce:
ON SQLite
Create database N:\SqLite\outStream.db

CREATE TABLE FromNum (id int NOT NULL, Num numeric(5, 5));
INSERT INTO FromNum (id, Num) VALUES (1, .0);

On SQL Server

CREATE TABLE [dbo].[ToNum](
[id] [int] NOT NULL,
[Num] [numeric](5, 5) NULL,
PRIMARY KEY CLUSTERED
([id] ASC) );

PowerShell script:

[string]ToTableName = "SERVER.Test.dbo.ToNum"
[string]FromFileName = 'N:\SqLite\outStream.db'

[string[]]ttname = ToTableName.Split(".")
[string]ServerName = ttname[0]
[string]dbName = ttname[1]
[string]schema = ttname[2]
[string]DestinationTable = ttname[3]

sqlString = "SELECT * FROM FromNum"
con = New-Object -TypeName System.Data.SQLite.SQLiteConnection -ArgumentList 
"Data Source=$FromFileName"
#$con = New-Object -TypeName System.Data.Odbc.OdbcConnection -ArgumentList 
"DSN=SqLite;Database=N:\SqLite\outStream.db;Direct=True" this is Devart ODBC

con.Open()

SourceSQLCommand = con.CreateCommand()
SourceSQLCommand.CommandText = sqlString

DestinationConnectionString = "Data Source=$ServerName;Initial 
Catalog=$dbName;integrated security=false"

try
{
reader = SourceSQLCommand.ExecuteReader()
bulkCopy = New-Object ("System.Data.SqlClient.SqlBulkCopy") 
DestinationConnectionString, 
([System.Data.SqlClient.SqlBulkCopyOptions]::TableLock -bor 
[System.Data.SqlClient.SqlBulkCopyOptions]::UseInternalTransaction)
bulkCopy.DestinationTableName = DestinationTable
bulkcopy.EnableStreaming = true;
bulkCopy.BatchSize = 5 #The number of rows in each batch sent 
to the server
bulkCopy.BulkCopyTimeout = 0 #the number of seconds before a 
time-out
bulkCopy.WriteToServer($reader) 
#copy all rows to the server
}
catch
{
ex = _.Exception
Write-Error ex.Message
}
finally
{
reader.Close()
con.Close()
}

Error Message:

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

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

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

[Burtsev , Dmitriy]

I try both "SELECT 1,0;" and "SELECT 1, cast(0 as real)"?
The same 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."

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hick Gunter
Sent: Wednesday, January 11, 2017 11:29 AM
To: 'SQLite mailing list'
Subject: Re: [sqlite] Need help with System.Data.SQLite

What is the result if you exchange your currently executed statement to "SELECT 
1,0;" ? Or to "SELECT 1, cast(0 as real)"?

The effect of declaring a NUMERIC(5,5) field in SQLite is that it's affinity 
becomes NUMERIC. The value .0 thus becomes the integer 0. The other legal 
values (.1 to .9) are all converted to real (double) floating point 
numbers, each to the closest possible approximation (or, if the approximation 
is not "close enough", as text !!!).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Burtsev, Dmitriy
Gesendet: Mittwoch, 11. Jänner 2017 15:48
An: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
Betreff: Re: [sqlite] Need help with System.Data.SQLite

I didn't assign any value directly.

This is what I did:

1.   On SQLite create source table.
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) create empty destination table.

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 SqlConnection, 
[System.Data.SqlClient.SqlBulkCopyOptions]::TableLock
SqlbulkCopy.DestinationTableName = "ToNum"
Sqlbulkcopy.EnableStreaming = true

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

This code is working with NUMERIC(11,0), NUMERIC(3,2) , NUMERIC(5,3) but not 
working with NUMERIC(5,5).

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of GB
Sent: Wednesday, January 11, 2017 2:25 AM
To: SQLite mailing list
Subject: Re: [sqlite] Need help with System.Data.SQLite

SQLite does not really care about precision and scale. See 
https://sqlite.org/datatype3.html for more information.

The culprit lies in the use of SqlDecimal which cannot be used by 
System.Data.SQLite directly and has no implicit conversion to something 
understandable by System.Data.SQLite (for more information about SqlDecimal see 
https://msdn.microsoft.com/en-us/library/xaktx377(v=vs.110).aspx).

However, an explicit conversion exists, so an explicit cast to System.Decimal 
would work. Maybe you could try this at the Point where you assign the Value 
(more information about the explicit operator can be found here: 
https://msdn.microsoft.com/en-us/library/xhbhezf4.aspx).

Also have a close look at what is being fed into the other numeric columns. I 
doubt these are SqlDecimals too.

Burtsev, Dmitriy schrieb am 10.01.2017 um 15:33:
> Thank you for response.
>
> It looks like we are on different pages here. Let me start from the beginning.
>
> We are moving data between SQLite database and Microsoft SQL Server. At this 
> time we are using Excel files but we run into some Excel limitation.
> I am trying to change Excel files to SQLite database files.
>
> The test table has several NUMERIC type columns. We have NUMERIC(11,0), 
> NUMERIC(3,2) , NUMERIC(5,3). Our code works fine until we add NUMERIC(5,5) 
> column.
> It looks like the problem is not with NUMERIC type in general, but only when 
> precision is equal to scale.
>
> 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."
>
>
> -Original Message-
> From: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of GB
> Sent: Tuesday, January 10, 2017 2:08 AM
> To: SQLite mailing list
> Subject: 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 

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

[Burtsev , Dmitriy]

I didn't assign any value directly.

This is what I did:

1.   On SQLite create source table.
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) create empty destination table.

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 SqlConnection, 
[System.Data.SqlClient.SqlBulkCopyOptions]::TableLock
SqlbulkCopy.DestinationTableName = "ToNum"
Sqlbulkcopy.EnableStreaming = true

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

This code is working with NUMERIC(11,0), NUMERIC(3,2) , NUMERIC(5,3) but not 
working with NUMERIC(5,5).

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of GB
Sent: Wednesday, January 11, 2017 2:25 AM
To: SQLite mailing list
Subject: Re: [sqlite] Need help with System.Data.SQLite

SQLite does not really care about precision and scale. See 
https://sqlite.org/datatype3.html for more information.

The culprit lies in the use of SqlDecimal which cannot be used by 
System.Data.SQLite directly and has no implicit conversion to something 
understandable by System.Data.SQLite (for more information about SqlDecimal see 
https://msdn.microsoft.com/en-us/library/xaktx377(v=vs.110).aspx).

However, an explicit conversion exists, so an explicit cast to System.Decimal 
would work. Maybe you could try this at the Point where you assign the Value 
(more information about the explicit operator can be found here: 
https://msdn.microsoft.com/en-us/library/xhbhezf4.aspx).

Also have a close look at what is being fed into the other numeric columns. I 
doubt these are SqlDecimals too.

Burtsev, Dmitriy schrieb am 10.01.2017 um 15:33:
> Thank you for response.
>
> It looks like we are on different pages here. Let me start from the beginning.
>
> We are moving data between SQLite database and Microsoft SQL Server. At this 
> time we are using Excel files but we run into some Excel limitation.
> I am trying to change Excel files to SQLite database files.
>
> The test table has several NUMERIC type columns. We have NUMERIC(11,0), 
> NUMERIC(3,2) , NUMERIC(5,3). Our code works fine until we add NUMERIC(5,5) 
> column.
> It looks like the problem is not with NUMERIC type in general, but only when 
> precision is equal to scale.
>
> 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."
>   
>   
> -Original Message-
> From: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of GB
> Sent: Tuesday, January 10, 2017 2:08 AM
> To: SQLite mailing list
> Subject: 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
> 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
___
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

[Burtsev , Dmitriy]

Thank you for response. 

It looks like we are on different pages here. Let me start from the beginning.

We are moving data between SQLite database and Microsoft SQL Server. At this 
time we are using Excel files but we run into some Excel limitation. 
I am trying to change Excel files to SQLite database files.

The test table has several NUMERIC type columns. We have NUMERIC(11,0), 
NUMERIC(3,2) , NUMERIC(5,3). Our code works fine until we add NUMERIC(5,5) 
column.
It looks like the problem is not with NUMERIC type in general, but only when 
precision is equal to scale.

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."
 
 
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of GB
Sent: Tuesday, January 10, 2017 2:08 AM
To: SQLite mailing list
Subject: 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
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] 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

[sqlite] Need help with System.Data.SQLite.SQLiteDataReader and NUMERIC(5, 5) column

[Burtsev , Dmitriy]

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