1 / 25

CGI-SQL

CGI-SQL. Retrieve Informix data directly into Excel where your users really want it Faster and more flexible than reporting packages such as Crystal or Cognos No drivers or any other PC setup required More secure and controlled than ODBC

svea
Download Presentation

CGI-SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CGI-SQL • Retrieve Informix data directly into Excel where your users really want it • Faster and more flexible than reporting packages such as Crystal or Cognos • No drivers or any other PC setup required • More secure and controlled than ODBC • Natively compiled ESQL-C program which therefore performs excellently • Used for many years in major UK corporation • Low cost solution only available from Oninit

  2. Oninit CGI-SQL Test System In this presentation, we will be using Oninit’s publicly accessible CGI-SQL test system at: www.cgisql.com We will use default database “stores_demo”, which is the standard demonstration database provided with Informix. Example screen-shots are based on Excel 2007, although CGI-SQL works with any version that supports Web Queries (Excel 97 onwards). All versions of Informix are supported.

  3. CGI-SQL / Examples / Web Based: Entry

  4. CGI-SQL / Examples / Web Based: Purpose This demonstration page uses a conventional HTML form to pass parameters to CGI-SQL once you press the submit button. It shows how the SQL content (which can only be a single SELECT statement) can be passed dynamically together with login details, database name, and the maximum number of rows to be returned. The results are shown on the next slide.Tip: if you need to use CGI-SQL with a very large and complex SELECT statement, create a VIEW.

  5. CGI-SQL / Examples / Web Based: Results

  6. Accessing CGI-SQL inside Excel Now suppose you want this query to be run automatically from within an Excel workbook, so that, when it is opened, it will automatically refresh the contents from the Informix database via CGI-SQL. Note that the HTML submit produced a URL equivalent to the following (with redundant parameters removed):http://www.cgisql.com/cgi-bin/cgi-sql.cgi?SQL=SELECT+company,+city+FROM+customer+ORDER+BY+1

  7. Specifying an Excel Web Query: Example

  8. Specifying an Excel Web Query: Process Choose Data / Get External Data / Web Based from the Excel 2007 ribbon menu, or Data / Import External Data / New Web Query from the Excel 2003 menu. Type or paste in the URL in the “Address” box and press the “Go” button. Click the yellow arrow next to the data table. Click the “Options” button and deselect “Treat consecutive delimiters as one”. You sometimes also need to “Disable date recognition” (do this later only if the data is being misinterpreted). Press “OK” and “Import”.

  9. Excel Web Query Properties: Example

  10. Excel Web Query Properties: Example Click on “Properties”. Enter a more suitable “Name” for the query. Enable “Refresh data when opening the file”. Make sure “Preserve cell formatting” is enabled. Disable “Adjust column width” if you intend to set this manually. Change other settings as desired. Press “OK twice”.

  11. Excel Web Query Results

  12. Auto-Refreshing Excel Workbooks: Process Try closing and reopening the workbook. Depending on your security settings, you may get a warning such at the one shown on the next slide. Click on “Options”. Select “Enable this content”. Click “OK”.

  13. Auto-Refreshing Excel Workbooks: Example

  14. Parameterized Web Queries: IQY Files What if your users need to enter search criteria? This is achieved by first creating a plain text IQY (Internet Query) definition file, for example: WEB 1 http://www.cgisql.com/cgi-bin/cgi-sql.cgi Param1=["State","State code (%=all)"]&SQL=["SQL","Select statement"]&Format=Excel Variables should be named “Param1”, “Param2”, etc. It is recommended that the SQL statement is also treated as a dynamic parameter in an IQY file or it is otherwise difficult to change later. “Format=Excel” in always required in this context. The process for creating a Web Query using this file is illustrated in the next few slides. For further reading, see: http://support.microsoft.com/kb/157482

  15. Parameterized Web Queries: Data Source

  16. Parameterized Web Queries: SQL

  17. Parameterized Web Queries: Variable

  18. Parameterized Web Queries: Results

  19. Parameterized Web Queries: Process Choose Data / Get External Data / Existing Connections from the Excel menu. Click “Browse for more”, locate and select your IQY file. Change “Properties” as for simple Web Queries. Enter parameter values in turn. You can reference cell locations if you wish and tick “Use this value/reference for future refreshes” so that you are not prompted next time. The data is returned into the worksheet. The next slide shows the results with cell and column formatting to produce a more finished look – and “Filters” to display subsets – all of which will be retained when the data is refreshed if specified in “Properties”.

  20. Parameterized Web Queries: Formatted

  21. CGI-SQL Parameter List The following is the complete list of optional CGI parameters: User / Password – Informix authentication details Database – Informix database name Format – “Text” or “Excel” to force MIME type Heading – fixed first line (“None” suppresses column headings) MaxRows – maximum rows to return (default “65535”) The following server-side environment variables also apply: CGISQLUSER / CGISQLPASSWORD / CGISQLDATABASE – defaults for above parameters CGISQLTIMEOUT – maximum seconds the query is allowed to run before being automatically terminated

  22. CGI-CSV A companion server-side CGI script is also provided to enable pre-prepared results of scheduled jobs to be returned to a Web Query. CGI-CSV is given a single parameter representing the report directory name. The latest Gzip-compressed CSV (Comma Separated Value) file in this folder is located, expanded and returned. For example: http://www.cgisql.com/cgi-bin/cgi-csv.cgi?name=test

  23. How It All Works: Overview

  24. How It All Works: Detail Routine reports - particularly those with long run times - can be run overnight every week or month by the UNIX scheduler. These Shell scripts, which either have embedded SQL statements or call 4GL programs in more complex cases, create zipped CSV files on a public FTP server. Users would typically access these files - and Excel templates into which they can be loaded - via Web links, but they can equally be accessed directly from the FTP server. Reports that require parameters from the user can be accessed via Web pages that use the CGI generic SQL “SELECT” statement runner CGI-SQL to return dynamic results displayed with the Internet Explorer Excel plug-in. Alternatively, Excel applications can be designed to refresh their contents automatically, either directly from the database via CGI-SQL, or using the CGI-CSV script to return the contents of the latest CSV file in a specified folder from the public FTP server. Web Queries embedded in Excel applications that allow parameters to be entered by the user are created using Import Data with IQY query definition files.

  25. Next Steps Contact Oninit via www.oninit.com and we will: Discuss your requirements with you. Assist with the installation of Apache on existing hardware if no suitable Web server is available. Locally compile CGI-SQL for your platform and install all associated scripts. Develop Excel reports for you or give on-site training on relevant Excel techniques. Provide remote support.

More Related