Binding form fields to SQL Stored Procedures (OpenInsight 64-bit)
At 25 NOV 2019 06:52:36AM mdaniel wrote:
Good morning,
I'm attempting to migrate several Intranet based ASP applications that utilize MS SQL Server user defined stored procedures. The stored procedures basically fall into one of three categories - SELECT data for viewing or editing, UPDATE data into the SQL tables, or DELETE SQL Table records.
Can OI10 forms bind/link to the data elements to a user defined stored procedures? Meaning, after entering a record id in the form, it executes the stored procedure, retrieves the data, and populates the form fields. Also, after editing a field it then uses the UPDATE stored procedure to save the data back to the SQL Table. Additionally, many of the form fields included multiple choices which are assigned in combo boxes. Each of these combo boxes Lists are populated via the results from a different stored procedure.
Mike Daniel
480-699-0212
At 27 NOV 2019 06:35PM Donald Bakke wrote:
Mike - Unless OI 10 introduced a new feature I'm aware of, there isn't support for this in the way you are requesting.
Databinding must be against a defined data source. So if you want to use a form's internal databinding logic then you must connect to a data source and then bind the form's controls to the table/columns from that data source.
If that isn't sufficient, you could create a non-databound form and use the relevant form events (READ/WRITE/DELETE) to call your own stored procedure (BTW, I'm assuming you mean a Basic+ stored procedure and not a SQL stored procedure). You can do this easily enough but you'll have to map the data results to the fields yourself.
You could also write your own BFS, thus making your own data connector and then you can call any code you want when the READ, WRITE, DELETE, etc., database actions are called. Alternatively, you can still bind a form to an OI table (for convenience) but instead of creating a BFS, just create an MFS that intercepts the database actions and then call your stored procedure. We've done this several times.
At 27 NOV 2019 10:56PM mdaniel wrote:
Hi Don,
The stored procedure's I'm referring are SQL and not Basic +.
Mike Daniel
480-699-0212
At 28 NOV 2019 01:32AM Donald Bakke wrote:
I see. Well, honestly I don't think this changes your options. Either way you are dependent on using Basic+ to make a connection to a SQL server. The only difference is that Instead of calling a SQL script you are calling a SQL stored procedure.