Relativity Union and multiple commands

"execute hundreds of statements at a time.  Relativity does not like this."

The ODBC SQL specification (a Microsoft creation) does not have this syntax as part of the language.  It would be an extension.  Relativity does not extend the ODBC specification in this way.

Use a scripting engine.  Perhaps PowerShell (if you are on Windows)?

"UNION"

The help file topic is "SELECT Statement (Advanced)".  There you will find UNION [ALL].  UNION is an extension of the ODBC SQL specification.  However, there is this limitation:

   "The ORDER BY clause may not be specified in a select-statement within a UNION or UNION ALL clause"

What are you trying to use UNION for.  There may be another way...

Cliff,

First let me point out that in the Relativity Data Manager and Relativity Data Client help files, there is a link to a "SQL Grammar" topic that describes the grammar that Relativity supports.  Clicking on that topic, we see:

"The Relativity ODBC driver supports all SQL statements and clauses in the ODBC minimum grammar with the following limitations"

So, yes, it doesn't support TOP and a bunch of other nice stuff.  Some day, perhaps we may support more of the SQL Grammar, but at this time, it is what it is.

The SQL Grammar described in the ODBC 2.0 book describes a statement-list:

statement-list ::= statement | statement; statement-list

and indicates that this is an Extended SQL statement.

Now, looking at the select-statement in the ODBC 2.0 book, the extended select statement is described as

select-statement ::=

   SELECT [ALL | DISTINCT] select-list

   FROM table-reference-list

   [WHERE search-condition]

   [GROUP BY column-name [, column-name]...]

   [HAVING search-condition]

   [UNION [ALL] select-statement]...

   [order-by-clause]

And there is a note that in "ODBC 1.0, the UNION clause was in the Core SQL grammar and did not support the ALL keyword".

So, turning to the Relativity help file, it looks like Relativity already supports the extended grammar's SELECT statement, but in neither the ODBC book nor the Help file, does the UNION support a list of SELECT statements.  They both merely say "select-statement".

So, my recommendation for you is to use something like Access to enter the SELECT statements.  It will parse the SQL statement, issue simpler SQL statements to Relativity, and then merge the results into the more complex result set that you seek.

Please remember that Relativity was never intended to be a general purpose SQL engine. Its job is to supply an ODBC interface to your COBOL data.

Mike says, "The SQL Grammar described in the ODBC 2.0 book describes a statement-list:

statement-list ::= statement | statement; statement-list"

The ODBC 3.8 reference (available on the web, haven't seen a book) does not have a production for statement-list.  

Mike also says, "... use something like Access..."

There really is nothing like Access.  Its SQL engine is notorious for introducing its own notion of SQL before passing on the wreckage to the ODBC driver.

There are simple ODBC clients available that do not impose the issues that Access raises.  I really think the multi-statement issue can be dealt with using a scripting language: PowerShell, PHP, etc.

While not absolutely clear, I don't think Cliff is looking for statement-list and UNION at the same time.  The biggest issue with UNION, at least for me, is the inability to specify ORDER BY in the underlying SELECT statements, therefore relying a lot on the 'natural order' of one of the COBOL keys.  Also, in my experience, while UNION may in theory be a solution to a problem, it might be possible to deal with the issue that Cliff is having using Relativity-specific mechanisms, such as a multi-file table (that is, a single table that has multiple physical files - such as a history table that has one history file/year).  That is why I asked about what he is trying to accomplish.  Now, if only Cliff would respond... 

<<There really is nothing like Access.  Its SQL engine is notorious for introducing its own notion of SQL before passing on the wreckage to the ODBC driver.>>

OK. I defer to your greater experience in this area, but the point is still the same: please quit expecting Relativity to be a general purpose database.

Tom,

personally I do not use the UNION statement, but one of my co-workers uses it currently to create a list of available options in a record set and adds in a blank option that is not in Cobol.

select option from Cobol_File

union

select '' as option

I have not had a chance to try and use your scripting option to handle my multiple update statements at once, but I appreciate the advice and will try that.

Thank you Michael. It is just a learning process to take something that allowed for much greater what I would call data handling and stream it down to Relativity that is more structured and in that sense more limited to just presenting the Cobol data. But hey if we don't ask for bells and whistles they will never be considered. So I do look forward to the day where you have TOP and ORDER BY as part of an expanded set of tools to use with Relativity. I find it odd that in Designer you can control the number of rows and errors that are encountered but not outside of Designer, so it seems like there would be some way to expand. Your note about Relativity being a supply interface of Cobol data is understood.

Tom,

I use ODBC test and Dbeaver to access the Relativity information while some co-workers actually do the visual studio applications so I am wondering if you could give a small example of using PowerShell to connect to ODBC and execute a couple of update commands?  Say the DSN is named RelativityData and the Cobol file is Data and I want to update a field called element.

update Data set element = 1 where key is '100'

update Data set element = 2 where key is '200'

I am in customer meetings all day, so a specific example is not possible at this time.

However, I find this example of using ODBC from PowerShell.  

   unixwars.blogspot.com/.../talking-to-mysqlmariadb-from-powershell.html

The underlying database in this example is MySQL but, with the exception of the connection string, the techniques taught in the example should be directly applicable.

For information regarding connection strings as used by Relativity, see the help files referenced by Mike S.  The topic is, "Connection Strings (Advanced)".

Using a scripting engine, especially one as capable as PowerShell, allows for a more sophisticated approach to error handling and formatting of results than a simple statement-list.

Tom - I will investigate this further - thanks for the link.

Tom,

Did not totally get PowerShell to work yet but I found a way around my issue for now.  Thanks.