Request for Advice

[cdmmaui]

Hello Everyone,

I am looking to convert a very mature product (25 years old) using DBFCDX to MS SQL. I decided on MS SQL over MySQL as a survey of customers and integration requests preferred MS SQL.

I have 433 programs and 177 DBFs. I want to convert LISTBOXES to XBROWSE then handle the data conversion and the data management (Insert, Update, Delete).

I need to complete this conversion in less than 90 days.

What are your recommendations for handling this conversion.

Thank you in advance for your advice.

Sincerely,

[Gale FORd]

I know this isn't what you are asking for, but why not use Advantage Database Server.
You can still use SQL commands on any new code if you want but very little of your current code would need to be modified.
I converted a complete dispatch system in a few days. Server is very simple to install.

[nageswaragunupudi]

I have 433 programs and 177 DBFs. I want to convert LISTBOXES to XBROWSE then handle the data conversion and the data management (Insert, Update, Delete).

From my experience, I would sincerely suggest this order:

1. Database and table design adopted to MSSQL. Because you know all your tables and their relationships this should not take much time
2. Data migration. Very easy with the tools / functions provided by FWH
3. Browse and reports. I can assure that this is the EASIEST part of all. XBrowse is the easiest to program with inbuilt add/edit/delete and reporting support. We can finish around 10 browses comfortably in a day.

90 days is very comfortable time for a programmer who is already aware of the data structures, relations and the main business logic of the application. Assume full concentration on this project only.

Please do not start with conversion of listboxes to browses with DBFs. You will end up redoing them after migration of data.

Added:
I request you to spend some time on database design. There should be a lot of scope to optimize on the design because the present software is 25 year old. It is also possible to avail MSSQL features like calculated columns, triggers, stored procedures, relational constraints, etc. to include most of the business logic.

[driessen]

Gale,

You mean Advantage Database Server by Sybase?
You know what the price is?

Thanks.

[nageswaragunupudi]

If it is client-server software both ADS and MSSQL are priced. SQLEXPRESS and ADS local client are free and between them SQLEXPRESS is far better.

In any case Mr Otriz already surveyed and took a decision.

[Rick Lipkin]

Darrell

Ms Sql Server is a good choice .. About 6 ( or more ) years ago I took my Time and Reporting system I created for SC State government ( over 3k users ) and migrated it to Sql Server.

This app used dbfcdx and was distributed to some 50 servers across the state to capture the local employees hourly and funding information... meaning I had local data in over 50 locations and had to distribute my .exe to each server.

I had one of the Dba's 'challenge me' to take that Enterprise application and migrate the data ( all the data ) to Sql Server. To sweeten the pot .. the DBA gave me total Ownership control and table creativity of that database on one of the Agency's centralized Sql Servers .. How could I turn that deal down ?

That is when I stumbled into ADO and had to re-think development in terms of SQL and not .dbf. If I recall, you are fairly comfortable with ADO and the biggest hurdle you will have is modifying your code to use recordsets vs table data as in .dbf... and that was the same challenge I was faced with.

Enrico helped me a lot and I became comfortable and proficient in my SQL mindset and syntax. The bottom line as Rao mentions .. you already have expert familiarity with the data and the application which is BIG plus. Your hurdles will be migrating the data and re-writing your .dbf table calls to ADO. As far as the dbf to sql conversion .. that is easy and I will be glad to share some code. xBrowse makes listbox creation much easier in comparison to the FW standard listbox class.

As Rao again mentions .. look at your data and remember you will need a Primary Key for each table .. not necessarily related to another table .. just a unique row indicator. I would not get too bogged down in setting relations at the table level as it is much easier to manage at the code level.

90 days should not be a problem .. once you learn how to migrate over your .dbf table calls to recordsets, the quicker the migration will happen... it just 'clicked' with me.

If you need help, this is the best place to get syntactical advice and suggestions .. Press on my friend !! Sql Server and ADO is a good choice.

Rick Lipkin

[Gale FORd]

Yes, it is a product of Sybase/SAP now. I think we purchased 100 users for around $4000.
But the installation was a snap. All of the data files remained the same since they were dbf/cdx. We started in compatibility mode so the old software could run on the same data at the same time as the new client/server version was running. I can still run all of the same software if I want, like R&R report writer, Foxpro (still need to be carfull), etc.

They have clients for everything. You can still use it with connection strings and SQL commands from web/java/whatever if you want.
It is truly feature rich with data dictionary, triggers, stored procedures, backup, etc.

[elvira]

I´m studing and learning ADO, because with same code you can use Access, MySQL, SQL, Oracle, ...

