SCM

[#1010973] specified cast is invalid - datetime fields

View Trackers | Bugs | Download .csv | Monitor

Date:
2011-01-05 23:04
Priority:
3
State:
Open
Submitted by:
Nick H (thehill)
Assigned to:
Nobody (None)
Npgsql Version:
2.0.11
Category:
Group:
Resolution:
None
Summary:
specified cast is invalid - datetime fields

Detailed description
after upgrading to 2.0.11 i am now seeing lots of errors "Specified cast is not valid.", the problem seems to datetime fields.

I have spent a lot of time investigating this problem and i am out of ideas now.
I have read about the breaking change in 2.0.10 and i am not sure if that is related, but i dont fully understand.
I am continuign to use 2.0.8 at the moment but would like to keep current and more importantly understand the subtlety of what is going on.
I have tried to create the simplest, self-contained scenario to demonstrate the problem.

running against postgresql 9.0.2 but have tried against various versions and there doesnt seem to be any difference.


DDL to create database structure
****************************
CREATE SEQUENCE table1_pk_seq;

CREATE TABLE table1
( pk integer NOT NULL,
ts timestamp with time zone,
CONSTRAINT table1_pkey PRIMARY KEY (pk));
****************************

'Form class - apologies for VB
****************************
Public Class Form2

Dim connstring As String = String.Format("HOST={0};DATABASE={1};USER ID={2};PASSWORD={3};COMPATIBLE={4};UseExtendedTypes=True;", "localhost", "testdb", "postgres", "whatever", "2.0.8.0")
Dim conn As New NpgsqlConnection(connstring)

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

'create the datatable
Dim dt As New DataTable("table1")
dt.Columns.Add("pk", GetType(Int32))
dt.Columns.Add("ts", GetType(DateTime))

'configure the dataadapter
Dim selectcommand As New NpgsqlCommand("select pk,ts from table1", conn)
Dim da As New NpgsqlDataAdapter(selectcommand)
Dim cmdbuilder As New NpgsqlCommandBuilder(da)
da.InsertCommand = cmdbuilder.GetInsertCommand
da.UpdateCommand = cmdbuilder.GetUpdateCommand
da.DeleteCommand = cmdbuilder.GetDeleteCommand

'add some data
For i As Int32 = 1 To 5
dt.Rows.Add({pk(), Now})
Next
da.Update(dt)
dt.AcceptChanges()

'now try and update the data
For Each dr As DataRow In dt.Rows
dr.Item("ts") = Now.AddDays(7)
Next
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'THIS IS WHERE THE PROBLEM LIES
'IN 2.0.8 THIS WILL WORK, BUT IN 2.0.11 IT WILL PRODUCE THE ERROR 'SPECIFIED CAST IS NOT VALID'
da.Update(dt)
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
End Sub

Function pk() As Int32

If conn.State = ConnectionState.Closed Then conn.Open()
Dim cmd As New NpgsqlCommand("select nextval('table1_pk_seq');", conn)
Return CInt(cmd.ExecuteScalar)

End Function

End Class
****************************

It would take me a while to list all of the things i have tried but briefly,
setting comatability in the connection string seems to make no difference.
problem will go away if i set cmdbuilder.ConflictOption.OverwriteChanges() but i dont want to do that, this leads me to believe the problem lies with the parameters that are being set to ensure concurrency


As requested by Francisco i have changed this to timestamp without time zone, b ut there is no change, the same error still occurs

Followup

Message
Date: 2011-04-04 15:41
Sender: Francisco Figueiredo jr.


I didn't receive it :(

I also checked my spam folder.

Are you sending me a zip file with any binary file on it? I noticed that gmail blocks mail which has binary attaches. If possible, send me a 7z file or remove any binary file from you zip.

Also, you can send me a plain text message so we can check if your message is arriving.

Thanks in advance.
Date: 2011-04-04 06:40
Sender: Miłosz Kubański

Hi.

I've just send it again from different email account.
Date: 2011-04-02 00:24
Sender: Francisco Figueiredo jr.


Hi!

I didn't receive your mail :(
Date: 2011-03-28 12:57
Sender: Miłosz Kubański

Have you receive my email with test project?
Date: 2011-03-25 17:41
Sender: Francisco Figueiredo jr.


This is very strange...

You can send it to me:

francisco aaaaaatttt npgsql ddddoottt org
Date: 2011-03-25 15:00
Sender: Miłosz Kubański

Unfortuanteley, I don't have such button. I can make a snapshot but still cant upload it ;).
I'm using the following url to go to this thread: http://pgfoundry.org/tracker/?func=detail&atid=590&aid=1010973&group_id=1000140 I've tried also 3 different browser (chrome, FF, IE) and none of it posses such button.
Maybe I can send it to some email? I can try to upload it on some other server and give you an url but probably I will have such url at monday.
Date: 2011-03-25 14:45
Sender: Francisco Figueiredo jr.

Nope. When logged in just scroll to the bottom. There is a button which you can use to upload a file.
Date: 2011-03-25 14:22
Sender: Miłosz Kubański

I would like to do it, but I don't know how. Do I need some privileges which enable me a upload link/button ?
Date: 2011-03-25 13:19
Sender: Francisco Figueiredo jr.

Hi, Miłosz Kubański

Please, upload the test case so we can give it a try and fix the bug.

Thanks in advance.
Date: 2011-03-25 08:53
Sender: Miłosz Kubański

I've just repeated a test myself using attached dll. The problem still exists.
Date: 2011-03-25 08:45
Sender: Miłosz Kubański

First of all I've reported a bug to our ORM support. They reproduced an error and tried the dll in version 2.0.12. As they said the problem still exists on 2.0.12. If you like I can send you a whole test project to reporoduce a problem. Just unzip it, execute sample db creation script, open solution and execute a test.
Date: 2011-03-24 16:44
Sender: Francisco Figueiredo jr.


Hi!

What happens if you use cvs or the version I uploaded in this bug report? Does it work ok for you?
Date: 2011-03-24 14:22
Sender: Miłosz Kubański

Sorry for a silly question. How can I sent you a test package? I can't see any upload functionality.


Regards,
MiloszeS
Date: 2011-03-24 14:13
Sender: Miłosz Kubański

Hi,

we have a similar problem with "Specified cast is not valid." exception. An error occurs when in the db we have a time field and it's mapped on DateTime object in .Net. The previous version (2.0.10 and older) seems to work ok.

I'll attach a file with tests which help to reproduce a problem.


Best Regards,
MiloszeS
Date: 2011-03-09 00:32
Sender: Nick H

francisco

sorry for such a long delay in getting back on this, been
super busy.

thanks,the updated code solves the problem that i initially
posted, but now i have a new problem with trying to use a
field of type time without time zone

it seems to work if i use a timespan field but not sure what
impact that has for me. In 2.0.8 it works OK with a datetime
field

thanks



Imports Npgsql

Public Class Form2

Dim connstring As String =
String.Format("HOST={0};DATABASE={1};USER
ID={2};PASSWORD={3};", "localhost", "testdb", "postgres",
"******")
Dim conn As New NpgsqlConnection(connstring)

Private Sub Button1_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Button1.Click

NpgsqlEventLog.Level = LogLevel.Debug
NpgsqlEventLog.LogName = "C:\log.txt"
NpgsqlEventLog.EchoMessages = True

If System.IO.File.Exists(NpgsqlEventLog.LogName) Then
System.IO.File.Delete(NpgsqlEventLog.LogName)
End If

conn.Open()

Try
Dim cmd As New NpgsqlCommand("DROP TABLE
table2;", conn)
cmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

Try
Dim cmd As New NpgsqlCommand("DROP SEQUENCE
table2_pk_seq;", conn)
cmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

Try
Dim cmd As New NpgsqlCommand("CREATE TABLE
table2 ( pk integer NOT NULL, tim time without time zone,
CONSTRAINT table2_pkey PRIMARY KEY (pk) ) WITH ( OIDS=FALSE
); ", conn)
cmd.ExecuteNonQuery()
cmd.CommandText = " CREATE SEQUENCE
table2_pk_seq INCREMENT 1 MINVALUE 1 MAXVALUE
9223372036854775807 START 20 CACHE 1; "
cmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

'create the datatable
Dim dt As New DataTable("table2")
dt.Columns.Add("pk", GetType(Int32))

dt.Columns.Add("tim", GetType(DateTime))
'dt.Columns.Add("tim", GetType(TimeSpan))

'configure the dataadapter
Dim selectcommand As New NpgsqlCommand("select
pk,tim from table2", conn)
Dim da As New NpgsqlDataAdapter(selectcommand)
Dim cmdbuilder As New NpgsqlCommandBuilder(da)
cmdbuilder.ConflictOption =
ConflictOption.CompareAllSearchableValues

da.InsertCommand = cmdbuilder.GetInsertCommand
da.UpdateCommand = cmdbuilder.GetUpdateCommand
da.DeleteCommand = cmdbuilder.GetDeleteCommand

If conn.State = ConnectionState.Closed Then conn.Open()

Dim sequencecommand As New NpgsqlCommand("select
nextval('table2_pk_seq');", conn)
'add some data
For i As Int32 = 1 To 5

dt.Rows.Add({CInt(sequencecommand.ExecuteScalar), Now})

'dt.Rows.Add({CInt(sequencecommand.ExecuteScalar),
Now.TimeOfDay})
Next
da.Update(dt)
dt.AcceptChanges()

For Each dr As DataRow In dt.Rows
dr.Item("tim") = Now.AddMinutes(30)
Next

da.Update(dt)
conn.Close()

End Sub
End Class
Date: 2011-02-02 21:11
Sender: Francisco Figueiredo jr.


No problem.

In order to compile it for .net 3.5 you have to right click in project->properties. In the screen which follows you have to change the Target Framework to 3.5 in the Application Tab. In the Build tab you have to remove the NET40;ENTITIES conditional compilation symbols.

After that you will be able to rebuild all and get an Npgsql.dll file :)

I'll compile one here and upload it.
Date: 2011-02-02 20:29
Sender: Nick H

francisco, i did manage to check the project out but was
getting into a few issue with .net framework versions.
i needed 3.5 in the 2010 solution and as c# isnt my weapon
of choice, ususaly vb, i couldnt change it. was changing it
in the obvious spot but it wasnt having it
really busy at the moment as well so not got enough time to
get stuck into it properly
binary would be best if you could

thanks
Nick
Date: 2011-02-02 17:29
Sender: Francisco Figueiredo jr.


Sure!

Check our user manual: http://npgsql.projects.postgresql.org/docs/manual/UserManual.html

Section 2.3 has instructions about how to get code from cvs.

There is a vs.net 2008 and 2010 projects which will create an Npgsql.dll file for you.
If you want, I can send you a compiled binary so you can give it a try.

I hope it helps.
Date: 2011-01-31 21:03
Sender: Nick H

Francisco

thanks for looking at this.
i havent really had any experience of cvs yet, i have always
worked from binaries.
i will see what i can when i get some spare time - see if i
can get my head around it.
i would like to keep up the momentum though.
any simple pointers or a link to somewhere on the web that
would help to fast-track me would be appreciated
Date: 2011-01-28 19:15
Sender: Francisco Figueiredo jr.

I just committed a fix for this bug.

Please, if possible, get the latest cvs code and give it a try.
Date: 2011-01-28 18:20
Sender: Francisco Figueiredo jr.


I got a fix for this. Apparently Npgsql was having problems with RowUpdating event handler. I had to remove code which touch this part. I'm still doing some tests but your test case works ok. As soon as I finish my tests and see if everything is ok I'll commit it.
Date: 2011-01-17 04:31
Sender: Francisco Figueiredo jr.


Hi, Nick!

Thanks for your feedback.

I could reproduce your problem here.

Indeed is a problem with Npgsql when working with parameter values. It is getting lost with dbtype and actual parameter type. I'm checking how I'm gonna fix that.

As soon as I get something, I'll let you know.

Thanks again for feedback.
Date: 2011-01-15 14:28
Sender: Nick H

i have generated the log files fro both 2.0.8 which works
fine and 2.0.11 which genarates the error.
i have also attached a sample c# solution that demonstartes
the error.
c# is not my preferred language but hoppefully it will
demonstrate the problem
Date: 2011-01-13 14:01
Sender: Francisco Figueiredo jr.


Hi, Nick!

Can you enable Npgsql log and send it to me? This would be very helpful too. I tried here and got not errors. I'll do some more tests today and see if I can reproduce your problem.

Thanks in advance.

Attached Files:

Attachments:
testing1.zip
Npgsql.dll

Changes:

Field Old Value Date By
File Added589: Npgsql.dll2011-02-02 21:14fxjr
File Added585: testing1.zip2011-01-15 14:28thehill
Powered By FusionForge