Servoy Forum

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

Code: Select all

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:

Code: Select all

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...

Providence,

Does the log file provide more info?

Rob

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)?

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

Code: Select all

var _dsCUID = plugins.rawSQL.executeStoredProcedure("vam_beta", "{?=vsp_am_assignuidnew()}", [java.sql.Types.CHAR[15]], [1], 1);


Hi Providence,

An alternative could be:

Code: Select all

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.

Rob:

Attached is the servoy_log.txt

JM

Omar:

That worked. Thanks for that!

Still curious as to why I couldn't call the stored procedure from Servoy.

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:

Code: Select all

{call vsp_am_assignuidnew()}


instead of using the ?= at the beginning of the statement "call" is used.

Providence,

The real error is in the log file: the driver does not accept the sql:

Code: Select all

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.<init>(Unknown Source)
   at com.inet.tds.bc.<init>(Unknown Source)
   at com.inet.tds.al.<init>(Unknown Source)
   at com.inet.tds.x.<init>(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

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.

For those who don't like to read and just want a simple example:

Code: Select all

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)