Running SQL Stored Procedures from VL

[HamadSheikh]

Hi Guys, Is there a way to run SQL Server Stored Procedures from within Visual LANSA? A customer has an existing SQL Server database application built with .NET technologies and they're interested in converting it to a Visual LANSA + VL-WEB application. There is a lot invested in SQL stored procedures within the SQL database that they do not want to duplicate that logic using LANSA business rules. I understand that one way of accomplishing this is by using database table triggers on the SQL tables themselves, but I would prefer a solution where the actual SQL tables are left untouched. There are no problems in loading the SQL tables as LANSA OTHER files.

[JamesDuignan]

Hi Hamad,

Yes this is possible to do in LANSA with PC Other Files by using SELECT_SQL Free Format.
The call to the stored procedure is done via the 'Using' parameter, which can either be a direct call to the database such as:

SELECT_SQL FIELDS(#EMPNUMBER #LASTNAME #FIRSTNAME ) FROM_FILES((EMPLOYEEDATA)) USING(DBO.FINDEMPLOYEES)
ADD_ENTRY TO_LIST(#EMPLOYEES)
ENDSELECT

Alternatively a Query can be built up and passed as a string to via the using parameter:

#SQLQUERY:= "DBO. FINDEMPLOYEESNAMED@NAME = '" + #Name + "'"
SELECT_SQL FIELDS(#EMPNUMBER #LASTNAME #FIRSTNAME ) FROM_FILES((EMPLOYEEDATA)) USING(#SQLQUERY)
ADD_ENTRY TO_LIST(#EMPLOYEES)
ENDSELECT

For more examples and considerations for using stored procedures from SELECT_SQL Free Format see the documentation here:
http://docs.lansa.com/14/EN/lansa015/co ... FREE_E_eg5

[wizard]

Hello.
How actually this going to work? Did this query need to be done in method routine in the web page where we want to display output or in srvroutine?

[Stewart Marshall]

WebPages execute in the browser on the client device and have no access to the server other than by executing SRVRoutines.

This means that ALL database processing must execute on the server, in SRVRoutines coded in Server Modules.

[wizard]

This use for one user input only. Correct me if I am wrong.

#SQLQUERY:= "DBO. FINDEMPLOYEESNAMED@NAME = '" + #Name + "'"
SELECT_SQL FIELDS(#EMPNUMBER #LASTNAME #FIRSTNAME ) FROM_FILES((EMPLOYEEDATA)) USING(#SQLQUERY)
ADD_ENTRY TO_LIST(#EMPLOYEES)
ENDSELECT

The #NAME is the user input.

What if I have multiple user input?
How the #SQLQUERY gonna be?
How the SELECT_SQL statement gonna be?

Regards.