I want to read a pf and build a corresponding SQL statement and save the statemnet in text file.

pts.
Tags:
RPGLE
SQL
SQL statements
how can i read pf ?as pf is colomn speciffic have to read pf like it will have feilds and some constraint, key, record. how can i build a corresponding SQL statement ? i m new in AS/400 but i have to submit this utility to my manager this thursday.. can anybody help me in providing some code for that...or can can help me with some guidance. it will be greatful for me... thanks

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

Use STRSQL to start an interactive SQL session.

Type SELECT and press F4 to prompt, once you’ve filled in what you need, you can press enter and the statement will run. You can then see the SQL statement that you’ve just run above your entry field. You can copy and paste this to a document if you want.

Regards,

Martin Gilbert.

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • I can do that... but I m making a tool... in which u have to enter Library name,Source file and member name. It then convert that DDS(Physical file) in Corrosponding DDL (SQL statement) I have also watch the concstraint in the Physical file. And also i u can provide details of the keywords used in RPg and corrosponding Keyword in SQl Like RPG KEywords SQL Keywords DFT DEFAULT RANGE ?? or any other keyword
    0 pointsBadges:
    report
  • Gilly400
    Hi, So, basically what you're tring to do is create DDL/SQL statements to re-create the file based on the DDS source? You're probably going to need a program in something like RPG to read the DDS source and re-format it into a DDL/SQL statement. Then you're also going to need to retrieve the constraints from the file object (probably using DSPFD). You may also be able to use information from the Database Cross Reference Files (take a look at the files QADB* in library QSYS). For example - file QADBFCST is the constraint cross reference. I'm not sure what you mean by corresponding keywords between RPG and SQL - these are two very different approaches. Regards, Martin Gilbert.
    23,730 pointsBadges:
    report
  • Cwc
    No need to create a program to read DDS and convert to DDL - there is a system API, QSQGNDDL, that will do this.
    4,290 pointsBadges:
    report
  • graybeard52
    In iSeries Navigator, you can just right-clcik a PF and select "Create SQL" to create the SQL to build that file. OK, its not a utility, but it works.
    3,115 pointsBadges:
    report
  • @Cwc: Thansk a lot....API done My work.. thanks a lot., problem has been solved.. But there are some Feild level keywords..that are not working with API like Range and Comp. Can u please provide the solution.
    0 pointsBadges:
    report
  • Gilly400
    Hi, I guess for the other keywords you'll need to do one of the following :- 1. Read the DDS source and extract the keywords. 2. Use DSPFD/DSPFFD to extract the keywords. 3. Extract the keywords from the cross reference files in QSYS. Maybe someone else has some other ideas? Regards, Martin Gilbert.
    23,730 pointsBadges:
    report
  • Cwc
    Certain DDS keywords are not directly compatible with an SQL table created through DDL, so the API excludes them when creating the SQL statement. For the Select and Comp keywords, you could create an SQL view over the physical table / file, which is implemented as a logical file. In the view, you'd add the necessary criteria in the Select statement, and when that view is accessed, it would only retrieve the specified records.
    4,290 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following