Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
  HXTT Cobol v2.1
  HXTT DBF v5.2
  HXTT Excel v4.2
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  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 Text(CSV)
problem with use of distinct in sql statements
Mike Sims
2010-07-13 07:27:41.0
Hi I downloaded an salutation copy of the version 3 and 4 of your HXTT Text driver, however I am having problems. When ever I use the distinct command on columns of csv files which are integers, the results are not accurate.

The length of the data in this file is up to 8 numbers long. This is being brought in as a varchar in my sql client. The distinct is not accurate (many records are not being returned). I can work around the problem by casting the column as a Char() however, this is fixed length and I really need a data type that is variable length. To cover cases when this ID column is a greater length.
Re:problem with use of distinct in sql statements
HXTT Support
2010-07-13 07:52:46.0
Maybe you can try maxScanRows=-1 connection property. If you failed still, please let us see your sql and txt sample.
Re:Re:problem with use of distinct in sql statements
Mike Sims
2010-07-20 05:48:10.0
Yes already tried the maxScanRows with no success.

Checking the MetaData, it appears getPrecision and getColumnDisplaySize are both returning 4.

SQL below:

select distinct upper(trim("Account Id")) as appid, trim("Category") as resource from sampledata where resource = 'LBRX'

sampledata is too big to paste but I can provide over email.

Strangely, problem only occurs when using the upper and trim functions.
Re:Re:Re:problem with use of distinct in sql statements
HXTT Support
2010-07-20 23:18:21.0
>it appears getPrecision and getColumnDisplaySize are both returning 4.
>select distinct upper(trim("Account Id")) as appid, trim("Category") as resource
> from sampledata where resource = 'LBRX'
Maybe you're using an older package. Both fucntion will return 128 now. Please try with the latest package.
Re:Re:Re:Re:problem with use of distinct in sql statements
Mike Sims
2010-07-21 00:43:09.0
This was tested using the latest package. Same issue exists.
Re:Re:Re:Re:Re:problem with use of distinct in sql statements
HXTT Support
2010-07-21 01:14:12.0
>it appears getPrecision and getColumnDisplaySize are both returning 4.
Upload today. If it returns 4 still, please let us know your jdbc url.
Re:Re:Re:Re:Re:Re:problem with use of distinct in sql statements
Mike Sims
2010-07-21 01:57:02.0
Have downloaded and tried the very latest version. Results are the same however.

jdbc url is jdbc:text:////d:\Virtual Machines?_CSV_Header=true;MaxRowScan=-1
Re:Re:Re:Re:Re:Re:Re:problem with use of distinct in sql statements
Mike Sims
2010-07-21 02:01:31.0
apologies. I pasted the wrong URL. correct one is:

jdbc:text:////d:\Virtual Machines?_CSV_Header=true;MaxScanRows=0
Re:Re:Re:Re:Re:Re:Re:Re:problem with use of distinct in sql statements
HXTT Support
2010-07-21 02:03:16.0
Please send our your test sample, since we failed to recur your issue.

>select distinct upper(trim("Account Id")) as appid, trim("Category") as resource
> from sampledata where resource = 'LBRX'
It should return 128 length for upper(trim("Account Id")) .
Re:Re:Re:Re:Re:Re:Re:Re:problem with use of distinct in sql statements
Mike Sims
2010-07-21 02:21:59.0
email sent to support@hxtt.com with sample file and instructions on how to recreate issue.
Re:Re:Re:Re:Re:Re:Re:Re:problem with use of distinct in sql statements
HXTT Support
2010-07-21 02:48:37.0
Found the issue.

>jdbc:text:////d:\Virtual Machines?_CSV_Header=true;MaxScanRows=0
You should use jdbc:text:////d:\Virtual Machines?_CSV_Header=true;maxScanRows=-1
with select distinct trim("Account Id") as appid from sampledata where trim(Category) = 'LBRX'
Or

jdbc:text:////d:\Virtual Machines?_CSV_Header=true;maxScanRows=0
with select distinct "Account Id" as appid from sampledata where trim(Category) = 'LBRX' since "Account Id" has been converted to int value.

We will support distinct trim(intColumn) soon.

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