Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Trapping Information Box (Read 4913 times)
lniemetz
YaBB Newbies
*


I Love SQL*XL

Posts: 2
Trapping Information Box
11.12.08 at 19:05:16
 
I'm calling the execute PLSql statement from a macro (see below) and would like to capture the results like the information box displays:
"PL/SQL Command completed successfully, Rows affected=xxxx"
 number of rows etc. similar to the dynamic call, inorder to store the 'effect' in the spreadsheet.
 
    For Each c In myRange
     
    c.Select
    SQLXL.PLSQLShowParametersDlg = False
    SQLXL.PLSQLShowFeedback = False
    SQLXL.ExecutePLSQLStatement Text:=c.Value
    c.Offset(0, 1).Value = "..... can I display results here?....."
    Next c
 
The ShowFeedback displays multiple boxes, which have to be manually cleared or noted... not acceptable for an auditable production run...
Are the variables or display text available to the macro? If so, how can I access them?
 
Curious, Linda
Back to top
 
 
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Trapping Information Box
Reply #1 - 11.12.08 at 21:13:57
 
Hi,
 
I would solve this in PL/SQL actually. Please experiment a little with the following techniques. If it still doesn't do what you want let me know as there are alternatives.
 
Try putting your PL/SQL in a block with exception clause to create the successful and unsuccessful messages. You can use Excel cell addresses as parameters. SQL*XL will do the rest.
 
Code:
BEGIN
  ...your pl/sql statement(s) go here
  :A2 := SQL%ROWCOUNT;
  :A1 := 'successful';
EXCEPTION
WHEN OTHERS THEN
  :A1 := 'unsuccessful';
END;
 

Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
lniemetz
YaBB Newbies
*


I Love SQL*XL

Posts: 2
Re: Trapping Information Box
Reply #2 - 11.12.08 at 22:35:23
 
Interesting.  
 
I was hoping for something a little simpler; like the sql%rowcount being an exposed variable.... so that the code was all contained in the spreadsheet, not in the macro....
 
Thanks for the suggestion. Will see what I can do with it.
 
Linda
Back to top
 
 
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Trapping Information Box
Reply #3 - 16.12.08 at 21:57:06
 
Hi,
 
I have added a property to the statement where you can retrieve the number of rows affected: RowsAffected
 
The code would look something like this:
SQLXL.SQL.setText "begin mycall; end;"
SQLXL.Statements(1).Execute
Msgbox SQLXL.Statements(1).RowsAffected
 
Please note that this has been implemented for PL/SQL (Oracle) or TSQL (SQL Server) code and for DML (update, delete) /DDL (insert) statements.
 
Hope this helps.
 
It will be made available in SQL*XL 4.3.12
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Rowsaffected and Successful attributes
Reply #4 - 01.01.09 at 22:14:37
 
Rowsaffected and Successful attributes
 
A final test was done today on the properties rowsaffected and successful.  
I have put the VBA code I used to test this functionality below.
 
Code:
[b][size=16]Rowsaffected and Successful attributes[/size][/b]

A final test was done today on the properties rowsaffected and successful. 
I have put the VBA code I used to test this functionality below.

 [code]
Sub Macro1()
  SQLXL.Sql.setText "d elete from test;  "
  SQLXL.Sql.Statements(1).Execute
  
  MsgBox SQLXL.Sql.Statements(1).successful
  MsgBox SQLXL.Sql.Statements(1).rowsaffected
End Sub
 


Back to top
 
« Last Edit: 01.01.09 at 22:18:42 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1