0

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.

vishless
  • 829
  • 1
  • 5
  • 28
  • 1
    Looking at the VFP code and what is available in writing MySQL sprocs I would think that leaving it in the data layer and doing it in Ruby would be a easier approach. Either way you need to just identify and document what the VFP code is doing and replicate it. – Alan B Jul 28 '16 at 07:58
  • 1
    Forgot to mention that I've tried doing it in ruby. The problem is that when we load the data to ruby the memory footprint is high. – vishless Jul 28 '16 at 08:39
  • 3
    There is no automatic language converter or trick, you must write the code according to your business needs using MySQL syntax. – Ivan Cachicatari Jul 28 '16 at 12:26
  • 1
    @IvanCachicatari My aim was to gather some ideas on converting a procedural language to set based MySQL query, didn't mean to sound as if I was requesting exact MySQL syntax, I apologize if my question was breeding confusion. – vishless Jul 29 '16 at 05:48
  • 1
    @Vizkrig Foxpro commands are based on current records and opened tables, I'm old school developer I used vfp too, is hard to translate vfp code without sample data or before-after samples, some legacy procedures now can be solved with one single line depending on language. – Ivan Cachicatari Jul 29 '16 at 11:39

1 Answers1

1

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 :)

Missy
  • 1,286
  • 23
  • 52