Introduction to SQLRPGLE in IBM i - for Beginners

This video covers the basic concept of SQLRPGLE in IBM i (AS400),
This covers below topics,
1. Simple SQLRPGE select
2. Dynamic SQL statement
3. Create drop table using SQLRPGLE
4. Reading file using Cursor
5. Scroll cursor

[@abekelly9935]

Excellent presentation sir. Best regards

[@yusy4code]

Thanks for ur feedback sir...

[@higiniofuentes2551]

OMG! Thank you Mohammed! A very dense video but with a lot of explanations. VERY useful. Higinio

[@zavedalimeer4047]

A very nice explanation. please do more videos.

[@seancollins1734]

Nice video.

[@gugun2660]

Awesome❤

[@kaattupoochi5992]

One doubt bro... What is the use of Set option commit = *None ?

[@aapurvagarwaal3635]

Thanks Yusuf for the video. Excellent Explanations. I have a doubt regarding the fetching of the record. The records fetched will be basis on the keys in the Database or RRN. If I sort by different keys using order by other than mentioned in the DDS of the file , will the fetching of records work like in accordance with order by clause.

[@yusy4code]

I feel so. The record should be fetched according to the SQL command.

[@BudiSantoso-er6cq]

Thanks Sir.. God bless you.

[@yusy4code]

Budi Santoso ThankYou 🙂

[@krishnachaitanyapulicharla82]

Hi... very good understand video for me to assign to var and ds, I have a question,, SET OPTION COMMIT is mandatory to declare or ?, pls share some brief about

[@rajkirank5499]

Is there any url can get more number of sqlrpgle examples

[@Toribiogp2]

Que buen video amigo. And I have a question, Could I call a sqlrpgle external program from a sqlrpgle program? Thanks for all.

[@gavaskarchittim9204]

Hi Yusuf,Nice explanation easy to understand ..have small doubt ... in what scenario normally we go for Embedded SQL rather that LF,OPNQRYF

[@yusy4code]

It is clearly seen that if we need to handle complex SQL statement then we can go for Embedded SQL rather than LF. But LF comes handly if our requirement is simple. E.g. Retrieve data based on certain conditions. OPNQRYF will be useful if we want to execute a query in CL. But there are alternatives like RUNSQL command etc.

[@paolonervi2208]

Hi Mohammed, thank you for your videos! I tried to compile the first SQLRPGLE example putting the source in an IFS file. No way to make it work. Do you have any thoughts about it ? Is it mandatory to put the source in the classic QRPGLESRC file and define the type as SQLRPGLE...or is any way to make it working also with the source in an IFS file? Thank you for the time you dedicate answering to me. Greetings from Italy.

[@sandhyanandyala8825]

What is the use of set option Commit = *None at start of pgm

[@marcelofirmapaz5724]

Genio total!!!

[@yusy4code]

Gracias..!!!

[@PRAVINCHOTHE2008]

Hye Yusuf, Nice initiative bro.. remember me... same project DFS - from CSM team.. keep going bro..

[@yusy4code]

Yes.. How can I forget.. those night shifts and production calls :)

[@PRAVINCHOTHE2008]

ya bro...

[@thabangnukeri6622]

Hi Yusuf, I tried using the same login to read from a PF and store multiple rows into a Data Structure. I still get SqlCode = -811. Is there anything i could be doing wrong or you suggest?

[@raghumohanreddykasarla2844]

Can you please explain the same concepts in COBOL..

[@sehrishsaiyed4118]

Hi I am really stuck at a point in a project and need your help

[@krishnap2867]

ya yusuf, easy way ofunderstanding things are you done

[@ramstein74]

do i need to load the file as you did in the second line of the rpg FStudPF IF E K Disk ???

[@yusy4code]

Actually Not (If we are going to use only Embedded SQL). If we are mixing it up (means, SETLL , CHAIN as well embedded) then we need to declare in F spec.

[@manojgupta7478]

Hi Yusuf, Thanks for your vedios, Is there any way I could practice it. Like some server access on free or rental?

[@yusy4code]

There is a option of getting 3 month subscription for 25$ from TexAS400 texas400.com/as400account.html

[@kishorgopal7113]

please help to understand the concept of error handling in RPGLE program

[@chauhanrahul155]

How to learn sqlrpgle?Do we have to see videos of SQL?

[@yusy4code]

You need to learn by practicing more. I do not have dedicated series of videos for SQLRPGLE. Follow RPGPGM.com for interesting SQLRPGLE stuffs.

[@DattaPadal]

Do we really need to define the file we are using for SQL statements in F spec? ( like STUDPF in the example) ?

[@yusy4code]

