Hi Ray,
Again - I am just a user of base not privy to decisions or direction.
On 9/18/06, Jahn, Ray (R.) <[EMAIL PROTECTED]> wrote:
Andrew Jensen wrote:
> ... technical insight about ...
> ... SQL Query stored in OO Base GUI and ...
> ... SQL View stored in DB engine ...
Frank Schönheit wrote:
> ... JDBC technical support for SQL specifications ...
Andrew and Frank, thank you for the technical details.
Can someone clarify a few ambiguous issues
raised in the discussion so far?
Thanks.
Ray
-----
My following comments are built mainly from a
users' perspective, not from technical developers',
although I am involved in software development.
Databse users are in the SQL domain, talk in SQL,
and consense by SQL specification. They do not care
a bit whether SQL functionality is achieved by OO Base,
MS Access, MS C#, Java, Python, C++, JDBC, ODBC, etc.
IMO they are not in the SQL domain. They are in the domain of whatever
database management system they are using, the two are not synonymous.
The RIGHT product (GUI, etc.) is determined by
users experience, not by the easiest pathway of
implementation of chosen technologies.
I could not agree more.
* data parameters (host variables) in SQL standards
Does any version of SQL standards restrict
the usage of data parameters in SQL statements?
I have not found SQL specification that explicitly
prohibits data parameters in Create View statements.
Advice on data parameters in SQL standard is appreciated.
No SQL standard allows for replaceable parameters in a view that I know of.
SQL 2003 ( ISO 9075 ) does not, but it does support table functions, that
can for all intent and purpose create what appears to be a view with
parameters. The same has been done for a long time with Oracle, SQL-Server
and DB2 using stored procedures to take the users parameters and then apply
them to a select statement from a view. I believe this was the impetuous or
one of them for adding this support. ( Pure speculation on my part )
For example this document
http://www.sigmod.org/record/issues/0403/E.JimAndrew-standard.pdf published
by the ACM - SIGMOD you will find this reference on page 3:
"SQL-bodied table functions, on the other hand, allow
for so called "parameterized views" (as a reminder,
regular SQL views are fixed at the time they are
created)."
* HSQL DB support of data parameters in Create View
Drew, do you recall the versions of HSQL DB engine
and GUI or CUI used in your tests?
My tests involved the following.
engine: HSQL DB 1.8.0.5 stand alone engine
type: stand alone database file
CUI 1: java -cp d:/hsqldb/lib/hsqldb.jar
org.hsqldb.util.DatabaseManagerSwing
CUI 2: java -cp d:/hsqldb/lib/hsqldb.jar org.hsqldb.util.DatabaseManager
SQL: see the Create View statement with data parameter below
test: working, no Java exception thrown
verify: "commit; shutdown script;" to close file
*.script file still retains
the Create View statement
along with the data parameter
Create View "vv view" As
Select *
From "tt table"
Where "column 01" Like :xx_user_input;
A view can be created with a criteria, but these must be literal. ( as
mentioned above - fixed at the time of creation )
I ran a test - just now - using both the 1.0.8.5 jar with OOo 2.0.4 and
HSQLDB 1.0.8.5 in server mode with the supplied database manager as a swing
application.
If I attempt to execute a Create view command with a statement such as this
it fails.
The test consisted of running the following commands as a script in both
CREATE TABLE TEST1 ( ID IDENTITY, VAL VARCHAR( 50 ) );
INSERT INTO TEST1 VALUES ( NULL, 'Tom' );
INSERT INTO TEST1 VALUES ( NULL, 'Dick' );
INSERT INTO TEST1 VALUES ( NULL, 'Harry' );
INSERT INTO TEST1 VALUES ( NULL, 'Thelma' );
INSERT INTO TEST1 VALUES ( NULL, 'Donna' );
INSERT INTO TEST1 VALUES ( NULL, 'Harriet' );
CREATE VIEW V1 AS ( SELECT * FROM TEST1 WHERE VAL LIKE 'H%' );
CREATE VIEW V2 AS ( SELECT * FROM TEST1 WHERE VAL LIKE :PART_NAME );
In both environments the last statement throws an error, ':: unrecognized
token'.
But like you say - what does this mean to the end user..not a hill of beans
that's what. They just want to keep track of something.
* OO Base GUI
I am confused about the purpose of OO Base GUI.
Apparently HSQL DB engine 1.8.0.5 has already
implemented both string concatenation || operator
and data parameters (host variables) as specified
in SQL standard. (hand shaking between GUI and DB
engine is still needed on data parameters)
The concatenation token of double pipes not being recognized by the base
query designer- I believe, and Frank will scold me if I am
wrong...*chuckle*..was just a dumb mistake.
Does OO Base GUI attempt to supplant SQL capabilities
that belong to DB engines and
that are already implemented by DB engines?
Every DBMS package attempts to augment them. Looking at the test script I
did above ,for example. Using the database manager supplied by HSQLDB I am
unable to run this command either:
SELECT * FROM TEST1 WHERE VAL LIKE :PART_NAME
Why - because the engine does not support replacing the parameter. It has no
real UI after all except for stdin and stdout. (so to speak)
This, the substitution step, is usually supplied by software that
communicates with the engine. Most of these use something called a prepared
statement to do so.
Of course I can do so in a Base query.
Does OO Base GUI attempt to compensate for the
SQL incompleteness of some SQL DB engines?
Is it not precisely part of the reasons why
users choose one DB engine over the others?
* users' experience, convenience and benefit on
Stored Query in OO Base GUI vs.
SQL View stored in DB engine
Maybe I should add another word "confusion" to above.
To layman users, there is no practical distinction in the
functionality between Queries stored in OO Base GUI and
SQL View stored in DB engine. They will not even care!
Perhaps it is why MS Access GUI design chose not to offer
to users two modules of essentially same query functionality.
There is no competitive advantage over MS Access by offering
two different and confusing pathways for queries.
Speaking in the SQL domain there is a very real difference between a table
and a view.
However, back at the OOBase user domain...maybe they would be better off if
they did not see the word VIEW - unless they did something pro-active to do
so. In other words, having it as a prominent part of the UI may lead to
mis-use and confusion?
But that is just one opinion.
Moreover, the current View in the table section is assigned
the same code of table revision. This is a very odd condition.
Despite the semantics of virtual tables for SQL View,
View revision needs the Query GUI code, not table GUI code.
Has any OO Base or QA community member ever reported success
or ease about revising the View definition using OO Base GUI?
I suggest the following changes in OO Base GUI:
-- abolish the GUI distinction between SQL View and Stored Query,
-- merge SQL View and Stored Query functionality and code,
-- offer only one pathway to define and revise View and Query
(clarity to layman users).
The code merge will automatically offer for both
Stored Query and SQL View
-- SQL operator || capability
-- SQL data parameter (host variable) capability
as each is already provided in separate OO Base GUI modules.
Please prioritize on user benefit for any software decision.
User preferrence should trump technical opinions,
if any conflict, except for data integrity and security.
Strive not to substitute what technical developers considered
best for users' preferrence, no matter how strongly developers
have opined. The consequence can be severe market loss.
A recent example is the announcement on 2006-09-15 by
Ford Motor Company to layoff 1/3 salaried employees
in its North America (USA) division.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
