Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access 7.1.142
  HXTT Cobol 5.0.142
  HXTT DBF 7.1.143
  HXTT Excel 6.1.146
  HXTT Json 1.0.114
  HXTT Paradox 7.1.142
  HXTT PDF 2.0.142
  HXTT Text(CSV) 7.1.142
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Word 1.1.142
  HXTT XML 4.0.143
Offshore Outsourcing
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
   
   
   
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)

HXTT Text(CSV)
Different results of running SQL query with four csv files by using HXTT csv driver
Dicky Ng
2020-04-01 08:07:21.0
Hi HXTT support,

Hope you are doing well.

I have an experience of an issue to run the below SQL query to retrieve data from four csv files by using your HXTT Text/CSV driver. They return the different results. I also found the results are different from SQL Developer. Please see the SQL query as below:

select distinct
O.SafeName as SafeName, OP.ObjectPropertyValue as Safe_Address,
FL.FileName as Safe_FileName, AC.BO_LoginID as BUS_Owner
from
ACP_Details AC
left join
ownerslist O on rtrim (AC.ACA)=O.OwnerName
left join
ObjectProperties20200129 OP on O.SafeId=OP.SafeId
left join
FilesList20200129 FL on FL.SafeId=OP.SafeId
Where
O.OwnerName like 'APP%Manager'
and
OP.ObjectPropertyName='Address'
and
OP.SafeID=245;


We are using two different versions of HXTT CSV drivers in the TEXT_JDBC30.jar. We would get the different results. See the details as below:

1. For Specification-Title: HXTT Text (CSV) JDBC 3.0 Package
Implementation-Title: com.hxtt.sql.text
Specification-Version: 7.0.005 on December 19, 2018

- After to run the above SQL query, it returns 208 rows.

2. For Specification-Title: HXTT Text (CSV) JDBC 3.0 Package
Implementation-Title: com.hxtt.sql.text
Specification-Version: 7.0.033 on September 12, 2019

- After to run the above SQL query, it returns 0 row.
- If remove the filter of "Where O.OwnerName....." from the SQL query, it would return 43 rows.

However, if I imported those four csv files into data tables under SQL developer, I run the above SQL query in SQL Developer and then return 1 row. ( It is expected result. )

Therefore, I don't know that is it a bug in HXTT Text/CSV driver or something wrong in using Left Join Statements. Can you please find free to have a look and then give me any advice? Many thanks for your help in advance.

Regards,

Dicky
Re:Different results of running SQL query with four csv files by using HXTT csv driver
HXTT Support
2020-04-01 20:22:55.0
Passed test with v7.0.086, which was released on April 01, 2020.
Re:Re:Different results of running SQL query with four csv files by using HXTT csv driver
Dicky Ng
2020-04-02 02:51:34.0
Hi HXTT Support,

Thank you for your feedback.

Regarding to your provided link to download v7.0.086, I clicked on the link to download the JDBC 3.0 Trial version. I checked the version under the MANIFEST.INF file. It said the Specification-Version: 7.0.085 on March 24, 2020. Can you please advise that I download the right driver or not? Many thanks in advance.

Regards,

Dicky
Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv driver
HXTT Support
2020-04-02 03:43:04.0
You can use that 7.0.085 too. If you met issue, please let us know.
Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv driver
Dicky Ng
2020-04-08 09:44:14.0
Hi HXTT Support,

Thank you for your comment.

Anyway, my colleague has downloaded the Text_JDBC43.jar with the version of HXTT CSV Driver 7.0.087 for me to test.

During the testing, I have used the same set of the four csv files to test again. I haven't tested the above SQL query with four csv files yet. Because I found this new version of HXTT driver having a problem to load two of the four csv files. The simple SQL query, details as below, is taking too long to query data and then hangs.

select distinct
OP.ObjectPropertyValue as Priv_Account
from
OP.ObjectProperties20200129
where OP.ObjectPropertyName='UserName'

This csv file, ObjectProperties20200129, only contains 22853 rows. However, if I tried to cut down this csv file to less than 1000 rows, it would be ok to complete to query the data.

So do you know what happen to the latest version of HXTT CSV driver. Is it any limitation of number of rows or size for the csv file? However, for the old versions of HXTT CSV driver ( 7.0.003 and 7.0.033 ), I did not see this problem. It was ok to query data from those four csv files. Just the query results were incorrect. Hope it is clear. Please advise. Many thanks for your help in advance.

Regards,

Dicky
Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv driver
HXTT Support
2020-04-08 23:24:43.0
If possible, please email us your ObjectProperties20200129 sample. BTW, I think your sql is
select distinct
OP.ObjectPropertyValue as Priv_Account
from
OP.ObjectProperties20200129 as OP /* You missed an as OP */
where OP.ObjectPropertyName='UserName'
Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv drive
Dicky Ng
2020-04-09 00:54:17.0
Hi HXTT,

