I’m a Toad Data Point (TDP) newbie and I’m using TDP version 3.5 Pro Edition. My apologies if this question has already been asked and answered somewhere else–I did some searching but didn’t find my exact issue.
I’d like to be able to create and store local analysis tables using the cross-connection query capability in TDP. However, I’m running into the following error:
ERROR on line 1, col 14, offset 13: Heterogeneous Query extension found but appropriate QP5 language extension not set
Here is my simple query example:
CREATE TABLE Local Storage Connection.LOCALSCHEMA.L_EXAMPLE_TABLE AS
SELECT * FROM MYDB.WORLD.MYDBSCHEMA.EXAMPLE_TABLE
While this is just a really simple example query to demonstrate my problem, I’d really like to be able to write cross-connection queries that use multiple local and external tables to create and store new local analysis tables–kind of like using DB links.
Another post suggests that I might not be able to copy a table locally like this using a cross-connection query and that I should possibly use the Data Import Wizard instead–is this really my only option?
You can store the results of your cross-query in local storage. No problem. Just execute your query in Query Builder or editor. Then right click on grid and choose “Send to | Local Storage”. A window will pop up and you can name your table and change data types. This is how you get data into local storage from Cross-Connection Query. We do not support create table statement as show above.
I still have a problem though, I’m encountering this error message when I try to right the table to Local Storage via a right click and send to:
System.NullReferenceException
Object reference not set to an instance of an object.
Stack Trace:
at Quest.Toad.QueryBuilder.SqlBuilder.EvalReplacer.get_ReplacedSql()
at Quest.Toad.QueryBuilder.SqlBuilder.EvaluateSql(Connection connection, String sql, Boolean force)
at Quest.Toad.Db.Connection.PrepareQuery(IDbConnection connection, Boolean release, String sql, Object[] parameters)
at Quest.Toad.Db.Connection.Execute(IDbConnection connection, String sql, Object[] parameter)
at Quest.Toad.LocalStorage.BaseLocalStorageProvider.loadSql()
Is this a problem with my TOAD settings?
Also, I am still able to automate the writing of my query results table to Local Storage in this case? I’m trying to think ahead on how to automate this process.
In automation the ExecutSQL activity has an option to save the output to local storage. You would use that to automate. On the error above, what type of database are you connected to and what is your SQL in the Query Builder?
I’m guessing you’re asking about the database I’m querying and not the Local Storage (so far the Local Storage is a bit of a mystery to me). The remote database that I’m connecting is an Oracle 10g database.
My SQL in the Query Builder is rather complicated. It has three sections, each which join 7 tables based on certain conditions and union all the results of each of the three sections. I just tried something much more simple and didn’t receive the error above. Since I’m connecting to an Oracle database, I’m also using Oracle’s (+) syntax instead of explicit join statements. Given this, I’m wondering if there are some limitations I should keep in mind for these cross-connection queries and/or whether there are some guidelines for writing queries that would help me avoid problems?
I played around with my query a bit and even if I only performed one of the three queries (which is still joining 7 tables but no longer performs the union all) and used explicit joins (instead of Oracle’s (+) syntax), I still get a System.NullReferenceException when I try to write my results table to the Local Storage. Any advice would be appreciated.
I should also mention (in case it is relevant) that along with the System.NullReferenceException above, I also get the following error when trying to write the results of this query to Local Storage.
System.ApplicationException
QP5 Message => msg 'Function 5117 (QP5_SHL_INIT_CHUNKER): Handle (0x18401190) passed to API seems to point to a deleted QP5 object of type SHL.
YES = continue, NO = continue and stop bugging me, CANCEL = abort program’
caption ‘API Guard (QP5 version 5.264.14062.32545)’
type ‘19’
I went over this issue with a couple of developers. They say they have seen this and fixed it in the current Beta. Please download the current Beta and give it a test drive.
Yes, I did try the beta. I did not experience the same problem with the beta, so this appears to be fixed to me in the beta. Is it possible that this fix will be provided sooner than the next major version release of Toad Data Point?
Debbie, I just received another error that seems like it might possibly be related. My query is doing a simple SELECT * FROM TABLE_NAME and then I am right clicking the results and sending them to Local Storage. Here is the error I am getting:
MySQL Database Error
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near [some field values are reported here].
Inspecting the one record that appeared to produce this error and the values reported, I’m wondering if there are some characters that might be giving TDP some trouble? Perhaps a carriage return, tab, or other character that might not have been anticipated in a free-form database text field?
Does Toad Data Point have any documented limitations with regard to writing data to Local Storage with special characters? Or perhaps any limitations with regard to the length of a field that can be written to Local Storage?
I don’t know of any limitations at this point. is there anyway I can get all of the rows that is causing the problem? The best way to capture this and preserve the datatypes is to excute the query in a SQL Edtior and save as Toad Editor file (*.tef). This saves the query, rows and data types. You send send to my email and reference this forum thread. debbie.peabody@quest.com
Hi Debbie, I am getting this error, while running a Cross-connect query using CC Query Buiilder. I am using Toad Data Point 3.7.1.1011 (prof edition)
Error:9/30/2015 11:09:52 AM 0:00:00.051: Cross Query Database Error: ERROR: line 16, column 4, ending line 16, column 58: Found ‘`ExampleDB (username), usename …’: Heterogeneous Query extension found but the appropriate QP5 language extension flag was not set