ODBC 17 For SQL Server upgrade error in TD7.1

[andeni]

I am using TD7.1, Recently, our project upgrade ODBC to ODBC 17 For SQL Server.
1.an Invalid character value for case specification error occurred when converting date/time in the SQL statement.
For example: Call SqlPrepareAndExecute (hSqlGTemp, 'SELECT CONVERT (CHAR (10), DATEADD (DAY, - 1, CONVERT (DATETIME,:sGDateTemp)), 101) INTO: sGDateTemp').
2.In addition, when I use date/time binding variables in SQL, there will be no value or execution errors.
such as: delete from tableA where datetimes=: tempdatetime , tempdatetime type is date/time and value is like this 2022-11-07-02.44.28.75000. this statement Executed, but did not delete any data, despite such data.
3.At the same time, when most stored procedures are executed, an invalid cursor state error will appear, but no cursor is used in the stored procedures .

BTW, there was no error in using old ODBC SQL Server..
Thank you very much. If anyone have any suggestions.

[andeni]

Does anyone have the same problem？

[Steve Leighton]

.
Use a date literal instead of bind vars - its more efficient too.

Also , try a valid SS date format - space after dd .

e.g.
format your Date var before plugging into your Sql

Code: Select all

Set sGDateTemp = SalFmtFormatDateTime ( sGDateTemp , 'yyyy-MM-dd hhhh:mm:ss') 

or in your Sql:

Code: Select all

....where datetimes = \'' || SalFmtFormatDateTime ( sGDateTemp , 'yyyy-MM-dd hhhh:mm:ss') || '\''  

[andeni]

Hi Steve, Thanks for your advise, but the project using date/time bind vars many places,It will be a huge project to modify them.
Do you know any other way or is there have a fix in new version？

[Steve Leighton]

.
Sorry Don't know. We only ever use OLEdB for SQLServer.

But I'm not sure its the driver.
If you're using CONVERT , you need to be sure the value held in your string bind var :sGDateTemp is in the correct format.

It works Ok if sGDateTemp = '2022-11-07 02:44:28:750' .. Note space after yyyy-MM-dd not '-' and Note milliseconds can only be 3 chars long not 5 as your example.
Using your example of '2022-11-07-02.44.28.75000' CONVERT returns an error.

[andeni]

Thanks,Steve. I try to format date/time value like you said before, it work.
And 2022-11-07-02.44.28.75000 this value is from database . i using sql statement like this seletc datetimes into :sGDateTemp from tablea where a=1, the value in database is 2022-11-07-02.44.28.750,but i got 2022-11-07-02.44.28.75000.
As you said,using odedb to connect sql server, can you share a full example to me.
Thank you very much.

[FRBhote]

Yes. I too would like to know how to use OLEDB.

[RainerE]

Hi!

We also use OLEDB and this works fine.
We have migrated our large application developped in the last 25 years.
We used TD 4.2 while migrating to MS SQL Server.
After this we have successfully migrated to TD 7.4.4.

There are some things to keep in mind:

1. Connection
There are two ways to connect to MS SQL Server:
a) SqlCreateSession(hSession, 'Your ConnectionString') and SqlCreateStatement(hSession, hSql)
b) Set SqlUDL = 'Your Connection String' and SqlConnect(hSql)
We use b. But we are limited to one database this way. Otherwise the program does not run stable.
Using a) makes it possible to stable connect to multiple databases, so we use this in some special functions/classes where we need to connect to more than one database.
You can have the whole connection string in 'Your ConnectionString', but due do security reasons you should not include User and Password here but use SqlUser and SqlPassword instead.

Code: Select all

Set SqlDatabase = ''
Set SqlUser = 'YourUser'
Set SqlPassword = 'YourPassword'
Set SqlUDL = 'SQLOLEDB;Data Source=YourServer;Initial Catalog=YourDatabase;Persist Security Info=FALSE;Encrypt=Yes;Extended Properties="CLIENTCURSOR=TRUE;";'
! or MSOLEDBSQL or MSOLEDBSQL.19 instead of SQLOLEDB for more security (must be installed separately)
Set SqlResultSet = TRUE
Set SqlIsolationLevel = 'RL'
Call SqlConnect(hConnection)
Call SqlSetParameter(hConnection, DBP_AUTOCOMMIT, FALSE, '')
Call SqlSetLockTimeout(hConnection, YourTimeout)

