Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Cobol v2.1
  HXTT DBF v5.2
  HXTT Excel v4.2
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
  HXTT XML v1.2
Offshore Outsourcing
Oracle Data Import/Export
DB2 Data Import/Export
Sybase Data Import/Export
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Hongxin Technology & Trade Ltd. of Xiangtan City (abbr, HXTT)

HXTT ACCESS
Problem with parentheses
Nikita
2007-08-29 00:08:12.0
Consider simple table:
CREATE TABLE t1 (ID int)

HXTT Access throws an error when the following query is executed:
SELECT * FROM (t1 AS a INNER JOIN t1 AS b ON a.id=b.id) WHERE EXISTS (SELECT * FROM t1 c WHERE c.id = b.id)
Invalid column: b.idjava.sql.SQLException: Invalid column: b.id
Error Code:213249
SQL State:S0022

at com.hxtt.global.SQLState.SQLException(Unknown Source)
at com.hxtt.sql.dc.a(Unknown Source)
...


Though if parentheses in the from-clause are ommited everything is fine:
SELECT * FROM t1 AS a INNER JOIN t1 AS b ON a.id=b.id WHERE EXISTS (SELECT * FROM t1 c WHERE c.id = b.id)


This is an example of a problem that I've met while trying to use HXTT Access with our existing database. Meanwhile Access accepts both queries equally well. Moreover, Access tends to put parentheses everywhere when views are constructed using its wizard.

So, of course we can fix particular problem query. But this is not acceptible solution as there is no guarantee that this problem will not arise with new views.

Is there any possibility that you fix that problem in HXTT Access?

Thanks in advance,
Nikita
Re:Problem with parentheses
HXTT Support
2007-08-29 01:14:18.0
>SELECT * FROM (t1 AS a INNER JOIN t1 AS b ON a.id=b.id) WHERE EXISTS (SELECT * FROM t1 c WHERE c.id = b.id)
>Invalid column: b.idjava.sql.SQLException: Invalid column: b.id
>Though if parentheses in the from-clause are ommited everything is fine:
With/without () is used to decided whether all columns in a join table is visible/invisible for outside sql. For instance, SELECT * FROM (t1 AS a INNER JOIN t1 AS b ON a.id=b.id) as ab WHERE EXISTS (SELECT * FROM t1 c WHERE c.id = ab.id) will work.
Re:Re:Problem with parentheses
Nikita
2007-08-29 02:32:59.0
First of all, it is not clear how to refer the field id of table b and not table a. In the example above there is no problem as a.id=b.id. But how to rewrite query if we change <> to =?
SELECT *
FROM (t1 AS a INNER JOIN t1 AS b ON a.id<>b.id)
WHERE EXISTS (SELECT * FROM t1 c WHERE c.id = b.id)


Secondly, () are used in Access to set the order of joining and it doesn't affect visibility.
So, in queries like:
SELECT * FROM (t1 INNER JOIN t2 ON t1.id=t2.id) INNER JOIN t3 ON t1.id=t3.id
we can't simply omit parentheses as Access will not accept such query.


So, it appears that views defined in Access and correct for Access are not compatible with HXTT ACCESS. Right?

Re:Re:Re:Problem with parentheses
HXTT Support
2007-09-02 22:30:26.0
v2.1.068 changed the visibility of columns in JOIN table with parentheses from invisible to visible.
Re:Re:Re:Re:Problem with parentheses
Nikita
2007-09-03 03:42:27.0
Indeed, the problem is fixed. Thank you!

PS: it seems that you have banned my ip: 83.219.159.2 from visiting
http://www.hxtt.com/support_view_issue.jsp

I'm sorry, if my posts seemed offensive to you. Hope you remove the ban
so we can continue evaluating your product for the needs of our company.
Re:Re:Re:Re:Re:Problem with parentheses
HXTT Support
2007-09-03 04:40:34.0
Checked, and removed your IP from spam list. Because some robots submit always rubbish messages to our support site, so that our web has to filtered it automatically.

Search Key   Search by Last 50 Questions




Google
 

Address: 9 Station Rd., Xiangtan City, Hunan Province, P.R. China
Postcode: 411100
Phone: (86)731-58225727
Fax: (86)731-58225727
Email: webmaster@hxtt.com
Copyright © 1999-2011 Hongxin Technology & Trade Ltd. | All Rights Reserved. | Privacy | Legal | Sitemap