(Maybe don't) Replace SQLBase with another database such as MySQL or MS SQLServer

[FRBhote]

If the database is not huge it makes no sense to change from Sqlbase.

Our applications connect to both Sqlbase and Sqlserver. We use Timestamp as the rowid.

The only difficulty is that the Decode is a Case and has to be provided for in the TD code. Also the Choose & Find functions require a function written in Sqlserver.

Everything else works with a substitute.

Substitute=ROWID,TIMESTAMP
substitute=@UPPER,UPPER
substitute=@DECODE,DECODE
substitute=@LOWER,LOWER
substitute=@left,left
substitute=@LEFT,LEFT
substitute=@SUBSTRING,SUBSTRING
substitute=@mod,MOD
substitute=@MOD,MOD
substitute=@nullvalue,ISNULL
substitute=@NULLVALUE,ISNULL
substitute=@right,RIGHT
substitute=@RIGHT,RIGHT
substitute=@ROUND,ROUND
substitute=@CHOOSE,dbo.SCHOOSE
substitute=@CODE,ASCII
substitute=||,+
substitute=@TRIM,dbo.TRIM
substitute=@ABS,ABS
substitute=@LENGTH,LEN
substitute=@SCAN,charindex
substitute=@FIND,dbo.FIND
substitute=SYSTABLES,MsysObjects
substitute=@MID,SUBSTRING
substitute=Mid,SUBSTRING
;substitute=@VALUE,
;substitute=@MID,MID
substitute=@VALUE,VAL
substitute=@DATEVALUE,TO_DATE
;substitute=not null with default,DEFAULT '0' NOT NULL
;substitute=NOT NULL WITH DEFAULT,DEFAULT '0' NOT NULL
substitute=not null with default,NOT NULL DEFAULT '0'
substitute=NOT NULL WITH DEFAULT,NOT NULL DEFAULT '0'
substitute=pctfree,
substitute=PCTFREE,
;substitute=AUTOINCREMENT,IDENTITY(1,1),
;substitute=NUMBER,DECIMAL
;substitute=DATE,DATETIME,
substitute=SYSTABLES,SYS.TABLES
substitute=pctfree 10,
substitute=PCTFREE 10,,

[Steve Leighton]

Igor Ivanovic wrote:

.....The support is really great, I was helped out although sometimes it was not even directly related to OT Gupta products.
Worth every cent I invested in OT Gupta.

+1000.

Igor Ivanovic wrote:

.....SQLServer as I was forced once to use it and for me it was a nightmare being used to work with SQLBase.

ditto. SQLServer is ok, but no way the panecea.

FRBhote wrote:

.....If the database is not huge it makes no sense to change from Sqlbase..

Agree ++. Unsure what's classed as 'huge', but we running a 165Gb SQLBase dB - no issues. Some tables/indexes >5m rows, with no ( not many ) dramas. Maybe UNLOAD takes a while though

[isential]

Thank you very much!

But if I have a product that I want to sell for about 2,000 EUR, I have to add about 1,300 EUR for SQLBase if I need more than one user access at the same time. This makes the product unattractive.

Therefore the question about another SQL database, which is suitable for us in small installations (2-5 users). GLS or not, in our case we have all the business logic in the application: no triggers, no stored procedures, no nothing! We only store data and that's it. Unfortunately, this is where the RowId gets in the way, because we use it extensively.

As soon as we have some more time, we will convert the databases to MySQL and/or SQLServer.

Thanks again!

Translated with www.DeepL.com/Translator (free version)

[RainerE]

Hi,

as told in other treads, we have migrated our TD 4.2 / SQLBase 11 application to a TD 7.4.4 / MS SQL Server 2016 application.
We use the MS OLEDB provider (V18 or V19).

The large application (developped in the last > 25 years) has been migrated successfully and is working without problems.

But we want to simplify error handling in case of lost updates.

With SQLBase we used 'where rowid=:sRowid' for update and delete commands and got an SQL error, if the rowid did not match (the records had been changed by another user in the meantime).

With MS SQL Server and OLEDB we use 'where rowid=:sRowid' or 'where rowid=0x' || sRowid for update and delete commands.
Rowid is a column of datatype timestamp.
After this we call SqlGetModifiedRows() and raise a global event, if 0 rows were modified. Under the global event, we call our SQL error handling as we do under the global SAM_SqlError.
We have written an own function SqlCheckConcurrencyOperation(hSql) for that purpose (which internally calls SqlGetResultSetCount() and raises the global event).
This works perfectly.

But we can forget to call SqlCheckConcurrencyOperation() after calling SqlPrepareAndExecute() or SqlExecute() in case an update or delete with rowid=... is executed.
And we must not call it after executing a select statement or an update/delete without rowid.
So I want to make this better!
I thought about always calling SqlCheckConcurrencyOperation(hSql).
To do so, in SqlCheckConcurrencyOperation(hSql) I need to determine, if the statement executed with hSql is an update/delete with rowid=... or if it is any other kind of statement (select, update/delete without rowid=...).
I thought about calling SqlGetCommandText(hSql) to get the command (which works properly) and then I need to figure out, if it is an update/delete with rowid=...
But this is not so easy and may lead to bugs. An update or delete statement could e.g. have a subselect with rowid=...
=> Simply checking that a statement begins with update or delete and has the text 'rowid=' after the keyword 'where' is not enough. This could find rowid=... in the where-part of a subselect instead of the update or delete statement.
In C# a property of a command is the type of command (which is determined by the command class of the .Net framework).
Does anyone knows a way to easyly determine the type of a SQL statement in TD (if MS OLEDB is used)?

Does anyone has another solution for my situation (it's only a situation, not a problem)?

Does anyone use update/delete triggers with an internal error handling for this?

Kind regards,
Rainer