[cdmmaui]

Hello,

Thanks to everyone for their advice!

I have one question about handling several MS SQL statements. If I want to insert a record in to tableA then update a balance in tableB, do I need perform the following each item or there a more efficient way to handle this?

cSqlIns := "INSERT INTO tableA (serial, amt) VALUES ('" + cSerial + "'," + nAmt + ")"
oSql:=TOleAuto():New("ADODB.Recordset")
oSql:Open( cSqlIns, xSQL )

cSqlUpd := "UPDATE tableB SET balance=balance+" + nAmt + " WHERE serial='" + cSerial + "'"
oSql2:=TOleAuto():New("ADODB.Recordset")
oSql2:Open( cSqlUpd, xSQL )

[nageswaragunupudi]

Normally such updates are handled through triggers on the transaction table. Triigers are to be written in TransactSQL for MSSQL. This is efficient and error-free approach.

If we do not want to learn T-SQL, triggers,etc and want to deal with ADO alone:
Here is the way.

1. For executing statements preferred way is to use oCn:Execute() but not opening Recordsets

2. We need to nest the insert and update opeartions inside BEGIN and COMMIT Transactions.

Eg:
oCn := <Open connection>

lInserted := .f.
oCn:BeginTrans()
TRY
oCn:Execute( cSqlIns )
oCn:Execute( cSqlUpd )
oCn:CommitTrans()
lInserted := .t.
CATCH
oCn:RollBackTrans()
END
if lInserted
? "Insert success"
else
? "Insert fail"
endif

Btw open recordsets with connection strings is a less efficient way than opening with connection object.

[reinaldocrespo]

Darrel;

I'm inclined to think that you have decided on Ms-SQL only out of ignoring ADS. The whole thing would be much simpler if you go the ADS route. You won't have to make any changes to your code (hardly). With time you will be able to change some navigational code as well as reporting code to SQL. AND BEFORE anyone speaks about price - I find ADS 100 times less expensive than MS-SQL.

On your 2nd question, multiple SQL statements may separated by semicolons and then executed. You may refer to these as "SQL scripts". Here is one such (multi-statement) script I'm currently executing against an ADS server. You will find several Inserts, Updates, Merge as well as sql code syntax:

