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

[isential]

I have a fundamental question, which I cannot and do not want to ask in the "official" forum. I can't, because I currently have no "CLS contract" anymore and I don't want to, otherwise I'll be stoned to death there.

We have a TD application (TD 6.2) which is almost 30 years old. The application uses SQLBase, which we would like to replace with MySQL or SQLServer.

Our database has a lot of data, but otherwise it does not use any of the database's major peculiarities. For example, relations are only created by the application in the business logic. Otherwise neither triggers nor stored procedures are used. The only peculiarity is the RowID.

What do I have to pay attention to when using another database like MySQL or MS SQL Server?

How do I connect other databases (ODBC, OLEDB or something else)?

How can the RowID be mapped in other databases? We use it to detect changes to a record from other users.

I am already looking forward to your numerous and helpful answers.

Best regards

René

[a_sivababu]

I am running the application with below tools....

1. Team Developer 7.3 win 32 & Win 64 and also in TD.NET WPF in Desktop
2. TDMobile 2.3 and Mobile applications are deployed in Amazon EC2.
3. PostgreSQL 12 - 64 bit created in intranet and in Amazon RDS with S3 as well and connecting through ODBC.

I was initially using SQLBase for few years then converted to Oracle Express Edition & stayed for few years and then converted to SQL Server Express Edition & stayed for few years and then finally landed in PostgreSQL. you can scale your database vertically and Horizontally without any limitations.

ROWID is not available in PostgreSQL but there is a CTID and UUID data type if you want to store it in database but not sure this will support for you.

if you want to go with commercial then you can explore Oracle or SQL Server. If you want to go with open source then you can go with MySQL or PostgreSQL. No idea about rowid in MySQL and SQL Server as well.

I am sure others in the forum can have better opinion than my word.

Thanks
Siva A

[JonasK]

Our application currently supports Oracle and Microsoft Sql Server, we also had an quite succesfull migration on PostgreSQL. Connection to Sql Server and PostgreSQL is possible via ODBC, then you must edit your sql.ini-file with the suiting connections. The only thing you must pay attention is when using specific database-function (like CONVERT, LENGTH) in your program, these have to be converted.

Sql.ini

Code: Select all

[win32client.dll]
comdll=sqlodb32
comdll=sqlws32

[odbcrtr]
odbctrace=off
odbctracefile=sql.log
remotedbname=MSSQL,dsn=MSSQL

For your ROWID-problem you could edit your code to manually generate a suiting id (or maybe use triggers instead).

[isential]

Thanks a lot! This gives us hope that we could replace the database with another one.

Have a nice weekend!

René

[Steve Leighton]

.

Our database has a lot of data, but otherwise it does not use any of the database's major peculiarities.

So why swap ? As well as SQLBase, I've used Oracle. SqlServer, Informix, DB2 all with TeamDeveloper and none of them are the panecea.
You need a really good reason to swap, otherwise you're just swapping one set of pains for another. Been there done that many times.

SQLBase is terrific if you're using it right !

p.s. ODBC is being deprecated by MS. If you do insist on swapping, then you should use OLE DB as MS are focusing on it over ODBC in the future.

p.p.s. Upgrade your app to TD7.2 - then it will only be <1 year old, not 30.

[isential]

.

Our database has a lot of data, but otherwise it does not use any of the database's major peculiarities.

So why swap ?

For reasons of cost. With few users at the same time SQLBase makes our product disproportionately more expensive. Since we use the database only rudimentarily, we see no technical reason to pay for it. MySQL, PostgreSQL, MariaDB, SQLServer (also Express Edition) etc. are either free of charge or already available at the customer.

As well as SQLBase, I've used Oracle. SqlServer, Informix, DB2 all with TeamDeveloper and none of them are the panecea.
You need a really good reason to swap, otherwise you're just swapping one set of pains for another. Been there done that many times.

Sure, but if you take the costs into account in our case, the other candidates suddenly become very attractive.

SQLBase is terrific if you're using it right !

As I said: We use the database only rudimentarily. Other databases should be able to do that without any problems and that cheaper or even for free.

