First attempt at Data-Into with YAJL parser

[B.Hauser]

Jon,
JSON_TABLE is NOT more complex than extracting information from a complex JSON document with YAJL.
In the same way you design your nested data structures, you design the nested COLUMN definitions in the JSON_TABLE Function statement.
We already had the discussion multiple time.

I personally prefer JSON_TABLE over DATA_INTO.

In my classes I teach both methods. RPG with YAJL and DATA_INTO first and after SQL. The majority of my attendees prefer the SQL way.

Birgitta

[JonBoy]

Jon,
JSON_TABLE is NOT more complex than extracting information from a complex JSON document with YAJL.
Birgitta

I didn't say it was Birgitta. All I said was I hadn't seen examples that matched the complexity of this document. And I didn't mention YAJL at all. But if I had I would have to disagree with you anyway because at least YAJL is a step by step process that most anyone can understand just by reading through the code. Verbose - yes.

I do think though that DATA-INTO has utility in shops where they are not familiar with the kind of SQL techniques that are required. You are an SQL nut. You eat, sleep and breathe it. For those of us who don't the kind of syntax required for JSON_TABLE() can quickly become a read-only scenario. That said I am trying very hard to learn all of the new SQL capabilities but still wrestling with my dislike of the syntax peculiarities.

[B.Hauser]

For those of us who don't the kind of syntax required for JSON_TABLE() can quickly become a read-only scenario. That said I am trying very hard to learn all of the new SQL capabilities but still wrestling with my dislike of the syntax peculiarities.

That's why IBM i preaching you need a DBE who is able to generate (those) complex SQL statements, wraps them into views and generates the right indexes to get the best performance.
Any average programmer can use a view and set may be one or two global variables, without any idea how the wrapped SQL statement looks like. It is almost like calling a function with one or to parameters.
... and you definitely need much less source code. I just reworte a program and converted native I/O into embedded SQL. I only needed a 10th of code lines and the new program is even much more flexible then the old one. And even performance is better.

But I didn't want to start a discussion (embedded) SQL versus RPG. It never means OR it is always AND, use the tool that fits the best what you want.
... and if you only have a hammer, you need to use it.

Birgitta

[JonBoy]

That's why IBM i preaching you need a DBE who is able to generate (those) complex SQL statements, wraps them into views and generates the right indexes to get the best performance.
Any average programmer can use a view and set may be one or two global variables, without any idea how the wrapped SQL statement looks like.

No argument. But what works well for a 10+ man shop is rarely viable in a one or two person shop.

1 100% agree that using the tool that best fits is the right answer. Sometimes the best fit has to be constrained by the available skills.

[wegrace]

But what works well for a 10+ man shop is rarely viable in a one or two person shop.

This!

[gwilburn]

No argument. But what works well for a 10+ man shop is rarely viable in a one or two person shop.

1 100% agree that using the tool that best fits is the right answer. Sometimes the best fit has to be constrained by the available skills.

I agree 100%. We are a 1-man shop when it comes to programming. I really like SQL and SQL embedded in RPG. However, I was quickly confused (and turned off) by the JSON_TABLE syntax in the example document I found.

How many IBM i shops have a DBE nowadays? According to our IBM Business Partner, it is very common for shops to not have a programmer.

It doesn't hurt to have choices - use the tool that you're most comfortable with. Or, as in this case, the tool that actually works (YAJL).

[B.Hauser]

DBE is not a full time job (at least nor on the i! And not even in big companies).
You need simply one of your programmers who is able to write the views wrapping the complexity of the SQL code. Additionally he has to generates/maintains the appropriate indexes to get the best performance.
All other programmers (independend which programming language they use) use these views and that's it.

BTW I never got any education in SQL myself and also never had the luxury or a big programmer crowd. Mostly I had to do and fix everything by myself.

Birgitta

[gwilburn]

DBE is not a full time job (at least nor on the i! And not even in big companies).
You need simply one of your programmers who is able to write the views wrapping the complexity of the SQL code. Additionally he has to generates/maintains the appropriate indexes to get the best performance.
All other programmers (independend which programming language they use) use these views and that's it.

BTW I never got any education in SQL myself and also never had the luxury or a big programmer crowd. Mostly I had to do and fix everything by myself.

Birgitta

I understand what you are saying... There is ONE programmer... me.

I have written some fairly complex views and used the index advisor. This is just my opinion as a consumer - something IBM tends to ignore.

[Scott Klement]

I know JSON very well and work with it every day, but I cannot work with JSON_TABLE, it gives me a headache.

[mjhaston]

Reviving an old thread, I'm using YAJL, which is awesome. I'd like to check out data-into now that we're at V7R3.

Currently I'm reading my JSON right into my RPG program with the code below and then I work with docNode and all of it's sub-elements.

docNode = yajl_stdin_load_tree(*on : errMsg);

How would I get docNode into "something" so that I could use it like below. Would I read it in using YAJL and "convert" it to character somehow? Is there an IBM supplied way to read the standard input?

It's yelling at me about the first parameter.

data-into products %data(docNode : 'case=any countprefix=count_')  
                   %parser('YAJLINTO');

THE FIRST PARAMETER FOR %DATA MUST BE A CHARACTER OR UCS-2 EXPRESSION.


Thanks.
Mike

[gwilburn]

docnode is likely a pointer... The first parameter when using data-into should be a character variable (or streamfile if you use 'doc=file' in your second parameter).

With DATA-INTO you do not need to worry about keeping track of nodes, etc. You need only have your PRODUCTS data structure match the JSON structure.

[mjhaston]

Yes, that's where I'm lost. I usually use the pointer with YAJL, but I'm wondering how to read my document right into a character variable in the RPG. I call my RPG programs in an Ajax Request and pass JSON to and from.

[Scott Klement]

Assuming you have the current version of YAJL wiht DATa-INTO support loaded, you can do this:

 
 data-into products %data('*STDIN' : 'case=any countprefix=count_')                      %parser('YAJLINTO');

and remove code using "yajl_stdin_load_tree" and similar, you don't need any calls to the yajl_xxx subprocedures when using data-into.

[mjhaston]

Thanks Scott. Looks like we need a few PTFs to take advantage of DATA-INTO. I thought just going up to V7R3 would do it. I'll have to request what we are missing. Same with installing NodeJS for a different project.

[mjhaston]

Scott, a completely lazy question from me, but if I'm getting a JSON string from my web app that I could just pass onto an API call, could I do something like this?

docNode = yajl_stdin_load_tree(*on : errMsg);

and then POST it?

response = http_string( 'POST'                
                      : url                   
                      : docNode               
                      : 'application/json'    
                      );

I know this doesn't work, but could I get docNode into a variable and use it intact?