Creating views with stored procedures in Sybase ASE 15.7
5 December, 2013
Hello, I have a problem using stored procedures of Sybase ASE 15.7 for creating a view.
Note: I am using Yellowfin 6.3, Operating System Windows 7, Java 64 bits, Sybase 64 bits
I will explain the steps:
a)I am using the demo database included in Sysbase ASE 15 (in the wizard of installation you will see the option to install database samples). The name of the database is pubs2.
b)I have created a stored procedure, this is the script to create the stored procedure:
[code]
create proc authors4
( @nombre varchar(30) )
as
begin
select *
from authors
where au_fname = @nombre
end
[/code]
When I try to create the view I have the following error:
Oh no! An error occurred saving your details to the database. Please try again later
I have looked in the logs file and it seems a problem with the result value: (here you will see some lines of the exception in the log)
YF:2013-12-05 11:44:15:ERROR (MIViewProcess:callStoredProcedure) - Error: java.s
ql.SQLException: Parameter '@RETURN_VALUE' not found in the parameter list.
java.sql.SQLException: Parameter '@RETURN_VALUE' not found in the parameter list
.
at net.sourceforge.jtds.jdbc.JtdsCallableStatement.findParameter(JtdsCal
lableStatement.java:88)
at net.sourceforge.jtds.jdbc.JtdsCallableStatement.registerOutParameter(
JtdsCallableStatement.java:357)
at com.hof.mi.process.MIViewProcess.callStoredProcedure(MIViewProcess.ja
va:15109)
I have read your different posts about the subject:
http://www.yellowfinbi.com/YFForum-Return-value-for-Stored-Proc-?thread=102274
http://www.yellowfinbi.com/YFForum-creating-view-based-on-stored-procedure-?thread=142602
But it hasn't solved my problem
How Can I solve?
Thanks
Note: I am using Yellowfin 6.3, Operating System Windows 7, Java 64 bits, Sybase 64 bits
I will explain the steps:
a)I am using the demo database included in Sysbase ASE 15 (in the wizard of installation you will see the option to install database samples). The name of the database is pubs2.
b)I have created a stored procedure, this is the script to create the stored procedure:
[code]
create proc authors4
( @nombre varchar(30) )
as
begin
select *
from authors
where au_fname = @nombre
end
[/code]
When I try to create the view I have the following error:
Oh no! An error occurred saving your details to the database. Please try again later
I have looked in the logs file and it seems a problem with the result value: (here you will see some lines of the exception in the log)
YF:2013-12-05 11:44:15:ERROR (MIViewProcess:callStoredProcedure) - Error: java.s
ql.SQLException: Parameter '@RETURN_VALUE' not found in the parameter list.
java.sql.SQLException: Parameter '@RETURN_VALUE' not found in the parameter list
.
at net.sourceforge.jtds.jdbc.JtdsCallableStatement.findParameter(JtdsCal
lableStatement.java:88)
at net.sourceforge.jtds.jdbc.JtdsCallableStatement.registerOutParameter(
JtdsCallableStatement.java:357)
at com.hof.mi.process.MIViewProcess.callStoredProcedure(MIViewProcess.ja
va:15109)
I have read your different posts about the subject:
http://www.yellowfinbi.com/YFForum-Return-value-for-Stored-Proc-?thread=102274
http://www.yellowfinbi.com/YFForum-creating-view-based-on-stored-procedure-?thread=142602
But it hasn't solved my problem
How Can I solve?
Thanks
Hi Jordi,
Can you please try using the latest Sybase JDBC driver.
I have attached it to this post.
You will need to ;
1. Goto your data source connection and hit 'test connection'.
Note the driver details .
1. Stop Yellowfin
2. Go to yellowfinappserverwebappsROOTWEB-INFlib and remove the file jconn3.jar (just put it somewhere outside of the Yellowfin folder)
3. Save the attached file into the above location.
4. Start Yellowfin and test your data source connection to ensure your driver version has changed.
5. Try your stored procedure.
Please let me know how you go.
Regards,
David
Can you please try using the latest Sybase JDBC driver.
I have attached it to this post.
You will need to ;
1. Goto your data source connection and hit 'test connection'.
Note the driver details .
1. Stop Yellowfin
2. Go to yellowfinappserverwebappsROOTWEB-INFlib and remove the file jconn3.jar (just put it somewhere outside of the Yellowfin folder)
3. Save the attached file into the above location.
4. Start Yellowfin and test your data source connection to ensure your driver version has changed.
5. Try your stored procedure.
Please let me know how you go.
Regards,
David
Hi David, I can't see any attached file.
Anyway I have tried with the jdbc included in the sysbase 15. In the installation folder of Sysbase exists a folder named jconnect-7_0. I have seen to files:
jconn4.jar
jTDS3.jar
I have deleted from the lib folder of Yellowfin:
jconn3.jar
jtds-1.2.5.jar
and I have copied the files of jconnect7 but It doesn't work.
I will wait for your news.
Anyway I have tried with the jdbc included in the sysbase 15. In the installation folder of Sysbase exists a folder named jconnect-7_0. I have seen to files:
jconn4.jar
jTDS3.jar
I have deleted from the lib folder of Yellowfin:
jconn3.jar
jtds-1.2.5.jar
and I have copied the files of jconnect7 but It doesn't work.
I will wait for your news.
Hi Jordi,
Sorry didn't attach it properly it looks like.
I just attached it.
When you say it doesn't work, do you mean you have the same stored procedure error?
Are you able to test your data source connection and get the driver details as mentioned in step 4 above.
Also, not sure what the JTDS driver is for. You should only need to worry about the jconn4.jar .
Regards,
David
Sorry didn't attach it properly it looks like.
I just attached it.
When you say it doesn't work, do you mean you have the same stored procedure error?
Are you able to test your data source connection and get the driver details as mentioned in step 4 above.
Also, not sure what the JTDS driver is for. You should only need to worry about the jconn4.jar .
Regards,
David
I have followed your steps but it does't works.
The details for jconn3 and jconn4 are the same.
I have attached a screenshot of the details.
The details for jconn3 and jconn4 are the same.
I have attached a screenshot of the details.
Hi Jordi,
Thanks for that.
I've run a few tests and come up with some strange results I cannot completely understand as yet.
If I use Sybase IQ driver, then my view & stored proc works without any issues.
But, if I choose Sybase ASA or ASE, my view never returns tables.
What I'm still trying to understand is how the Sybase IQ/ASE/ASA differ in terms of drivers, as they all seem to use the same one.
I'm also having issues getting the Syntax right for the JDBC URL, when using the 'Generic JDBC' option and manually choosing the driver.
Do you have SybaseIQ as an available drop-down?
Regards,
David
Thanks for that.
I've run a few tests and come up with some strange results I cannot completely understand as yet.
If I use Sybase IQ driver, then my view & stored proc works without any issues.
But, if I choose Sybase ASA or ASE, my view never returns tables.
What I'm still trying to understand is how the Sybase IQ/ASE/ASA differ in terms of drivers, as they all seem to use the same one.
I'm also having issues getting the Syntax right for the JDBC URL, when using the 'Generic JDBC' option and manually choosing the driver.
Do you have SybaseIQ as an available drop-down?
Regards,
David
Hi David, I have triedyour solution. Now I can see the stored procedures but at the moment to put the parameter the system returns an error.
See the image.7
See the image.7
Hi Jordi,
Sorry for the delay, but I do not see a screenshot or error :(.
Could you also get the error from the Yellowfin.log file when this happens, as it will show what is happening behind the scenes.
Thanks,
David
Sorry for the delay, but I do not see a screenshot or error :(.
Could you also get the error from the Yellowfin.log file when this happens, as it will show what is happening behind the scenes.
Thanks,
David
Hello,
I'm having the same problem as Jordi .
My data-source is Sybase ASE version 15.5 and I can not create a view from a stored procedure .
I tried putting the driver jconn3 and I could not select the option Sybase ASE on Connect the data-source . It must also be the driver jtds - 1.2.5.jar in the lib
When you create a view with stored procedure in Sybase ASE 15.5 I get the following message :
When I go to the next step I can not drag fields to the category.
Hope you can help me.
regards
I'm having the same problem as Jordi .
My data-source is Sybase ASE version 15.5 and I can not create a view from a stored procedure .
I tried putting the driver jconn3 and I could not select the option Sybase ASE on Connect the data-source . It must also be the driver jtds - 1.2.5.jar in the lib
When you create a view with stored procedure in Sybase ASE 15.5 I get the following message :
When I go to the next step I can not drag fields to the category.
Hope you can help me.
regards
Hi,
I think the comment above has also been raised here :
http://www.yellowfinbi.com/YFForum-Error-creating-a-view-from-a-stored-procedure-in-Sybase-ASE-15-5-?thread=155010
It looks like we need to test this in our office, so bare with us while we get this setup.
I will update both posts once the test has been completed.
Sorry for the inconvenience this has caused.
Regards,
David
I think the comment above has also been raised here :
http://www.yellowfinbi.com/YFForum-Error-creating-a-view-from-a-stored-procedure-in-Sybase-ASE-15-5-?thread=155010
It looks like we need to test this in our office, so bare with us while we get this setup.
I will update both posts once the test has been completed.
Sorry for the inconvenience this has caused.
Regards,
David
hi david,
thanks for the reply.
I wait impatiently the result.
Regards.
thanks for the reply.
I wait impatiently the result.
Regards.
Hi Guys,
Just letting you know I'm in the process of downloading Sybase 15.7 , and it's taking a while :(.
So won't be able to test today.
Regards,
David
Just letting you know I'm in the process of downloading Sybase 15.7 , and it's taking a while :(.
So won't be able to test today.
Regards,
David
Hi David,
Do not worry.
You could also try it with version 15.5, we need to work on this version, also are not equal because one uses jconn4 and the other jconn3.
Thank you very much for everything.
Regards.
Miguel
Do not worry.
You could also try it with version 15.5, we need to work on this version, also are not equal because one uses jconn4 and the other jconn3.
Thank you very much for everything.
Regards.
Miguel
Just an update.. downloaded and installed, now just trying to figure out how to use it and create some stored procs ;).
Regards,
David
Regards,
David
Hi Guys,
Another update on this.
It�s a pain to use Sybase :(.. we can get it started, but run into issues when creating tables and then trying to connect.
We�re going to re-install on Linux and try there.
But.. some good news. This month some fixes are going into 7 for some issues with stored procedures , as there seem to be issues with Oracle & Postgres.
So this could all be related.
We going to see if the same fix can also be applied for Sybase.
In any case, you should be able to use Sybase stored procedures in the February 7 update (due on Friday 28th of Feb).
Please let me know if there was anything else you were after in the meantime.
Regards,
David
Another update on this.
It�s a pain to use Sybase :(.. we can get it started, but run into issues when creating tables and then trying to connect.
We�re going to re-install on Linux and try there.
But.. some good news. This month some fixes are going into 7 for some issues with stored procedures , as there seem to be issues with Oracle & Postgres.
So this could all be related.
We going to see if the same fix can also be applied for Sybase.
In any case, you should be able to use Sybase stored procedures in the February 7 update (due on Friday 28th of Feb).
Please let me know if there was anything else you were after in the meantime.
Regards,
David
hi,
We are waiting for the new version of yellowfin. Already available?
I saw on the website that besides updating the version 7 also has been done with version 6.3. Have you solved the stored procedures for this version?
Regards,
Miguel
We are waiting for the new version of yellowfin. Already available?
I saw on the website that besides updating the version 7 also has been done with version 6.3. Have you solved the stored procedures for this version?
Regards,
Miguel
Hi Miguel,
Yes this should also be working in Yellowfin 7.
Please let us know if you do run into issues on the latest builds.
Regards,
David
Yes this should also be working in Yellowfin 7.
Please let us know if you do run into issues on the latest builds.
Regards,
David