Page 1 of 2

Stored Procedures in Dev Environment

PostPosted: Fri Mar 15, 2019 8:09 pm
by ryan.m.beasley
I've just finished deploying a huge update to the servoy application I maintain into dev and I'm running into some issues with calling stored procedures from my sql server database I'm connected to. When running the client locally everything works as it should, however when I'm running it when the solution has been deployed to the dev environment I get this error:

Mar 15, 2019 2:03:04 PM com.servoy.j2db.util.Debug error
SEVERE: TypeError: Cannot find function sp_apply_overlay_to_ssp in object SPDataSourceServer[allnames,length]. (SPCentral/forms/ssp_input_sctm/ApplyOverlays#144) (Form Context: ssp_input_sctm), JSEvent(type = action, source = ScriptButton[sv__14574E25_D32A_449E_8A5B_79F595A86788:Apply Overlay(s) and Baselines], formName = ssp_input_sctm, elementName = <no name>, timestamp = Fri Mar 15 14:03:03 EDT 2019,modifiers = 16,x =0,y = 0,data = null)
at SPCentral/forms/ssp_input_sctm/ApplyOverlays:144 (ApplyOverlays)

org.mozilla.javascript.EcmaError: TypeError: Cannot find function sp_apply_overlay_to_ssp in object SPDataSourceServer[allnames,length]. (SPCentral/forms/ssp_input_sctm/ApplyOverlays#144)
at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:4224)
at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:4202)
at org.mozilla.javascript.ScriptRuntime.typeError(ScriptRuntime.java:4235)
at org.mozilla.javascript.ScriptRuntime.typeError2(ScriptRuntime.java:4254)
at org.mozilla.javascript.ScriptRuntime.notFunctionError(ScriptRuntime.java:4326)
at org.mozilla.javascript.ScriptRuntime.getPropFunctionAndThisHelper(ScriptRuntime.java:2562)
at org.mozilla.javascript.ScriptRuntime.getPropFunctionAndThis(ScriptRuntime.java:2544)
at org.mozilla.javascript.gen.SPCentral_forms_ssp_input_sctm_ApplyOverlays_44._c_ApplyOverlays_0(SPCentral/forms/ssp_input_sctm/ApplyOverlays:144)
at org.mozilla.javascript.gen.SPCentral_forms_ssp_input_sctm_ApplyOverlays_44.call(SPCentral/forms/ssp_input_sctm/ApplyOverlays)
at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:399)
at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3534)
at org.mozilla.javascript.gen.SPCentral_forms_ssp_input_sctm_ApplyOverlays_44.call(SPCentral/forms/ssp_input_sctm/ApplyOverlays)
at com.servoy.j2db.scripting.ScriptEngine.executeFunction(ScriptEngine.java:665)
at com.servoy.j2db.BasicFormController.executeFunction(BasicFormController.java:1074)
at com.servoy.j2db.BasicFormController.executeFunction(BasicFormController.java:956)
at com.servoy.j2db.BasicFormController.executeFunction(BasicFormController.java:827)
at com.servoy.j2db.FormController$ScriptExecuter.executeFunction(FormController.java:1042)
at com.servoy.j2db.ui.BaseEventExecutor.fireEventCommand(BaseEventExecutor.java:299)
at com.servoy.j2db.ui.BaseEventExecutor.fireEventCommand(BaseEventExecutor.java:265)
at com.servoy.j2db.ui.BaseEventExecutor.fireActionCommand(BaseEventExecutor.java:233)
at com.servoy.j2db.ui.BaseEventExecutor.fireActionCommand(BaseEventExecutor.java:228)
at com.servoy.j2db.smart.dataui.AbstractScriptButton$2.actionPerformed(AbstractScriptButton.java:660)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$500(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

I'm using the same database, connection string, etc. in the dev server as I am locally. I have the query procedures property checked for that server. The stored procedure exists in the database. I don't understand why I'm unable to call it in the dev environment. Any help?

Re: Stored Procedures in Dev Environment

PostPosted: Thu Mar 21, 2019 5:19 pm
by jcompagner
first lets double check if the procedures are set to true in the servoy.properties file:

in the WAR (that is a zip) you have a file WEB-INF\servoy.properties

is there the server.N.queryProcedures=true ?
(N is the index of the server)

Also make sure that the actual servoy.properties file that is used: <user.home>/.servoy/server/[context]/ also has the right content.
you can see it on the main admin page which is really used: Settings file for this server: XXXXXXXX

also double check that property on the admin page of that server..

Re: Stored Procedures in Dev Environment

PostPosted: Thu Mar 21, 2019 5:54 pm
by jcompagner
besides that can you check if what is returned when you do ask o the datasources.sp.server.allnames ?
Is that really just empty? So nothing is loaded?

Re: Stored Procedures in Dev Environment

PostPosted: Thu Mar 21, 2019 6:03 pm
by ryan.m.beasley
I just checked all three locations and the query procedures parameter is set to true. As for the datasources.sp.server.allnames, I don't have that stored procedure.

Re: Stored Procedures in Dev Environment

PostPosted: Thu Mar 21, 2019 9:05 pm
by ryan.m.beasley
or are you referring to the stored procedures I'm calling in my application? If so, the error I'm getting when I call a stored procedure is this:
TypeError: Cannot find function sp_name_of_procedure in object SPDataSourceServer[allnames,length].

Re: Stored Procedures in Dev Environment

PostPosted: Fri Mar 22, 2019 2:16 pm
by jcompagner
no "allnames" is a special property on that scope that should return all the id's it really has
But if a look closely to the output of "SPDataSourceServer[allnames,length]" that means that nothing is there except the 2 standard properties "allnames" and "length"
So for some reason it really doesn't load or is not able to load the stored procedures from the server.
So if the server property is really true, and there is nothing in the log that reports any kind of error, then i can't tell what really goes wrong ,because it seems to me that the database server really just does not return anything

Re: Stored Procedures in Dev Environment

PostPosted: Sat Mar 23, 2019 10:00 pm
by ryan.m.beasley
Everything works locally, though. When I'm running from the developer I can pull all of the stored procedures just fine.

Re: Stored Procedures in Dev Environment

PostPosted: Mon Mar 25, 2019 1:14 pm
by jcompagner
then we just need a case in our jira, with a setup how to reproduce this.
Something must be different in the setup i don't see how this behavior could be different.

Re: Stored Procedures in Dev Environment

PostPosted: Mon Mar 25, 2019 3:02 pm
by ryan.m.beasley
how would we go about doing this?

Re: Stored Procedures in Dev Environment

PostPosted: Mon Mar 25, 2019 5:50 pm
by jcompagner
i just tried to reproduce it on postgresql database
(purely locally)
created a function calling that in servoy, developer works
Then i create a WAR from the developer, deployed that on a local tomcat that connects to the same database as the developer
and the SP did get called just fine.
So i really can't reproduce this, we need to fully have your setup, are you really sure you do connect to the exact same database then your developer?

Re: Stored Procedures in Dev Environment

PostPosted: Mon Mar 25, 2019 8:55 pm
by ryan.m.beasley
absolutely certain. I'm pulling through the same exact data in the dev version that I pull when I access it locally. The only thing that's not pulling through is the stored procedures. Is there some way of setting up a screenshare call to show you my set-up. Are you using the same Driver that I'm using? com.microsoft.sqlserver.jdbc.SQLServerDriver

Re: Stored Procedures in Dev Environment

PostPosted: Tue Mar 26, 2019 10:21 am
by jcompagner
i am not testing against SQL Server i am just testing against my locally developer postgresql..
But there should not be any difference.. we just ask the server for its procedures and that is just a database meta data call, there is no difference in that call when in developer or deployed.. its exactly the same thing.
Problem is that we need to see/debug what is happening, so we need a setup where i can have a WAR that is deployed and connects to a database where it should get stored procs from (it does in developer) and then suddenly it doesn't when deployed.
Just looking through a screenshare is of no help.

Re: Stored Procedures in Dev Environment

PostPosted: Tue Mar 26, 2019 10:55 am
by rvanderburg
Did you check if the db user in your production environment has the correct permissions on those stored procedures/functions?

Re: Stored Procedures in Dev Environment

PostPosted: Tue Mar 26, 2019 3:07 pm
by ryan.m.beasley
This is actually my dev environment right now (trying to get this worked out in dev first then deploying to prod) and I'm using the same sqlid to access both my dev and local environment (they share the same database), and locally I'm accessing them just fine with the same sqlid. I did also check to be sure and the user is a db_datawriter so it does have the ability to run any of the stored procedures.

Re: Stored Procedures in Dev Environment

PostPosted: Wed Mar 27, 2019 10:47 am
by murmi
Hi

> I did also check to be sure and the user is a db_datawriter so it does have the ability to run any of the stored procedures.

As for my understanding: the db_datawriter role doesn't give a user enough rights to execute stored procedures. You have to grant the user execute rights on the schema where the sp's reside in.

see: https://docs.microsoft.com/en-us/sql/re ... erver-2017