<%@ Language="JScript" %>
<%
//*****************************************************************************
//
// actions/ado.actions6.asp
//
// GoLive actions for Microsoft ADO.
//
// ADOBE SYSTEMS INCORPORATED
// Copyright 1999-2002 Adobe Systems Incorporated. All Rights Reserved.
//
// NOTICE: Notwithstanding the terms of the Adobe GoLive End User
// License Agreement, Adobe permits you to reproduce and distribute this
// file only as an integrated part of a web site created with Adobe
// GoLive software and only for the purpose of enabling your client to
// display their web site. All other terms of the Adobe license
// agreement remain in effect.
//
// *****************************************************************************
// UTILITIES
//
function encodeSQL(str)
{
return String(str).split("'").join("''");
}
function getCswNameFromForm()
{
return GetFormValue("$cswName");
}
function getDataFromForm(firstRecord, lastRecord, cswName)
{
var data = new Array;
for (var i = firstRecord; i <= lastRecord; i++) {
var record = new Array;
for (var column in datatypes) {
var value = GetFormValue(column + "[" + i + "]");
if (value != "_no_form_value") {
record[column] = value;
}
}
data[data.length] = record;
}
if (RuntimeDebug) {
var msg = "Data Info:
\n";
msg += "\n";
for (var i = 0; i < data.length; i++) {
for (var name in data[i]) {
msg += "| data[" + i + "] | " + name + " | " + data[i][name] + " |
\n";
}
}
msg += "
\n";
RuntimeDebugMessage(msg);
}
return data;
}
function getKeysFromForm(firstRecord, lastRecord, cswName)
{
var keys = new Array;
for (var i = firstRecord; i <= lastRecord; i++) {
keyString = "RECORD_KEY[" + cswName + "]=" + GetFormValue("$key[" + i + "]");
keys[keys.length] = queryStringToArray(keyString);
}
if (RuntimeDebug) {
var msg = "Keys Info:
\n";
msg += "\n";
for (var i = 0; i < keys.length; i++) {
for (var name in keys[i]) {
msg += "| keys[" + i + "] | " + name + " | " + keys[i][name] + " |
\n";
}
}
msg += "
\n";
RuntimeDebugMessage(msg);
}
return keys;
}
// -----------------------------------------------------------------------------
// SQL utilities
// -----------------------------------------------------------------------------
// wrapSQLValue
//
// Inserts provider-specific annotations (quotes, brackets, etc.) around values
// based on their type and use. Uses globals "datatypes" and "connection".
function wrapSQLValue(fieldName, value, mode)
{
var datatype = datatypes[fieldName];
var retString = "";
var tempValue = encodeSQL(value);
var glTrue = "1";
var glFalse = "0";
var wildCardChar = "";
var prefix1999 = "";
var suffix1999 = "";
var prefix2000 = "";
var suffix2000 = "";
var trueValue = "1"; // Access: "true"
var falseValue = "0"; // Access: "false"
switch(String(connection.Properties.Item("Provider Friendly Name"))) {
case "Microsoft OLE DB Provider for ODBC Drivers":
switch(String(connection.Properties.Item("DBMS Name"))) {
case "ACCESS":
wildCardChar = "%";
prefix1999 = "#";
suffix1999 = "#";
prefix2000 = "#";
suffix2000 = "#";
break;
case "Oracle8":
case "Oracle":
wildCardChar = "%";
prefix1999 = "to_date('";
suffix1999 = "','yyyy/mm/dd hh24:mi:ss')";
prefix2000 = "to_date('";
suffix2000 = "','yyyy/mm/dd hh24:mi:ss')";
break;
case "Microsoft SQL Server":
wildCardChar = "%";
prefix1999 = "'";
suffix1999 = "'";
prefix2000 = "'";
suffix2000 = "'";
break;
default:
if (RuntimeDebug) {
RuntimeDebugMessage("Error: Unsupported ODBC driver: \"DBMS Name\" = [" + connection.Properties.Item("DBMS Name") + "]
\n");
}
wildCardChar = "%";
prefix1999 = "'";
suffix1999 = "'";
prefix2000 = "'";
suffix2000 = "'";
break;
}
break;
case "Microsoft OLE DB Provider for SQL Server":
case "Microsoft OLE DB Provider for Jet":
wildCardChar = "%";
prefix1999 = "'";
suffix1999 = "'";
prefix2000 = "'";
suffix2000 = "'";
break;
case "Microsoft OLE DB Provider for Oracle":
wildCardChar = "%";
prefix1999 = "to_date('";
suffix1999 = "','yyyy/mm/dd hh24:mi:ss')";
prefix2000 = "to_date('";
suffix2000 = "','yyyy/mm/dd hh24:mi:ss')";
break;
default:
if (RuntimeDebug) {
RuntimeDebugMessage("Error: Unsupported ODBC driver: \"Provider Friendly Name\" = [" + connection.Properties.Item("Provider Friendly Name") + "]
\n");
}
wildCardChar = "%";
prefix1999 = "'";
suffix1999 = "'";
prefix2000 = "'";
suffix2000 = "'";
break;
}
switch(Number(datatype)) {
case adEmpty:
if (mode == "SET") {
retString = "''";
} else if (mode == "WHERE") {
retString = " IS NULL";
}
break;
case adBoolean:
if (mode == "SET") {
retString = (value == glTrue) ? trueValue : falseValue;
} else if (mode == "WHERE") {
retString = ((value == glTrue) ? "<>" : "=") + falseValue;
}
break;
case adBigInt:
case adDecimal:
case adDouble:
case adInterger:
case adNumeric:
case adSingle:
case adSmallInt:
case adTinyInt:
case adUnsignedBigInt:
case adUnsignedInt:
case adUnsignedSmallInt:
case adUnsignedTinyInt:
case adVarNumeric:
case adCurrency:
if (mode == "SET") {
retString = tempValue;
} else if (mode == "WHERE") {
retString = "=" + tempValue;
}
break;
case adDate:
case adDBDate:
case adDBTime:
case adDBTimeStamp:
var checkYear = new Date(value);
if (mode == "SET") {
if (checkYear.getYear() <= 99) {
retString = prefix1999 + tempValue + suffix1999;
} else {
retString = prefix2000 + tempValue + suffix2000;
}
} else if (mode == "WHERE") {
if ((tempValue == "")||(tempValue.toUpperCase() == "NULL")) {
retString = " IS NULL";
} else {
if (checkYear.getYear() <= 99) {
retString = "=" + prefix1999 + tempValue + suffix1999;
} else {
retString = "=" + prefix2000 + tempValue + suffix2000;
}
}
}
break;
case adBSTR:
case adChar:
case adWChar:
case adVarChar:
case adVarWChar:
if (mode == "SET") {
retString = "'" + tempValue + "'";
} else if (mode == "WHERE") {
if (tempValue.toUpperCase() == "NULL") {
retString = " IS NULL";
} else if (tempValue == "") {
retString = "=''";
} else {
retString = " like '" + tempValue + wildCardChar + "'";
}
}
break;
case adLongVarChar: // Access Memo type / Oracle long text
case adLongVarWChar:// MS-SQL long Unicode string
if (mode == "SET") {
retString = "'" + tempValue + "'";
} else if (mode == "WHERE") {
retString = ""; // cannot use at Where clause
}
break;
default:
if (RuntimeDebug) {
RuntimeDebugMessage("Error: Unsupported datatype: datatype = [" + datatype + "]
value=[" + value + "]
\n");
}
break;
}
if (tempValue == "") {
retString = "NULL";
}
return retString;
}
function setClause(data)
{
var fields = new Array;
for (var name in data) {
fields[fields.length] = name + " = " + wrapSQLValue(name, data[name], "SET");
}
return " SET " + fields.join(", ");
}
function whereClause(key, cswName)
{
var fields = new Array;
for (var column in key) {
if (column != ("RECORD_KEY[" + cswName + "]") && typeof(key[column]) != "undefined") {
var columnSplit = column.split("[");
var fieldName = columnSplit[0];
fields[fields.length] = fieldName + wrapSQLValue(fieldName, key[fieldName+"["+cswName+"]"], "WHERE");
}
}
if (fields.length == 0)
return "";
return " WHERE " + fields.join(" AND ");
}
function columnList(data)
{
var fields = new Array;
for (var name in data) {
fields[fields.length] = name;
}
return " (" + fields.join(", ") + ") ";
}
function valuesClause(data)
{
var fields = new Array;
for (var name in data) {
fields[fields.length] = wrapSQLValue(name, data[name], "SET");
}
return " VALUES (" + fields.join(", ") + ") ";
}
function printConnectionException(e) {
var msg = "SQL Error = [" + e + "]
\n";
msg += "\n";
msg += "| name | "
+ e.name + " |
|---|
\n";
msg += "| number | "
+ e.number + " |
|---|
\n";
msg += "| description | "
+ e.description + " |
|---|
\n";
msg += "| message | "
+ e.message + " |
|---|
\n";
msg += "
\n";
RuntimeDebugMessage(msg);
}
function executeSql(connection, sql)
{
if (RuntimeDebug) {
RuntimeDebugMessage("SQL = [" + sql + "]
\n");
}
try {
connection.Execute(sql);
} catch(e) {
if (RuntimeDebug)
printConnectionException(e);
}
}
function printSqlErrors(connection)
{
if (!RuntimeDebug)
return;
var err = new ActiveXObject("ADODB.Error");
var msg = "ADO.Error " + connection.Errors.Count + " error(s)
\n";
msg += "\n";
for (var i = 0; i < connection.Errors.Count; i++) {
err = connection.Errors(i);
msg += "| error[" + i + "] | Number | "
+ err.Number + " |
|---|
\n";
msg += "| | Source | "
+ err.Source + " |
|---|
\n";
msg += "| | Description | "
+ err.Description + " |
|---|
\n";
msg += "| | HelpFile | "
+ err.HelpFile + " |
|---|
\n";
msg += "| | HelpContext | "
+ err.HelpContext + " |
|---|
\n";
msg += "| | SQLState | "
+ err.SQLState + " |
|---|
\n";
msg += "| | NativeError | "
+ err.NativeError + " |
|---|
\n";
}
msg += "
\n";
RuntimeDebugMessage(msg);
}
// -----------------------------------------------------------------------------
// Record navigation utilities
function join_url(parts)
{
return parts["base_url"] + "?" + parts["query"];
}
function parse_url(url)
{
var base_and_query = String(url).split("?");
var parts = new Array;
parts["base_url"] = base_and_query[0];
parts["query"] = base_and_query[1];
return parts;
}
function addParams(url, params, cswName)
{
var parts = parse_url(url);
var args = queryStringToArray(parts["query"]);
if (typeof(args["RECORD_INDEX["+cswName+"]"]) != "undefined") {
args = resetRecordNavigation(args, cswName);
}
for (var key in params) {
args[key] = params[key];
}
parts["query"] = queryArrayToString(args);
return join_url(parts);
}
function decrementRecordIndex(url, cswName)
{
var parts = parse_url(url);
var args = queryStringToArray(parts["query"]);
var param = "RECORD_INDEX[" + cswName + "]";
if (typeof(args[param]) != "undefined") {
var recordIndex = args[param];
args = resetRecordNavigation(args, cswName);
args[param] = Math.max(1, recordIndex - 1);
}
parts["query"] = queryArrayToString(args);
return join_url(parts);
}
// *****************************************************************************
// SERVER SIDE ACTIONS
//
// -----------------------------------------------------------------------------
// Action handler functions
function SubmitChanges(connection, tableName)
{
var cswName = getCswNameFromForm();
var firstRecord = Number(GetFormValue("$firstRecord"));
var lastRecord = Number(GetFormValue("$lastRecord"));
var keys = getKeysFromForm(firstRecord, lastRecord, cswName);
var data = getDataFromForm(firstRecord, lastRecord, cswName);
if ((keys.length > 0 && keys[0]["RECORD_KEY["+cswName+"]"] =="_newRecord")
|| (firstRecord > lastRecord)) {
// new record case - do a SQL insert
var position = Request("$position");
if (typeof(position) != "undefined") {
keys = getKeysFromForm(position, position, cswName);
data = getDataFromForm(position, position, cswName);
for (var i = 0; i < data.length; i++) {
var sql = "INSERT INTO " + tableName + columnList(data[i]) + valuesClause(data[i]);
executeSql(connection, sql);
}
}
} else {
// Update existing record(s)
for (var i = 0; i < data.length; i++) {
var sql = "UPDATE " + tableName + setClause(data[i]) + whereClause(keys[i], cswName);
executeSql(connection, sql);
}
}
// TODO: Error content source?
if (connection.Errors.Count > 0) {
printSqlErrors(connection);
Redirect(String(GetFormValue("$update_failure")));
} else {
Redirect(String(GetFormValue("$update_success")));
}
}
function AddRecord(connection, tableName)
{
var cswName = getCswNameFromForm();
var position = GetFormValue("$position");
var keys = getKeysFromForm(position, position, cswName);
var data = getDataFromForm(position, position, cswName);
if (keys.length > 1) { // block data is not supported
Redirect(String(GetFormValue("$add_failure")));
}
for (var i = 0; i < data.length; i++) {
var sql = "INSERT INTO " + tableName + columnList(data[i]) + valuesClause(data[i]);
executeSql(connection, sql);
}
if (connection.Errors.Count > 0) {
printSqlErrors(connection);
Redirect(String(GetFormValue("$add_failure")));
} else {
var url = String(GetFormValue("$add_success"));
var keys = new Array;
keys["RECORD_KEY["+cswName+"]"] = "$lastRecord";
Redirect(addParams(url, keys, cswName));
}
}
function DeleteRecord(connection, tableName)
{
var firstRecord = GetFormValue("$firstRecord");
var lastRecord = GetFormValue("$lastRecord");
var cswName = getCswNameFromForm();
var keys = getKeysFromForm(firstRecord, lastRecord, cswName);
if (keys.length > 1) { // block data is not supported
Redirect(String(GetFormValue("$delete_failure")));
}
var decrementedSuccessUrl = decrementRecordIndex(String(GetFormValue("$delete_success")), cswName);
var firstRecord = GetFormValue("$firstRecord");
var lastRecord = GetFormValue("$lastRecord");
if (firstRecord > lastRecord) { // $newRecord or $copyRecord
Redirect(decrementedSuccessUrl); // just cancel empty form page
}
// delete last record in a block
var i = keys.length - 1;
var sql = "DELETE FROM " + tableName + whereClause(keys[i], cswName);
executeSql(connection, sql);
if (connection.Errors.Count > 0) {
printSqlErrors(connection);
Redirect(String(GetFormValue("$delete_failure")));
} else if (keys.length > 1) {
// there are still records visible in this block; don't move cursor
Redirect(String(GetFormValue("$delete_success")));
} else {
Redirect(decrementedSuccessUrl);
}
return;
}
function ClearFormData(connection, tableName)
{
var firstRecord = GetFormValue("$firstRecord");
var lastRecord = GetFormValue("$lastRecord");
var cswName = getCswNameFromForm();
var data = getDataFromForm(firstRecord, lastRecord, cswName);
if (data.length > 1) { // block data is not supported
Redirect(String(GetFormValue("$clear_failure")));
} else {
var url = String(GetFormValue("$clear_success"));
var keys = new Array;
keys["RECORD_KEY["+cswName+"]"] = "$newRecord";
Redirect(addParams(url, keys, cswName));
}
}
function CopyFormData(connection, tableName)
{
var firstRecord = GetFormValue("$firstRecord");
var lastRecord = GetFormValue("$lastRecord");
var cswName = getCswNameFromForm();
var keys = getKeysFromForm(firstRecord, lastRecord, cswName);
var data = getDataFromForm(firstRecord, lastRecord, cswName);
if (firstRecord > lastRecord) { // $newRecord or $copyRecord
var url = String(GetFormValue("$copy_success"));
var keys = new Array;
keys["RECORD_KEY["+cswName+"]"] = "$newRecord";
Redirect(addParams(url, keys, cswName));
}
if ((data.length > 1)||(keys.length > 1)) { // block data is not supported
Redirect(String(GetFormValue("$copy_failure")));
} else {
var url = String(GetFormValue("$copy_success"));
keys[0]["RECORD_KEY["+cswName+"]"] = "$copyRecord," + keys[0]["RECORD_KEY["+cswName+"]"]; // magic parameter for copy form
Redirect(addParams(url, keys[0], cswName));
}
}
//-----------------------------------------------------------------------------
// Dispatch
if (RuntimeDebug) {
debugPrintQueryString();
debugPrintFormData();
debugPrintObscuredData();
}
var connectString = unobscure(GetFormValue("$connect"));
var connection = new ActiveXObject("ADODB.Connection");
try {
connection.open(connectString);
}
catch (e) {
Response.Status = "500 Internal server error";
if (RuntimeDebug)
printConnectionException(e);
Response.End();
}
var source = unobscure(GetFormValue("$source"));
var tableName = getTableNameFromSource(source);
var datatypes = queryStringToArray(unobscure(GetFormValue("$datatypes")));
if (GetFormValue("$SubmitChanges") != "_no_form_value")
SubmitChanges(connection, tableName);
else if (GetFormValue("$AddRecord") != "_no_form_value")
AddRecord(connection, tableName);
else if (GetFormValue("$DeleteRecord") != "_no_form_value")
DeleteRecord(connection, tableName);
else if (GetFormValue("$ClearFormData") != "_no_form_value")
ClearFormData(connection, tableName);
else if (GetFormValue("$CopyFormData") != "_no_form_value")
CopyFormData(connection, tableName);
else { // default action
SubmitChanges(connection, tableName);
}
%>