RE: Using SQL Server

Hi "pas" !
	
	We migrate several of our applications from Ifx to SQL-Server, with very few problems. 

	The first thing we had to do was to replace all the references to rowids and serial type within the code of the applications. To solve the serial problem we choose to replaced them by integer type columns and the implementation of a mechanism to put them to work as closely and automatically as possible as the "old" serials, through the use of a new set of tables and functions dedicated to generate and control the sequence of values to set on each row of every table in the application. Off course you can always go through the use of the ENTITY type in SQL SERVER. It works basically like a serial, and you can find more information about it in the SQL-Server documentation. It's perhaps easier way to go. I must say Fourjs provides a set of tools in their toolkit to help you on this task, that work by using triggers. 
	After this done, the rowids were replaced by references to either to those columns or, whenever possible, to the already existing tables primary keys.

	The second major problem was related to the Informix outer joins. We had to review then, to match the prerequisites imposed by Fourjs in order to make them work properly: use table names as prefixes to the column names (table1.column1 = table2.column2), remove unions between select statements using outer joins (we had very few indeed), and to do not use conditions on the outer tables in the where section of the SQL statements. In same cases we had to duplicate the prepares using one version to Informix and a second one to SQL Server since one of our goals was to preserve the functionality of the applications in both DBMS. Some work, but not too much pain...

	The worst nightmare came from transactions: we found that whenever an SQL error occurs SQL-Server automatically roles back the transaction without the knowledge of Fourjs. As result Fourjs interpreter remains convinced there still was a pending transaction when in reality the transaction was already closed! This was a major problem until we figured it out. We solve the problem by centralizing the transactions management in a few functions (one to start them up, other to commit them and a third to roll them back) and by putting a variable to keep the status of the transactions. Then we used a "SELECT @@TRANCOUNT" to inquiry the SQL Server about the number of pending transactions (please do not forget SQL Server does accept nested transactions). Every time the function dedicated to manage transactions is to be called we test the result returned by the select statement and the value of the
  variable dedicated to keep the status of the transactions within the code. When they don!
 't match we execute a prepared "begin transaction" statement that's results on a new transaction on the SQL Server not accounted by the Fourjs and therefore regaining synchronisation between the two.

	A word of caution about Informix MATCHES "something*" keyword and prepared SQL statements, since most probably you will have problems there. Replaced them by SQL Server LIKE "something%" statement or avoid the preparation of this kind of statements: use direct SQL statements instead.

	Another one about UNLOAD and LOAD statements and date formats. Please do not forget SQL SERVER uses a datetime format. We had a few problems here related to the date format in the text files. No big deal really but you will probably have to foresee it.

	Finally we had to convert the code of stored procedures and triggers from Informix to SQL Server, one by one. There is no workaround, I'm afraid!

	And, as far I recall, that was it. Off course we still had one or two surprises in the way, but no big deals indeed. They were easily resolved.  

HIH
Best regards
Nuno Godinho

-----Mensagem original-----
De: [email protected] [mailto:[email protected]]
Enviada: quarta-feira, 29 de Marco de 2006 7:25
Para: [email protected]
Assunto: [fourjs-users] Using SQL Server


Hi Genero developers!
My company is planning to migrate some existing Unix/Informix applications in Windows / MS SQL Server environment. 
Some of them are written in pure I-4GL and some others are already in Genero.
We have experiences from Genero in Unix/Informix and we are pretty satisfied. We don't have tried Genero and SQL Server.
Have you experimented on this environment or already developed stable applications?
Are there any issues you 'd like to share? Which version of SQL Server? What about SQL Server 2005? Are there any difficulties or differences if we want to use stored procedures and triggers (except the fact that we have to migrate Informix SPL code to T-SQL). Genero documents some issues (ODI Adaptation Guide For SQL Server 2000) and I think it is fair to have all these in mind but it seems that we can proceed. Are there any other "hidden" issues?
Please send me your comments or proposals

Regards
PM