Code: Select all | Expand

   //This sql will fetch and post records from remit into payfile 
   //after that, it sets isPosted field on remits table to true
   //to avoid it being posted more than once.
   //The reason I'm Disabling triggers is to make posting faster 
   //while avoiding the saving of new balances info on the claim
   //as these should not be considered into the audit-trail 4/2/2013 1:37:40 PM
   //
   ::cPostToRemitSQL := ;
   "DECLARE tbl CURSOR;                                     \n"+;
   "DECLARE tmptbl CURSOR;                                  \n"+;
   "DECLARE nDeduct NUMERIC( 10, 2 );                       \n"+;
   "DECLARE nCoins NUMERIC( 10, 2 );                        \n"+;
   "DECLARE nPatAdj NUMERIC( 10, 2 );                       \n"+;
   "DECLARE nInsAdj NUMERIC( 10, 2 );                       \n"+;
   "DECLARE nStatus INTEGER ;                               \n"+;
   "                                                        \n"+;
   "EXECUTE PROCEDURE sp_DisableTriggers( NULL, NULL, FALSE, 0 );\n"+;
   "OPEN tbl AS SELECT r.[Check], r.Insurance, r.Chkdate,   \n"+;
   "                   r.account, r.ClaimKey, r.BillCode,   \n"+;
   "                   r.Srv_date, r.Rcv_date, r.Deposit_date,   \n"+;
   "                   r.amt_paid, r.adjusted, r.deduc, r.icn,   \n"+;
   "                   r.ins_assg, r.adj_reason,                 \n"+;
   ;//if patient has been charged any amount, either as a straight-out
   ;//deductible or in the form of an adjustment, then [billedDeduc] 
   ;//will not be zero.   
   "              s.pat_charge + s.pat_adjust AS [billedDeduc],\n"+;
   ;//"              l.deduct AS [billedDeduc],                \n"+;
   "              s.ClaimKey AS isFoundInSrvTbl,            \n"+;
   "              s.isClosed AS isClosed,                   \n"+;
   "              s.Co_ins AS Co_ins,                       \n"+;
   "              p.ClaimKey AS isFoundInPayTbl             \n"+;
   "              FROM remits r                                 \n"+;
   "     LEFT JOIN service s ON s.ClaimKey = r.ClaimKey     \n"+;
   "     LEFT JOIN prclines l ON l.ClaimKey = r.ClaimKey    \n"+;
   "           AND l.proc_code = r.BillCode                 \n"+;
   "     LEFT JOIN payfile p ON p.ClaimKey = r.ClaimKey     \n"+;
   "           AND p.proc_code = r.billcode                 \n"+;
   "           AND p.recipt_num = r.[check]                 \n"+;
   "           AND p.INS_PAY = r.amt_paid                   \n"+;
   "           AND p.Insurance = r.Insurance                \n"+;
   "         WHERE [check] = '$1$'                          \n"+;
   "               AND r.payer = '$2$'                          \n"+;
   "               AND chkDate = '$3$'                          \n"+;
   "           AND TRIM( r.ClaimKey ) <> ''                 \n"+;
   "               AND isPosted = FALSE ;                       \n"+;
   "                                                            \n"+;
   "BEGIN TRANSACTION ;                                     \n"+;
   "WHILE FETCH tbl DO                                      \n"+;
   "                                                        \n"+;
   "//----------------------------------------------------- \n"+;
   ;//Post remittance entries to payfile
   "  nDeduct = 0.00 ;                                      \n"+;
   "  nPatAdj = 0.00 ;                                      \n"+;
   "  nInsAdj = tbl.adjusted ;                              \n"+;
   "  ncoIns = tbl.ins_assg ;                               \n"+;
   "//----------------------------------------------------- \n"+;
   ;//"//Avoid re-posting same check when re-imported or re-entered\n"+;
   ;//isFoundInPayTbl is being commented because I found that 
   ;//often times a remittance will list the same claim more than once.
   ;//The 1st time it may be positive and the 2nd negative or 
   ;//a combination of these.  Therefore, I'm processing all lines 
   ;//on the remittance even when the claim line already has 
   ;//a payment posted.
   ;//
   ;//" IF /*tbl.isFoundInPayTbl IS NULL AND*/ tbl.isFoundInSrvTbl IS NOT NULL THEN \n"+;
   ;//
   "                                                        \n"+;
   " IF tbl.isFoundInSrvTbl IS NOT NULL THEN                \n"+;
   "                                                        \n"+;
   ;//if deductibles are being ignored when posting, then adjustments to 
   ;//change patient responsibility are not processed.
   ;//also ignore deductible if this claim line was billed with 
   ;//deductible already.
   "  IF $4$ = TRUE AND tbl.deduc > 0.00           \n"+;
   "         AND tbl.Co_ins = ''                   \n"+;
   "         AND tbl.[billedDeduc] = 0.00 THEN     \n"+;
   "     nPatAdj = -tbl.Deduc;                              \n"+;
   ;//"     nInsAdj = tbl.deduc ;                           \n"+;
   "  ELSEIF tbl.Co_ins <> '' AND tbl.deduc > 0.00 THEN     \n"+;
   "     nCoIns = tbl.deduc + tbl.ins_assg ;                \n"+;
   ;//"     nInsAdj = nCoIns + tbl.adjusted;                   \n"+;
   "  END;                                                  \n"+;
   "                                                        \n"+;
   "  INSERT INTO payfile ( ClaimKey, adm_num, Proc_code,   \n"+;
   "                      Insurance, Pay_date,              \n"+;
   "                      last_edit, Rcv_date,              \n"+;
   "                      serv_date, ins_pay,               \n"+;
   "                      ins_adjust, pat_adjust,           \n"+;
   "                      adj_reason, recipt_num,           \n"+;
   "                            co_ins_asg, operator )            \n"+;
   "               VALUES ( tbl.ClaimKey, tbl.Account, tbl.billcode,\n"+;
   "                      tbl.Insurance, tbl.chkdate,           \n"+;
   "                  tbl.Deposit_date, tbl.Rcv_date,       \n"+;
   "                  tbl.Srv_date, tbl.amt_paid,           \n"+;
   "                  tbl.adjusted, nPatAdj,                \n"+;
   "                  tbl.adj_reason, tbl.[Check],          \n"+;
   "                        nCoIns, User() );                     \n"+;
   "                                                               \n"+;
   " END;                                                   \n"+;
   "END WHILE ;                                             \n"+;
   "                                                        \n"+;
   "CLOSE tbl;                                              \n"+;
   ;//reopen remits but this time aggregate payfile entries per claim.
   ;//On this pass we will post payfile aggregates to claims and insert new 
   ;//claims status into claimsstatus table.
   "                                                         \n"+;
   "OPEN tbl AS SELECT ClaimKey, Insurance,              \n"+;
   "                   FileName, Srv_date, ChkDate,      \n"+;
   "                   SUM( amt_paid ) AS amt_paid       \n"+;
   "          FROM remits r                              \n"+;
   "         WHERE [check] = '$1$'                       \n"+;
   "               AND r.payer = '$2$'                       \n"+;
   "               AND chkDate = '$3$'                       \n"+;
   "           AND TRIM( r.ClaimKey ) <> ''              \n"+;
   "               AND isPosted = FALSE                      \n"+;
   "      GROUP BY claimkey, insurance, FileName, Srv_date, chkDate;\n"+;
   "                                                         \n"+;
   "//Update service.adt totals                          \n"+;
   "WHILE FETCH tbl DO                                   \n"+;
   "                                                         \n"+;
   "  OPEN tmptbl AS SELECT ClaimKey,                    \n"+;
   "                        SUM( ifNull( ins_pay, 0.00 ) ) AS ins_pay,   \n"+;
   "                        SUM( ifNull( ins_adjust, 0.00 ) ) AS ins_adjust, \n"+;
   "                        SUM( ifNull( pat_adjust, 0.00 ) ) AS pat_adjust, \n"+;
   "                        SUM( ifNull( co_ins_asg, 0.00 ) ) AS co_ins_asg  \n"+;
   "                   FROM payfile                      \n"+;
   "                  WHERE ClaimKey = tbl.ClaimKey      \n"+;
   "               GROUP BY ClaimKey ;                   \n"+;
   "                                                         \n"+;
   "  IF FETCH tmptbl THEN                               \n"+;
   "     UPDATE service SET ins_paymen = tmptbl.ins_pay, \n"+;
   "                    pat_adjust = tmptbl.pat_adjust,  \n"+;
   "                    ins_adjust = tmptbl.ins_adjust,  \n"+;
   "                    co_ins_asg = tmptbl.co_ins_asg   \n"+;
   "      WHERE ClaimKey = tbl.ClaimKey;                 \n"+;
   "                                                         \n"+;
   "  END;                                               \n"+;
   "  CLOSE tmptbl;                                      \n"+;
   "                                                         \n"+;
   "  nStatus = NULL ;                                   \n"+;
   "  OPEN tmptbl AS SELECT real_amt - (                 \n"+;
   "                        IFNULL( ins_paymen, 0.00 ) + \n"+;
   "                        IFNULL( pat_charge, 0.00 ) + \n"+;
   "                        ifNull( ins_adjust, 0.00 ) + \n"+;
   "                        ifNull( co_ins_asg, 0.00 ) ) AS iBal, \n"+;
   "                        ifNull( ins_paymen, 0.00 ) AS amt_paid \n"+;
   "                   FROM service                      \n"+;
   "                  WHERE ClaimKey = tbl.ClaimKey;     \n"+;
   "                                                         \n"+;
   "  IF FETCH tmptbl THEN                               \n"+;
   "     IF ROUND( tmptbl.iBal, 2 ) = 0.00 THEN             \n"+;
   "        nStatus = 1;                                 \n"+;
   "     ELSEIF tmptbl.iBal > 0.00 AND ROUND( tmptbl.amt_paid, 2 ) = 0.00 THEN \n"+;
   "        nStatus = 2 ;                            \n"+;
   "     ELSEIF tmptbl.iBal > 0.00 THEN              \n"+;
   "        nStatus = 3 ;                            \n"+;
   "     ELSEIF tmptbl.iBal < 0.00 THEN              \n"+;
   "        nStatus = 5 ;                            \n"+;
   "     END;                                        \n"+;
   "  END;                                           \n"+;
   "  CLOSE tmptbl;                                   \n"+;
   "                                                      \n"+;
   "  IF nStatus IS NOT NULL THEN                     \n"+;
   "     MERGE ClaimsStatus ON ClaimKey = tbl.ClaimKey AND \n"+;
   "                           FileName = tbl.FileName AND \n"+;
   "                           rcv_date = tbl.ChkDate   \n"+;
   "      WHEN MATCHED THEN UPDATE SET status = nStatus  \n"+;
   "      WHEN NOT MATCHED THEN INSERT( ClaimKey, insurance,\n"+;
   "                       Status, FileName, Srv_date,   \n"+;
   "                       rcv_date, operator, Data )    \n"+;
   "              VALUES( tbl.ClaimKey, tbl.Insurance,   \n"+;
   "                       nStatus, tbl.FileName,        \n"+;
   "                      tbl.Srv_date, tbl.ChkDate,    \n"+;
   "                      User(), 'Remittance Posting. ' );\n"+;
   "                                                     \n"+;
   "      UPDATE service SET laststatus = nStatus, //All is good \n"+;
   "                         lastActivity = tbl.Chkdate  \n"+;
   "       WHERE ClaimKey = tbl.ClaimKey                 \n"+;
   "         AND ( lastActivity IS NULL                  \n"+;
   "          OR lastActivity <= tbl.Chkdate             \n"+;
   ;//It is possible for 277s responses to be received as an answer to a 
   ;//276 with a "4" status (white flag) and have an 835 with an older 
   ;//date be posted.  Payment status of 1 should take precedence even when
   ;//277 with received status (white flag) has a more recent date.
   "          OR nStatus = 1 );                          \n"+;
   "                                                         \n"+;
   "  END ;                                              \n"+;
   "END;                                                 \n"+;
   ;
   "//----------------------------------------------------- \n"+;
   "//Flag each entry as isPosted = TRUE                    \n"+;
   "UPDATE remits SET isPosted = TRUE                       \n"+;
   " WHERE [check] = '$1$'                                  \n"+;
   "   AND payer = '$2$'                                \n"+;
   "   AND chkDate = '$3$'                                  \n"+;
   "   AND isPosted = FALSE                                 \n"+;
   "   AND EXISTS ( SELECT s.ClaimKey                       \n"+;
   "                  FROM service s                        \n"+;
   "                 WHERE s.ClaimKey = remits.ClaimKey );  \n"+;
   "                                                        \n"+;
   "COMMIT;                                                 \n"+;
   "                                                        \n"+;
   "CLOSE tbl;                                              \n"+;
   "EXECUTE PROCEDURE sp_EnableTriggers( NULL, NULL, FALSE, 0 );\n"
   
 

