DinoChiesa/sql-1.asp

## sql-1.asp
<%@ language="Javascript" %>

<script language="javascript" runat="server" src='json2.js'></script>
<script language="javascript" runat="server" src='stringExtensions.js'></script>
<script language="javascript" runat="server" src='contentNego.js'></script>
<script language="javascript" runat="server">

(function() {

    // In an ASP scenario, this fn gets "exported"
    // to the global namespace, outside the scope of the
    // containing anonymous function.
    processRequest = function() {
        var method = (Request.ServerVariables('REQUEST_METHOD') + '').toLowerCase();
        switch (method) {
            case 'get':
              handleGet();
              break;
            case 'options':
              header('Allow: GET, OPTIONS');
              break;
            default:
              status('405 Method Not Allowed');
              break;
          }
    };


    // All fns defined with normal syntax are available only
    // within this scope, but to all within this scope via
    // function hoisting.
    function header(str) {
        var ix = str.indexOf(':'),
            name = str.substring(0, ix),
            value = str.substr(ix + 1);
        Response.AddHeader(name, value);
    }

    function status(line) {
        Response.Status = line;
    }

    function formatAsText(r) {
        var x = '', i, L = r.length, row, p;
        for (i=0; i<L; i++) {
            row = r[i];
            for (p in row) {
                if (row.hasOwnProperty(p)) {
                    x += p + ': ' + row[p] + '\n';
                }
            }
        }
        return x;
    }

    function formatAsXml(r) {
        var x = '<root>', i, L = r.length, row, p;
        for (i=0; i<L; i++) {
            x += '<item>';
            row = r[i];
            for (p in row) {
                if (row.hasOwnProperty(p)) {
                    x += '<' + p + '>' + row[p] + '</' + p + '>';
                }
            }
            x += '</item>';
        }
        x += '</root>';
        return x;
    }

    function htmlPageHeader() {
        var h = '<html>\n<head>\n  <title>Query SQL from JS</title>\n' +
            '<style type="text/css">\n' +
            'body{font-family:sans-serif;}\n' +
            'table{' +
            '  border-spacing:0;\n' +
            '  border-collapse:collapse;\n' +
            '  font-size:11pt;\n' +
            '  border-top:1px solid #888;border-left:1px solid #888;\n' +
            '  border-bottom:0px none #888;border-right:0px none #888;\n' +
            '  margin:0;padding:0;margin-bottom:0;}\n' +

            'thead th, tbody td{' +
            '  padding:0.2em 0.3em;\n' +
            '  margin:0;\n' +
            '  border-right:1px solid #888;border-bottom:1px solid #888;\n' +
            '  border-left:0px none #888;border-top:0px none #888;}\n' +
            'thead th{background-color:rgb(221, 221, 255);}\n' +
            'tbody td{text-align:left;}\n' +
            '</style>\n' +
            '</head>\n' +
            '<body>\n' +
            '<h2>Query MSSQL from Javascript in ASP</h2>\n';
        return h;
    }

    function htmlPageTrailer() {
        var t = '</body></html>';
        return t;
    }


    function tablify(a) {
        var L = a.length,  i,  r = '<table>', row, p;
        if (L>0){
            row = a[0];
            r += '<thead><tr>';
            for (var p in row) {
                if (row.hasOwnProperty(p)) {
                    r += '<th>' + p + '</th>';
                }
            }
            r += '</tr></thead><tbody>';
            for (i=0;i<L;i++) {
                row = a[i];
                r += '<tr>';
                for (var p in row) {
                    if (row.hasOwnProperty(p)) {
                        r += '<td>' + row[p] + '</td>';
                    }
                }
                r += '</tr>';
            }
            r +=  '</tbody>';
        }
        r +=  '</tbody></table>';
        return r;
    }

    function formatAsHtml(r) {
        var x = htmlPageHeader() +
            tablify(r) + htmlPageTrailer();
        return x;
    }

    function handleGet() {
        var t = Request.QueryString('like') + '',
            token = (t == 'undefined') ? null:t,
            r = queryDb(token),
            cnego = CNego.figureDesiredContentType(),
            output;

        header('Content-Type: ' + cnego[0]);

        switch (cnego[1]) {
            case CNego.contentFlavor.HTML:
            Response.Write(formatAsHtml(r));
            break;
            case CNego.contentFlavor.XML:
            Response.Write(formatAsXml(r));
            break;
            case CNego.contentFlavor.JSON:
            Response.Write(JSON.stringify(r));
            break;
            case CNego.contentFlavor.Text:
            Response.Write(formatAsText(r));
            break;
        }
    }

    function byLastName(a, b) {
        var nameA = a.lname, nameB = b.lname;
        if (nameA < nameB) //sort string ascending
            return -1;
        if (nameA > nameB)
            return 1;
        return 0;
    }

    function queryDb(like) {
        var conn, rs, result = [], C, rec;
        conn = new ActiveXObject("ADODB.Connection");

        conn.ConnectionString =
            'Provider=SQLOLEDB.1;' +
            'Integrated Security=SSPI;' +
            'Persist Security Info=False;' +
            'Initial Catalog=AVWKS2008LT;' +
            'Data Source=.\\SQLEXPRESS';

        conn.Open();
        rs = new ActiveXObject("ADODB.Recordset");
        var query =
            'SELECT distinct ' +
            '  Firstname as fname, ' +
            '  LastName as lname, ' +
            '  EmailAddress as email ' +
            'FROM SalesLT.Customer ';

        if (like !== null) {
            query += "WHERE LastName like '" + like + "%' ";
        }

        rs.Open(query, conn, 1, 3);
        while (!rs.EOF) {
            C = rs.Fields.Count;
            rec = {};
            for (i=0; i<C; i++) {
                rec[rs.Fields(i).Name] = rs.Fields(i).Value.trim();
            }
            result.push(rec);
            rs.MoveNext();
        }
        conn.Close();
        result.sort(byLastName);
        return result;
    }

    // for diagnostic purposes
    function say(x) {
        Response.Write(x + "<br/>\n");
    }

}());

