MS SQL Stores Procedures

[steveuba]

Hello forum,

does any one know how to call a stored proc in LC to have it return data from MS SQL Server 2008?, i am currently working on a business software and have a BI (business intelligence analyst) build me a stored procedure that i can use in MS Excel (ODBC) by passing in parameters to the stored proc and it returns data in MS Excel. When i try the same stored proc using LC database library nothing gets returned from the DB server (MS SQL 2008 R2), so wondering if anyone knows a way to make this work?.

The stored proc by the way saves on tons of lines of actual SQL typed out codes (by just allowing me to pass in parameters and retrieves results from the database).

Thanks in advance for any leads

Stephen

[FireWorx]

Hi,
I have a rather robust livecode application that runs on windows and connects to a MS SQL Server Database via ODBC. I have not accessed stored procedures in SQL SERVER although I have a code library stack that I use to load livecode procedures on start up such as Sql queries with parameters, code that re formats the date or cleanses data from containing special characters such as commas and unwanted apostrophe, or wraps data in apostrophe prior to input. This can cut down on the total amount of code you have to write. One of my best sources for database info in these forums is a guy with the screen name Bangkok. You can probably IM him from the member directory. He has helped me out in the past.
Good Luck.
Dave

PS. Please report back I can use the info.

[steveuba]

Hello FireWorx,

Thanks for the reply on this, i have emailed Bangkok and hoping for his response. Will keep you updated on any comments from him and for sure will bring his feedback into normal forum conversation thread for everyones benefits.

Steveuba

[steveuba]

Hello FireWorx,

Finally we now have a solution for running

Stored Procedures

by passing parameters to get results from SQL2008R2. The solution came via

Code: Select all

MS Excel Library for LiveCode - 1.3.3

by a very brilliant, overtly helpful and intelligent

Zryip TheSlug

Code: Select all

http://www.aslugontheroad.com

.

So this is the skinny on using his external to make Stored Procs work, which of course requires you purchase the plugin (which is a great way to support indie developer work), keep in mind we need the data outside of MS Excel for an internal solution we are building for our internal users, so going through MS Excel was unavoidable as this is the only way we can get to have a system that automatically runs a stored proc when our business application starts up (I don't have to over state or remind that I have not seen a pure LC solution to running Stored Procs by itself).

(1). Initialize ExcelLib in your main stack (plugin by Zyrip).
(2). Use his ExcelLib commands

Code: Select all

XCEL_Workbook_Open, XCEL_PivotTable_New_FromSQL, XCEL_PivotTable_Fld_Define

to encapsulate and run the

Stored Procedure

.
(3). Dump the data result in your MS Excel spreadsheet and pass in the data a multi-dimentional array, this way you can send the array to your

DataGrid

or just dump the result in plain text.

There is a considerable amount of lag using this method, but it is due to the fact of using the ExcelLib in this fashion as a walk around to run stored procs and the side effects of MS Excel loading up and populating the results from the stored proc run.

Zyrip's ExcelLib is well commented and he constantly updates the plugin by adding new functionalities to it. As the plugin stands now, it is robust and allows for quite a great deal of flexibility with running some business oriented solutions.

There was a lot of trial and errors on our part in get the data how we want it, but all in all to us just an afternoon.

This is my update on the Stored Proc, and I hope this will help someone out there, like me for months, trying to find a solution to running stored procs in LC.

Hope this all helps and I can share more on ExcelLib if needed, but your best bet will be reaching out to

Zyrip

for questions on how ExcelLib plugin.

Good luck all,

Steveuba