Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
  HXTT Cobol v2.1
  HXTT DBF v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  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 DBF
Query problem involving spaces
JF Laplante
2009-08-20 08:25:08.0
I recently purchased your DBF driver and I'm using it with ColdFusion v8 in JDBC mode with DBASEIII type tables.

I'm in the process of migrating some applications that were using the old Microsoft Visual Foxpro driver. So far; the only adjustment that I had to do to my queries was the date format. Now I have a problem in the simplest query and cannot find an answer.

With my old driver (MS Visual Foxpro) these two queries will return one row each:

SELECT ava,qsd,qe1,qfs
FROM STK
WHERE PRO = 'L180 PC '

SELECT ava,qsd,qe1,qfs
FROM STK
WHERE PRO = 'L180 '

Now with the HXTT, only the second query will return one row. The first query will return none. It seems like every time that there's a space in the where clause (like the space between 'L180' and 'PC'); no rows are returned with HXTT and one is returned with Visual Foxpro. Obviously; the correct answer is one row to each query.

I installed the latest update to the HXTT driver to make sure that I used the latest version.

Can you hekp me resolve this?

Thanks in advance.
Re:Query problem involving spaces
HXTT Support
2009-08-20 09:21:28.0
>Can you hekp me resolve this?
In standard sql, 'L180 PC ' doesn't equal to 'L180 ' or 'L180'. You can use
SELECT ava,qsd,qe1,qfs
FROM STK
WHERE PRO ilike 'L180%'

Re:Re:Query problem involving spaces
JF Laplante
2009-08-20 12:35:17.0
Thank you for your answer.

I never said in my case that these queries were returning the same answer.

Of course I know that these queries are not equal. The two example I gave you are two DIFFERENT queries wich each give different rows. I'm just having a problem where one query give the correct answer and the other gives 0 rows with HXTT but give the correct row with Visual Foxpro ODBC driver.

If you want; forget the second example and read the case like this:

With my old driver (MS Visual Foxpro) this query will return one row:

SELECT ava,qsd,qe1,qfs
FROM STK
WHERE PRO = 'L180 PC '

But with the HXTT driver; the same query returns zero row.

Can you help me?

Thanks.

Re:Re:Re:Query problem involving spaces
HXTT Support
2009-08-20 17:35:17.0
>WHERE PRO = 'L180 PC '
Remove the tail space characeter, and try WHERE PRO = 'L180 PC'.
Re:Re:Re:Re:Query problem involving spaces
JF Laplante
2009-08-21 17:50:41.0
I did try it with the TRIM function with the same results.

The record itself does have trail spaces wich are put there by the main DOS application that maintain those tables.

I can provide you with the table if you want.

Thanks in advance.

JF.
Re:Re:Re:Re:Re:Query problem involving spaces
HXTT Support
2009-08-21 18:42:06.0
Please send us such a sample.
Query problem involving spaces
JF Laplante
2009-08-26 13:28:01.0
Sorry for the delay. Since my last post; I've discovered several other instances where HXTT gives eroneous responses compared to other drivers and I'm in the process of removing it from production. I will send you shortly a sample .DBF file with its index file to identify the problem.
Re:Query problem involving spaces
JF Laplante
2009-09-02 06:07:20.0
Here are the files that I'm using for my tests:

http://www.jflaplante.org/stk.zip

Please tell me when you download them so I can remove them from public access.

I did some tests and my queries works with the Microsoft VFP driver and the also work with the STELS JDBC driver but somehow they fail with the HXTT driver that I purchased. Additionaly, when I write to a table with the HXTT driver; the main DOS application who uses the files gives out an error saying that the index are corrupted after a write with HXTT.

Here's a few queries variations with different drivers. The header contains the driver information and the number of records returned and the datasource (driver) used. I'm using ColdFusion 8.01 to access those tables.

