Re: Creating XML document via SQL - Db2 for i 7.2 -- RPG400-L

Henrik,

The reason why I didn't mention Java, is because I've not used it since
2002. I don't add value to the reader talking about something I've not used
in over 15 years. No conspiracy here.

C# borrowed (plagiarized?) a lot from Java. From what I remember about
Java, they are extremely similar. If our Java team asks for my assistance
on a project, I will be more prepared now, simply for learning C#.

For the record, the C# applications I've been asked to work on recently,
access DB2 on IBM i and Oracle, not SQL Server. The LINQ (SQL syntax) code
is being used to query and process in memory collections not the database.
I've not used LINQ enough to know if it would also be useful for database
access. I'm inclined to think there are better ways of accessing the
database, like ORM layers. In our case, the designers of the application
chose, years ago, to use Entity Framework (an ORM layer).

Also for the record, I'm pretty fond of Javascript, and am glad to see you
building NodeJS applications. If there were more hours in the day, I'd be
learning NodeJS. Javascript is the most widely used language in the world,
and I've always been fond of server side solutions. Building applications
in NodeJS on IBM i can really widen your available labor pool.

If you used the XMLTABLE or JSON_TABLE functions in your NodeJS SQL calls
to the database that fetch XML or JSON, my guess is that would be able to
return result sets, if that were more convenient for an application. I've
not used NodeJS, but I'd be surprised if those functions would not work in
an SQL call from NodeJS. Given Javascript has great facilities for working
with JSON and XML, there is, however, less reason to use SQL functions XMLTABLE
or JSON_TABLE. For other languages where it is much more laborious to work
with XML or JSON, I think the SQL XMLTABLE and JSON_TABLE functions can
really improve productivity.

I recall a while back where someone asked for help on one of the forums
here for parsing an XML document. I spent about an hour and replied with a
working / tested solution. The reader chose to ignore the XMLTABLE
function solution I sent, and almost a week later, with help from a lot of
other people, was still trying to get the more laborious solution working
that was using the XML functions that extract small pieces of the XML
document at a time. If people want to build a much larger volume of code
and take a lot longer to do so, that is their choice. For that extra effort
and cost, they better be gaining something like better performance, or it
was a waste of time and money, and an unnecessary risk to meeting their
project's milestones.

Mike

On Mon, Mar 12, 2018 at 6:06 AM, Henrik Rützou <hr@xxxxxxxxxxxx> wrote:

Mike

How convenient to leave out the world’s most popular business programming
language (JAVA) and only base your “trend analysis” on Microsoft
proprietary product like C#, ADO and LINQ that always has a MSSQL database
in back and like IBM i/DB2 runs proprietary SQL.

I admit that SQL is pushing other techniques aside when it comes to DB
access, but that doesn’t tell anything about SQL used as a programming
language for other functionality.

Today I also work with javascript/node.js as the backend where SQL is seen
as “just another external service with a downloadable connector” and a
performance bottleneck that should be kept within the narrow ANSI/ISO
standard for portability and only used in dedicated REST/CRUD services were
it, by the way, not is used to either form the end-output or is used to
translate raw input from the HTTP buffer since there are no way to convert
a raw form/url encoded and escaped POST/GET input streams to anything that
matches anything in SQL. So, there are in any case a mapping from the DB
format to the external I/O interface of the REST service. Besides that,
there are always preprocessing before you come to the actual DB connection
e.g. security check, load of user specific rules and metadata for
conversion etc.

Node is for the moment mostly used as backend for front-end-applications
but that covers about 80% of the entire programming done today and with the
exception of isolated REST/CRUD you generally don’t find SQL used in any
part of the development simply because there is no database but only data
stores in the form of objects that holds data (result sets) and has built
in methods to access the data – all done in OOP.

So, you have to see at the full stack infrastructure of all the programming
techniques and elements used in the whole application production line.

Personally I consider SQL as a great language to connect to an internal RDB
but mapping to/from external client formats is most often in my experience
too complicated and therefore better done in traditional programming
languages simply because the internal database design seldom are the hand
that fit the glove of the external format – if it was so everybody had
built EDIfact and X.12 (that just is other hierarchical formats in another
syntax than XML/JSON) for years with SQL and I have never heard that done.

Another thing is that if you run node.js the SQL connectors doesn’t return
neither XML nor JSON as intermediate result set but a javascript object
that besides the result set also contain basic metadata for each column but
still misses the metadata that maps internal formats to external formats
basically the obvious - dates in numeric fields or date format of a data
field, Booleans that may be Y/N but in the interface 1/0 but also code
lists where 1,2,3 perhaps means USD, EUR, GBP etc.

The more advanced mapping is perhaps based on user rules, ´where User A
wants dates in ISO and clear text in French and User B wants dates in MDY
and clear text in English where the DB maybe has text in Danish and the
I18N text is translated by the REST service by either an I18N repository or
maybe by calling Google Translate Service. BTW how do you extend SQL with
Google Translate API? And finally there is of course the security issues.

So when it comes down to complex production ready code there is no such
thing as simple one statement solutions and should you be able to do all,
you would probably end up with a 1200 lines one statement SQL so nothing is
gained.