Sorry for my mistake. The simple SQL query should be

Select distinct
OP.ObjectPropertyValue as Priv_Account
from
ObjectProperties20200129 OP
where
OP.ObjectPropertyName='UserName'

However, If I ran the SQL query without a filter of "Where OP.ObjectPropertyName='UserName'", it would be able to complete to load the data. Hope this can help.

Unfortunately, I cannot send you the csv file. Because this is our customer data file. Hope you understand. Anyway the previous versions of 7.0.003 and 7.0.033 did not have this issue for the above SQL query. Many thanks for your help in advance.

Regards,

Dicky
Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv dr
HXTT Support
2020-04-09 02:03:14.0
Tried many
insert into ObjectProperties20200129 select * from ObjectProperties20200129; /* a data file with two only data rows

SafeId,ObjectPropertyName,ObjectPropertyValue
245,Address,Street 1
240,Name,Mack

*/
to get a csv file
with 1048576 data rows.

Then
update ObjectProperties20200129 set ObjectPropertyName='abc' where recno()=155;

Used delayedClose=60 to enable temporary index test (without it, it seems normal and fast too). No issue for:
Select distinct OP.ObjectPropertyValue as Priv_Account from ObjectProperties20200129 OP where OP.ObjectPropertyName='abc'

So, if possle, please copy two sham data rows, and your connection property so that we can recur your issue.

Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
HXTT Support
2020-04-12 02:23:08.0
2020-04-12 fixed a temporary index file bug since 2019-11-28

Please download it.
Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
Dicky Ng
2020-04-14 09:18:09.0
Hi HXTT Support,

Thank you for the latest version of HXTT CSV Driver 7.0.088.

This version is no problem to load the data from my four CSV files. However, I tested the below SQL query but it still returns 0 rows.

select distinct
O.SafeName as SafeName,
OP.ObjectPropertyValue as Safe_Address,
FL.FileName as Safe_FileName,
AC.BO_LoginID as BUS_Owner
from
ACP_Details AC
left join
ownerslist O on rtrim (AC.ACA)=O.OwnerName
left join
ObjectProperties20200129 OP on O.SafeId=OP.SafeId
left join
FilesList20200129 FL on FL.SafeId=OP.SafeId
where
O.OwnerName like 'APP%Manager'
and
OP.ObjectPropertyName='Address'
and
OP.SafeID=245

Moreover, I tried another SQL query as below. I also got 0 rows.

select distinct
OP.ObjectPropertyValue as Priv_Account,
O.SafeName as SafeName
from
ACP_Details AC,
ownerslist O,
ObjectProperties20200129 OP,
FilesList20200129 FL
where
rtrim (AC.ACA)=O.OwnerName
and
O.SafeId=OP.SafeId
and
FL.SafeId=OP.SafeId
and
O.OwnerName like 'APP%Manager'
and
OP.ObjectPropertyName='UserName'

I don't know why. Anyway I tried to run both SQL queries to query four data tables in SQL Developer. I can get the right results. Please advise. Many thanks for your help in advance.

Regards,

Dicky
Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
Dicky Ng
2020-04-20 01:04:08.0
Hi HXTT Support,

Sorry to bother you. Just see that do you have any update for this problem or any suggestion? Many thanks for your help in advance.

Regards,

Dicky
Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
HXTT Support
2020-04-20 03:54:58.0
Please download the latest package, which has disabled the latest code for new format for temporary index file. Your issue should disappear.
Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
Dicky Ng
2020-04-20 08:34:08.0
Hi HXTT Support,

Thank you for your update.

We have downloaded the latest version of HXTT Text Driver 7.0.090 to test. Unfortunately, I still get 0 row.

I have tried to add one more safeid into my below SQL query. It also returns 0 rows. ( I expects to return 5 rows. )

select distinct
O.SafeName as SafeName,
OP.ObjectPropertyValue as Safe_Address,
FL.FileName as Safe_FileName,
AC.BO_LoginID as BUS_Owner
from
ACP_Details AC
left join
ownerslist O on rtrim (AC.ACA)=O.OwnerName
left join
ObjectProperties20200129 OP on O.SafeId=OP.SafeId
left join
FilesList20200129 FL on FL.SafeId=OP.SafeId
where
O.OwnerName like 'APP%Manager'
and
OP.ObjectPropertyName='Address'
and
OP.SafeID= (245, 202)

However, something is strange that if I do not use the WHERE statement in this SQL query, see the below, I would get the return value of 43 rows. ( but this is not expected value. )

