Utilities:
CLOBRead - ASP/VBScript function reads CLOB data
CLOBUpdate - ASP/VBScript subroutine updates (writes) CLOB data
Create SQL Server and MySQL Inserts - WSH SQL Server database -> text file in SQL Server & MySQL formats.
Upload File - ASP/VBScript script uploads text, binary data, or a file without "outside help"
Copy Database - CScript file copies Oracle data to Microsoft SQL Server tables
Validate Script - JavaScript code checks your required fields
Send Template Email - ASP/VBScript sub sends an email with template-like variables
Dermis - Templating Framework for ASP Classic
CLOBRead - ASP/VBScript function reads CLOB data
I wrote this function when my organization ran our IIS website using an Oracle database. I couldn't find a routine to get ASP to read an Oracle CLOB. So, I wrote one. Here it is.
This ASP function reads a CLOB (character large object field) from your Oracle table.
You must specify the Oracle table, column, where clause, and DSN string.
Table: name of Oracle table where your data resides.
Column: name of column in your Oracle table where your data resides.
Where clause: what you would specify in your SQL call to get your data, such as (select lots_of_customer_info_columnname from customer_table where) "CustomerID='123456'"
DSN: data source name. Should be either:
1. application("CustomerDB_DSN") (if you're using global.asa in the root path of your webserver)
2. "DRIVER={Oracle ODBC Driver};DBQ=ora8i.domain.com;UID=userid;pwd=password"
Call using:
strReturnString=CLOBRead(strTable, strCLOBColumn, strWhereClause, strDSN)
Examples:
1. strBigOracleString=CLOBRead("customer_table", "lots_of_customer_info_columnname", "CustomerID='"&strCustomerID&"'", application("CustomerDB_DSN"))
2. strBigOracleString=CLOBRead("customer_table", "lots_of_customer_info_columnname", "CustomerID='"&strCustomerID&"'", "DRIVER={Oracle ODBC Driver};DBQ=ora8i.domain.com;UID=userid;pwd=password")
CLOBUpdate - ASP/VBScript subroutine updates (writes) CLOB data
I wrote this function when my organization ran our IIS website using an Oracle database. I couldn't find a routine to get ASP to update an Oracle CLOB. So, I wrote one. Here it is.
I could update a CLOB table with data using Oracle's built-in functions, I was limited by a SQL statement of 4k (by default) or less.
This ASP subroutine writes a CLOB (character large object datatype) to your Oracle table, up to 2M (or whatever VBScript's string size limit is).
You must specify the Oracle table, column name, what content you want to write (a string), where clause, and DSN string.
Table: name of Oracle table where your data resides.
Column: name of column (with a datatype of CLOB) in your Oracle table where to write your data.
String To Write: the actual data to write. CLOBs can be up to 2G, I think VBScript's string size limit is 2M.
Where clause: what you would specify in your SQL call to write your data, such as (update customer_table set lots_of_customer_info_columnname='this customer''s entire geneaology is ...' where) "CustomerID='123456'"
DSN: data source name. Should be either:
1. application("CustomerDB_DSN") (if you're using global.asa in the root path of your webserver)
2. "DRIVER={Oracle ODBC Driver};DBQ=ora8i.domain.com;UID=userid;pwd=password"
Call using:
CLOBUpdate(strTableCU, strCLOBColumnCU, strContentCU, strWhereClauseCU, strConnectionStringCU)
Examples:
1. CLOBUpdate("customer_table", "lots_of_customer_info_columnname", "this customer's entire geneaology is: "&strMassiveGeneologyString, "CustomerID='"&strCustomerID&"'", application("CustomerDB_DSN"))
2. CLOBUpdate("customer_table", "lots_of_customer_info_columnname", "this customer's entire geneaology is: "&strMassiveGeneologyString, "CustomerID='"&strCustomerID&"'", "DRIVER={Oracle ODBC Driver};DBQ=ora8i.domain.com;UID=userid;pwd=password")
Create SQL Server Inserts - Copy SQL Server database info -> text file in SQL Server & MySQL formats.
MySQL backs up data to a text file, but SQL Server backs up to a binary file. I wrote this for a personal project. So far, the largest file this script generated was 20M, which zipped to 2M. Nice!
This script does not affect your current data. It only outputs SQL commands.
Execute using cscript CreateSQLInserts.vbs SQLFilePrefix.
Creates three files: SQLFilePrefix.sqlserver.tables.sql, SQLFilePrefix.sqlserver.storedprocedures.sql, and SQLFilePrefix.mysql.tables.sql.
Note: This script does not handle views, constraints, triggers, full-text catalogs, or foreign keys.
strConnectionString: your SQL Server connection string
aryWriteTableData: output drop, create, and insert commands for these tables in SQL Server and MySQL formats.
aryStoredProcedures: output drop and create commands for these stored procedures, SQL Server only.
Upload File - ASP/VBScript script uploads text, binary data, or a file without "outside help"
I didn't want to pay for a utility to upload a file to my webserver. So, I wrote this.
This ASP code, function, and example HTML form can upload a document to your webserver with very little fuss.
Form header must have: method="post" enctype="multipart/form-data"
VBScript code must not see request.form() before or after using this function
Call using: set hashFormData=ParseBinaryFormData(Request.BinaryRead(Request.TotalBytes))
Result: hashFormData(FormElementName)=FormElementData
<input type="file" name="upload">
hashFormData("upload")="text_or_binary_data"
Note: You may need to use request.BinaryWrite (hashFormData(FormElementName)) to write binary files
Perhaps not as flexible as ASPUpload, but this one's free.
Copy Data - CScript file copies Oracle data to Microsoft SQL Server tables
SQL server can import Oracle data fairly nicely. However, it's conversion feature makes a lot of assumptions along the way. It also completely ignores CLOBs.
I wrote this utility when I converted my organization's website from Oracle to SQL Server. It greatly simplified the process, and kept the data very pure. It also copies CLOBs.
Create the SQL Server table, columns, and datatypes before running this utility.
Note: It first deletes any data that may exist in the destination SQL Server table.
Also, I included a lot of the code I actually used when converting. It's a lot of extra unnecessary code, but I figured it wouldn't hurt.
Map of data types to convert (aryDataTypes):
1: convert from Oracle number to MS integer
2: convert from Oracle number to MS float
3: convert from Oracle varchar2 to MS varchar (or text)
4: convert from Oracle clob to MS text
5: convert from Oracle date to MS datetime
6: convert from Oracle number to MS bit
7: convert from Oracle clob to MS varchar (or text)
Oracle key datatypes (intOracleKeyDatatype):
1: number
2: varchar2
Format:
CopyData(strOracleTablename, strOracleKeyColumnname, intOracleKeyDatatype, aryOracleColumnNames, strOracleConnectionReadString, strMSTablename, aryMSColumnNames, aryDatatypes, strMSConnectionWriteString)
Example #1:
' - copy CommonStuff
aryOracleColumnNames=array("Title", "Content")
aryMSColumnNames=array("Title", "Content")
aryDataTypes=array(3, 4)
call CopyData("common_stuff", "Title", 2, aryOracleColumnNames, hashConnectionStrings("CommonStuffRead"), "common", aryMSColumnNames, aryDataTypes, hashConnectionStrings("CommonWrite"))
Example #2:
' - copy Employees
aryOracleColumnNames=array("employeeid", "username", "biographical", "birthdate", "active", "education", "experience", "publications", "specialization", "courses", "certificationsandawards", "other", "lastname", "firstname", "middlename", "faculty", "staff", "management", "webpicture", "email", "personalurl", "homephone", "workphone", "workfax", "officeroomnumber", "universityname", "collegename", "collegestreetaddress", "collegecity", "collegestate", "collegezip", "title", "department", "dean", "associatedean", "jacknumber")
aryMSColumnNames=array("employeeid", "username", "biographical", "birthdate", "active", "education", "experience", "publications", "specialization", "courses", "certificationsandawards", "other", "lastname", "firstname", "middlename", "faculty", "staff", "management", "webpicture", "email", "personalurl", "homephone", "workphone", "workfax", "officeroomnumber", "universityname", "collegename", "collegestreetaddress", "collegecity", "collegestate", "collegezip", "title", "department", "dean", "associatedean", "jacknumber")
aryDataTypes=array(1, 3, 4, 5, 6, 4, 4, 4, 4, 4, 4, 4, 3, 3, 3, 6, 6, 6, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 6, 6, 3)
call CopyData("directory_employees", "employeeid", 1, aryOracleColumnNames, hashConnectionStrings("DirectoryEmployeesRead"), "Employees", aryMSColumnNames, aryDataTypes, hashConnectionStrings("EmployeesWrite"))
Validate Script - JavaScript code checks your required fields
Generic form validation function. Pass all fieldnames that must be answered on the form.
radio button: fails if none are selected
text: fails if field is blank
select-one: fails if topmost entry is selected
returns true if form elements are okay, otherwise false
Format:
Validate(formfieldname_1, 'text if incomplete_1', formfieldname_2, 'text if incomplete_2', ..., formfieldname_N, 'text if incomplete_N')
<form method="post" action="email-webmaster.asp" onsubmit="javascript: return Validate(fromname, 'Your name', fromaddress, 'Your email address', subject, 'Subject', body, 'Message')">
Send Template Email - ASP/VBScript sub sends an email with template-like variables
Our organization's website needed to send emails often (who doesn't?). After learning a bit about Smarty, I put some ideas together and made this.
I'm now happy to write code that sends emails. :)
Create one file (suggestion: email_templates.asp) with all of your email templates.
You can give it any filename extension you want, or none at all. I recommend giving it a ".asp" extension so the embedded "response.end" won't let anyone see it's contents.
In your ASP code, create a dictionary object, and pass all variables to it you wish to appear in the email.
The template variable naming convention is loosely based off of the awesome Smarty system: {$first_name}, {$hour}, {$timezone_short}, etc.
There are a number of pre-defined values:
Month, day, year, hour, minute, second, timezone, am/pm; all time variables have zero-prefixed variations, too.
{$m}/{$dd}/{$yyyy} {$h12}:{$mmin}:{$ss} {$ampm} {$timezone_short}
Search for "Timestamp" in email_templates.asp.
Any unknown template variables will resolve to empty strings.
Disclaimer #1: It does not process arrays or dictionary objects like a real templating system should.
Disclaimer #2: The {assign var=strName value='your name'} directive is a bit delicate. The value must be delimited by single quotes only. The value element doesn't (currently) handle escaped single quotes. (I haven't needed it, so I haven't fixed it.)
Disclaimer #3: You'll need to configure the sub to see your IIS server. If you send it using another technique, you'll need to use that one instead.
Disclaimer #4: It does not handle emailing attachments. (Again, I haven't needed it, so I haven't added this feature.)
Format:
call send_template_email(strTemplateFilename, strTemplateName, dicVariables)
strTemplateFilename: absolute path to email_templates.asp
strTemplateName: section within strTemplateFilename to email. Example: "ForgotPassword" shows content between [ForgotPassword] ... [/ForgotPassword]
dicVariables: dictionary object which contains all of the variables you wish to pass to the template.
Dermis - Templating Framework for ASP Classic
I've been wanting to port Smarty to ASP/VBScript. I used Smarty's own PHP code. It took me a couple of months to get it functional enough to release it.
Project location & download: http://sourceforge.net/projects/dermis/
Blog: http://dermissystem.blogspot.com/
