-
1. Re: Remote Query Execution
rareddy Jul 25, 2013 8:59 AM (in response to aswathkhan)Do you have international characters in your data file? can you post the transformation you are using for customer table, i.e the TEXTTABLE construct you used.
-
2. Re: Remote Query Execution
shawkins Jul 25, 2013 9:57 AM (in response to rareddy)> When I Query like "Select * from Customer Limit 100" it works fine , but when I try to fetch 20,000 records I get the below error
That message appears whenever you attempt to do an operation (cursor request, lob read, or metadata request) after the result set/statement has been closed. I don't see lobs on your customer table (correct?), so that shouldn't be the issue. What is your client environment? If you are not explicitly closing from the client side, you'll want to check the server side logs to see if something is causing an unexpected termination.
> select "first name","last name" from customer limit 10000, I get the different error as,
Do you know the encoding of your file? Are you changing the encoding through the file translator encoding property?
-
3. Re: Remote Query Execution
aswathkhan Jul 26, 2013 6:21 AM (in response to shawkins)Steve,
1. My Client is SquirrelSQL, if limit the records upto 8192 it works fine, but anything more than that it fails.
I do not have LOBs as part of the data, but I do have couple of columns defined as String(4000)
Also, Server.log has no extra details apart form the error description.
2. I did not change any encoding property. But I had defined the character set as UTF-8 and then changed it to ISO-8859-1 just to chk whether it fixes the issue..
I changed the char set in source.xmi file as below,
<tags xmi:uuid="mmuuid:aa90da05-a8eb-4d56-b048-a2c6e5199207" key="connection:FlatFileCharSet" value="ISO-8859-1"/>
-
4. Re: Remote Query Execution
aswathkhan Jul 26, 2013 6:25 AM (in response to rareddy)Ramesh,
There is no international characters in the Customer Data, just plain english.
Following is the transformation text,
SELECT
"First Name", "Last Name", Gender, "Date of Birth", "NINO Number", Cliref, "Address 1", "Address 2", "Address 3", "Address 4", "Address 5", "Address 6", "Post Code", "Phone Number", Email, Role, "Policy Number"
FROM
(EXEC UnisureCustomer_source.getTextFiles('UnisureCustomer.csv')) AS f, TEXTTABLE(file COLUMNS "First Name" string, "Last Name" string, Gender string, "Date of Birth" string, "NINO Number" string, Cliref string, "Address 1" string, "Address 2" string, "Address 3" string, "Address 4" string, "Address 5" string, "Address 6" string, "Post Code" string, "Phone Number" string, Email string, Role string, "Policy Number" string HEADER) AS A
-
5. Re: Remote Query Execution
rareddy Jul 26, 2013 7:35 AM (in response to aswathkhan)Aswath,
The place to change the "encoding" is on the translator. In the VDB explorer windwow, when you select the source model for file, you can set translator override property called "encoding". The XMI file MUST NOT be edited manually, they are to be used only by the Designer. Also, XMI files are converted to another metadata format before they are consumed in the Teiid Server, and XMI files are not consulted.
Ramesh..
-
6. Re: Remote Query Execution
aswathkhan Jul 26, 2013 8:24 AM (in response to rareddy)Ramesh,
I could not figure out where the VDB explorer window is.Can you help me figuring out.
Note: I had to manually edit the xmi file as I could not deploy it using the console/Admin Shell.
Thanks,
Aswath
-
-
8. Re: Remote Query Execution
aswathkhan Jul 29, 2013 7:46 AM (in response to rareddy)Ramesh,
I did set the encoding property to ISO-8859-1 in the vdb explorer as seen in the scr shot attached. But I still could not query any records more than 8192 (strange number 1024 x 8). Refer the documnet attached for the scr shots. Also it happens only if I deploy and access remotely.
Regarding deployment,
The problem with my scenario is that th source location is different from the development and the test environment as development happend in windows and test environment is in Linux, so I had to change the path in the .xmi file as in the linux environment.
I did not get error while I deployed using the admin console, but when I tried accessing the VDB, I got the error like "Source file not found".
But then I did it manually using "*.dodeploy" placeholder file, it did worked. Not sure what was the problem.
-
Remote Error.docx 299.5 KB
-
-
9. Re: Remote Query Execution
rareddy Jul 29, 2013 8:50 AM (in response to aswathkhan)Aswath,
That looks ok, can you share your text file for me to test?
Deployment: Again, you should NOT change .xmi files manually, if the path for your file source is different in your development and test environment, then you should develop and test fully in development environment, and when you are trying to deploy to test environment, the datasource configuration should reflect the path specific to that environment. Otherwise you place the file on shared drive where the path is same in both. So, it is not an error.
When deploying a VDB manually by copying it to the "deployments" folder, you are required to create to .dodeploy file to deploy the vdb, but if it was same environment, you should have seen the "source file not found" error. So, fix the data source configuration. You can edit "standalone-teiid.xml" file under "resource-adapters" subsystem to reflect the correct path in that particular server and restart.
Ramesh..
-
10. Re: Remote Query Execution
rareddy Jul 29, 2013 8:51 AM (in response to rareddy)Also what is the error that you see when accessed remotely? Is limiting rows to 8192 in both local and remote?
-
11. Re: Remote Query Execution
aswathkhan Jul 31, 2013 3:54 AM (in response to rareddy)Ramesh,
As I said earlier, the following is error messages we get when we access the remotely deployed VDB. The below errors occurs only if we access the remote VDB, local VDB works fine.
When I Query like "Select * from Customer Limit 100" it works fine , but when I try to fetch 20,000 records I get the below error
org.teiid.jdbc.TeiidSQLException: org.teiid.core.TeiidProcessingException: TEIID30495 The request X2c170KLQExg.101 has been closed.
Similarly, when I execute the query lisitng the column names like,
select "first name","last name" from customer limit 10000, I get the different error as,
org.teiid.jdbc.TeiidSQLException: TEIID30179 java.nio.charset.MalformedInputException: Input length = 1
-
12. Re: Remote Query Execution
aswathkhan Jul 31, 2013 6:03 AM (in response to rareddy)Ramesh,
Due to data complaince reasons, I cannot share the text files with you.
Can you tell me, if you have seen this kind of behaviour with TEIID VDBs before, that the same data works fine locally but not when deployed remotely. What are the possible reasons?
-Aswath
-
13. Re: Remote Query Execution
rareddy Jul 31, 2013 7:23 AM (in response to aswathkhan)Aswath,
Clarify what you mean by Local VDB and Remote VDB? There is no such terminology. However, Teiid supports "local" and "remote" JDBC connections, where "local" means in-VM as to client JDBC application is in the same VM as the server.
According to the code and your exception there is an error during reading your file, which probably does not match the encoding or you have a corrupt file. I suggest may be trimming the file from top of number of rows that you can read, then re-reading it, until you reach to offending line.
No, I do not remember I seen error like this one.
Ramesh..
-
14. Re: Remote Query Execution
shawkins Jul 31, 2013 10:31 AM (in response to rareddy)Aswath,
Can you provide the corresponding server side log entries for these exceptions? That should provide a stacktrace and give more of indication of when the exceptions are happening.
Steve