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
Wrong results running access queries
Raul Cosio
2008-12-15 10:07:43.0
Hello,

I have a problem running some queries with access drivers. What I did is to run this query:

select
t.UNIDAD, t.RUTA, t.POSICION, t.FUERZA,
dr.[DM CODE] as DMCODE,
e.RUTA,
e.NOMBRE,
e.APELLIDOS,
e.EMPLEADO,
e2.EMPLEADO as DM_EMPLEADO,
e2.NOMBRE as DM_NOMBRE,
e2.APELLIDOS as DM_APELLIDOS,
str(ad.unidad) + ' ' + ad.area as areacode,
ad.ruta as amcode
from
[Territorios Unidades] t,
[Estructura MSD] e,
[0 Distritos Rutas] dr,
[Estructura MSD] e2,
[1 Areas Dtos] ad
where
t.ruta = e.ruta and
t.posicion = 'REP' and
dr.ruta = t.ruta and
e2.ruta = dr.[DM CODE] and
e2.posicion <> 'REP' and
iif(ad.ruta='31000', 'E3100',
iif(ad.ruta='32000', 'E3200', ad.ruta)) = dr.[DM CODE]

Then I saved the result in text format and compared to the result returned running the same query within MSAccess, the result is diffrent for almost all
records. Below is a CSV row from MSAccess and then from HXTT Access:

MSD2,MSD2,,,0,Vacante,32200720,Rolando Enrique Del Angel Meza,,,0,Vacante,,S1293,S1293
MSD2,MSD2,,,0,Vacante,32200720,Rolando Enrique Del Angel Meza,,,0,Vacante,,MSD129,MSD129

Is there some join in this query not supported by hxtt driver or do I have to consider some special condition in order to run it?
Re:Wrong results running access queries
HXTT Support
2008-12-15 23:03:00.0
Please send us such a mdb sample so that we can recur your issue.
Re:Re:Wrong results running access queries
Raul Cosio
2008-12-26 06:41:09.0
Hello,

unfortunately for security restrictions I'm not allowed to share that database, but trying to reproduce the problem in a db with similar data I've found two possible causes: first one is the fact that column names are identical in two cases: t.RUTA and e.RUTA, that can be solved easly with an alias, the second one is the string concatenation operation: "str(ad.unidad) + ' ' + ad.area as areacode" who hxtt driver is reporting as "2Sur" instead of the "2 Sur" reported in Access, is there some other operator I could use to solve this difference? thank you...
Re:Re:Re:Wrong results running access queries
HXTT Support
2008-12-27 00:22:06.0
>is there some other operator I could use to solve this difference?
Try ODBCTrimBehavior=false connection property.
Re:Re:Re:Re:Wrong results running access queries
Raul Cosio
2008-12-30 09:08:19.0
I ran the same query with the new property but it returned the same result. This is how I included de property:

Properties props = new Properties();
props.put("ODBCTrimBehavior", false);
Class.forName("com.hxtt.sql.access.AccessDriver");
Connection conn = DriverManager.getConnection("jdbc:access:/" + mdbFileName, props);

But the result reports "2Sur" instead of the "2 Sur" reported in Access in column "areacode"...

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