try {
    processRequest();
}
catch(e) {
    Response.Write(e.message);
}

</script>
	<%@ language="Javascript" %>

	<script language="javascript" runat="server" src='json2.js'></script>
	<script language="javascript" runat="server" src='stringExtensions.js'></script>
	<script language="javascript" runat="server" src='contentNego.js'></script>
	<script language="javascript" runat="server">

	(function() {

	// In an ASP scenario, this fn gets "exported"
	// to the global namespace, outside the scope of the
	// containing anonymous function.
	processRequest = function() {
	var method = (Request.ServerVariables('REQUEST_METHOD') + '').toLowerCase();
	switch (method) {
	case 'get':
	handleGet();
	break;
	case 'options':
	header('Allow: GET, OPTIONS');
	break;
	default:
	status('405 Method Not Allowed');
	break;
	}
	};


	// All fns defined with normal syntax are available only
	// within this scope, but to all within this scope via
	// function hoisting.
	function header(str) {
	var ix = str.indexOf(':'),
	name = str.substring(0, ix),
	value = str.substr(ix + 1);
	Response.AddHeader(name, value);
	}

	function status(line) {
	Response.Status = line;
	}

	function formatAsText(r) {
	var x = '', i, L = r.length, row, p;
	for (i=0; i<L; i++) {
	row = r[i];
	for (p in row) {
	if (row.hasOwnProperty(p)) {
	x += p + ': ' + row[p] + '\n';
	}
	}
	}
	return x;
	}

	function formatAsXml(r) {
	var x = '<root>', i, L = r.length, row, p;
	for (i=0; i<L; i++) {
	x += '<item>';
	row = r[i];
	for (p in row) {
	if (row.hasOwnProperty(p)) {
	x += '<' + p + '>' + row[p] + '</' + p + '>';
	}
	}
	x += '</item>';
	}
	x += '</root>';
	return x;
	}

	function htmlPageHeader() {
	var h = '<html>\n<head>\n <title>Query SQL from JS</title>\n' +
	'<style type="text/css">\n' +
	'body{font-family:sans-serif;}\n' +
	'table{' +
	' border-spacing:0;\n' +
	' border-collapse:collapse;\n' +
	' font-size:11pt;\n' +
	' border-top:1px solid #888;border-left:1px solid #888;\n' +
	' border-bottom:0px none #888;border-right:0px none #888;\n' +
	' margin:0;padding:0;margin-bottom:0;}\n' +

	'thead th, tbody td{' +
	' padding:0.2em 0.3em;\n' +
	' margin:0;\n' +
	' border-right:1px solid #888;border-bottom:1px solid #888;\n' +
	' border-left:0px none #888;border-top:0px none #888;}\n' +
	'thead th{background-color:rgb(221, 221, 255);}\n' +
	'tbody td{text-align:left;}\n' +
	'</style>\n' +
	'</head>\n' +
	'<body>\n' +
	'<h2>Query MSSQL from Javascript in ASP</h2>\n';
	return h;
	}

	function htmlPageTrailer() {
	var t = '</body></html>';
	return t;
	}


	function tablify(a) {
	var L = a.length, i, r = '<table>', row, p;
	if (L>0){
	row = a[0];
	r += '<thead><tr>';
	for (var p in row) {
	if (row.hasOwnProperty(p)) {
	r += '<th>' + p + '</th>';
	}
	}
	r += '</tr></thead><tbody>';
	for (i=0;i<L;i++) {
	row = a[i];
	r += '<tr>';
	for (var p in row) {
	if (row.hasOwnProperty(p)) {
	r += '<td>' + row[p] + '</td>';
	}
	}
	r += '</tr>';
	}
	r += '</tbody>';
	}
	r += '</tbody></table>';
	return r;
	}

	function formatAsHtml(r) {
	var x = htmlPageHeader() +
	tablify(r) + htmlPageTrailer();
	return x;
	}

	function handleGet() {
	var t = Request.QueryString('like') + '',
	token = (t == 'undefined') ? null:t,
	r = queryDb(token),
	cnego = CNego.figureDesiredContentType(),
	output;

	header('Content-Type: ' + cnego[0]);

	switch (cnego[1]) {
	case CNego.contentFlavor.HTML:
	Response.Write(formatAsHtml(r));
	break;
	case CNego.contentFlavor.XML:
	Response.Write(formatAsXml(r));
	break;
	case CNego.contentFlavor.JSON:
	Response.Write(JSON.stringify(r));
	break;
	case CNego.contentFlavor.Text:
	Response.Write(formatAsText(r));
	break;
	}
	}

	function byLastName(a, b) {
	var nameA = a.lname, nameB = b.lname;
	if (nameA < nameB) //sort string ascending
	return -1;
	if (nameA > nameB)
	return 1;
	return 0;
	}

	function queryDb(like) {
	var conn, rs, result = [], C, rec;
	conn = new ActiveXObject("ADODB.Connection");

	conn.ConnectionString =
	'Provider=SQLOLEDB.1;' +
	'Integrated Security=SSPI;' +
	'Persist Security Info=False;' +
	'Initial Catalog=AVWKS2008LT;' +
	'Data Source=.\\SQLEXPRESS';

	conn.Open();
	rs = new ActiveXObject("ADODB.Recordset");
	var query =
	'SELECT distinct ' +
	' Firstname as fname, ' +
	' LastName as lname, ' +
	' EmailAddress as email ' +
	'FROM SalesLT.Customer ';

	if (like !== null) {
	query += "WHERE LastName like '" + like + "%' ";
	}

	rs.Open(query, conn, 1, 3);
	while (!rs.EOF) {
	C = rs.Fields.Count;
	rec = {};
	for (i=0; i<C; i++) {
	rec[rs.Fields(i).Name] = rs.Fields(i).Value.trim();
	}
	result.push(rec);
	rs.MoveNext();
	}
	conn.Close();
	result.sort(byLastName);
	return result;
	}

	// for diagnostic purposes
	function say(x) {
	Response.Write(x + "<br/>\n");
	}

	}());

	try {
	processRequest();
	}
	catch(e) {
	Response.Write(e.message);
	}

	</script>