Hi everyone, I’ve received a couple private emails about returning result sets from SQL2000 Stored Procedures using ADO. Here’s an example for you. Hope it helps, Bill I created this in a .txt file for easier reading, but the forum message attachment isn’t working correctly–so, sorry about the formatting. If you want the text file, just send a note to me and I’ll shoot you a copy. bill.reese@guardian.com ****************************************************** Return an ADO 2.6 RecordSet from an SQL Stored Procedure using Navision 2.60f This simple example pulls all our costing information (we don’t use Navision for costing or pricing–too complex) and returns the info we need in a recordset object (autRs). autRs = ADO RecordSet autConn = ADO Connection SQ = a single quote GetCostingExampleSp(VAR lngVar : Integer) CLEAR(autConn); CLEAR(autRs); IF GetADOConnection(autConn, ‘YourSqlServerName’, ‘YourDatabaseName’) THEN BEGIN // autConn is passed by reference and opened in GetADOConnection() // ‘…’ is the data source (SQL Server) // ‘…’ is the initial catalog (or database name) for the connection open statement CREATE(autRs); // The GRD_spGetAllCost stored procedure has 8 parameters. // This is what the spText variable will look like: // GRD_spGetAllCost(‘DW01218’,’’,‘07/01/02’,1,‘008’,‘DOMWIND’,’’,0) spText := ‘GRD_spGetAllCost(’ + SQ + ‘DW01218’ + SQ + ‘,’ + SQ + SQ + ‘,’ + SQ + ‘07/01/02’ + SQ + ‘,’ + ‘1,’ + SQ + ‘008’ + SQ + ‘,’ + SQ + ‘DOMWIND’ + SQ + ‘,’ + SQ + SQ + ‘,0)’; // All the work is done here… autRs := autConn.Execute(spText, lngVar, 4); // 4 = adCmdStoredProc MESSAGE(‘Records → %1’, autRs.RecordCount); // Our system returns 7 records // Use normal ADO.Recordset.Field()s to get at your data. END; CLEAR(autConn); CLEAR(autRs); // Just a simple library piece we use to create the ADO Connection object for us GetADOConnection(VAR autConn : Automation “‘Microsoft ActiveX Data Objects 2.6 Library’.Connection”;txtDataSource : Text[30];txtInitial // Try to open the passed ADO connection object txtConn := ‘PROVIDER=SQLOLEDB.1;Persist Security Info=False;Trusted_Connection=Yes;Initial Catalog=’+txtInitialCatalog+’;’; txtConn := txtConn + ‘Data Source=’ + txtDataSource + ‘;’; IF CREATE(autConn) THEN BEGIN autConn.ConnectionString := txtConn; autConn.CursorLocation := 3; // adUserServer = 2, adUseClient = 3 autConn.Open; IF autConn.Errors.Count = 0 THEN BEGIN IF autConn.State = 1 THEN BEGIN // adStateOpen = 1 // Its open, let’s press… EXIT(TRUE); END; END; END; EXIT(FALSE);
Does anybody knows how to get Return values as I get it in Output window. Like, outputs of PRINT commands in stored procedures? I have batch job in Navision 3.60, over ADO and need to save output as kind of log file or message to client.
Moved to SQL Forums,