Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
7
Erster Beitrag:
vor 6 Monaten, 2 Wochen
Letzter Beitrag:
vor 6 Monaten, 2 Wochen
Beteiligte Autoren:
Arie, Paulo Oliveira, Fabrice Harari, kingdr

wd22 - sqlserver error

Startbeitrag von Arie am 30.01.2018 09:17

Hi,

I have a strange isssue and can't get my fingers on it.
I have micrsoft sql server holding a database with some info. Using sql-manager I can see all info is okay.

When I open a connection from Windev using the OLEDB driver, all dates are treated like full strings so instead of getting 20180130 I'm getting "2018-01-" as a result which is of course a bad date. And the app is throwing all kind of errors.

When using the native sql driver from pcsoft I have no issue at all?!?!
Even when using hfsql control centre to connect to the sqlserver it shows the right info.

Is there some setting I overlooked of what?

Antworten:

Hi Arie,

from what you are describing, I would have a look in the settings of the ODBC client... Or maybe the connection string you are using needs tweaking...

Best regards

von Fabrice Harari - am 30.01.2018 11:16
It's an Enterprise database and you may try out select getdate() directly from Sqlserver Management Studio, I bet you'll get eg 2018-01-30 06:16:37.440 and
all you have to do is to apply select convert(varchar, getdate(),112) to ensure an
absolute answer regardless of any driver as the native does the converstion for you
to facilitate pcSoft standard or go browsing SET DATEFORMAT for detail in sqlserver
default date setting.

Enterprise database, to me, means very traditional (ie no matter what, it has to be like that and you have to eat it, no choice) and needs extra energy to make it a fit.

HTH

King

von kingdr - am 30.01.2018 13:24
When I switch from hOledbSQLServer (which is actually SQLOLEDB) to SQLNCLI11 it also works flawless.
Must be something on by dev-machine, but can't find it. I leave it for now, I can live with the native driver and/or the SQLNCLI11 driver.

Not sure what the differnce is thougt between SQLOLEDB and SQLNCLI11, anyone?

von Arie - am 30.01.2018 13:51
I also have a local sqlexpress database running without problems. Comparing this one with it's 'big brother' I now see that some date-fields are defined as DATE instead of DATETIME in the sqlserver.

Changing that solves the mysterious date errors, which makes sense of course.

Now I have to find out how I created the database and tables in the first place. I remember I did HCreationIfNotFound on one place, but also used the sql generated from the analysis at another place.

von Arie - am 31.01.2018 09:22
There is definitely a difference when creating table using the regular OLEDBSQL driver compared to the native driver

Let's say I have an table decrsibed in the analysis with a text-field, date-field, time-field a 1-byte int-field and a currency field

With SQLOLEDB I get this after HCreation()
varchar(10)
datetime
datetime
smallint
numeric(24,6)

With the native driver I get this
varchar(10)
date
time
tinyint
money

With the sql-script generated from th analysis I get
varchar(10)
datetime
datetime
smallint
money

So what would be the best option to create tables when using an sqlserver. Always using a clean and neat script?

von Arie - am 31.01.2018 09:49
Be carefull with the sqlserver version and sqlncli version.

The date and time types are only valid for sqlserver >=2008
If you use one older version of sqlncli or the sqlserver the only type available is datetime

von Paulo Oliveira - am 31.01.2018 11:19
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.