Menu
▾
▴
[netrexx-pipelines] SQL Stage and help request
|
From: Jeff H. <Je...@Je...> - 2020-08-16 19:12:23
|
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<p>I have committed a <b>sql</b> stage.</p>
<p><tt>/** sql</tt><tt><br>
</tt><tt><br>
</tt><tt>
+-;-+</tt><tt><br>
</tt><tt>>>--SQL--+-----------------+--+-------------------------+-+---+--><</tt><tt><br>
</tt><tt> +-(-| options |-)-+ +-sql_statement_string-(3)+</tt><tt><br>
</tt><tt><br>
</tt><tt>OPTIONS:</tt><tt><br>
</tt><tt> +------------------------------------------------+</tt><tt><br>
</tt><tt> |-v-+--------------------------------------------+-+-|</tt><tt><br>
</tt><tt> | +-/sql.properties/-------+ |</tt><tt><br>
</tt><tt> +-PROPERTIES-+-filename_Qword-(7)-----+-(5)+-+</tt><tt><br>
</tt><tt> | +-NOHEADERS-+ |</tt><tt><br>
</tt><tt> +-+ +-(5)(6)(9)------------------+</tt><tt><br>
</tt><tt> | +-HEADERS---+ |</tt><tt><br>
</tt><tt> +-URL-Qword-(7)----------------------------+</tt><tt><br>
</tt><tt> +-JDBCDRIVER-Qword-(7)---------------------+</tt><tt><br>
</tt><tt> +-DBMS-Qword-(7)(8)------------------------+</tt><tt><br>
</tt><tt> +-DB_NAME-Qword-(7)(8)---------------------+</tt><tt><br>
</tt><tt> +-USER-Qword-(7)(8)(10)--------------------+</tt><tt><br>
</tt><tt> +-PASS-Qword-(7)(8)(10)--------------------+</tt><tt><br>
</tt><tt><br>
</tt><tt><br>
</tt><tt> uses jdbc to select from any jdbc enabled dbms</tt><tt><br>
</tt><tt><br>
</tt><tt> properties file ("sqlselect.properties" default) is read
to find jdbcdriver name and url</tt><tt><br>
</tt><tt> sample properties file:</tt><tt><br>
</tt><tt><br>
</tt><tt> #JDBC driver name</tt><tt><br>
</tt><tt> #Tue Feb 03 23:29:43 GMT+01:00 1998</tt><tt><br>
</tt><tt> jdbcdriver=com.imaginary.sql.msql.MsqlDriver</tt><tt><br>
</tt><tt> url=jdbc:msql://localhost:1114/TESTDB</tt><tt><br>
</tt><tt> # the following are not needed for some DBMS, ex: SQLite</tt><tt><br>
</tt><tt> user=db_user_name</tt><tt><br>
</tt><tt> pass=password_for_db</tt><tt><br>
</tt><tt><br>
</tt><tt> if this file is not found default (compiled in) values
are used</tt><tt><br>
</tt><tt><br>
</tt><tt> (1) when using a sql select * (all columns) from the
commandline, quote the query</tt><tt><br>
</tt><tt> as in java pipes.compiler (query) "sql select * from
dept | console"</tt><tt><br>
</tt><tt> (2) the netrexx/jdbc combination is extremely case
sensitive for column and table names</tt><tt><br>
</tt><tt> (3) this sql_select_string executed, then statements are
read from the primary input stream.</tt><tt><br>
</tt><tt> this is optional in NetRexx Pipelines only.</tt><tt><br>
</tt><tt> (4) CMS does not use the stream input</tt><tt><br>
</tt><tt> (5) NetRexx Pipelines only</tt><tt><br>
</tt><tt> (6) CMS Pipelines is implied HEADERS only.</tt><tt><br>
</tt><tt> (7) A Qword is an optionally quoted word. If it contains
spaces, it must be quoted.</tt><tt><br>
</tt><tt> (8) EXPERIMENTAL Subject to change. DBMS is the kind of
database, e.g. SQLite.</tt><tt><br>
</tt><tt> DB_name is the file name. These are used in place
of URL and JDBCDRIVER.</tt><tt><br>
</tt><tt> SQLite is the only one tested as of 8/15/20.</tt><tt><br>
</tt><tt> (9) the SQLSELECT stage uses HEADERS as the default.</tt><tt><br>
</tt><tt> (10) USER & PASS are needed for some DBMSs and not
others, ex. SQLite.</tt><tt><br>
</tt><tt><br>
</tt><tt> Priority order for URL, JDBCDRIVER, DBMS, DB_NAME, USER,
& PASS (first one found rules):</tt><tt><br>
</tt><tt> 1. option in the SQL command string</tt><tt><br>
</tt><tt> 2. from secondary input stream</tt><tt><br>
</tt><tt> 3. from "sqlselect.properties" file</tt><tt><br>
</tt><tt> or from file specified by PROPERTIES option</tt><tt><br>
</tt><tt> 4. Builtin</tt><tt><br>
</tt><tt>*/</tt><br>
</p>
<p>This adds the options of <b>DBMS</b> & <b>DB_NAME</b> and <b>USER</b>
& <b>PASS</b>. These have also been added to the <b>sqlselect</b>
stage.<br>
</p>
<p>It has handling for <b>DBMS SQLITE</b> and <b>MYSQL</b>. It
works on my system for SQLite. But there are several dependencies
and I don't know if it works on all systems, which of course must
have SQLite installed where it can be found. And I have done no
testing of MySQL, as I don't have it here.</p>
<p>If anyone has a MySQL setup I could try creating a table,
populating it, searching it, and deleting it, I would be very
grateful. Please send me the logon credentials for my account to
my personal email -- NOT on this open list! Or knows the JDBC
credentials for any of the other databases. Thank you! <br>
</p>
<p>Incidentally, I have run this kind of test, in anticipation of a
HELP facility:</p>
<p><tt> pipe (ct)</tt></p>
<p><tt> literal ,<br>
select image from stages where stage = 'fanout'; ,<br>
select note from stages where stage = 'fanout'; ,<br>
|<br>
split ; |<br>
sql (dbms sqlite db_name
"C:\\Users\\Jeff\\NetRexx-code\\documentation\\njpipes\\stages.db")|<br>
cons ? </tt><br>
</p>
<div style="display:inline-block;white-space:pre;background-color:#012456;font-family:'Consolas',monospace;font-size:10pt;padding:4px;"><span style="color:#CCCCCC;background-color:#012456;">PS C:\Users\Jeff\documents\pipe tests> </span><span style="color:#F9F1A5;background-color:#012456;">java</span><span style="color:#CCCCCC;background-color:#012456;"> ct
+-STOP--ALLEOF-----------+
>>--FANOUT--+------------------------+--------><
+-STOP--+-ANYEOF-------+-+
+-IMMEDIATe-(1)+
+-number-------+
<div class=p-note><ul><li>
(1) CMS only
</li></ul></div></span></div>
<p>It works! The database needs some work before there is a HELP.
Some of the diagrams, like this one, have the first line skewed
left. And the current <b>note</b> has HTML tags in it, there
will have to be a clear text field created. And, can I expect the
database is in the "proper" location?</p>
<p>Jeff<br>
</p>
<p><br>
</p>
<p><br>
</p>
</body>
</html>
|
