SQL and C Program

[rynspate]

Good afternoon all,

I am hoping to do something very simple with embedded SQL statements in C. This will be my first working program and I've been playing with the C language for a bit over a year now. I want to run a simple select statement in C, and print with printf. I believe if I can get there I can complete what I'm hoping to do.

I have been reading online and know I need to use CRTSQLCI, but honestly haven't quite grasped that yet.

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlca.h>
int main(){


    EXEC SQL
    SELECT * FROM TABLE(QSYS2.JOB_INFO(
    JOB_STATUS_FILTER => '*OUTQ',
    JOB_USER_FILTER => 'JKLEIN'
    )) WHERE JOB_NAME_SHORT = 'BOS043JRNL'
    order by JOB_ENTERED_SYSTEM_TIME DESC;

};

Perhaps I'm shooting for something outside of my foundation knowledge right now, but I need some guidance.

[JonBoy]

Have you checked thiis section of the IBM docs? https://www.ibm.com/docs/en/i/7.4?to...c-applications (if you are not on 7.4 use the pull-down on the top left to select the appropriate release).

I'm not much of a C programmer - I do all my imbedded SQL in RPG and COBOL but it looks to me to be a comprehensive set of instructions. At the foot of the page it also points to a full embedded SQL sample that goes well beyond what you need but ...

If you have already studied all of that and still have problems you need to tell us where you are experiencing difficulty.

[rynspate]

JonBoy, I believe what I can should be sufficient? I'm getting an error though that I am unsure of:

Message . . . . : RSPEIGHT43/QCSRC(CSQLTEST), 9.26: CZM0076(10) Character
constant '*OUTQ' has more than 4 characters. No more than rightmost 4
characters are used.
Cause . . . . . : A character constant can only have up to four bytes. &N
Recovery . . . : Change the character constant to contain four bytes or
less.

It's pointing to line 9, "JOB_STATUS_FILTER => '*OUTQ',

Not entirely sure what it means entirely, but I changed the single quotes to double quotes, recompiled, and that error went away however, I don't think I'm entirely out of the woods. I'm getting 'unknown identifier errors' on EXEC and JOB_STATUS_FILTER.

Guess I have to dive in a little deeper.

[JonBoy]

I suspect that you are not using the right compile command. You should be using CRTSQLCI. Had you done so you should have got the error message that I did which was that no INTO was specified.

When using embedded SQL you need to tell the pre-compiler where to put the data. This is a grossly simplified version of your code which does compile.

int main()
{
[B]char jobName[28][/B];
EXEC SQL
SELECT job_name_short [B]into :jobName[/B]
FROM table(QSYS2.JOB_INFO( JOB_USER_FILTER => "PARIS"));
};

It compiles into a module using this: CRTSQLCI OBJ(TEST1) OUTPUT(*PRINT) OPTION(*XREF)

Note that this code is not really going to work properly because this SQL presumes a single row and the SELECT produces more than one row. What I _should_ have done is use the select clause in a declare cursor statement and then use the cursor to page through the rows. If you can't find examples in C (although I know the use of a cursor is included in the examples I pointed you to earlier) look for RPG examples - the fundamentals are the same.

Note also the use of a colon in front of the jobName variable in the INTO. The colon marks it as a host variable and they can also be used elsewhere in the select (for example the where clause)>

Hope this helps. There are far more C programmers on the midrange.com C list so you might also try for help there if you need more. You have already more than exhausted my knowledge I'm afraid.

[rynspate]

Exhausted or not, I'm just glad you got it to work. I have been using the CRTSQLCI command and have been hitting the wall quite a bit, but below is what I was able to make work.

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
exec sql include sqlca;
int main(){
int cnt;


EXEC SQL
SELECT COUNT(EXIT_POINT_NAME) INTO cnt
FROM QSYS2.EXIT_POINT_INFO;
printf("The count of EXIT_POINT_NAME is %d\n", cnt);
};

Using the : in front of cnt, was returning a compilation error that cnt was unusable or not defined. Removing the : allowed the compile to complete but I get the error Position 40 Column or global variable CNT not found......

I will have to check out the midrange.com but seeing as this seems to be such a simple error, you might be able to still lend a hand.

[Barbara Morris]

For SQLC, you have to code a DECLARE SECTION to identify the host variables you're going to use.

Host variables in C/C++: https://www.ibm.com/docs/en/ssw_ibm_...jphostsqlc.htm
Host structures in C/C++: https://www.ibm.com/docs/en/ssw_ibm_...hoststrucc.htm

[rynspate]

Barbara I really appreciate you taking the time to help me out. I have played around with the DECLARE section and I obviously find myself hitting the same situation over and over again because I'm not understanding how to declare the appropriate variable.

