SQL datatypes - timestamp

Classic Servoy
1

Having successfully connected to an internal MS SQL 2000 database, I started to set up basic tables through Servoy.

I added a column ‘dob’ as type ‘DATETIME’, Allow Null checked, no length entered, to store an individual’s date of birth.

I then checked that the column had been added to the SQL Server database.
It was there as type : ‘TIMESTAMP’, length 8, null value checked.

I created a form in Servoy and added the ‘dob’ field plus name fields which had also been added to the table.

I add a record and then click outside the fields to write the data and the following error was returned :

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Disallowed implicit conversion from data type datetime to data type timestamp, table ‘servoy_test.dbo.player’, column ‘dob’. Use the CONVERT function to run this query. [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Disallowed implicit conversion from data type datetime to data type timestamp, table ‘servoy_test.dbo.player’, column ‘dob’. Use the CONVERT function to run this query.

Anybody give me any pointers as to why this error is being returned if the field types are the same ?

This error is returned whether there is a date entered in the field or not !

2

Most JDBC drivers map the datatypes correctly to corresponding datatypes in the backend. Some drivers don’t do this though. If your database driver does not map datatypes correctly, then you can use the Advanced button to map to the datatype you want.

goto preferences>TAB dbservers and click “advanced”.
In this dialog , select your driver.

You can also look in the help (hit F1 while Servoy is open) and search for “advanced”.
open topic title “DB Servers”

3

Thanks, Maarten

I have tried that option and then restarted Servoy Developer.

Going back into Preferences and Advanced it does not show the MS SQL driver…just the

4

It’s correct that you see select after restart, in the advanced selection you always have to first select the driver you are creating a mapping for. Just make sure that under com.microsoft.jdbc.SQLServerDriver you map datetime to datetime. After setting this restart Servoy and all will work fine. I’ll ask our webmin to add it to the FAQ.

5

Thanks, Jan…Got it

I was confused over the datatype shown in the Advanced dropdown which was showing datetime → timestamp !

Worked first time as soon as I had changed it to : datetime → datetime

I should have checked my ‘Teach yourself SQL’ book first !!!

Very quickly regarding date entry formatting…Can you tell Servoy to accept ANY delimiter as opposed to a single nominated delimiter within prefs ?

6

Harry Catharell:
Very quickly regarding date entry formatting…Can you tell Servoy to accept ANY delimiter as opposed to a single nominated delimiter within prefs ?

No, currently you can’t.