CODEWITHSUNDEEP
mysqlstored-proceduresvisual-foxprofoxproset-based
vishless
vishless

Reputation: 898

Converting Foxpro program to MySQL stored procedure

I am in a process on converting a legacy system to web app using Ruby on Rails and MySQL.

There are few places that I'm stuck at while converting the data layer to MySQL procedures.

Giving a scenario below;

FUNCTION first_function
SELE Table1
REPL Table1.SmaCode WITH SMA(code,HcPc,FromDate)
ENDFUNC

FUNCTION SMA
... Lot of conditions ...
Lookup(param1,param2) * Parameters are based on the conditions above
.. Lot more conditions ....
ENDFUNC

FUNCTION Lookup
temp = Output of select on Check table 
return temp
ENDFUNC

Here SMA is another function which has so many conditions and it also calls another function Lookup. In Lookup function it query a table named Checks, the parameter to Lookup is based on the SMA.

Please see the pastebin of the source code in disucssion, if you need more insight. http://pastebin.com/raw/Hvx3b8zN

How can I go and convert this kind of functions to MySQL procedures?

Edit: I'm looking for insights on this from people who've already done these types of conversions, from procedure oriented languages to set based stored procedures to be exact.

Upvotes: 0

Views: 437

Answers (1)

Missy
Missy

Reputation: 1368

The commentators are all right and I upticked them all. You have to actually write the code but it's not too hard once you get going.

The first thing I do is to examine my code and rewrite all the straightforward things like DELETE FOR .... into DELETE WHERE...

Then I look at my loops and think about how I can treat that data as a set. A lot of times, SCANs can be written as a regular query when you use appropriate JOIN conditions and WHERE conditions. There are a lot of query tools like CASE and subqueries that let you get a lot done with very little code. MySQL allows temporary tables and that can come in very useful. Lookups can often be done with subqueries.

On occasions, I have to use FETCH and WHILE loops but I avoid that as much as possible because it is slow and SQL is set based.

Just get started on the easy stuff and you'll get the hang of it :)

Upvotes: 1

Related Questions