Solved

Meaning of FoxPro statement

Posted on 2011-02-28
17
865 Views
Last Modified: 2012-05-11
I have these codes in a FoxPro program that I need to convert to Shell script and SQL:

SELE 6
USE 'j:\sdf_log_data\raw_data\daily_fig\daily_fig_' + alltrim(dtos(edate1)) + '.dbf' excl

FOR f = 1 to 7
   fd1 = 'F' + alltrim(str(f))
  SELE 6
       APPEND blank
      REPL fd with fd1 fig with &fd1
NEXT f

full program is attached


Can anyone tell me what is the difference between fd1 and &fd1 in this case.
sdf-log-ftp-prg.txt
0
Comment
Question by:tindavid
  • 5
  • 4
  • 4
  • +2
17 Comments
 
LVL 14

Expert Comment

by:tusharkanvinde
Comment Utility
the & is called macro substitution. Before running the command, the command is evaluated after substituting the variable with the variable value

so

fd1='F1'
replace fig with &fd1

is the same as
replace fig with f1


There should have been a comma in the replace statement. Is the original working

roughly translated it would be something like

select f1 as fd , f1 as fig from SomeTable where SomeWhereClause
union all
select f2 as fd , f2 as fig from SomeTable where SomeWhereClause
union all
......

upto 7

and the result is then inserted into some other table




0
 

Author Comment

by:tindavid
Comment Utility
any difference in "REPL fd with fd1 fig with &fd1" ?
0
 
LVL 14

Expert Comment

by:tusharkanvinde
Comment Utility
"REPL fd with fd1 fig with &fd1

should be

"REPL fd with fd1 , fig with &fd1

0
 
LVL 3

Expert Comment

by:AndrewJen
Comment Utility
A small clarification...

fd1='F1'
replace fig with &fd1

is the same as
replace fig with 'f1'
0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 250 total points
Comment Utility
What do you mean by "shell script"? wscript shell?
Or do you mean an SQL script, porting vfp to SQL Server?


Looking at the attached overall code, this is what this part of it does:
1. Opening a table daily_fig_YYYYMMDD.dbf with YYYYMMDD being the date in edate1
2. Inserting 7 records into that table.
    Field fd is set to f1...f7 in the 7 records, field fig is set to the value of the variables f1...f7 which are set in previous code.

This translates into 7 inserts:
Insert into daily_fig_YYYYMMDD (fd, fig) values ('f1',f1)
Insert into daily_fig_YYYYMMDD (fd, fig) values ('f2',f2)
Insert into daily_fig_YYYYMMDD (fd, fig) values ('f3',f3)
Insert into daily_fig_YYYYMMDD (fd, fig) values ('f4',f4)
Insert into daily_fig_YYYYMMDD (fd, fig) values ('f5',f5)
Insert into daily_fig_YYYYMMDD (fd, fig) values ('f6',f6)
Insert into daily_fig_YYYYMMDD (fd, fig) values ('f7',f7)

I don't know if you also have a table for each date or if you migrated to a single daily_fig table with a date field instead, which would be advisable.

Overall the code is not readable and I'd not migrate it at all but write from scratch via the specifications of what data is needed where. If the code is the specs and there is no additional documentation, then good luck.

Bye, Olaf.
0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
This program can run another 10 years under VFP9 so to convert it to something else seems to be contraproductive...

Maybe concerns about the fox life are forcing you to use Shell script and SQL even when FoxPro language is still good choice for this task... But it is your decision.

The code looks like a Foxbase program partially patched to the OO world. Mixing numeric work areas with named aliases is not good, to use explicit drive and path names in code is also not good and the major rewrite should be considered.

Because f1, f2, f3, ... are variables, the SQL code oferred by tusharkanvinde should rather look like this sample:

INSERT INTO daily_fig_YYYYMMDD (fd, fig)
VALUES ("f1", @f1), ("f2", @f2), ("f3", @f3), ...

Of course, the final format depends on SQL dialect used on your SQL server.

0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
Wow, so many posts... I've been replying to the tushar's one.

Just some clarifications:

1) post ID:35005699 does not reflect alternate syntax because
"REPL fd with fd1 fig with &fd1

is exacltly same as

"REPL fd with fd1 , fig with &fd1


2) post ID:35005755  is NOT correct
fd1='F1'
replace fig with &fd1

is NOT the same as
replace fig with 'f1'

BUT it means
replace fig with f1

3) Olaf is accurate as obviously :-)

0
 
LVL 14

Expert Comment

by:tusharkanvinde
Comment Utility
I always thought replace required a , between the fields. Looks like it is not required.

Tindavid

I agree with Olaf, find out what the program does and rewrite it.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
Thanks, pcelba,

