Introduction to the Powerful Database Access Function Wb.sql
Overview
Database access is a common function in application development, including connecting to databases, querying data, updating data, deleting data, transaction processing, and executing arbitrary SQL statements. WebBuilder provides the API method Wb.sql for executing any SQL statements and retrieving their returned results.
In this article, we focus on the application scenarios of this method. For details about the API, please refer to Wb.sql.
Creating a Stored Procedure
Take the SQL Server database as an example to illustrate how to use Wb.sql to access a stored procedure and retrieve all data within it.
In a SQL Server database, we have the following stored procedure:
create procedure test_proc
(
@inParam int, -- Input parameter
@outParam int out -- Input & output parameter
)
as
begin
set @outParam=@inParam+2 -- A Set output parameter value
select * from wb_user -- B Return result set 1
delete from wb_key where 1=0 -- C Return affected rows
select * from wb_role -- D Return result set 2
return 123 -- E Procedure return value
end
The above procedure contains 2 input parameters (1 of which is input/output) and 5 outputs:
- A: Output parameter value
- B: Result set from the first select
- C: Number of affected rows from delete
- D: Result set from the second select
- E: Return value from the return statement
Accessing the Stored Procedure
To call the procedure, set input parameters, and get all 5 outputs, use the Wb.sql method:
let result = Wb.sql({ sql: '{{*returnValue*}=call test_proc({?myInParam?}, {*outParam*})}', params: {myInParam: 3 }, db: 'wb-sqlserver' });
In the code above, {?name?} denotes an input parameter and {*name*} denotes an output parameter. For details, see the API docs: Wb.sql.
After execution, the result is returned as a JSON object for further processing, similar to:
Summary
WebBuilder enables simple, user-friendly, high-performance database access. After running the code, you do not need to handle database resource or connection release — the system manages this automatically.
