通过数据库集成报表

         从有报表工具的那天开始，就开始面临如何在应用种集成的问题。目前主要的集成方式是报表服务器启动web服务，在程序中通过URL跳转到报表服务器上。这种方式不安全，它要求客户端必须可以访问报表服务器的服务。下面来将一种新颖的集成方式：基于数据库C/S集成。它的做法是将报表放在数据库中，应用程序连接到数据库中，调用函数还运行报表，并获取运行结果进行展示。这种方式优势非常明显，只要应用程序可以连接到数据库就可以运行报表。它是跨平台的，与操作系统无关。目前只有SQLDOC可以支持这种集成方式，因为它的引擎是和分布式异构数据库SQLHUB结合在一起的。下面分别以.NET和Java为例说明具体的集成方法： 

 .NET中集成示例代码（C#语法）

using System;

using System.Data;

using System.IO;

using System.Drawing;

using System.Collections;

using System.ComponentModel;

using System.Windows.Forms;

using Hgsql.Data;

 

namespace Hgsql.Client

{

     ///<summary>

     /// Form1 的摘要说明。

     ///</summary>

     public class SqldocForm : System.Windows.Forms.Form

     {

         private AxPdfLib.AxPdf axPdf;

         ///<summary>

         ///必需的设计器变量。

         ///</summary>

         private System.ComponentModel.Container components = null;

 

         public SqldocForm()

         {

              //

              // Windows 窗体设计器支持所必需的

              //

              InitializeComponent();

 

              //

              // TODO: 在 InitializeComponent 调用后添加任何构造函数代码

              //

         }

 

         ///<summary>

         ///清理所有正在使用的资源。

         ///</summary>

         protected override void Dispose( bool disposing )

         {

              if( disposing )

              {

                   if(components != null)

                   {

                       components.Dispose();

                   }

              }

              base.Dispose( disposing );

         }

 

         #region Windows 窗体设计器生成的代码

         ///<summary>

         ///设计器支持所需的方法 - 不要使用代码编辑器修改

         ///此方法的内容。

         ///</summary>

         private void InitializeComponent()

         {

              System.Resources.ResourceManager resources = new System.Resources.ResourceManager(typeof(SqldocForm));

              this.axPdf = new AxPdfLib.AxPdf();

              ((System.ComponentModel.ISupportInitialize)(this.axPdf)).BeginInit();

              this.SuspendLayout();

              //

              // axPdf

              //

              this.axPdf.Dock = System.Windows.Forms.DockStyle.Fill;

              this.axPdf.Enabled = true;

              this.axPdf.Location = new System.Drawing.Point(0, 0);

              this.axPdf.Name = "axPdf";

              this.axPdf.OcxState = ((System.Windows.Forms.AxHost.State)(resources.GetObject("axPdf.OcxState")));

              this.axPdf.Size = new System.Drawing.Size(292, 273);

              this.axPdf.TabIndex = 0;

              //

              // SqldocForm

              //

              this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);

              this.ClientSize = new System.Drawing.Size(292, 273);

              this.Controls.Add(this.axPdf);

              this.Name = "SqldocForm";

              this.Text = "SQLDOC集成";

              this.WindowState = System.Windows.Forms.FormWindowState.Maximized;

              this.Load += new System.EventHandler(this.SqldocForm_Load);

              ((System.ComponentModel.ISupportInitialize)(this.axPdf)).EndInit();

              this.ResumeLayout(false);

 

         }

         #endregion

 

         private void SqldocForm_Load(object sender, System.EventArgs e)