2. Table Prefixes
Instead of sysadm., tables and functions and ... need to be prefixes with dbo.

3. Multiuser Update
You can update a row either with 'where rowid = :sRowid' or 'where rowid = 0x' || sRowid.
The column rowid needs to be a column of datatype timestamp (and can have any name).
You will not get an error message after this.
You have to call SqlGetModifiedRows() instead and check, if a row has been modified.

4. Error handling
You have to use backend error handling instead of the normal SQLBase error handling.

5. Aggregate functions
You have to use the names and syntax of SQL Server (aggregate) functions.

6. NULL versus ''
To insert a null value with a bind variable, you have to set sBind = STRING_Null. Set sBind = '' does not work.
We have changed some classes and created triggers for all the other tables to change '' to NULL on insert and update.
And to concatenate strings, you have to use + instead ||.

7. Date and Time
There are some specials regarding date and time.
We use datatype date for date and datetime2 for datetime.
Note that SQL Server does not allow dates less than 01.01.1753, but SQLBase does. We had to correct some data values which have accidently inserted with lower year values.
Instead of SYSDATE/SYSDATETIME you have to use GetDate()
Either use bind variables, or fomat a date/datetime '2022-12-05' / '2022-12-05 14:22:00' (!including the ').

8. TD bugs
There have been some bugs regarding Unicode and some regarding Datetime in TD 7.4.1. We use TD 7.4.4 because of this.

9. reserved words
If you use object names which are reserved words in SQL Server, you need to change them or enclose them in "".
And the names of ALL objects must be unigue.

10. Commit/Rollback without previous change
If you call Commit/Rollback and did not change anything before, SQL Server throws an error. Your have to catch and ignore this error (return TRUE on SqlExecute()).

11. Outer Joins
You have to change SQLBase outer join syntax to the standard (+) -> left/right outer join.

12. Converting data types
Instead of implicitely converting datatypes, you have to manually conver them (number <=> char, ...).

Exists
Neiter '... check exists' nor SqlExists() do work.
Use SqlGetModifiedRows() or SqlPrepareAndExecute() and SqlFetchNext() instead.

Timeout handling
You need to set the timeout for each Sql Handle, not only once.

And so on...


And you have to convert your database, if you do not want to create a new empty one.
We have written a program, which connects to SQLBase and SQL Server, creates the required objects (tables, views, indexes, foreign keys, ...) and copies the data.
The program is adapted to our special needs.

It took us some manmonth to convert the database and our program. But we never missed SQLBase since then

Rainer

[Steve Leighton]

.
Yes . Converting from ODBC to OLEdB should certainly be considered a 'Project' . Either on SQLServer or SQLBase.
But Andeni who asked for the advice, is already running SQLServer via ODBC. Not SQLBase.
So everything is more straight forward, than also having to migrate from SQLBase. ( leave out Rainer's last step - 'Convert your database' ).

But can highly recommend OLEdB and use of SqlUDL to contain the connection string.
It can either contain the name of a .udl file , or the connection string itself.
There are many fine-tunings you can include in the connection string that makes it a lot richer than ODBC.
nd also faster response time - especially if you get your Packet size right and cursor setting.
e.g. '....Extended Properties="CLIENTCURSOR=TRUE;SSPROP_INIT_PACKETSIZE=8192"; '
or just '...;Packet Size=8192;...'
Also you don't need a SQL.ini or a ODBC.ini or ODBC config file.

Have been through converting from ODBC to OLEdB a few times - everything Rainer says is spot on and very relevant. esp. steps 1), 6) and 7)

Note that the SQLServer OLEdB driver comes in a couple of versions - the latest being 'MSOLEDSQL.19' ( comes with SS 2019 ) ,
but we found using 'MSOLEDBSQL.1' driver ( comes with SS 2017 ) works perfect for any version of SS.
'SQLOLEDB' driver is waaay out of date by now.

