Trying to executeStoredProcedure on VFP

Questions, tips and tricks and techniques for scripting in Servoy

Trying to executeStoredProcedure on VFP

Postby Providence1 » Fri Sep 14, 2012 7:51 pm

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
Providence1
 
Posts: 456
Joined: Tue Aug 17, 2004 2:36 am
Location: New York, NY

Re: Trying to executeStoredProcedure on VFP

Postby rgansevles » Mon Sep 17, 2012 10:45 am

Providence,

Does the log file provide more info?

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: Trying to executeStoredProcedure on VFP

Postby omar » Mon Sep 17, 2012 12:51 pm

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)?
Intrasoft, Founder
Omar van Galen
omar@intrasoft.nl
+31-(0)6-21234586
Servoy Developer
omar
 
Posts: 377
Joined: Sat Feb 12, 2011 4:51 pm
Location: Intrasoft, The Netherlands

Re: Trying to executeStoredProcedure on VFP

Postby Providence1 » Mon Sep 17, 2012 3:33 pm

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);
Providence
Providence1
 
Posts: 456
Joined: Tue Aug 17, 2004 2:36 am
Location: New York, NY

Re: Trying to executeStoredProcedure on VFP

Postby omar » Mon Sep 17, 2012 4:16 pm

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.
Intrasoft, Founder
Omar van Galen
omar@intrasoft.nl
+31-(0)6-21234586
Servoy Developer
omar
 
Posts: 377
Joined: Sat Feb 12, 2011 4:51 pm
Location: Intrasoft, The Netherlands

Re: Trying to executeStoredProcedure on VFP

Postby Providence1 » Mon Sep 17, 2012 5:10 pm

Rob:

Attached is the servoy_log.txt

JM
Attachments
Push to VAM Error.txt
(34.15 KiB) Downloaded 398 times
Providence
Providence1
 
Posts: 456
Joined: Tue Aug 17, 2004 2:36 am
Location: New York, NY

Re: Trying to executeStoredProcedure on VFP

Postby Providence1 » Mon Sep 17, 2012 9:40 pm

Omar:

That worked. Thanks for that!

Still curious as to why I couldn't call the stored procedure from Servoy.
Providence
Providence1
 
Posts: 456
Joined: Tue Aug 17, 2004 2:36 am
Location: New York, NY

Re: Trying to executeStoredProcedure on VFP

Postby omar » Tue Sep 18, 2012 10:47 am

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.
Intrasoft, Founder
Omar van Galen
omar@intrasoft.nl
+31-(0)6-21234586
Servoy Developer
omar
 
Posts: 377
Joined: Sat Feb 12, 2011 4:51 pm
Location: Intrasoft, The Netherlands

Re: Trying to executeStoredProcedure on VFP

Postby rgansevles » Mon Oct 01, 2012 9:10 am

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
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: Trying to executeStoredProcedure on VFP

Postby omar » Sat Jun 28, 2014 3:23 pm

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)
Intrasoft, Founder
Omar van Galen
omar@intrasoft.nl
+31-(0)6-21234586
Servoy Developer
omar
 
Posts: 377
Joined: Sat Feb 12, 2011 4:51 pm
Location: Intrasoft, The Netherlands


Return to Methods

Who is online

Users browsing this forum: No registered users and 7 guests