datasource=siriuslocal_stels --> driver = Stels JDBC
datasource=siriuslocal_jdbc----> driver = HXTT JDBC
datasource=siriuslocal------------> driver = Microsoft VFP ODBC driver



Testtrim (Datasource=siriuslocal_stels, Time=16ms, Records=0) in C:\ColdFusion8\wwwroot\dmanic\testtrim.cfm @ 08:51:41.041

SELECT ava,qsd,qe1,qfs
FROM STK
WHERE PRO = 'L180 PC '

Testtrim (Datasource=siriuslocal_stels, Time=16ms, Records=1) in C:\ColdFusion8\wwwroot\dmanic\testtrim.cfm @ 08:51:41.041

SELECT ava,qsd,qe1,qfs
FROM STK
WHERE PRO = 'L180'

Testtrim (Datasource=siriuslocal_stels, Time=0ms, Records=1) in C:\ColdFusion8\wwwroot\dmanic\testtrim.cfm @ 08:51:41.041

SELECT ava,qsd,qe1,qfs
FROM STK
WHERE PRO = 'L180 PC'

Testtrim (Datasource=siriuslocal_stels, Time=31ms, Records=1) in C:\ColdFusion8\wwwroot\dmanic\testtrim.cfm @ 08:51:41.041

SELECT ava,qsd,qe1,qfs
FROM STK
WHERE PRO = trim('L180 PC ')

Testtrim (Datasource=siriuslocal_jdbc, Time=15ms, Records=0) in C:\ColdFusion8\wwwroot\dmanic\testtrim.cfm @ 08:51:41.041

SELECT ava,qsd,qe1,qfs
FROM STK
WHERE PRO = 'L180 PC '

Testtrim (Datasource=siriuslocal_jdbc, Time=47ms, Records=1) in C:\ColdFusion8\wwwroot\dmanic\testtrim.cfm @ 08:51:41.041

SELECT ava,qsd,qe1,qfs
FROM STK
WHERE PRO = 'L180'

Testtrim (Datasource=siriuslocal_jdbc, Time=0ms, Records=0) in C:\ColdFusion8\wwwroot\dmanic\testtrim.cfm @ 08:51:41.041

SELECT ava,qsd,qe1,qfs
FROM STK
WHERE PRO = 'L180 PC'

Testtrim (Datasource=siriuslocal_jdbc, Time=0ms, Records=0) in C:\ColdFusion8\wwwroot\dmanic\testtrim.cfm @ 08:51:41.041

SELECT ava,qsd,qe1,qfs
FROM STK
WHERE PRO = trim('L180 PC ')

Testtrim (Datasource=siriuslocal, Time=391ms, Records=1) in C:\ColdFusion8\wwwroot\dmanic\testtrim.cfm @ 08:51:41.041

SELECT ava,qsd,qe1,qfs
FROM STK
WHERE PRO = 'L180 PC '

Testtrim (Datasource=siriuslocal, Time=16ms, Records=1) in C:\ColdFusion8\wwwroot\dmanic\testtrim.cfm @ 08:51:41.041

SELECT ava,qsd,qe1,qfs
FROM STK
WHERE PRO = 'L180'

Testtrim (Datasource=siriuslocal, Time=15ms, Records=1) in C:\ColdFusion8\wwwroot\dmanic\testtrim.cfm @ 08:51:41.041

SELECT ava,qsd,qe1,qfs
FROM STK
WHERE PRO = 'L180 PC'

Testtrim (Datasource=siriuslocal, Time=0ms, Records=1) in C:\ColdFusion8\wwwroot\dmanic\testtrim.cfm @ 08:51:41.041

SELECT ava,qsd,qe1,qfs
FROM STK
WHERE PRO = trim('L180 PC ')


Thanks in advance.

JF.
Re:Re:Re:Re:Re:Re:Re:Re:Query problem involving spaces
HXTT Support
2009-09-02 08:00:13.0
>Please tell me when you download them so I can remove them from public access.
Download.

