Trying to executeStoredProcedure on VFP

Classic Servoy
1

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

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:

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…

2

Providence,

Does the log file provide more info?

Rob

3

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

4

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

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

Hi Providence,

An alternative could be:

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.

6

Rob:

Attached is the servoy_log.txt

JM

7

Omar:

That worked. Thanks for that!

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

8

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:

{call vsp_am_assignuidnew()}

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

9

Providence,

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

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

10

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:

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)