int main(){
BEGIN DELCARE SECTION
int cnt;
END DECLARE SECTION
EXEC SQL
SELECT COUNT(EXIT_POINT_NAME) INTO :cnt
FROM QSYS2.EXIT_POINT_INFO;
printf("The count of EXIT_POINT_NAME is %d\n", cnt);
};

As you can see above, I added the BEGIN/END DECLARE SECTION according to the documentation. What I'm hoping to return is numeric so I believe an int would suffice, but looking at the 'numeric host variables' portion of the link you forwarded to me, I really don't know how to read the flow chart that they have posted, nor do they give an example.

One of our Database Admins suggested something along the lines of DECLARE @variablename as INT, but that didn't seem to work.

[rynspate]

Ok, so I was able to get something created. I took a step back from the code I have in the above post, and just tried to see if I could create a module using CRTSQLCI and I was able to.

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlsystm.h>
exec sql include sqlca;

int main(){
EXEC SQL
CALL QSYS2.QCMDEXEC('DSPFD "FILE"(BANK77//*ALL) TYPE(*MBR)
OUTPUT(*OUTFILE) OUTFILE(RSPEIGHT43/JRNFILE)');
};

Now to test it fully, I will have to figure out how to
1. call this module in another C program, if I'm understanding this correctly
2. use CRTPGM to bind my C application with this SQL module I created.

Wish me luck.

[rynspate]

Man, I finally found something that is useful! It only took me a week and a half.

https://www.ibm.com/docs/en/i/7.3?topic=applications-determining-equivalent-sql-c-c-data-types

[JonBoy]

Man, I finally found something that is useful! It only took me a week and a half.

https://www.ibm.com/docs/en/i/7.3?to...c-c-data-types

That was one of the items on the list at the URL I gave you after your first post. Maybe you should revisit that list.

[rynspate]

Forgive me John Boy, I'm a bit of a skimmer at times. Below is what I created to test out embedded sql statements. I just wanted to prove out to myself that I could do something simple, like run a CL command with QSYS2.QCMDEXC. Which in turn did work.

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlsystm.h>
exec sql include sqlca;

int main(){
printf("------ENTER MAIN------\n");
EXEC SQL
CALL QSYS2.QCMDEXC('DSPFD FILE(BANK43/*ALL) TYPE(*MBR)
OUTPUT(*OUTFILE) OUTFILE(RSPEIGHT43/JRNFILE)');
EXEC SQL
CALL QSYS2.QCMDEXC('CRTSAVF FILE(RSPEIGHT43/SQLSAVF)');
printf("------EXIT MAIN------\n");
};

One thing that frustrated me was declaring a variable that was usable within the embedded SQL statements. Getting the equivalent SQL and C variable type has been a bit tricky. JOB_NAME, in the SQL table, is a VARCHAR(28). I've declared

char[28] jobName;

but I still get an error about the variable not having an equivalent SQL data type.

[rynspate]

Alright, I finds myself, the issue was how I was declaring my variable in C.

Originally I declared it as char[28] jobName. I, again, had tunnel vision and forgot that NUL terminated variables, i.e. char arrays, are declared char jobName[28]...

NOW I'M COOKING WITH GAS!

[Barbara Morris]

I hadn't ever tried to do embedded SQL using C. It looks like a bit of a nightmare. Maybe it's just an issue of my own familiarity, but it looks like it's a zillion times easier to use RPG. For one thing, you don't need to code a DECLARE section for RPG.

So rynspate, if you need to do a lot of embedded SQL, it might be worth having a look at RPG, even if you can hardly spell "RPG" :-)

Here's your exit-point program written in RPG (source member type SQLRPGLE), compile with CRTSQLRPGI).

It's not exactly the same, since the DSPLY opcode behaves different from printf(). (It's possible to call printf, if necessary, but it's a bit more code.)

**free
dcl-s cnt int(10); // declare a 4-byte integer (really!)

EXEC SQL
SELECT COUNT(EXIT_POINT_NAME) INTO :cnt
FROM QSYS2.EXIT_POINT_INFO;
dsply ('The count of EXIT_POINT_NAME is ' + %char(cnt));
return;

[Scott Klement]

I hope you understand that you don't need SQL to run QCMDEXC?

[rynspate]

Hey Barabara,

I was able to get it to work. I didn't need the DECLARE statements. My main issue was using the correct C data type equivalent to the returned SQL data type. I've never touched RPG and it may be worth looking into, but again, I'm a bit more comfortable with C and I've gotten my program to work as desired. I just have to format the text a bit and it should be good.

Scott, I'm quite certain I don't need SQL to run QCMDEXC. I just wanted to do just a dummy test to make sure I wasn't missing anything else glaringly obvious.