select distinct
O.SafeName as SafeName,
OP.ObjectPropertyValue as Safe_Address,
FL.FileName as Safe_FileName,
AC.BO_LoginID as BUS_Owner
from
ACP_Details AC
left join
ownerslist O on rtrim (AC.ACA)=O.OwnerName
left join
ObjectProperties20200129 OP on O.SafeId=OP.SafeId
left join
FilesList20200129 FL on FL.SafeId=OP.SafeId

Hope the above information can help you to investigate the problem. Many thanks again for your help.

Regards,

Dicky
Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
HXTT Support
2020-04-20 17:45:24.0
Checked. Your sql has a minor syntax.
> OP.SafeID= (245, 202)

OP.SafeID in (245, 202)

then you should get the correct result.
Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
Dicky Ng
2020-04-21 00:50:28.0
Hi HXTT Support,

Sorry for my mistake again. it should be OP.SafeID in (245, 202). I also tried OP.SafeID in (245, 202, 35) and got 0 row. Thanks.

Regards,

Dicky.

Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
HXTT Support
2020-04-21 01:31:43.0
Try
select distinct
O.SafeName as SafeName,
OP.ObjectPropertyValue as Safe_Address,
FL.FileName as Safe_FileName,
AC.BO_LoginID as BUS_Owner,

O.OwnerName, OP.ObjectPropertyName, OP.SafeID /* Added it to see what's your data rows*/

from
ACP_Details AC
left join
ownerslist O on rtrim (AC.ACA)=O.OwnerName
left join
ObjectProperties20200129 OP on O.SafeId=OP.SafeId
left join
FilesList20200129 FL on FL.SafeId=OP.SafeId
Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
Dicky Ng
2020-04-21 04:45:31.0
Hi HXTT Support,

Thank you for your quick response.

As above your comments with SQL query, I have tried to test that query. It also 43 rows as same as the SQL query without your additional line.

Anyway, I think I am able to find out what goes on for HXTT Text driver and the SQL query. It is very strange. I tried to run the below SQL query with / without your additional line on the HXTT Test Tools.

select distinct
O.SafeName as SafeName,
OP.ObjectPropertyValue as Safe_Address,
FL.FileName as Safe_FileName,
AC.BO_LoginID as BUS_Owner,
O.OwnerName, OP.ObjectPropertyName, OP.SafeID
from
ACP_Details AC
left join
ownerslist O on rtrim (AC.ACA)=O.OwnerName
left join
ObjectProperties20200129 OP on O.SafeId=OP.SafeId
left join
FilesList20200129 FL on FL.SafeId=OP.SafeId

The results return only having data on the column of "AC.BO_LoginID as BUS_Owner" but other columns are empty. Seems the HXTT driver only retrieve the data from the csv file of "ACP_Details AC".

Therefore, I tried to run the below SQL query with "Where ..." to retrieve any data from the column of "AC.BO_LoginID".

select distinct
O.SafeName as SafeName,
OP.ObjectPropertyValue as Safe_Address,
FL.FileName as Safe_FileName,
AC.BO_LoginID as BUS_Owner,
O.OwnerName, OP.ObjectPropertyName, OP.SafeID
from
ACP_Details AC
left join
ownerslist O on rtrim (AC.ACA)=O.OwnerName
left join
ObjectProperties20200129 OP on O.SafeId=OP.SafeId
left join
FilesList20200129 FL on FL.SafeId=OP.SafeId
Where
AC.BO_LoginID = 'U390696'

Then it returns 1 row. It is same that only the column of "AC.BO_LoginID as BUS_Owner" contains U390696" but other columns are empty.

As the above my finding, it seems HXTT CSV driver does not like the "Left Join" statement. Hope this time the above information can help you to identify the issue. Many thanks in advance.

Regards,

Dicky


Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
HXTT Support
2020-04-21 05:14:25.0
O.OwnerName, OP.ObjectPropertyName, OP.SafeID ,recno('ac'),recno('o'),recno('op') can show the recno()

then

select OwnerName,* from ownerslist O where reno()=theShowNumber to see whether it's a null value.
Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
Dicky Ng
2020-04-21 06:24:00.0
Hi HXTT Support,

As above your comment, I have tried to run the below SQL query you suggest.

Select distinct
O.SafeName as SafeName,
OP.ObjectPropertyValue as Safe_Address,
FL.FileName as Safe_FileName,
AC.BO_LoginID as BUS_Owner,
O.OwnerName, OP.ObjectPropertyName, OP.SafeID,
recno('ac'),recno('o'),recno('op')
from
ACP_Details AC
left join
ownerslist O on rtrim (AC.ACA)=O.OwnerName
left join
ObjectProperties20200129 OP on O.SafeId=OP.SafeId
left join
FilesList20200129 FL on FL.SafeId=OP.SafeId

The result returns that the column of "AC.BO_LoginID as BUS_Owner" contains data, the column of "recno('ac') contains number but the columns of "recon('o')" and "recno('op') contains all zero.