Actually Not (If we are going to use only Embedded SQL). If we are mixing it up (means, SETLL , CHAIN as well embedded) then we need to declare in F spec.

[@rameshreddy8137]

Nice Explanation and could you tell me what are the advantages over rpgle vs sqlrpgle

[@yusy4code]

1. Working with Groups of Data: SQL is terrific at selecting and manipulating groups of data. If you want to update/delete all the records in a file matching a certain criteria, use SQL. SQL can change or delete a group of records in a single statement, whereas native I/O would require you to loop through a file and issue individual update or delete statements. 2. Massaging Columns: SQL has a slew of columnar functions designed to tally, total, summarize, manipulate, and calculate columns of data. Many program features such as substringing, averaging, and math functions can be performed during the record selection phase. Columns can even be returned that don’t exist in the file, such as counts, calculations, literals, and dates. 3. Aggregate Data. SQL lets you easily aggregate columns in like rows: For instance, if you wanted to find a list of all the different zip codes in a mailing address file and count how many addresses were in each zip code, SQL can easily accomplish this in a single statement. In native I/O you would have to loop through a file and increment counter fields or use arrays and/or multiple-occurrence data structures to aggregate like data. Combine this capability with 1 and 2 above and SQL absolutely shines. Full article : www.itjungle.com/2005/08/10/fhg081005-story01/

[@rameshreddy8137]

Thank You Yusuf, Really Appreciate your Help

[@eugepetters2003]

Mohammed! you have an video tutorial for configure an emulator to do some practice? Thanks!

[@yusy4code]

The steps needed to configure emulator after installing is given in the description of another video here kzbin.info/www/bejne/Y4a4pJ9jbNJ4ra8

[@ScottThompson-uz9dt]

Where is the dds for the StudPF physical file. You can't do anything without that.

[@avijitsharma5050]

So i guess getting data using cursor is faster rathen than getting data using SETLL + READE ? just wanted to know which one is faster

[@yusy4code]

Actually it depends, if we have multiple random fetches then SETLL & READE / CHAIN is better choice. But we have a bulk update or delete based on a specifi condition then embedded SQL is better. Read it here when to use SQL in more detail: www.itjungle.com/2005/08/10/fhg081005-story01/

[@rameshreddy8137]

Nice Explanation Great Efforts, How can we define SQL table in F spec what is the diff btw PF and SQL table

[@yusy4code]

From my understanding both are same. SQL table equivalent is PF and SQL View equivalent is LF.

[@rameshreddy8137]

Thanks for your Reply, can we declare sql table in F spec can we read the record format how we know the recformat there is no rec format in SQL right

[@bhanuteja6261]

hey Yusuf, i am getting SQL0312 error ''SQL0312 30 11 Position 46 Variable DPT not defined or not usable. ''

[@yusy4code]

If your code is in pub400 then give me the source details so that i can have a look.

[@bhanuteja6261]

Ya sure... My code is there in user01251/teja(RPGLE007)

[@yusy4code]

Variable "Name" is not declared as stand alone variable DName Like(ENAME) change the above line to DName S Like(ENAME)

[@pallenaresh7254]

Hi Sir Are you teaching in offline Please let me know we will join Please sir help us

[@tanutyagi2550]

Can someone please suggest how to declare a file like STUDPF above?

[@bhanuteja6261]

I am trying it in PUB400.com

[@karthikmuthamsetti7161]

As this is about basics of SQLRPGLE, you should have explained how the SQLRPGLE syntax looks like in the program. Also, you kept on writing the program and showed the result. But you didn't explain what each line specifies and what does it serves. For ex, no explanation for "Commit = *NONE " line. The beginners expect explanation and syntax.

[@ravalikakumanu5960]

Yes

[@yogmahirazz]

Hi yusuf when I am using file field studdpt using like keyword I am getting SQL0312 Position 46 Variable studdpt1 not defined or not usable....why sqlrpgle not able to take definition from file itself because it already declared 462823/USER0444/QPAD060311 from pub400 user0444

[@yusy4code]

I have just checked your session and i hope you have fixed the issue. If you still have issue let me know the program name so i will check and let u know.

[@yogmahirazz]

I created field as standalone that's why it's working...but using like keyword to get filed definition from file it was not working you can chk in user04441/mypractice/PGMSQLRPG4

[@yusy4code]

ok here is the error: DEID LIKE(EMPID) if we define with blank declaration type then it will think it is inside Data Structure. But for us it is a stand alone variable. but the definition is like(empid) so change your code like below DEID S LIKE(EMPID)

[@yogmahirazz]

It's working thanks... :)

[@phpBasics]

Apparently, the people making these types of videos can't afford an audio interface

[@yusy4code]

Thanks for the feedback. I will try to improve the audio interface.