SQLServer Spatial Date Format problem

[bscott]

Hi all, I'm trying the SqlServer Spatial for the first time and i have some problems with dates

I'm setting an FdoDateTime correctly but on an update or insert i'm getting this error on some dates.

RDBMS: [Microsoft][ODBC SQL Server Driver][SQL Server]La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

It's a French SqlServer

Date that works ok 2007-01-01 Date that failed 2007-01-13 The format here is YYYY-MM-DD

My guess here is that MM and DD are substitued.

Nabble thread :​http://osgeo-org.1803224.n2.nabble.com/SqlServerSpatial-Date-Format-problem-td6283546.html#a6528218

Bruno Scott

[bscott]

Up to date Trunk based patch

[bscott]

I'm coming back on this ticket.

The SqlServerSpatial provider format the dates as YYYY-MM-DD And some localized SqlServer sees that date as YYYY-DD-MM. It is safer to use the ISO format YYYYMMDD (without -)

Excerpt from ​/ How to Set the Day/Month/Year Date Format in SQL Server:

A possible solution to this is to use the ISO Standard format for sending the datetime data to SQL Server, which is "YYYYMMDD" (no separators). Using the ISO format is more "international," and is independent of the default language

I'm joining a new trunk based up to date patch other patches can be ignored

[jng]

In 7558:

Use locale-independent date format for handling dates in SQL Server.

Patch by Bruno Scott.

Fixes #766

[jng]

In 7615:

Merged revision(s) 7534-7563 from trunk:
Fix a problem that the SHP file cannot be read if the SHP file has been opened for writing.

Reason:
If one connection is writing to the SHP file and the other connection connects to the same SHP file for reading and writing,
ShpFileSet::GetSpatialIndex(...) function will not work properly. This function will close the idx file and try to delete it.
But the deletion will not be performed since the idx file is locked at this moment. So "mSSI" member variable holds the invalid
file handler after the CloseFile() function is called. Later, "mSSI" is used to manipulate the index file, an exception is thrown.

Fix:
If the idx file cannot be deleted, "mSSI" member is deleted and set to NULL. Later, "mSSI" member will be created again if its value
is NULL.
........
#931: WMS Connection returns SSL connect error
Currently, the SSL version we set for curl is 1, which is CURL_SSLVERSION_TLSv1. Now we encounter a case that the WMS server is using TLS 1.2, but we get error CURLE_SSL_CONNECT_ERROR when connecting to this server. It works when setting SSL version to CURL_SSLVERSION_DEFAULT. I haven't found the root cause yet.
I submit the change to make it works. We may need to add the SSL version to configuration document later.
........
#932 WFS provider: value of number attribute is changed

When connecting to a WFS server who has an attribute in xsd:long format, the value which is bigger than 2147483647 (e.g. 2555555556) is shown incorrectly in Map3D (2147483647).

It is because we use wtol to convert a string a long number. The max value is 2147483647. To fix the issue, we need to add a new method ToInt64 in class FdoStringP.
........
#932 WFS provider: value of number attribute is changed

Continue to fix #932. Use new API FdoStringP::ToInt64 in method FdoXmlFeatureReaderImpl::GetInt64().
........
#934: Fix test baseline for GenericRdbms test suite

• General: Remove whitespace in various master text files that were failing content comparisons in various test cases
• MySQL:
  • MySqlReaderTest::TestExceptionalCase: Fix incorrect case on SQL query
• ODBC:
  • OdbcConnectionUtil::SetupMySqlDSN: Re-enable MySQL DSN registration. The test ODBC test suite for MySQL passes with the current MySQL Connector/ODBC, so I don't know why DSN registration was disabled.
• SQLServerSpatial:
  • SqlServerConnectTests::do_rdbi_connect: Change the connection string to use Uid/Pwd instead of Trusted_Connection. This allows us to run this particular test case against SQL Server 2017 on Linux. The old test has been #ifdef'd out through a new SQL_SERVER_XPLAT preprocessor macro that is defined by default (meaning Uid/Pwd is now tested by default)

Current suites with known test failures as of this commit is now:

• MySQL: 1 (down from 3)
• ODBC (MySQL): 0 (down from 49)
• PostgreSQL: 2 (down from 4)
• SQLServerSpatial: 2 (down from 6)

........
#934: Fix SchemaMgrTests.testConfigError failing for SQLServerSpatial. The configuration document being loaded (and expecting to fail) failed with a different error because the configuration document was not fed through OverrideBender.xslt (like other test configuration documents). This meant that the test case was trying to load OracleProvider.dll causing the test to fail with a different error than the one expected.
........
The current UpdateVersion.exe bombards UAC prompts every time it is run. Apparently just building a new version from source produces an UpdateVersion.exe that does not have this annoyance.

Fixes #667
........
Fix xalan build failure on Debug|x64
........
Fix output/intermediate path for UnitTestPostGIS to be consistent with other GenericRdbms-based unit test executables.
........
#934: The cause of all the content comparison test failures is due to inconsistency in how UnitTestUtil::PrintException outputs exception messages in release and debug modes. This submission establishes the correct baseline for master content and ensures UnitTestUtil adheres to it in both release and debug.
........
#934: Clean up apply_schema_test6_master.txt it had debug-only exception messages
........
#934: Update LogicalPhysicalFormatter.xslt to scrub out file/line number prefix on error messages. Debug FDO exceptions will carry this information and isn't going through UnitTestUtil::PrintException so they are being written out to schema content, failing the comparison tests.
........
#934: Update LogicalPhysicalFormatter.xslt only scrub out the file/line number if it is actually present in the error message.
........
Modify datastore query to use HAS_DBACCESS to cut off inaccessible databases.

Fixes #933
........
Modify UnitTestUtil::CreateDB to allow creating data stores without FDO metadata tables.
........
Fix SQL Server date columns not appearing in schema.

Patch by Bruno Scott. Unit test by me.

Fixes #875
........
Use locale-independent date format for handling dates in SQL Server.

Patch by Bruno Scott.

Fixes #766
........
Mark as read-only data properties whose mapped SQL Server physical columns are computed.

Includes unit test to verify computed columns come out as read-only data properties.

Fixes #920
........
OGR Provider changes:

• Make sure unit test project builds in Debug|x64
• Add test case to check that DescribeSchema works with VRT data sources
• Fix access violation in insert command discovered running unit tests under Debug|x64. The insert command was using "new OGRFeature" instead of OGRFeature::Create() which meant allocation and destruction was occurring on different heaps.

........
#937:

• Constant-ify "FID" and "GEOMETRY" special property names
• Add macros for:
  • Ensuring an open connection (ENSURE_OPEN_CONNECTION)
  • Throwing an equivalent FDO exception if CPLGetErrorMsg() is a non-empty string after certain GDAL/OGR API calls (CHECK_CPL_ERROR)
• Throw if we have non-empty strings from CPLGetErrorMsg() after enumerating OGR layers and in OgrFdoUtil::ConvertClass()
• Add extra VRT unit tests to test additional VRT data sources and to verify that exceptions are now thrown when describing the schema of various invalid VRT data sources.

........
#937: Include VrtTest in Makefile.am
........
#937: Ensure property names are tilde-fied by the OGR provider as dots in FDO property names are illegal. Add a test VRT that demonstrates the problem and unit tests to verify the fix.
........