Forum Discussion

vbace2's avatar
vbace2
Occasional Contributor
14 years ago

Project Variables - DB Table - DB2

Using TestComplete Enterprise version 8...



I created a DBTables store using an ODBC connection to a DB2 database.  I actually used a query to pick the appropriate columns and rows I wanted.  I was able to do this and get valid data from the DB2 database.



I then tried to create a DB Table variable for my project.  Again, I tried to use an ODBC connection to the same database as I did for the store.  I was able to connect to the database and I was able to see a list of tables.  When I see the list of tables, it says at the top "Select a database table, view or query from which the DB Table variable will retrieve data.".  When creating the store, I was able to specify a query.  Here, it doesn't look like I can create a query.  Also, if I select a table and click the View Table button, I get an error that says "Unable to display the table data. The following error occurred: [IBM][CLI Driver][DB2/NT]SQL0104N An unexpected token '* from' was found following 'select '. Expected tokens may include: '<value_expr_primary_or_stub>'. SQLSTATE=42601".  I tried to use it in a script to see if it would work there.  I tried to log the column count and I get pretty much the same message.



Am I doing something wrong? I seem to be able to create a store from the database just fine.  I also seem to be able to pull up the tables when trying to create a DB Table variable for the project.  It just doesn't seem to work correctly to retrieve the data.  Maybe there is something wrong in the application?



If it helps, the following is what the default value shows for the DB Table variable.  The connection string looks the same as the connection string for the store.  The table only shows the table name though.  I would have thought the schema name would be included in there all.  The table isn't owned by the user, and the same name could be in multiple schemas.



{Table: "STABLE"; connection string: "Provider=MSDASQL.1;Password=dbpass;Persist Security Info=True;User ID=dbuser;Data Source=CARHORSD"}



Thanks

6 Replies

  • vbace2's avatar
    vbace2
    Occasional Contributor
    Just to add an update to this...



    I tried creating a store and selecting a table/view.  When I do that, I get an error that says dbuser.mytable (or myview) is undefined. The reason is that mytable (and myview) do not live under the dbuser schema.  It is automatically trying to add that to the table name.  Also, if I use the custom query and I just select a table name or user name in the SQL designer, I get a message that says it is unable to find the table.  Again, I am thinking it is trying to add the user that I am logged into as the schema/owner of the table.  If I just type out my SQL, it works for me.



    Thanks

  • Hello Dan,





    We have investigated possible causes of the described problem. 

    Probably, the issue is related to the OLE DB Provider specifics. When retrieving data from a table, TestComplete calls the Connection.Execute method of the corresponding OLE DB Provider with the Options parameter set to adCmdTable. According to this MSDN article, "This value is not supported by the OLE DB Provider for AS/400 and VSAM or the OLE DB Provider for DB2".





    Currently I can suggest that you try using one of the alternative providers listed at http://www.connectionstrings.com/ibm-db2.





    Does this work for you?
  • vbace2's avatar
    vbace2
    Occasional Contributor
    Ok, I have switched to the IBM OLE DB Provider for DB2.  I'm not sure why I didn't select that in the first place.  I guess I missed it.  Anyway, even with the IBM OLE DB Provider for DB2 I am still having issues.  I am basically having the same issues that I was when using the Microsoft OLE DB Provider for ODBC Drivers.  However, I do have an update...



    Originally, I said I could only create a DB Table store if I selected Custom Query and typed out my query.  It is still true that if I want to create a custom query that I need to type out my query.  I can type it out in the "Enter SQL Query" window, or I can click the Design button and type it out in the SQL tab of the SQL Designer.  I can't use the SQL Designer and select the tables or views from the list.  The tables and views do not have the schema associated with them.



    Originally, I didn't think I could select the data source types of Table or View, that I had to select Custom query.  However, I can use the view and table options.  After I select which view or table I want from the dropdown list, I just need to add my schema name and a period in front of the table name.  That seems to work just fine.



    However, I still can't figure out the project variables.  When it comes time to select the table name, I am just given a scrollable list where I can just select a table.  Since the schema name is not in front of the table, I don't think it can find the table.  Also, unlike the DB Table stores, I can't type the schema name in front of the table name.



    Any ideas?

  • Hello Dan,





    Thank you for informing us about your progress. We have found out that DB2 requires specifying a scheme name before a table name, and this is the cause of the problem. Currently, TestComplete does not allow specifying a scheme name for a table name in a DB Table project variable. We will try to fix this problem in one of future versions.





    At the moment, I recommend that you keep using a DBTable checkpoint to store the data.