         {

              try

              {

                   JdbcConnection conn = new JdbcConnection();

                   conn.ConnectionString =

                       "driver={com.hg.jdbc.HgDriver}" //JDBC驱动名称

                       + ";url={jdbc:hg:net:@localhost:1980:hg}" //JDBC数据库连接URL

                       + ";user=hg" //数据库用户名

                       + ";password=hg" //数据库用户口令

                       + ";host=localhost" //JDBC服务器

                       + ";port=2007"; //JDBC服务器服务端口

                   //打开连接

                   conn.Open();

                   IDbCommand cmd = conn.CreateCommand();

                   //运行文档

                   cmd.CommandText = "doc_run('文化新闻','','db','','pdf','lob')";

                   string lobid = cmd.ExecuteScalar().ToString();

                   cmd.CommandText = "blob_length('" + lobid + "')";

                   //获取结果文档长度

                   long len = Convert.ToInt64(cmd.ExecuteScalar().ToString());

                   string str;

                   string[] strs;

                   //将文档存储到本地

                   FileStream fs = new FileStream("c:/sqldoc.pdf", FileMode.Create);

                   BinaryWriter w = new BinaryWriter(fs);

                   byte[] buf = new byte[1024];

                   sbyte sb;

                   for (int i = 0; i <= len / 1024 + 1; i++)

                   {

                       cmd.CommandText = "blob_read('" + lobid + "','" + (i * 1024 + 1) + "',1024)";

                       str = cmd.ExecuteScalar().ToString();

                       if (str.Length > 0)

                       {

                            strs = str.Split(new Char[] {','});

                            for (int j = 0; j < strs.Length; j++)

                            {

                                 sb = Convert.ToSByte(strs[j]);

                                 buf[j] = (byte) (sb & 0xFF);

                            }

                            w.Write(buf, 0, strs.Length);

                       }

                   }

                   fs.Close();

                   //清除临时lob

                   cmd.CommandText = "lob_clear('" + lobid + "')";

                   cmd.ExecuteNonQuery();

                   conn.Close();

                   //在pdf控件中显示

                   axPdf.src = "c:/sqldoc.pdf";

              }

              catch (Exception ex)

              {

                   MessageBox.Show(this, ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);

              }

         }

         static void Main()

         {

              SqldocForm form = new SqldocForm();

              Application.Run(form);

         }

     }

}

Java中集成示例代码

package com.hg.doc;

 

import java.io.BufferedOutputStream;

import java.io.File;

import java.io.FileOutputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

 

public class JdbcTest {

    public static void main(String[] args) {

        try {

           //注册本地驱动

            Class.forName("com.hg.jdbc.HgLocDriver");

            //获取本地连接

            Connection conn = DriverManager.getConnection("jdbc:hg:loc:hg", "hg", "hg");

            /*

            //注册网络驱动

            Class.forName("com.hg.jdbc.HgDriver");

            //获取网络连接

            Connection conn = DriverManager.getConnection("jdbc:hg:net:@localhost:1980:hg", "hg", "hg");

            */

            Statement stmt = conn.createStatement();

            String sql;

            //运行文档

            sql = "doc_run('雇员','','db','','pdf','lob')";

            String lobid = queryValue(stmt, sql);

            sql = "blob_length('" + lobid + "')";

            //获取结果文档长度

            long len = Long.parseLong(queryValue(stmt, sql));

            String str;

            String[] strs;

            //将文档存储到本地

            BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(new File("c:/sqldoc.pdf")));

            byte[] buf = new byte[1024];

            for (int i = 0; i <= len / 1024 + 1; i++)

            {

                sql = "blob_read('" + lobid + "','" + (i * 1024 + 1) + "',1024)";

                str = queryValue(stmt, sql);

                if (str.length() > 0)

                {

                    strs = str.split(",");

                    for (int j = 0; j < strs.length; j++)

                    {

                        buf[j] = Byte.parseByte(strs[j]);

                    }

                    out.write(buf, 0, strs.length);

                }

            }

            out.close();

            //清除临时lob

            sql = "lob_clear('" + lobid + "')";

            stmt.execute(sql);

            stmt.close();

            conn.close();

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    public static String queryValue(Statement stmt, String sql) throws SQLException {

        ResultSet rst = stmt.executeQuery(sql);

        rst.next();

        String str = rst.getString(1);

        rst.close();

        return str;

    }

}