|
|
|
|
SelfRunner for other
RDBMS's -
soon to follow.
|
Self-Runner_odbc - Real-time ODBC-HTM Report.
Batch script to generate an "
all-you-need-is-a-connect-string-and-a-browser" htm page
to display
real-time data from through an ODBC connection.
This script uses an ODBC connection to an
ORACLE database.
At this time I am
not sure about equivalents in other RDBMSs for the SET MARKUP HTML
command in Oracle.
If there are such commands available, then it should be just a
matter of changing the query and the connect string to make this
script work on ANY database which an be connected through ODBC.
Here are the
steps:
Create an ODBC
connection to an ORACLE database
Configure the
connection details
Test the ODBC
Connection
Create an entry in
the tnsnames.ora for the newly created ODBC connection
Verify
Connectivity with SQLPLUS
Modify the
connection string and run the batch script
|
::
=========================================================================================
:: SelfRunner^_odbc.bat Real-time
AnyOdbcDatabase-Htm Report
::
=========================================================================================
:: Generate and run a self-running sql-htm page from a windows
batch file.
:: 1.Tabbed Browsing: Tested on IE. Please turn off
tabbed browsing to refresh on same page.
:: 2.Activex Control:
Please choose yes to allow blocked content message when IE opens.
::
Works fine with default IE activex security setting.
::
Annoyance: Extra click to allow activex control that runs the
script.
:: 3.Command Window : Position the Command Window away
from the top left of the page.
::
Start-Run-Cmd-RightClickOnTitleBar-Properties-WindowPosition-
::
Left-750;Top-350-PressOkay-Choose SavePositionForFutureWindows...
:: 4.Meta Character Mess-up : Meta characters in echo statements
that need to be escaped by
::
a ^ are, acc to docs, in this list [ & && | || ( ) <
> << >> ].
:: 5.Use at your own risk.
::
========================================================================================
:: Jerome Christopher. [Jerome_christopher@yahoo.com]
::
http://htmsql.blogspot.com/ :: http://www.sqlhtm.com/sqlhtm/selfrunner-anyodbcdb
::
========================================================================================
(
echo set pages 200
echo set lines 180
echo set feedback off
echo set termout off
echo set HEADING OFF
echo
--------------------------------------------------------------------------------------
echo alter session set nls^_date^_format^='dd-mon-yyyy
hh24:mi:ss';
echo SELECT ^'^<^'^|^|^'style
type^=^'^|^|^'^'^'^'^|^|^'text^'^|^|^'^/^'^|^|^'css^'^|^|^'^'^'^'^|^|^'^>^'^|^|
chr^(10^)^|^|chr^(13^)^|^|
echo ^'body {font:7pt
Arial^,Helvetica^,sans^-serif; color:black;
background:lightgreen;padding:0px 0px 0px
0px;background-image:url^(^"C:^\Documents and Settings^\JC^\My
Documents^\pinetrees.gif^"^)}^'^|^|chr^(10^)^|^|chr^(13^)^|^|
echo ^'p {font:bold 8pt
Arial^,Helvetica,sans^-serif; color:red;
background:lightaqua;padding:0px 0px 0px 0px; margin:0px 0px 0px
0px;}^'^|^|chr^(10^)^|^|chr^(13^)^|^|
echo ^'table
{font:7pt Arial^,Helvetica^,sans-serif; color:Black;
background:green; padding:0px 0px 0px 0px; margin:0px 0px 0px
0px;vertical^-align:top}^'^|^|chr^(10^)^|^|chr^(13^)^|^|
echo
^'tr {font:7pt Arial^,Helvetica^,sans-serif;
color:Black; background:; padding:0px 0px 0px 0px; margin:0px 0px
0px 0px;}^'^|^|chr^(10^)^|^|chr^(13^)^|^|
echo
^'td {font:bold 8pt
Arial^,Helvetica^,sans-serif; color:black; background:cyan;
padding:0px 0px 0px 0px; margin:0px 0px 0px
0px;}^'^|^|chr^(10^)^|^|chr^(13^)^|^|
echo
^'th {font:bold 6pt
Arial^,Helvetica^,sans-serif; color:yellow; background:darkgreen;
padding:0px 0px 0px 0px;}^'^|^|chr^(10^)^|^|chr^(13^)^|^|
echo
^'h1 {font:bold 15pt
Arial^,Helvetica^,Geneva^,sans-serif; color:black;
background:silver; }^'^|^|chr^(10^)^|^|chr^(13^)^|^|
echo
^'h2 {font:bold 9pt
Arial^,Helvetica^,Geneva^,sans-serif; color:black;
background:magneta; margin^-top:0px;
margin^-bottom:0px;}^'^|^|chr^(10^)^|^|chr^(13^)^|^|
echo
^'a {font:bold 8pt
Arial^,Helvetica^,sans-serif; color:black;
background:darkkhaki;padding:0px 0px 0px 0px; margin^-top:0pt;
margin^-bottom:0pt;
vertical^-align:top;}^'^|^|chr^(10^)^|^|chr^(13^)^|^|
echo
^'^<^'^|^|^'^/^'^|^|^'style^>^' FROM dual;
echo
--------------------------------------------------------------------------------------
echo SET MARKUP HTML ON TABLE ^"^" ENTMAP OFF
echo SET
HEADING ON
)>c:\sr_odbc.out
::
======================================================================================
@echo
off
del
c:\sr_odbc1.tmp c:\sr_odbc2.tmp c:\sr_odbc3.tmp
echo spool
c:^\SelfRunner^_odbc.htm
>c:\sr_odbc1.tmp
echo set termout
off
>>c:\sr_odbc1.tmp
echo prompt
^<html^>^<head^>
>>c:\sr_odbc1.tmp
echo prompt ^<script
type^=^"text^/javascript^"^>
>>c:\sr_odbc1.tmp
echo prompt function
runApp^(which^)
>>c:\sr_odbc1.tmp
echo prompt { WshShell ^= new
ActiveXObject^(^"WScript.Shell^"^);
>>c:\sr_odbc1.tmp
echo prompt
WshShell.Run
^(which^,1^,false^);
>>c:\sr_odbc1.tmp
echo prompt var win ^=
window.open^(^"c:^/SelfRunner^_odbc.htm^"^,^"^_self^"^)>>c:\sr_odbc1.tmp
echo
prompt if ^(win^) win.onload ^=
runApp
>>c:\sr_odbc1.tmp
echo prompt
}
>>c:\sr_odbc1.tmp
echo prompt
^<^/script^>
>>c:\sr_odbc1.tmp
::
======================================================================================
type
c:\sr_odbc.out
>c:\sr_odbc2.tmp
copy c:\sr_odbc1.tmp+c:\sr_odbc2.tmp
c:\sr_odbc3.tmp
>NUL
::
======================================================================================
echo prompt
^<^/head^>
>>c:\sr_odbc3.tmp
echo prompt
^<body^>
>>c:\sr_odbc3.tmp
echo prompt ^<table border^=20
title^=SelfRunner-Real-TimeOracleDataRefresh ^>
^<tr^>^<td valign^=^"top^"^>
>>c:\sr_odbc3.tmp
rem echo select name
DbName^,to^_char^(sysdate^,^'dd^-mon^-yy hh:mi:ss pm Dy^'^) Today
from
v^$database;
>>c:\sr_odbc3.tmp
echo select name
DbName^,to^_char^(sysdate^,^'dd^-mon^-yy hh:mi:ss pm Dy^'^) Today
from v^$database; >>c:\sr_odbc3.tmp
echo
prompt ^<INPUT TYPE^=button VALUE^=^"SelfRunner^_ODBC - Press To
Refresh - See The Values Change In Real Time !!!^"
TITLE^=^"ClickHere^" STYLE^=^"color:yellow;
background^-color:darkgreen^"
onClick^=^"runApp^(^'file:^/^/c:^/SelfRunner^_odbc.bat^'^)^"^>
>>c:\sr_odbc3.tmp
rem echo prompt ^<^/td^>^<td
valign^=^"top^"^>
>>c:\sr_odbc3.tmp
echo prompt
^<^/td^>^<^/tr^>^<tr^>^<td
valign^=^"top^"^>
>>c:\sr_odbc3.tmp
echo prompt ^<a^>1.Some sysstat
parameters to
watch:^<^/a^>
>>c:\sr_odbc3.tmp
echo select * from v$sysstat where
value^>0 and name
in
>>c:\sr_odbc3.tmp
echo
^(
>>c:\sr_odbc3.tmp
echo 'recursive
calls'
>>c:\sr_odbc3.tmp
echo ,'recursive cpu
usage'
>>c:\sr_odbc3.tmp
echo ,'DB
time'
>>c:\sr_odbc3.tmp
:: echo ,'cluster wait
time'
>>c:\sr_odbc3.tmp
:: echo ,'sorts
^(rows^)'
>>c:\sr_odbc3.tmp
echo ,'sorts
^(memory^)'
>>c:\sr_odbc3.tmp
echo ,'sorts
^(disk^)'
>>c:\sr_odbc3.tmp
echo ,'physical
reads'
>>c:\sr_odbc3.tmp
echo ,'redo
size'
>>c:\sr_odbc3.tmp
echo ,'redo
wastage'
>>c:\sr_odbc3.tmp
echo ,'table scans ^(short
tables^)'
>>c:\sr_odbc3.tmp
echo ,'table scans ^(long
tables^)'
>>c:\sr_odbc3.tmp
echo ,'table fetch continued
row'
>>c:\sr_odbc3.tmp
echo ,'user I^/O wait
time'
>>c:\sr_odbc3.tmp
echo
^)
>>c:\sr_odbc3.tmp
echo order by name
;
>>c:\sr_odbc3.tmp
rem echo prompt
^<^/td^>^<table^>^<tr^>^<td
valign^=^"top^"^>
>>c:\sr_odbc3.tmp
echo prompt ^<^/td^><td
valign^=^"top^"^>
>>c:\sr_odbc3.tmp
echo prompt ^<a^>2.Top 4 users -
ordered by
seconds_in_wait:^<^/a^>
>>c:\sr_odbc3.tmp
echo select * from ^(select
sid,serial#,username,status,program,type,user,module,logon_time,wait_time,seconds_in_wait
from v$session where type^<^>'BACKGROUND' order by
seconds_in_wait desc^) where
rownum^<5;
>>c:\sr_odbc3.tmp
echo prompt ^<^/td^><td
valign^=^"top^"^>
>>c:\sr_odbc3.tmp
echo prompt ^<a^>3.Top 4 Filestat
info - ordered by physical
reads:^<^/a^>
>>c:\sr_odbc3.tmp
echo select * from ^(select * from
v$filestat order by phyrds desc ^) where rownum ^<
5;
>>c:\sr_odbc3.tmp
rem echo prompt
^<^/td^>^<^/tr^>^<^/table^>^<hr^>
>>c:\sr_odbc3.tmp
echo prompt ^<^/td^><td
valign^=^"top^"^>
>>c:\sr_odbc3.tmp
echo prompt ^<a^>4.Top 4 waits
from system_event - ordered by time_waited:
^<^/a^>
>>c:\sr_odbc3.tmp
echo select * from ^(select * from
v$system_event order by time_waited desc^) where rownum ^< 5;
>>c:\sr_odbc3.tmp
echo
prompt
^<^/td^>^<^/tr^>^<^/table^>
>>c:\sr_odbc3.tmp
echo spool
off
>>c:\sr_odbc3.tmp
echo
exit
>>c:\sr_odbc3.tmp
sqlplus -s "system/oracle@xe_odbc"
"@c:\sr_odbc3.tmp"
>>c:\sr_odbc3.tmp
echo
^<^/body^>^<^/html^>
>>c:\SelfRunner^_odbc.htm
::
======================================================================================
start
"C:\Program Files\Internet Explorer\iexplore.exe"
c:\SelfRunner^_odbc.htm
exit
| |
|