>I did some tests and my queries works with the Microsoft VFP driver and the also >work with the STELS JDBC driver but somehow they fail with the HXTT driver
> that I purchased.
Because only HXTT DBF will utilize your STK.CDX file to quicken query. Run once "reindex all on STK", then HXTT DBF will work normal too.

> Additionaly, when I write to a table with the HXTT driver; the main DOS
> application who uses the files gives out an error saying that the index are
> corrupted after a write with HXTT.
Yeah. You need to use lockType=VFP connection property to let your DOS application and HXTT DBF can do concurrent data modification.
Re:Query problem involving spaces
JF Laplante
2009-09-02 10:21:47.0
>Because only HXTT DBF will utilize your STK.CDX file to quicken query. Run once "reindex all on STK", then HXTT DBF will work normal too.

This index file was created by the DOS application. This DOS application also has a re-index function which is executed from time to time. Your statement seems to imply that HXTT DBF will be the only application using the CDX file. If I remove the index file; I get better results but your driver needs to be able to share the same index file. I'm I missing something? Strangely enough, the VFP ODBC driver is the only one to gives out a result for the first query.

>Yeah. You need to use lockType=VFP connection property to let your DOS application and HXTT DBF can do concurrent data modification.

The problem I get is not due to concurrent data modification. Even if I close down the other application completely; the second that HXTT DBF writes to the table, the index becomes corrupted for the other application and only a 're-index' from the DOS application can repair the problem. The index I provided to you was freshly re-contructed by the DOS application.

Thanks again for your help.

JF.
:Re:Query problem involving spaces
JF Laplante
2009-09-05 05:56:45.0
Hi, it has been a few days since this case has been updated. I would really need some help because I had to completely remove your driver from production and I feel I paid for something that I cannot use.

Thanks for your help.

JF.
Re:Re:Re:Re:Re:Re:Re:Re:Query problem involving spaces
JF Laplante
2009-09-15 14:02:06.0
Hi, it has been almost 2 weeks since I wrote my last post concerning my problem. I would appreciate some help. As it stands; right now; I can't use your driver for production and I feel like I paid a hefty amount of money for nothing.

Please read my post and use the sample database that I provided to provide me with solutions OR issue me a refund for my purchase.

Thanks

JF.
Re:Re:Re:Re:Re:Re:Re:Re:Query problem involving spaces
HXTT Support
2009-09-16 22:14:42.0
Sorry for missed that thread.

>Even if I close down the other application completely; the second that HXTT DBF
>writes to the table, the index becomes corrupted for the other application and
>only a 're-index' from the DOS application can repair the problem.
But we failed to recur your issue. Please email us such a dbf sample with sql.
Re:Re:Re:Re:Re:Re:Re:Re:Query problem involving spaces
HXTT Support
2009-09-16 22:15:54.0
Whether your DOS application can read correctly a reindexed index file which run "reindex all on youtable" by HXTT DBF?
Re:Re:Re:Re:Re:Re:Re:Re:Query problem involving spaces
JF Laplante
2009-09-17 09:05:49.0
The sample queries and the sample database are given in the 8th post of this thread.

As for the re-index; your driver must be able to work with the DOS application triggered re-index like other drivers do (VFP and Stels) because I have no means of detecting an application re-index and initiating a HXTT re-index afterward each time it happens.

Thanks for your help.
Re:Re:Re:Re:Re:Re:Re:Re:Query problem involving spaces
HXTT Support
2009-09-19 07:48:34.0
In our test, HXTT DBF won't corrupt your index file. What's your insert sql through HXTT DBF.
BTW, in our older test for ColdFusion MX6.1 before a few years, Connection String seems abnormal now. You should have to put Connection String into JDBC URL, for instance: jdbc:dbf:/c:data?lockType=VFP . I don't know whether that issue exists in ColdFusion v8 too, but you can try it.

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