-
1. Re: Sql server StoredProcedure
shawkins Jun 4, 2015 3:38 PM (in response to anilnair)1 of 1 people found this helpful> I can see the stored procedure in my model though the storedprocedure name got suffixed with _1(as shown in the image)
Did you set the useProcedureSpecificName option? That is generally not needed and could be why you are getting the _1 suffix. If not then you'll want to check the metadata for sql server directly in a db explorer view, squirrel, etc.
> is there any configuration that i would have to do once I import the stored procedure from sql server inorder to make it visible and handle the result set it returns
The importer is looking for the result set columns in the DatabaseMetaData getProcedureColumns. If none are reported by sql server, then they will have to be added manually.
-
2. Re: Sql server StoredProcedure
anilnair Jun 4, 2015 6:42 PM (in response to shawkins)Thank you Steven for the reply.
The image that i had attached while importing objects from Sql server that's when it adds a suffix _1.
So now after selecting "Use fully Qualified Names" option during import I see the names of the storedprocedure like shown in the attached image.
still am confused with the name it takes while importing like in this case it takes the
Name:Companys.dbo.GetEmployeeDetails;1
Name In Source :"Companys"."dbo"."GetEmployeeDetails;1"
also I see this storedprocedure marked with a Icon fx.
I am using java program to connect to this VDB and the client code looks something like this
CallableStatement statement = connection.prepareCall("{call GetEmployeeDetails()}");
statement.execute();
ResultSet results = statement.getResultSet();
While (results.next()) {
System.out.println(results.getString(1) + "-"+ results.getString(2));
}
results.close();
statement.close();
The result is
org.teiid.jdbc.TeiidSQLException: TEIID30357 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30357 GetEmployeeDetails does not exist.
So I am not sure what name should I be using in the code to execute the storedprocedure.
The second thing is you mentioned
The importer is looking for the result set columns in the DatabaseMetaData getProcedureColumns. If none are reported by sql server, then they will have to be added manually.
I imported the storedprocedure from Sql server and the text for the storedprocedure is below
Create proc [dbo].[GetEmployeeDetails]
AS
BEGIN
select firstname,lastname from Employees
END
So once i imported I do not see any result set exposed(can be seen in the same image).So according to the comments it means that I would need to manually add these. Is there any example or documentation on how to add these when not exposed.
-
3. Re: Sql server StoredProcedure
rareddy Jun 4, 2015 10:31 PM (in response to anilnair)That looks like regression somewhere, which version of Designer are you using?
As an alternative, you can try using the "Teiid Connection Importer" in the Designer for importing the model and see if that is better. How you are executing the stored procedure looks correct.
-
4. Re: Sql server StoredProcedure
anilnair Jun 8, 2015 2:46 PM (in response to rareddy)I am using the Teiid Designer 9.1 beta.
I have imported the database objects using the importer(from sql server) and after Importing I can see the stored procedure name in the DataSource section of the xmi file.But the column names corresponding the storedprocedure are missing
Like I said previously when i do a preview data (designer) this is the exception I get
select * from ( exec "Companys"."Companys"."dbo"."GetEmployeeDetails1;1"() ) AS X_X
org.teiid.runtime.client.TeiidClientException: org.teiid.api.exception.query.QueryValidatorException: Remote TEIID30492 Command must project at least one symbol
I think as per steven :
The importer is looking for the result set columns in the DatabaseMetaData getProcedureColumns. If none are reported by sql server, then they will have to be added manually.
I saw similar thread on resultset not getting mapped Mapping stored procedures from SQL Data Source
So It would be of great I get pointers to some examples or documentation for mapping resultsets while using stored procedures from SQL server.
Thanks
Anil
-
5. Re: Sql server StoredProcedure
rareddy Jun 8, 2015 3:30 PM (in response to anilnair)Anil,
Basically you can right click on the imported stored procedure and add child->resultset, and add the expected columns in the resultset matching return columns from physical source.
Ramesh..
-
-
7. Re: Sql server StoredProcedure
anilnair Jun 8, 2015 7:55 PM (in response to anilnair)Thank you Ramesh and Steven.
I was able to get it working ..