This procedure creates a visual display of your table data. The result is an html file in
the c: directory. Example picture is below:
weight | height | years | iq |
    | | | |
| |||
First put all downloaded files put in C: directory (only htm and jpg). Then you have to create procedure psoHTML.
Data for charts are from table which is created by us, but it must satisfy some rules. First column is primary key - varchar and it represent object. Other columns must be integer and they are properties of objects. The name of column represent column property. For example objects can be persons and properties are weight, height, years,
etc. When data is inserted in the table we call call procedure with parameters (name of table and name of new html file), result is new created file with that name in directory C: (please don't forget to copy files to directory C:). This procedure is limited to 4 object but you can
easily upgrade it to more with basic
knowledge of graphical utilities.
Here is the stored procedure:
CREATE PROCEDURE psoHTML
@table varchar(30),
@html varchar(20)
AS
/*created by IVICA MASAR
15-06-2003
pso@vip.hr
*/ CREATE TABLE #temptable1 (number int, property decimal(9,2), maxi decimal(9,2))
CREATE TABLE #temptableHTML (code varchar(1000))
INSERT INTO #temptableHTML VALUES ('<html>'+
'<head>'+
'<title>SIMPLE CHART</title>'+
'<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">'+
'</head>'+
'<body bgcolor="#FFFFFF" text="#000000">'+
'<table width="100%" border="0" cellspacing="10" cellpadding="0">'+
'<tr bgcolor="#CCFFFF"> ')
DECLARE @trenutniRed varchar(30),
@tableRow1 varchar(3000),
@sql varchar(300),
@firstColumnName varchar(30),
@HTMLfile varchar(1000),
@columnNumber varchar(30),
@fs int,
@ole int,
@file int
select @HTMLfile = 'C:\' + @html + '.htm'
SELECT @firstColumnName = max(c.name)
FROM sysobjects t, syscolumns c
WHERE t.type='U' AND t.id=c.id AND t.name=@table AND length > 4
DECLARE SysKursor INSENSITIVE SCROLL CURSOR
FOR SELECT c.name FROM sysobjects t, syscolumns c WHERE t.type='U' AND t.id=c.id AND t.name=@table AND length = 4
FOR READ ONLY
OPEN SysKursor
FETCH NEXT FROM SysKursor INTO @trenutniRed
WHILE @@Fetch_Status = 0
BEGIN
SELECT @tableRow1 = '<td><div align="center"><font size="3" color="#000066"><b>'
+ @trenutniRed + '</b></font></div></td>'
INSERT INTO #temptableHTML SELECT @tableRow1
FETCH NEXT FROM SysKursor INTO @trenutniRed
END
INSERT INTO #temptableHTML SELECT '</tr><tr bgcolor="#CCCCFF">'
FETCH FIRST FROM SysKursor INTO @trenutniRed
WHILE @@Fetch_Status = 0
BEGIN
DELETE FROM #temptable1
INSERT INTO #temptableHTML SELECT '<td nowrap>'
SELECT @sql = 'SELECT count(*) number, t1.'+ @trenutniRed +', (SELECT MAX(' + @trenutniRed +') from ' + @table + ') FROM ' + @table + ' t1, ' + @table + ' t2'
+ ' WHERE t1.' + @firstColumnName + ' >= t2.' + @firstColumnName
+ ' GROUP BY t1.'+ @trenutniRed +',t1.' + @firstColumnName
INSERT INTO #temptable1 exec(@sql)
INSERT INTO #temptableHTML
SELECT '<img src="col'+ CAST (number as varchar(3))+'.jpg" width="33" height="'
+ CAST (CAST((property/maxi*200) as int) as varchar(5))+ '"> ' FROM #temptable1
FETCH NEXT FROM SysKursor INTO @trenutniRed
INSERT INTO #temptableHTML SELECT '</td>'
END
CLOSE SysKursor
DEALLOCATE SysKursor
SELECT @columnNumber = count(*) FROM sysobjects t, syscolumns c WHERE t.type='U' AND t.id=c.id AND t.name=@table AND length = 4
INSERT INTO #temptableHTML SELECT '</tr><tr bgcolor="#FFFFCC"><td colspan="' + @columnNumber + '"><p>'
SELECT @sql = 'SELECT ''<img src="col'' + CAST(count(*) as varchar(5))+''.jpg"><i><font size="3"> '' + t1.' + @firstColumnName + '+''</font></i><br>'' from ' + @table + ' t1, ' + @table + ' t2 where t1.' + @firstColumnName + '>=t2.' + @firstColumnName + ' group by t1.' + @firstColumnName
INSERT INTO #temptableHTML exec(@sql)
INSERT INTO #temptableHTML SELECT '</p></td></tr></table>'
INSERT INTO #temptableHTML SELECT char(80) + char(83) + char (79)
INSERT INTO #temptableHTML SELECT '</body></html>'
--SELECT * FROM #temptableHTML
SELECT @sql = 'del '+ @HTMLfile
EXECUTE @ole = sp_OACreate 'Scripting.FileSystemObject', @fs OUT
EXEC master..xp_cmdshell @sql, NO_OUTPUT
EXECUTE @ole = sp_OAMethod @fs, 'OpenTextFile', @file OUT, @HTMLfile, 8, 1
DECLARE SysKursor INSENSITIVE SCROLL CURSOR
FOR SELECT code FROM #temptableHTML
FOR READ ONLY
OPEN SysKursor
FETCH NEXT FROM SysKursor INTO @tableRow1
WHILE @@Fetch_Status = 0
BEGIN
EXECUTE @ole = sp_OAMethod @file, 'WriteLine', Null, @tableRow1
FETCH NEXT FROM SysKursor INTO @tableRow1
END
CLOSE SysKursor
DEALLOCATE SysKursor
EXECUTE @ole = sp_OADestroy @file
EXECUTE @ole = sp_OADestroy @fsExample 1
CREATE TABLE pso1
(
object varchar(15) PRIMARY KEY,
property1 int,
property2 int,
property3 int,
property4 int,
property5 int,
property6 int
)
INSERT INTO pso1 VALUES ('FIRST OBJECT',180,100,27,120,2,1000);
INSERT INTO pso1 VALUES ('SECOND OBJECT',80,500,67,20,32,100);
EXEC psoHTML 'pso1', 'demo1'There is new file in directory C: with name demo1.htm (C:\demo1.htm)
Example 2
CREATE TABLE pso
(
person varchar(15) PRIMARY KEY,
weight int,
height int,
years int,
iq int
)
INSERT INTO pso VALUES ('Tom','180','100','27','120');
INSERT INTO pso VALUES ('Ann','130','45','15','130');
INSERT INTO pso VALUES ('Joe','60','20','10','80');
INSERT INTO pso VALUES ('Mark','207','150','35','90');
EXEC psoHTML 'pso', 'demo2'There is new file in directory C: with name demo2.htm (C:\demo2.htm).