Then I ran another SQL query, as below, which you asked.

select OwnerName,* from ownerslist O where reno()=theShowNumber

So I enter the 'theShowNumber' to 1 or 10 or 20. It returns the row with the data for all the columns.

Hope the above details are clear and can help. Thanks.

Regards,

Dicky
Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
HXTT Support
2020-04-21 06:36:25.0
>the column of "recno('ac') contains number
>but the columns of "recon('o')" and "recno('op') contains all zero.
For ac left join O, recon('o') contains zero means there's no data row match rtrim (AC.ACA)=O.OwnerName .

You can try a simple sql:
select AC.BO_LoginID as BUS_Owner, O.SafeName from ACP_Details AC, ownerslist O where rtrim (AC.ACA)=O.OwnerName and AC.BO_LoginID = 'U390696'
It should return 0 row.
Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
Dicky Ng
2020-04-21 06:49:44.0
Hi HXTT Support,

Yes, you're right that it return 0 row after to run the simple SQL query.

However, I tried to run the same SQL query under SQL developer. It return 17 rows.

Thanks.

Dicky
Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
HXTT Support
2020-04-21 17:39:40.0
So maybe there's space before your value?
Try trim function
select AC.BO_LoginID as BUS_Owner, O.SafeName from ACP_Details AC, ownerslist O where trim (AC.ACA)=trim (O.OwnerName) and AC.BO_LoginID = 'U390696'
to see what happen.
Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
Dicky Ng
2020-04-22 00:50:20.0
Hi HXTT Support,

As above your comment, I have tried your suggested SQL query with trim function. It still returns 0 rows. Thanks.

Regards,

Dicky
Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
HXTT Support
2020-04-22 05:04:25.0
If possible, you can use create table atable select top 1 * from ACP_Details AC where AC.BO_LoginID = 'U390696'
to create an atable sample.
Then create a sample btable create table btable select top 1 * from ownerslist O where where O.OwnerName='youwantData'.
Then email it to us, so that we can recur your issue.
Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
Dicky Ng
2020-04-22 07:26:55.0
Hi HXTT Support,

As your request, I have followed your instruction to create a couple of tables as below:

atable

ACPName,BO_LoginID,TO_LoginID,AppName,ACA,AD_Group,Description
CyberArk-USUNIXFOSIONUsers,U390696,ataan01,CyberArk,App-US-CyberArk-UNX-FOS-UK-ION-Users,App-US-CyberArk-UNX-FOS-UK-ION-Users,CyberArkSafeUserAccessthathaveAccesstoUSIONUnixAccountsSafe

btable

SafeID,SafeName,OwnerID,OwnerName
682,US-PRD-UNX-FOS-ION-ACCT,7422,App-US-CyberArk-UNX-FOS-UK-ION-Users

Hope the details can help. Thanks.

Regards,

Dicky
Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
HXTT Support
2020-04-24 06:13:27.0
Work normal for two data rows.

What's your jdbc url?
Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
Dicky Ng
2020-04-24 06:32:56.0
Hi HXTT Support,

Please find the below details of the jdbc URL:

1. the following jdbc URL the customer and us to use in our product. They got the same results.

- From us

jdbc:csv://///home/oracle/database/SampleData/CyberArkSafefiles/?_CSV_Header=true;_CSV_Quoter=";_CSV_EOL=\n;

- From the customer:

jdbc:csv://///home/oracle/database/SampleData/CyberArkSafefiles/?_CSV_Header=True;maxscanrows=-1;

2. The jdbc URL for HXTT Test Tool:

jdbc:csv:////temp?_CSV_Header=true;tmpdir=/temp;

Hope the above details are clear and can help. Many thanks.

Regards,

Dicky
Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
HXTT Support
2020-04-24 19:35:33.0
2020-04-25 fixed a bug for long string value for CSV format temporary index file on SOLO read mode since 2019-8-27
Re:Re:Re:Re:Re:Re:Re:Re:Different results of running SQL query with four csv files by using HXTT csv
Dicky Ng
2020-05-01 07:44:35.0
Hi HXTT Support,

Thank you for the latest version of HXTT Text Driver 7.0.095.

I have tested this driver with my SQL query. It returns 1 row. (It is expected row.) I also tried to test another SQL query with querying the same four csv files. It returns the value I expected. Therefore, I think the bug is fixed by HXTT Text Driver 7.0.095. Thank you very much for your time to help and solving out the problem.

Regards,

Dicky

Search Key   Search by Last 50 Questions




Google
 

Email: webmaster@hxtt.com
Copyright © 2003-2019 Heng Xing Tian Tai Lab of Xi'an City. | All Rights Reserved. | Privacy | Legal | Sitemap