Hope this helps;

Reinaldo.

[fraxzi]

Hi!

Based on my experienced.. Going to ADS is would be the first choice makes a little changes to your code.. and MSSQL is a lot more expensive and not as easy as ADS to administer.

If you are apt to SQL there are free choices Postgre, MySQL.. etc..

But my company just recently acquired application which uses MSSQL since we bought license from Microsoft I might as well try to build FW App using MSSQL just for the sake of technically compare other than ADS firsthand.

Kind regards.

[nageswaragunupudi]

I am not getting into the main discussion.
I suggest an alternative way of writing SQL statements in our programs.

Instead of writing like this

Code: Select all | Expand

   cSql := ;
   "SELECT FIRST,LAST,CITY,AGE,SALARY " + ;
   "FROM   CUSTOMER                   " + ;
   "WHERE  AGE > 50                   "

   ? cSql
 

we can also write this way:

Code: Select all | Expand

   TEXT INTO cSql
   SELECT FIRST,LAST,CITY,AGE,SALARY
   FROM   CUSTOMER
   WHERE  AGE > 50
   ENDTEXT

   ? cSql
 

I personally feel the second way of writing saves time and the sql script looks natural, the way we write and see when we write in the sql management studio / toad, etc

To handle variables we can use private variables and use macros too.