and if tindavid actually is translating to T-SQL translating f1 to f7 variable names into @f1..@f7 is also right. All the previous code setting these variables must also be translated, if that has not yet been done.

Bye, Olaf.
0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
Shouldn't we rather help to convert something from any language to FoxPro?

:-)
0
 
LVL 3

Expert Comment

by:AndrewJen
Comment Utility
re:
2) post ID:35005755  is NOT correct
fd1='F1'
replace fig with &fd1

is NOT the same as
replace fig with 'f1'

BUT it means
replace fig with f1

Mental note to self: do not respond to questions before morning coffee kicks in! ;^)
0
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
Well, in this case you could of course translate this foxpro code to better foxpro code first. Have you downloaded the attachment given in the question, pavel?

Bye, Olaf.

0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
Yes, I did (and saved it as UglyCode.PRG) but the part discussed in this question is close to the end, so tindavid has the work almost done probably.

The most important question now is if the difference between fd1 and &fd1 was explained sufficiently.
0
 

Author Comment

by:tindavid
Comment Utility
Dear All,

What I am doing is to convert FoxPro to be able running in Oracle Linux environment, the best and easies way is convert them into "Korn shell + SQL*Plus" as from our end , we don't want to change the architectual design of these FoxPro program, you can see they are using table name with "YYMMDD" as the suffix for their DBF files (in our case we will have to use Oracle table with YYMMDD suffix as well",  I guess only in this way, we will minimize the change to their application logic. I do appreicate you suggestion of using "YYYYMMDD" as suffix, but this could be changed later on.

Ok, so I have a rough answere from the post:

SELE 6
USE 'j:\sdf_log_data\raw_data\daily_fig\daily_fig_' + alltrim(dtos(edate1)) + '.dbf' excl

FOR f = 1 to 7
   fd1 = 'F' + alltrim(str(f))
  SELE 6
       APPEND blank
      REPL fd with fd1 fig with &fd1
NEXT f

fd with fd1 --> means fd will have the value of "F1", "F2", ....
fig with &fd1 --> means fig will have the address/point of 'F1'

from Olaf's answere and code:

This translates into 7 inserts:
Insert into daily_fig_YYYYMMDD (fd, fig) values ('f1',f1)
Insert into daily_fig_YYYYMMDD (fd, fig) values ('f2',f2)
Insert into daily_fig_YYYYMMDD (fd, fig) values ('f3',f3)
Insert into daily_fig_YYYYMMDD (fd, fig) values ('f4',f4)
Insert into daily_fig_YYYYMMDD (fd, fig) values ('f5',f5)
Insert into daily_fig_YYYYMMDD (fd, fig) values ('f6',f6)
Insert into daily_fig_YYYYMMDD (fd, fig) values ('f7',f7)


Now I have to ask what is the actual value of f1,f2,f3 ......  as these f1,f2, f3 ... are now a variable
from the original program.

Reason that I am asking this question is that we are the converter only, the customer do not have a proper FoxPro developer to answer my question. and I am about to convert many of this type of program.

Thank you folks for your help

David
0
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
From your code find every line setting fx variables, eg f variables are all inited to be 0:

	f1 = 0
	f2 = 0
	f3 = 0
	f4 = 0
	f5 = 0
	f6 = 0
	f7 = 0
	f8 = 0
	f9 = 0

Open in new window


right afterwards:
****** Planet 3 & IV ******
	?" Planet 3 & IV (SDF-)"
	SELE 1
	USE '&sdata1' SHARED
	f1=reccount()

Open in new window


So f1 is a reccount, which is as SELECT @f1=Count(*) From sometable.
sdata1 again is used via macro substitution, that is the value in sdata1 is used as the table name. Earlier sdata1 is set as sdata1 = dpath + 'sdf-' + right(alltrim(dtos(edate1)),6) + '.dbf', so again this is a table with a date in the format YYYYMMDD in its name, located in some path stored in dpath the table is called sdfYYMMDD.dbf, as only the right 6 chars of YYYYMMDD are used.

And so on and so on.

This is cumbersome and too much asked here, you need a vfp programmer to translate this code. But finding the lines in which variables are defined and set is not specific to foxpro, the syntax x=y is the same in many languages, so it is in foxpro. This is something you can find out yourself.

Bye, Olaf.
0
 
LVL 14

Expert Comment

by:tusharkanvinde
Comment Utility
There is no way you will be able to convert "many of these type of programs" without a VFP developer.

When you are transferring to oracle, I would have a single table and have the YYYYMMDD as a column there instead of creating a table for every day.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
YYYYMMDD in a table column: Yes, not only when converting to oracle, even in DBFs itself.

Bye, Olaf.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now