We have been using SQLBase since the early 90s. Very easy to maintain, but now the costs are pushing us down, because the clients have become price sensitive. They ask themselves (rightly) why others cost nothing or why SQLBase if another database is already available.

p.s. ODBC is being deprecated by MS. If you do insist on swapping, then you should use OLE DB as MS are focusing on it over ODBC in the future.

OLEDB had also declared Microsoft dead and then revived it... ODBC is old, secure, but a standard supported by all known databases. What about ADO.Net or even Entity Framework from within a Team Developer application?

p.p.s. Upgrade your app to TD7.2 - then it will only be <1 year old, not 30.

We have "updated" it about 4-5 years ago from 1.1.2 to 6.2... Why 7.2?

Greetings

René

[isential]

One more question that has come up: How do functions like "SalTblDoUpdate" and "SalTblDoDelete" behave when using another database? If I know it correctly, e.g. SalTblDoUpdate and SalTblDoDelete use the RowID of SQLBase.

[Harald]

For what purpose do you use the rowid? Rowid in SqlBase contains also a timestamp to detect records which were modified by somewhere else after you fetched the record. If you need this feature, you have to create a workaround for this, maybe a column which is filled by a trigger. If you don't need this, it's easy, Oracle has also a "rowid", but it contains only a uniqe record id, and on other brands you can create a column "rowid" with a sequence as default value.

Kind regards
Harald

[Steve Leighton]

.

How do functions like "SalTblDoUpdate" and "SalTblDoDelete" behave when using another database? If I know it correctly, e.g. SalTblDoUpdate and SalTblDoDelete use the RowID of SQLBase

All TD Sql* functions work with what-ever SQL statement you send to it. Either with or without ROWID.

If your SQL relies on ROWID, and you swap to a different dbms, then a column called 'ROWID' needs to be present.
e.g. In the case of SQLServer, you need to create a new artificial 'ROWID' column of type 'rowversion' , which works in a similar way to ROWID, although is not held on the dB as a string, as in SQLBase. So in any 'Where' clause you need to include a conversion something like : "Where ROWID = CONVERT(binary(8),'0x' + :ROWID , 1)"

As I said, in all dB swap cases, one thing leads to another. Believe me.

but if you take the costs into account in our case, the other candidates suddenly become very attractive

Unless you have a serious technical issue with SQLBase, then consider that the cost of a SQLBase GLS ( includes all the upgrades ) could quite posibly be cheaper than swapping to another DBMS, even if it is free - the conversion cost is definatley not. Depending on the complexity of your app of course,

We have "updated" it about 4-5 years ago from 1.1.2 to 6.2... Why 7.2?

TD6.2 is way beyond end-of-life , so if you have any issue with it , it wont be fixed or patched, if you have TD GLS or not.
Many other features / improvements after 6.2.

[isential]

Hi, Harald,

thanks for your comments. Yes, this is exactly what we use the RowID for, to detect if changes have been made after retrieving records.

For what purpose do you use the rowid? Rowid in SqlBase contains also a timestamp to detect records which were modified by somewhere else after you fetched the record. If you need this feature, you have to create a workaround for this, maybe a column which is filled by a trigger.

[isential]

Hallo Steve,

thank you very much!

If your SQL relies on ROWID, and you swap to a different dbms, then a column called 'ROWID' needs to be present.
e.g. In the case of SQLServer, you need to create a new artificial 'ROWID' column of type 'rowversion' , which works in a similar way to ROWID, although is not held on the dB as a string, as in SQLBase. So in any 'Where' clause you need to include a conversion something like : "Where ROWID = CONVERT(binary(8),'0x' + :ROWID , 1)"

Does that mean that the column to be checked MUST be called ROWID and I MUST also do the conversion so that the content matches the real SQLBase ROWID? If I only look at Microsoft's SQL Server, I wouldn't even need to do the conversion, because it's just a matter of checking to see if someone else has changed a record after it was retrieved. But I would want to do the same with MySQL. I hope MySQL offers something similar.

As I said, in all dB swap cases, one thing leads to another. Believe me.

It is certainly not painless, but if you want to serve small customers as well, you have to lower the price. In this case the costs for the database SQLBase.

