Executing wild character in SQL query in QTP
I am getting error while executing the Query in the QTP, but the same query shows the results in the SQL.
Query:- select distinct a.table_id, b.* from table a, table b___
Error:- Unspecified error
QueryString =select distinct a.table_id, b.* from table a, table b
To really know what's going on, I'd need to know a bit more about what you're doing. What type of database is it (Oracle, SQL Server)? What driver are you using to connect to the database? What types of fields are in table b?
Its a Oracle DB, Microsoft ODBC driver for SQL,
Actually while exploring we came to know even (select * from table a) is not working, whereas if we give select 'column_name' from table a. we are getting results.
You can try escaping the special character or you can replace it with its ANSI equivalent:
sSQL = "select " & Chr(42) & " from table a"
Actually, I suspect the issue is that the Microsoft ODBC driver doesn't handle all Oracle data types. If you have a timestamp field or certain other Oracle-only fields, the ODBC driver will not let you query them. You can try converting them to some other format in your query or leaving them out.
Alternatively, you can use the Oracle driver, instead. It should let you query everything. It requires that you install it on every machine you run the script on, though.
I dont think you can use * with queries using odbc.
Try to get all the column names instead and try.
Also try what mark suggested using ASCII
You can definitely use * with ODBC queries. I do it every day. "select * from table a" works just fine.
I'm very confident that what's happening is a data type issue with the driver, especially after he said he could select column_name.table_id but not select * from a. That means that one of the other fields in a is not selectable. Further evidence is that it works in SQL Developer, so we know the query is good.
I've hit this issue when querying timestamp, xml, and binary_float/binary_double data types. I'm sure there are others.
Last edited by belewda; 10-14-2015 at 05:29 AM.
Yeah Dennis, i should have been precise - the issue could be mostly becoz of the timestamps and generally most of the tables would have such columns.
I think it would be better write all the columns required and cast the timestamp if there are any issues.