Stored Procedures in Dev Environment

Questions and answers regarding general SQL and backend databases

Stored Procedures in Dev Environment

Postby ryan.m.beasley » Fri Mar 15, 2019 8:09 pm

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?
ryan.m.beasley
 
Posts: 60
Joined: Wed Oct 10, 2018 8:51 pm

Re: Stored Procedures in Dev Environment

Postby jcompagner » Thu Mar 21, 2019 5:19 pm

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..
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8454
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Stored Procedures in Dev Environment

Postby jcompagner » Thu Mar 21, 2019 5:54 pm

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?
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8454
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Stored Procedures in Dev Environment

Postby ryan.m.beasley » Thu Mar 21, 2019 6:03 pm

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.
ryan.m.beasley
 
Posts: 60
Joined: Wed Oct 10, 2018 8:51 pm

Re: Stored Procedures in Dev Environment

Postby ryan.m.beasley » Thu Mar 21, 2019 9:05 pm

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].
ryan.m.beasley
 
Posts: 60
Joined: Wed Oct 10, 2018 8:51 pm

Re: Stored Procedures in Dev Environment

Postby jcompagner » Fri Mar 22, 2019 2:16 pm

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
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8454
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Stored Procedures in Dev Environment

Postby ryan.m.beasley » Sat Mar 23, 2019 10:00 pm

Everything works locally, though. When I'm running from the developer I can pull all of the stored procedures just fine.
ryan.m.beasley
 
Posts: 60
Joined: Wed Oct 10, 2018 8:51 pm

Re: Stored Procedures in Dev Environment

Postby jcompagner » Mon Mar 25, 2019 1:14 pm

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.
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8454
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Stored Procedures in Dev Environment

Postby ryan.m.beasley » Mon Mar 25, 2019 3:02 pm

how would we go about doing this?
ryan.m.beasley
 
Posts: 60
Joined: Wed Oct 10, 2018 8:51 pm

Re: Stored Procedures in Dev Environment

Postby jcompagner » Mon Mar 25, 2019 5:50 pm

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?
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8454
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Stored Procedures in Dev Environment

Postby ryan.m.beasley » Mon Mar 25, 2019 8:55 pm

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
ryan.m.beasley
 
Posts: 60
Joined: Wed Oct 10, 2018 8:51 pm

Re: Stored Procedures in Dev Environment

Postby jcompagner » Tue Mar 26, 2019 10:21 am

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.
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8454
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Stored Procedures in Dev Environment

Postby rvanderburg » Tue Mar 26, 2019 10:55 am

Did you check if the db user in your production environment has the correct permissions on those stored procedures/functions?
rvanderburg
Site Admin
 
Posts: 68
Joined: Wed May 04, 2011 10:28 am

Re: Stored Procedures in Dev Environment

Postby ryan.m.beasley » Tue Mar 26, 2019 3:07 pm

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.
ryan.m.beasley
 
Posts: 60
Joined: Wed Oct 10, 2018 8:51 pm

Re: Stored Procedures in Dev Environment

Postby murmi » Wed Mar 27, 2019 10:47 am

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
Fritz Maurhofer
Maurhofer Informatik AG
CH-8340 Hinwil
murmi
 
Posts: 32
Joined: Thu Jul 22, 2010 5:10 pm

Next

Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 3 guests