# Trying to executeStoredProcedure on VFP **URL:** \ **Category:** Classic Servoy\ **Created:** [September 14, 2012, 5:51pm UTC](https://forum.servoy.com/t/trying-to-executestoredprocedure-on-vfp/16480 "2012-09-14T17:51:36Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Providence1](https://avatars.discourse-cdn.com/v4/letter/p/f14d63/32.png) [@Providence1](https://forum.servoy.com/u/Providence1)\ **Post date:** [September 14, 2012, 5:51pm UTC](https://forum.servoy.com/t/trying-to-executestoredprocedure-on-vfp/16480/1 "2012-09-14T17:51:36Z")
Hey all you VFP users out there… ## Do you have any idea why I’m getting this error with the parameters or the sql types set in the executeStoredProcedure ? CUID error: com.servoy.j2db.persistence.RepositoryException:com.servoy.j2db.persistence.RepositoryException:org.apache.commons.dbcp.SQLNestedException: Borrow callableStatement from pool failed Borrow callableStatement from pool failed ```auto function getNewUID() { var _dsCUID = plugins.rawSQL.executeStoredProcedure("vam_beta", "{?=vsp_am_assignuidnew()}", [java.sql.Types.VARCHAR], [1], 1); if (_dsCUID.getException()) { throw "CUID error: " + _dsCUID.getException(); } else if (_dsCUID.getMaxRowIndex() == 0) { throw "Can't get new CUID value"; } application.output("CUID: " + _dsCUID.getValue(1, 1), LOGGINGLEVEL.DEBUG) return _dsCUID.getValue(1, 1); } ``` Here is the stored procedure I’m trying to access: ```auto USE [comp1] GO /******Object: StoredProcedure [dbo].[vsp_am_assignuidnew] Script Date: 09/14/2012 10:49:20******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO / **ID: AM80003.01 Name: vsp_am_assignuidnew Owner: AM ScriptDate: 06/28/2010** / ALTER procedure [dbo].[vsp_am_assignuidnew] @cretvalue char(15) output as begin declare @lcuid char(37) select @lcuid = newid() select @cretvalue = left(@lcuid,8) + right(@lcuid,7) end ``` I thank you in advance for any enlightenment you may be able to provide… ---
**Author:** ![rgansevles](https://avatars.discourse-cdn.com/v4/letter/r/3d9bf3/32.png) [@rgansevles](https://forum.servoy.com/u/rgansevles)\ **Post date:** [September 17, 2012, 8:45am UTC](https://forum.servoy.com/t/trying-to-executestoredprocedure-on-vfp/16480/2 "2012-09-17T08:45:33Z")
Providence, Does the log file provide more info? Rob ---
**Author:** ![omar](https://avatars.discourse-cdn.com/v4/letter/o/f0a364/32.png) [@omar](https://forum.servoy.com/u/omar)\ **Post date:** [September 17, 2012, 10:51am UTC](https://forum.servoy.com/t/trying-to-executestoredprocedure-on-vfp/16480/3 "2012-09-17T10:51:07Z")
Hi, The stored procedure is not executing on VFP but on MS SQL Server but that’s a minor detail. Could it be that you are specifying a SQL Type VARCHAR whereas the return value is a char(15)? ---
**Author:** ![Providence1](https://avatars.discourse-cdn.com/v4/letter/p/f14d63/32.png) [@Providence1](https://forum.servoy.com/u/Providence1)\ **Post date:** [September 17, 2012, 1:33pm UTC](https://forum.servoy.com/t/trying-to-executestoredprocedure-on-vfp/16480/4 "2012-09-17T13:33:15Z")
Omar, I would say that yes, that is an issues as well. I did try with that expected datatype but i didn’t post that version to the forum but here are the same results: UID error: com.servoy.j2db.persistence.RepositoryException: com.servoy.j2db.persistence.RepositoryException: org.apache.commons.dbcp.SQLNestedException: Borrow callableStatement from pool failed Borrow callableStatement from pool failed ```auto var _dsCUID = plugins.rawSQL.executeStoredProcedure("vam_beta", "{?=vsp_am_assignuidnew()}", [java.sql.Types.CHAR[15]], [1], 1); ``` ---
**Author:** ![omar](https://avatars.discourse-cdn.com/v4/letter/o/f0a364/32.png) [@omar](https://forum.servoy.com/u/omar)\ **Post date:** [September 17, 2012, 2:16pm UTC](https://forum.servoy.com/t/trying-to-executestoredprocedure-on-vfp/16480/5 "2012-09-17T14:16:22Z")
Hi Providence, An alternative could be: ```auto var vDataset = databaseManager.getDataSetByQuery('new_mssql', 'select newid()', null, 1); var result = vDataset.getValue(1,1) application.output(utils.stringLeft(result,8)+utils.stringRight(result,7)); ``` This would essentially do the same. ---
**Author:** ![Providence1](https://avatars.discourse-cdn.com/v4/letter/p/f14d63/32.png) [@Providence1](https://forum.servoy.com/u/Providence1)\ **Post date:** [September 17, 2012, 3:10pm UTC](https://forum.servoy.com/t/trying-to-executestoredprocedure-on-vfp/16480/6 "2012-09-17T15:10:32Z")
Rob: Attached is the servoy\_log.txt JM ---
**Author:** ![Providence1](https://avatars.discourse-cdn.com/v4/letter/p/f14d63/32.png) [@Providence1](https://forum.servoy.com/u/Providence1)\ **Post date:** [September 17, 2012, 7:40pm UTC](https://forum.servoy.com/t/trying-to-executestoredprocedure-on-vfp/16480/7 "2012-09-17T19:40:19Z")
Omar: That worked. Thanks for that! Still curious as to why I couldn’t call the stored procedure from Servoy. ---
**Author:** ![omar](https://avatars.discourse-cdn.com/v4/letter/o/f0a364/32.png) [@omar](https://forum.servoy.com/u/omar)\ **Post date:** [September 18, 2012, 8:47am UTC](https://forum.servoy.com/t/trying-to-executestoredprocedure-on-vfp/16480/8 "2012-09-18T08:47:53Z")
Great, what makes this stored procedure a little more complex is the input parameter (which the RAWSQL plugin can handle but requires more knowledge of how it builds the syntax that it fires at MSSQL). I think if the stored procedure is altered so that it doesn’t require the declaration of the input parameters it will be easier. From previous posts I have learned that the procedure\_declaration statement should be like this: ```auto {call vsp_am_assignuidnew()} ``` instead of using the ?= at the beginning of the statement “call” is used. ---
**Author:** ![rgansevles](https://avatars.discourse-cdn.com/v4/letter/r/3d9bf3/32.png) [@rgansevles](https://forum.servoy.com/u/rgansevles)\ **Post date:** [October 1, 2012, 7:10am UTC](https://forum.servoy.com/t/trying-to-executestoredprocedure-on-vfp/16480/9 "2012-10-01T07:10:52Z")
Providence, The real error is in the log file: the driver does not accept the sql: ```auto Caused by: java.sql.SQLSyntaxErrorException: Unknown escape sequence '{ vsp_am_assignuidnew()}' at com.inet.tds.ac.a(Unknown Source) at com.inet.tds.ac.a(Unknown Source) at com.inet.tds.as.a(Unknown Source) at com.inet.tds.bc.(Unknown Source) at com.inet.tds.bc.(Unknown Source) at com.inet.tds.al.(Unknown Source) at com.inet.tds.x.(Unknown Source) at com.inet.tds.ac.a(Unknown Source) at com.inet.tds.as.prepareCall(Unknown Source) at com.servoy.j2db.Za.Za.Zm.prepareCall(Zm.java:34) ``` Rob ---
**Author:** ![omar](https://avatars.discourse-cdn.com/v4/letter/o/f0a364/32.png) [@omar](https://forum.servoy.com/u/omar)\ **Post date:** [June 28, 2014, 1:23pm UTC](https://forum.servoy.com/t/trying-to-executestoredprocedure-on-vfp/16480/10 "2014-06-28T13:23:53Z")
I recently had to execute some SQL Server stored procedures from Servoy and since the official documentation is targeted towards PostgreSQL it sets you off on the wrong foot. So I figured out what it should be the hard way and decided to share my findings so you won’t have to. I made a pdf on it which you can find here: [http://www.visualfoxpro.com/Calling%20SQL%20Server%20Stored%20Procedures%20from%20Servoy.pdf](http://www.visualfoxpro.com/Calling%20SQL%20Server%20Stored%20Procedures%20from%20Servoy.pdf). For those who don’t like to read and just want a simple example: ```auto var serverName = "sql2012" var sp_name = '{call sp_getCustomerRange(?,?,?)}' var args=new Array() args=[1660, 1670, 'Brasil'] var typearg=[0,0,0] var maxrecords = 100 var _dataset=plugins.rawSQL.executeStoredProcedure(serverName, sp_name, args, typearg, maxrecords)} application.output("No. of records: " + _dataset.getMaxRowIndex(),LOGGINGLEVEL.DEBUG) application.output("Dataset field value record 1 column 5: " + _dataset.getValue(1, 5),LOGGINGLEVEL.DEBUG) ```