but if you take the costs into account in our case, the other candidates suddenly become very attractive

Unless you have a serious technical issue with SQLBase, then consider that the cost of a SQLBase GLS ( includes all the upgrades ) could quite posibly be cheaper than swapping to another DBMS, even if it is free - the conversion cost is definatley not. Depending on the complexity of your app of course,

I have the costs, indeed, but only once! Meanwhile, I always have to justify the price to small customers in order to finally not get the order from the customer because of the costs. In the long run it is much more expensive.

We have "updated" it about 4-5 years ago from 1.1.2 to 6.2... Why 7.2?

TD6.2 is way beyond end-of-life , so if you have any issue with it , it wont be fixed or patched, if you have TD GLS or not.
Many other features / improvements after 6.2.

TD 1.12 was also not supported for ages until 2015/2016, but it worked! This cannot be said of many subsequent versions over many years. Constantly evaluating GLS contracts and new versions to see that it does not work. Money spent endlessly for nothing, except time and nerves.

With TD 6.2 we have a state, which we can use at the customer. Here the core application runs, but everything else is outsourced to external programs and libraries (C++/C#, List & Label etc.). Now it's the database's turn - we now have to make money for ourselves and not only for OpenText/Unify/Centura/Gupta...

[Steve Leighton]

.

Does that mean that the column to be checked MUST be called ROWID and I MUST also do the conversion so that the content matches the real SQLBase ROWID?

No. Only if your TD application is referring to a column called 'ROWID' . ( I assume it must be )
In SQLBase column ROWID is data type 'String'.
In SQLServer your column ROWID will be data type 'Rowversion' ( Timestamp ).

You can read a 'Timestamp' into an existing string variable ( in order to compare, or use in a 'Where' clause for example )
But, you cannot use that String variable in a SQLServer 'Where' clause - you would be comparing a String to a Timestamp ( on the dB )which will never work - hence the conversion is required if ever you are using that variable in a SQL Statement
( e.g. "SalTblDoUpdate" , which must be using a 'Where' clause ). Just saying....

************

p.s. Maybe try and negotiate for a special SQLBase deal with Mark Woodford: mwoodfor@opentext.com 'Senior Account Executive at Opentext - Europe'.
Everything is negotiable and he's very approachable and friendly.
Then you wouldn't need to change anything .

************

[Mirko]

Hello Steave,

Here we are mooving from SqlBase to SqlServer for some reasons

Using "Where ROWID = CONVERT(binary(8),'0x' + :ROWID , 1)" with SqlServer works perfectly but if I try and update with an invalid rowid I get no error (and no update) !!! Am I missing something (yes certenly SqlServer experience) ?

TIA
Mirko

[Steve Leighton]

.
ROWID in SQLServer does not actually exist, unless you have manually created a column called 'ROWID' defined as type 'rowversion'.
Having created a column that is outside of the dbms control, it does not know if its valid or not ( unlike SQLBase where ROWID is dbms controlled ), so you will never get a dbms generated error message if this artificial columns value does not exist.

Therefore if you are using ROWID as the key, before you do any update, you need to manually check the ROWID exists e.g.:
'Where CONVERT(VARCHAR(MAX), CONVERT(BINARY(8),' || p_sROWIDColName || '), 2 ) = :YourROWID'

Its a real pain, but SQLServer just doesn't know ROWID as SQLBase does, so my underlying mantra is always:
'Why the hell would you ever want to move from SQLBase to SQLServer in the first place'.

b.t.w. Note to Mr isential:
I believe the perceived ( high-ish ? ) cost of SQLBase is inversely proportional to the number of users who don't bother to buy GLS. Just saying.

isential says:

I currently have no "CLS contract" anymore and I don't want to

[Igor Ivanovic]

Couldn't agree more with Steve.
I have a vague experience with SQLServer as I was forced once to use it and for me it was a nightmare being used to work with SQLBase.
Didn't say SQLServer is no good but I usually set up the SQLBase server to my likings and forget about it. Not the case with other databases...
Oh yeah, being paying the GLS for all of the OT Gupta products for years. There where few times I considered canceling it in the past but didn't and didn't regret it.
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.

Just my 2C...