A brief technical preview of one of our newest products, CSV Server.|
CSV Server is a small web server that processes raw SQL queries against a database and returns the results in CSV format to the client's browser. The application can connect to any format database supported by ADO through either a native or ODBC driver.
The first question is why would a tool like this be required? The simple answers to that are speed and user friendliness. While you can build up CSV data with most web development
languages like PHP, ColdFusion, or even CGI Scripts, you will find the performance of interpreted languages like these on large tables quickly becomes an issue. In addition, while
your script is assembling the data your users are left staring at an hour glass waiting for their browser to download the data. CSV server combines the performance of a compiled purpose
built tool with a simple user interface that keeps your user informed real-time as their request is processed. All of this combined in one easy tool that can be called from any
web page with a single URL.
So how does it work? The server is called with two parameters from any web URL or Post. The parameters include a filename (for your CSV results) and an SQL statement. This request could come from something as simple as a fixed link on a web site to a more complex query generated by a form post. The server will then immediately pass back a small Ajax enabled web page to the browser to keep the user updated on the progress (what is currently happening along with a timer) as it processes the SQL request. When the request has been completed the user is shown the record count and the size of the resulting CSV file before downloading. The server also caches the results for the user allowing them to download it multiple times before it expires.
If the SQL query is invalid the user optionally receives the resulting database error or a generic error message. Likewise the user will be informed if the query returned no results. If valid records are returned by the query, the server assembles the included fields (only those returned by the SELECT clause) into a 'Complex CSV' format (explained below) and passes it to the client. The download of the resulting file is started automatically.
CSV Format: While we like to call this format "Complex CSV" in reality it's just "Proper CSV". Many CSV parsers don't handle true CSV formatted files correctly and have problems with fields that contain carriage returns, quotes, or commas. A proper CSV places quotes around all fields, and can include Carriage Returns or Line Feeds inside fields. (Hence one record is not always only on one line in the file) Also all double quote characters (") in the data are automatically doubled up (""). Microsoft Excel or any other proper CSV parser can interpret this correctly. The field names will always be returned as the first row of the CSV.
Compatibility: The Ajax interface has been tested on IE 5+, Netscape 6+, Mozilla, Firefox and Opera. The server itself is not dependent on IIS, ASP, or any other web platform, and can be run on any Windows system with ADO (MDAC 2.6+) installed and a TCP/IP connection. ODBC support is optional.
Options: This initial release contains few basic options. Future releases will include much more flexibility.
Optionally filter out all CR/LF's contained in data (one line per record, less complex CSV format)
Selectable MIME type to return the CSV file as
TCP port for the server (handy for running on port 81 along side an existing HTTP server)
The desired field (comma, tab, etc.) and record (CR-LF, LF, etc.) delimiters
Performance: On a very low end 800Mhz Pentium III, 384Mb RAM, Windows 2000 Server this tool can still provide good performance. It will assemble 10,000 records, 100 fields per record (1,000,000 fields of data in total), acquired from an ODBC linked Oracle 9 database (Oracle is running on the same machine), in about 10.3 seconds. The resulting CSV file is about 5.6 Meg. This is about 97,000 fields per second on the test platform. On faster hardware with a native database driver this speed would significantly increase.
Security: CSV Server also will accept only Select clauses in the SQL queries. This prevents damage to your data through errant Insert, Update, or Delete statements being passed to your database. As an added level of protection where possible the database is opened in a read-only mode to further prevent data problems.
If you're interested in exploring CSV Server for use in your project, please feel free to contact us for a demo version.