Code: Select all | Expand

function MakeSql

   local cSql
   
   PARAMETERS cCity, nAge
   
   nAge := cValToStr( nAge )
   
   TEXT INTO cSql
   SELECT *
   FROM   CUSTOMER
   WHERE  CITY = '&cCity' AND AGE > &nAge
   ENDTEXT
   
return cSql
 

If we are using Harbour, we should include "hbcompat.ch"

[reinaldocrespo]

Mr. Rao;

That is an excellent suggestion.

I use a class to manage SQL. The run() method of the class takes care of string-replacing $n$ for strings stored on an array and thus I would think I can do this:

Code: Select all | Expand

oQ := TAdsQuery():New()

TEXT INTO oQ:cSql 
   SELECT * 
     FROM customers
    WHERE city = '$1$'
END TEXT

oQ:aReplaceStrings := { 'New York' }
oQ:Run()
 

Indeed that makes for easier reading.

Thank you for sharing the idea.


Reinaldo.

[nageswaragunupudi]

That's a very nice idea to have such a class.

Suggestion for a small improvement. Method Run can accept parameters. Run method converts parameters into sql notation and calls replacestrings() and then executes. Then we can call

oQ:Run( 'NY', 40 )

Note: Still we can not handle NULLs this way and also can not handle dynamic SQLs. Still this is very convenient.

In my personal library for ADO, I handle Commands the same way. Finally I call
uResult := oCmd:Execute( cCity, nAge, .... )