Xopus

Main Menu

  • Schemas
  • CSS
  • Chrome
  • Firefox
  • Fund

Xopus

Header Banner

Xopus

  • Schemas
  • CSS
  • Chrome
  • Firefox
  • Fund
Schemas
Home›Schemas›Guru: Content Assist Plus in executing SQL scripts

Guru: Content Assist Plus in executing SQL scripts

By Warren B. Obrien
November 29, 2021
0
0

November 29, 2021

Paul tuohy

By now you should know that Run SQL Scripts provides content assistance (promotion) for Select statements (see Guru: ACS 1.1.8.3 Content Assist includes prompt for SQL!). But did you know that you can also use content assist for stored procedures, table functions, and parameters? Let’s see how it works, using some of the Integrated File System (IFS) procedures and functions provided by IBM i Services.

Invite a stored procedure

Open Execute SQL Scripts, type:

call qsys2.

Then press F4 (or Ctrl + Space). You will be presented with a list of all the stored procedures in the QSYS2 schema.

This is a very long list, so let’s refine it with IFS procedures by typing if. This will reduce the list to only procedures starting with the letters IFS.

IFS_WRITE is the one we want, so just hit enter and the script is updated with the name of the selected procedure. Now add opening and closing parentheses, place the cursor between the parentheses, and you are ready to request parameters.

Invite stored procedure parameters

Before looking at the prompt parameters, I would like to clarify something about SQL procedure and function parameters that a lot of people don’t seem to be familiar with. Although the syntax is different, SQL procedures and parameters work the same way as system commands: they can be entered by position or identified by a parameter keyword.

With the cursor placed between the two parentheses, press F4 and the list of stored procedure parameters is displayed.

If you move the cursor down in the parameter list, the right pane will provide details of what you need to provide for the parameter (assuming the person who wrote the procedure has commented on it accordingly).

Pay special attention to the information provided. The absence of a default keyword (as with the PATH_NAME parameter) indicates a required parameter.

Select the required settings and any other settings you want to change.

Press Enter and the script will be updated with the names of the parameters.

call qsys2.IFS_WRITE(PATH_NAME => , LINE => , FILE_CCSID => )

Now all you need to do is supply the parameter values ​​and execute the instruction.

call qsys2.IFS_WRITE(PATH_NAME => '/home/TUOHYP/myFile.txt', 
                     LINE => 'This is some text', 
                     FILE_CCSID => 1208);

Invite a table function

Just as simple is the prompt for a table function. Enter the following:

select * from table(qsys2.ifs

Press F4 to see a list of table functions, in the QSYS2 schema, that begin with the characters IFS.

Select the desired table function, place the cursor between the parentheses and press F4 to get the list of possible parameters.

Select the required settings and any other settings you want to change. Press Enter and the script will be updated with the names of the parameters.

select * from table(qsys2.IFS_READ(PATH_NAME => ))

All you have to do is provide the parameter values ​​and execute the next instruction.

select * from table(qsys2.IFS_READ(PATH_NAME => '/home/TUOHYP/myFile.txt'));

The ability to invite SQL procedures / functions and, in particular, their parameters is something that I have found invaluable.

RELATED STORY

ACS 1.1.8.3 Content Assistant includes a prompt for SQL!

Tags: 400guru, FHG, Four Hundred Guru, IBM i, IFS, Execute SQL scripts, SQL

New LTO 9 equipment, SAN switch on IBM tap
Getting started with Connectria’s IBM i and AIX hybrid architecture

Related posts:

  1. Biden administration signals sweeping shift in focus to deal with cyber concerns in government procurement Baker Donelson
  2. My five # 436 | Inbound Marketing Agency
  3. Spring Boot Tutorial Brian Matthews
  4. ChaosSearch Data Platform Now Available in the AWS Marketplace

Recent Posts

  • Google Chrome adds virtual credit card numbers to protect your real ones – TechCrunch
  • How to take screenshots using the built-in screenshot tool in Mozilla Firefox on Windows 11 2022
  • Three Bard Faculty Pen Reviews for Artforum May 2022 Edition
  • 10 CSS background templates you can use on your website
  • Automotive Chromium Market Size and Overview 2022-2030 | Key Players – HELLA KGaA Hueck, Thule Group AB, Lund International, Covercraft Industries, Pep Boys – Manny

Archives

  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • December 2021
  • November 2021
  • October 2021
  • September 2021
  • August 2021
  • July 2021
  • June 2021
  • May 2021
  • April 2021
  • March 2021

Categories

  • Chrome
  • CSS
  • Firefox
  • Fund
  • Schemas
  • Terms and Conditions
  • Privacy Policy