7.2 Beta 1
by Steven Hawkins
Hello all,
For those not following the blog or Twitter, 7.2 Beta 1 has been posted (built on Tuesday) http://www.jboss.org/teiid/downloads.html. We will be working hard to get the release out by the end of the month, so let us know how we're doing.
Steve
13 years, 6 months
procedure validation in 7.1.1
by Steven Hawkins
Hello all,
If there are no objections, I'd like to commit the additional changes for https://jira.jboss.org/browse/TEIID-1294. This would make VARIABLES.ROWCOUNT reserved and disallow the use of variable assignments from procedures, unless the procedure has a return parameter. Each of these could be a breaking change, but would have a very clear workaround (rename the variable, or use a scalar subquery that selects the appropriate out param or resultset column respectively). These validations will allow us to clean up our assignment syntax in 7.2 without breaking compatibility with 7.1.1.
Steve
13 years, 6 months
Re: [teiid-users] issues related to TEIID-1237
by Steven Hawkins
To be more clear, the parser will accept the existing syntax, but any toString of the resulting command (such as what happens in Designer) would cause output to be in the expected form.
Another approach to getting the update counts would be to introduce another reserved variable, such as ROWCOUNT. Then something like "ROWS_UPDATED = INSERT ...;" would get parsed into "INSERT ...; ROWS_UPDATED = VARIABLES.ROWCOUNT;". We could add a validation to 7.1.1 that treats ROWCOUNT as reserved for forward compatibility and then there is no potential of a subtle breaking change. ROWCOUNT could also be used for other purposes, such as Oracle's SQL%rowcount that can also tell you the position of the cursor for non-update statements.
----- "Steven Hawkins" <shawkins(a)redhat.com> wrote:
> Hello all,
>
> In looking at expanding our support for stored procedure handling in
> our procedure language there are a couple of other cleanups that would
> be good to discuss.
>
> 1. We should not allow the direct usage of stored procedures as
> subqueries. Instead of "WHERE col = (EXEC foo())", we should use
> "WHERE col = (SELECT * FROM (EXEC foo()) x)". I propose that we
> deprecate this usage and allow the parser to automatically convert to
> the latter syntax.
>
> 2. To be more concise/closer to standard syntax we should allow
> dynamic SQL statements to have the form "EXECUTE [(STRING|IMMEDIATE)]
> ...". The choice to use the STRING keyword in 5.5 was due to not
> having IMMEDIATE as a reserved word.
>
> Our assignment syntax has several issues. I propose that the parser
> continue to allow the existing syntax, but with the following changes.
>
> 3. The plsql assignment operator := will also be accepted. We do not
> yet need to make this the preferred syntax, but it may be a good idea
> as it disambiguates an assignment from the equality predicate boolean
> expression.
>
> 4. We should deprecate the direct assignment of commands to the
> variable. For example:
> a = select ...;
> b = insert into ...;
> c = update ...;
> d = delete ...;
>
> The proper syntax for query expressions is a scalar subquery, thus "a
> = select ...;" should be "a = (select ...);".
>
> For nearly every other database, DML statements do not project the
> rows updated. They also do have the possibility of providing a proper
> result set, such as with the PostgreSQL/Oracle insert returning
> clause. Both of these are reasons why you don't typically see syntax
> such as "b = insert into...;". The more common method for getting the
> update count is from an implicit variable. One possibility is to use
> our existing "ROWS_UPDATED" as that implicit variable. So rather than
> "ROWS_UPDATED = insert into ...; x = ROWS_UPDATED;", you would just
> have "insert into ...; x = ROWS_UPDATED;". For most update procedures
> this obviates the need to explicitly set the ROWS_UPDATED value as it
> will be set implicitly by the last DML statement. However this could
> potentially be a breaking change in an existing procedure, such as
> "...; ROWS_UPDATED = ...; insert into ...; END". Another option is to
> use more explicit syntax, like PostgreSQL, with "GET DIAGNOSTICS x =
> ROWCOUNT"
>
> This should also change the meaning of "e = exec foo...;". As per
> TEIID-1237, what we would like is "e = exec foo...;" to be a command
> statement with the side affect of setting e to the return value of the
> procedure. This syntax would only be valid if the procedure has a
> return parameter (currently it would be valid if the procedure has a
> resultset that has only a single column or no result set and only one
> out/inout or return parameter). The other change would be that the
> this statement would also set the implicit return cursor to the result
> set of the procedure if it has one. Since these would be potentially
> breaking changes (albeit in very narrow circumstances) we could put a
> validation in 7.1.1 that only allows that assignment if the procedure
> returns a result value and if there is a subsequent command statement.
> Otherwise we would have to use a slightly less intuitive syntax, like
> SQL Server, such as "exec e = foo...;".
>
> Any thoughts?
>
> Steve
13 years, 6 months
issues related to TEIID-1237
by Steven Hawkins
Hello all,
In looking at expanding our support for stored procedure handling in our procedure language there are a couple of other cleanups that would be good to discuss.
1. We should not allow the direct usage of stored procedures as subqueries. Instead of "WHERE col = (EXEC foo())", we should use "WHERE col = (SELECT * FROM (EXEC foo()) x)". I propose that we deprecate this usage and allow the parser to automatically convert to the latter syntax.
2. To be more concise/closer to standard syntax we should allow dynamic SQL statements to have the form "EXECUTE [(STRING|IMMEDIATE)] ...". The choice to use the STRING keyword in 5.5 was due to not having IMMEDIATE as a reserved word.
Our assignment syntax has several issues. I propose that the parser continue to allow the existing syntax, but with the following changes.
3. The plsql assignment operator := will also be accepted. We do not yet need to make this the preferred syntax, but it may be a good idea as it disambiguates an assignment from the equality predicate boolean expression.
4. We should deprecate the direct assignment of commands to the variable. For example:
a = select ...;
b = insert into ...;
c = update ...;
d = delete ...;
The proper syntax for query expressions is a scalar subquery, thus "a = select ...;" should be "a = (select ...);".
For nearly every other database, DML statements do not project the rows updated. They also do have the possibility of providing a proper result set, such as with the PostgreSQL/Oracle insert returning clause. Both of these are reasons why you don't typically see syntax such as "b = insert into...;". The more common method for getting the update count is from an implicit variable. One possibility is to use our existing "ROWS_UPDATED" as that implicit variable. So rather than "ROWS_UPDATED = insert into ...; x = ROWS_UPDATED;", you would just have "insert into ...; x = ROWS_UPDATED;". For most update procedures this obviates the need to explicitly set the ROWS_UPDATED value as it will be set implicitly by the last DML statement. However this could potentially be a breaking change in an existing procedure, such as "...; ROWS_UPDATED = ...; insert into ...; END". Another option is to use more explicit syntax, like PostgreSQL, with "GET DIAGNOSTICS x = ROWCOUNT"
This should also change the meaning of "e = exec foo...;". As per TEIID-1237, what we would like is "e = exec foo...;" to be a command statement with the side affect of setting e to the return value of the procedure. This syntax would only be valid if the procedure has a return parameter (currently it would be valid if the procedure has a resultset that has only a single column or no result set and only one out/inout or return parameter). The other change would be that the this statement would also set the implicit return cursor to the result set of the procedure if it has one. Since these would be potentially breaking changes (albeit in very narrow circumstances) we could put a validation in 7.1.1 that only allows that assignment if the procedure returns a result value and if there is a subsequent command statement. Otherwise we would have to use a slightly less intuitive syntax, like SQL Server, such as "exec e = foo...;".
Any thoughts?
Steve
13 years, 6 months
teiid jdbc connection questions
by Sun, Bo
Hi, Teiid users:
I started Teiid exploration and try to run the Quick start sample.
So far I have:
1) installed Jboss and Teiid run-time, and started Jboss with VDB DynamicPortfolio active.
2) installed Derby DB and populated sample tables/data, and started its NetworkServer.
3) I tested the Derby DB, I can select from tables
I have following questions hopefully you can help:
1) Please confirm that Teiid metadata repository save in xml formats in <jboss.home>/server/default/deploy directory (v.s. in a relational DB in MetaMatrx scenario).
2) in Sample README.txt file: it suggests to test Teiid VDB connection with following command:
$./run.sh localhost 31000 dynamicportfolio "select stock.* from product, (call MarketData.getTextFiles('*.txt')) f, TEXTTABLE(f.file COLUMNS symbol string, price bigdecimal HEADER) stock where product.symbol=stock.symbol"
It seems not working for me. What is the way you normally testing VDB connection?
3) I tried to use Squirrel to connect to teiid jdbc with following details:
Driver file: teiid-7.1.0.Final-client.jar
Class Name: org.teiid.jdbc.TeiidDriver URL:jdbc:teiid:DynamicPortfolio@mm://aistelab01:31000 (aistelab01 is the server where teiid installed)
username: admin
password: teiid
I got error: Teiid: unable to find a component used authenticate on to Teiid.
I am not able to jdbc connect to Derby DB also.
Please help
Best regards,
Bo Sun
13 years, 6 months