Solved MS SQLServer OLEDB error trapping

General discussion forum about all databases other than SqlBase.
Samad Sodul
Mauritius
Posts: 30
Joined: 29 Aug 2018, 15:21
Location: Mauritius

MS SQLServer OLEDB error trapping

Post by Samad Sodul » 11 May 2020, 06:43

Hi
I can add that the latest OLEDB driver is faster than latest ODBC driver connecting TD63/SQLSERVER 2017.

I run a Payroll Calculation for 250 employees
a) ODBC : 1mn 30 secs
b) OLEDB : 47 secs

I re-worked my libraries which help me to swap from ODBC/OLEDB or OLEDB/ODBC easily.

Thanks Steve for providing me the hint from example OLEDBSample_INSERTandSCROLL_TD63 some months ago.

Cheers
Samad Sodul
Ebene
Mauritius

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 294
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: MS SQLServer OLEDB error trapping

Post by Steve Leighton » 11 May 2020, 09:25

.
Thanks Samad for the OLEDB speed confirmation for SqlServer.
I agree that the latest driver is a lot faster than ODBC. esp. when the SSPROP_INIT_PACKETSIZE is tweaked accordingly.
And for no need for a messy Sql.ini on every client.
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

Samad Sodul
Mauritius
Posts: 30
Joined: 29 Aug 2018, 15:21
Location: Mauritius

Re: MS SQLServer OLEDB error trapping

Post by Samad Sodul » 11 May 2020, 09:53

Dear Steve,
I am not an expert in TD. Only to confirm if I am doing it right for OELDB.

Can you share a sample TD program for OLEDB connecting to SQL Server to catch the SQL Error, ( That is a piece of code for Begin Transaction, DBP_AUTOCOMMIT, Rollback, When SQL Error).

Thanks a lot

Samad

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 294
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: MS SQLServer OLEDB error trapping

Post by Steve Leighton » 13 May 2020, 04:27

Samad said:
Can you share a sample TD program for OLEDB connecting to SQL Server to catch the SQL Error,
Simple OLEDB error trapping example attached
TD_OLEDB_ErrorTrapping.zip
You do not have the required permissions to view the files attached to this post.
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

Samad Sodul
Mauritius
Posts: 30
Joined: 29 Aug 2018, 15:21
Location: Mauritius

Re: MS SQLServer OLEDB error trapping

Post by Samad Sodul » 13 May 2020, 05:19

Thanks a lot Steve for this example.

Best Regards

Samad

a_sivababu
India
Posts: 501
Joined: 10 Mar 2017, 08:46
Location: Chennai,India

Re: MS SQLServer OLEDB error trapping

Post by a_sivababu » 13 May 2020, 11:53

I feel there is no need for sql.ini for any router if the router dll is directly passed to SqlDatabase as it is below.

Set SqlDatabase="ConnectionString:comdll=sqlodb32;dbname=MSSQLDB"

All other required parameters can be set through SqlSetxxxxxx().

I am not sure SetZeroLengthStringToNull is supported to be appended in ConnectionString in SqlDatabase. I have raised ticket on this sometime back but not sure whether this is resolved now.

Thanks
Siva A

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 294
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: MS SQLServer OLEDB error trapping

Post by Steve Leighton » 13 May 2020, 22:27

.
Siva says:
there is no need for sql.ini for any router
Siva Thanks for the tip.
Assume you're referring only to ODBC routers, as unfortunately only works with ODBC - not TCP/IP ( comdll=sqlws32 ) as in the case of SQLBase native router.

But good to know if using odbc.
Thanks
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

a_sivababu
India
Posts: 501
Joined: 10 Mar 2017, 08:46
Location: Chennai,India

Re: MS SQLServer OLEDB error trapping

Post by a_sivababu » 14 May 2020, 03:27

Hi Steve,
I haven not tried for SQLBase but It should work for Sqlora32, Sqlodb32, sqlsyb32,sqlifx32.

In case of SB Native router, I am not sure how clientruntimedir works if you have more than on SB since this parameter can not be set through coding.

Thanks
Siva A

Igor Ivanovic
Site Admin
Site Admin
Croatia
Posts: 1429
Joined: 05 Mar 2017, 12:37
Location: Zagreb, Croatia

Re: MS SQLServer OLEDB error trapping

Post by Igor Ivanovic » 14 May 2020, 04:43

Hi all,

FYI, this works for SQLBase also!
I use it like this:

Code: Select all

 Set SqlDatabase = 'ConnectionString: data source=' || gbl_sWindowsServerName || ';transport=tcpv4;port=2155;servername='|| gbl_sSQLBaseServerName || ';dbname=' || gbl_sDatabaseName'
I couldn't find it in the docs, but was made aware of it by OT support.
For some reason it still does require you to have a generic sql.ini, didn't had time to investigate this, maybe it's because my country settings, or I have a hidden connect buried in my classes before the string was set up in my monstruous app. :D
Igor Ivanovic
Image

Return to “General Discussion”

Who is online

Users browsing this forum: No registered users and 0 guests