Note for the record that we have had no trouble with SQLBase sites -
Native or OLEdB connection, and we have some BIG databases running on SQLBase .

[andeni]

Hi RainerE, Thanks.
Hi Steve, Thanks.
When I try to using above coding to connect sql Server. It error in sqlconnenct(),the error is :[DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.
Because the sql server database server is disable TLS1.0 ,1.1,only enable TLS 1.2 .
we using SQL Server ODBC with version 10, everything is fine， but when we do the operation like above . It can't not connect database. also will show the SSL error.
so we upgrade the ODBC Driver with SQL Server 17.It can connect the database ,but will show the error that I first post.
If using Oledb will show the SSL error，We are back to the origin.
Do you know any other option? or anyone know that?
Thanks for your Help.

[RainerE]

Hi,
you try to use SQLOLEDB, correct?
The old SQLOLEDB, which is part of Windows itsself, does not support TLS 1.2
Download and install MSOLEDBSQL V18 and use this instead.
https://www.microsoft.com/de-de/downloa ... x?id=56730
You can use MSOLEDBSQL.19 V19 instead, if you want. But V18 works fine as Steve told, too.

@Steve:
We also used SQLBase 9.0.1 together with TD 4.2 for many years without problems. Our application connected to 3 databases each of size 80GB at the same time.
This was running very stable with a Windows service written with TD 4.2 running 7 days a week 24 hours (and which hat jobs to maintain the database (backup logs, backup database, quick check database, update statistics, reorganize database once a week, check database once a week) and several Windows clients.
We did not get SQLBase 11.0.1 work because of a bug, which was accepted by Gupta (Mike Vandine), but fixed in SQLBase 11.5.
We did not get SQLBase 11.5 work, it was not running stable on our server (Mike Vandine was involved and did not get it work, too).
So we stayed at SQLBase 9.0.1 up to Windows Server 2016 (with a little trick to install the latest PTF).
Then we migrated to MS SQL Server because this was on of the standard databases in the environment of our customer. Now our customer itself maintains the database and updates the database server and SQL Server service.

Rainer

[FRBhote]

So the server IP has to be specified?

We connect to different servers at different locations (at different times) with the same program. ODBC has the server details.

Any way out?

[FRBhote]

BTW, our programs run on both Sqlserver and Sqlbase. We use substitutes so a lot of work is saved.

To convert an Sqlbase statement to execute on SSMS, we have a small utility which does the trick. Both attached.

[RainerE]

So the server IP has to be specified?

The Server Name or IP has to be specified in the OLEDB connection string, yes.

We connect to different servers at different locations (at different times) with the same program. ODBC has the server details.
Any way out?

You can connect to multiple servers at the same time using OLEDB.
But you can only connect to one database using SqlUDL and SqlConnect().
If you want to connect to more than one databases (on one ore more servers), you can still use SqlUDL and SqlConnect() for the first database. For all other databases you must use SqlCreateSession() and SqlCreateStatement() instead. Otherwise this will not run stable. It may work, but we got problems with this.

BTW, our programs run on both Sqlserver and Sqlbase. We use substitutes so a lot of work is saved.

This way will not work for OLEDB because OLEDB does not use a sql.ini.
We thought of implementing a class which replaces parts of SQL statements depending on the database server used (SQLBase, SQL Server).
But this is somehow complex and does not do all tricks (see 3) in my long answer).
Because we do not need SQLBase access any more, we decided to check and change all SQL statements.

[qoute}To convert an Sqlbase statement to execute on SSMS, we have a small utility which does the trick.[/quote]

This utility does only a few parts of the trick. What about outer joins? What about other functions, which have a different syntax in SQL Server?

Regards,
Rainer

[FRBhote]

The utility works 90 % of the time. And outer joins are identical in both anyway.

Unfortunately, if I have to change the connexion string each time I access a customer's database, it is unviable. Viable for my customers, but not for me.

A file with the IP, I suppose is possible, but it will mean selecting the server first and then the relevant database.