Query problem involving spaces |
JF Laplante |
2009-08-20 08:25:08 |
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 |
>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 |
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 |
>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 |
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 |
Please send us such a sample.
|
Query problem involving spaces |
JF Laplante |
2009-08-26 13:28:01 |
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 |
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 |
>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 |
>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 |
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 |
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 |
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 |
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 |
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 |
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.
|