Created
January 13, 2016 10:56
-
-
Save 330k/52e780c78cca3325c0f6 to your computer and use it in GitHub Desktop.
HTML Application SQL Tool with ADODB
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<html> | |
<head> | |
<!-- | |
HTA SQL Tool by 330k | |
Copyright (c) 2016 330k | |
This software is released under the MIT License. | |
http://opensource.org/licenses/mit-license.php | |
--> | |
<title>HTA SQL Tool by 330k</title> | |
<HTA:APPLICATION ID="USQLEXE" APPLICATIONNAME="USQLEXE" BORDER="thick" BORDERSTYLE="normal" | |
CAPTION="yes" ICON="C:\WINDOWS\system32\rsnotify.exe" INNERBORDER="no" | |
MAXIMIZEBUTTON="yes" MINIMIZEBUTTON="yes" SHOWINTASKBAR="yes" SINGLEINSTANCE="no" | |
SYSMENU="yes" VERSION="1.0" WINDOWSTATE="normal" SCROLL="no" SCROLLFLAT="no" | |
SELECTION="yes" CONTEXTMENU="yes" NAVIGABLE="yes" /> | |
</head> | |
<script type="text/javascript"> | |
var doc = document; | |
eval('var document = doc'); | |
var shell = new ActiveXObject('WScript.Shell'); | |
//var http = new ActiveXObject("Msxml2.XMLHTTP"); | |
var fso = new ActiveXObject('Scripting.FileSystemObject'); | |
var ado; | |
var result_tsv = ''; | |
window.onunload = function(){ | |
closeADO(); | |
}; | |
window.onresize = function(){ | |
document.all.result.style.height = document.body.clientHeight - document.all.t1.clientHeight - document.all.status.clientHeight; }; window.onload = function(){ | |
window.onresize(); | |
try{ | |
document.all.sql.value = shell.RegRead("HKCU\\SQLEXEC\\SQLLOG"); | |
// document.all.filename.value = shell.RegRead("HKCU\\SQLEXEC\\LASTDB"); | |
// alert(USQLEXE.commandLine); | |
}catch( e ){ | |
// alert(e.message); | |
} | |
}; | |
function loadDatabase(){ | |
var filepath = document.getElementById('fileselector').value || document.getElementById('filename').value; | |
var readonly = document.getElementById('readonly').value; | |
var hdr = document.getElementById('hdr').value; | |
document.getElementById('filename').value = filepath; | |
document.getElementById('fileselector').parentNode.innerHTML = document.getElementById('fileselector').parentNode.innerHTML; | |
setTimeout(function(){ | |
_loadDatabase(filepath, readonly, hdr); | |
}, 10); // wait for unlock file | |
return false; | |
} | |
function _loadDatabase( filepath, readonly, hdr ){ | |
closeADO(); | |
ado = new ActiveXObject("ADODB.Connection"); | |
var connect = []; | |
if( filepath.match(/\.xls$/i) ){ | |
connect.push('Driver={Microsoft Excel Driver (*.xls)}; DBQ=' + filepath + ';HDR=' + (hdr ? 'Yes' : 'No') + ';ReadOnly=' + (readonly ? 1 : 0) + ';"'); | |
connect.push('Provider=Microsoft.Jet.OLEDB.4.0;Excel 8.0;DATABASE=' + filepath + ';HDR=' + (hdr ? 'Yes' : 'No') + ';ReadOnly=' + (readonly ? 1 : 0) + ';"'); | |
connect.push('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' + filepath + ';Extended Properties="Excel 8.0;HDR=' + (hdr ? 'Yes' : 'No') + ';ReadOnly=' + (readonly ? 1 : 0) + ';"'); | |
connect.push('Provider=MSDASQL.1;Extended Properties="DBQ=' + filepath + ';DefaultDir=C:\;Driver={Microsoft Excel Driver (*.xls)};DriverId=790;"'); | |
}else if( filepath.match(/(\.xlsx|\.xlsm|\.xlsb)$/i) ){ | |
connect.push('Provider=Microsoft.ACE.OLEDB.12.0; Data Source=' + filepath + '; Extended Properties="Excel 12.0;HDR=' + (hdr ? 'Yes' :'No') + ';ReadOnly=' + (readonly ? 1 : 0) + ';"'); | |
connect.push('Provider=Microsoft.ACE.OLEDB.12.0; Data Source=' + filepath + '; Extended Properties="Excel 12.0 Xml;HDR=' + (hdr ? 'Yes' :'No') + ';ReadOnly=' + (readonly ? 1 : 0) + ';"'); | |
connect.push('Provider=Microsoft.ACE.OLEDB.12.0; Data Source=' + filepath + '; Extended Properties="Excel 12.0 Macro;HDR=' + (hdr ? 'Yes' :'No') + ';ReadOnly=' + (readonly ? 1 : 0) + ';"'); | |
connect.push('Provider=Microsoft.ACE.OLEDB.12.0; Data Source=' + filepath + '; Extended Properties="Excel 12.0;IMEX=1;HDR=' + (hdr ? 'Yes' :'No') + ';ReadOnly=' + (readonly ? 1 : 0) + ';"'); | |
connect.push('Provider=Microsoft.ACE.OLEDB.12.0; Data Source=' + filepath + '; Extended Properties="Excel 14.0;HDR=' + (hdr ? 'Yes' :'No') + ';ReadOnly=' + (readonly ? 1 : 0) + ';"'); | |
connect.push('Provider=Microsoft.ACE.OLEDB.12.0; Data Source=' + filepath + '; Extended Properties="Excel 14.0;IMEX=1;HDR=' + (hdr ? 'Yes' :'No') + ';ReadOnly=' + (readonly ? 1 : 0) + ';"'); | |
connect.push('Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=' + filepath + ';ReadOnly=' + (readonly ? 1 : 0)); | |
}else if( filepath.match(/\.csv$/i) ){ | |
connect.push('Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=' + fso.GetParentFolderName(filepath) + ';ReadOnly=' + (readonly ? 1 : 0) + ';FirstRowHasNames=' + (hdr ? '1' : '0') + ';MaxScanRows=8;'); | |
connect.push('Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=' + fso.GetParentFolderName(filepath) + ';ReadOnly=' + (readonly ? 1 : 0) + ';FirstRowHasNames=' + (hdr ? '1' : '0') + ';'); | |
connect.push('Provider=Microsoft.Jet.OLEDB.4.0;TEXT;DATABASE=' + fso.GetParentFolderName(filepath) + ';ReadOnly=' + (readonly ? 1 : 0) + ';FirstRowHasNames=' + (hdr ? '1' : '0') + ';'); | |
}else if( filepath.match(/(\.txt|\.tsv)$/i) ){ | |
connect.push('Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=' + fso.GetParentFolderName(filepath) + ';ReadOnly=' + (readonly ? 1 : 0) + ';FirstRowHasNames=' + (hdr ? '1' : '0') + ';Format=TabDelimited' + ';MaxScanRows=8;'); | |
connect.push('Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=' + fso.GetParentFolderName(filepath) + ';ReadOnly=' + (readonly ? 1 : 0) + ';FirstRowHasNames=' + (hdr ? '1' : '0') + ';Format=TabDelimited' + ';'); | |
connect.push('Provider=Microsoft.Jet.OLEDB.4.0;TEXT;DATABASE=' + fso.GetParentFolderName(filepath) + ';ReadOnly=' + (readonly ? 1 : 0) + ';FirstRowHasNames=' + (hdr ? '1' : '0') + ';Format=TabDelimited' + ';'); | |
}else if( filepath.match(/\.mdb$/i) ){ | |
connect.push('Provider=Microsoft Office 12.0 Access Database Engine OLE DB Provider;Data Source="' + filepath + '";'); | |
connect.push('Provider=Microsoft.Jet.OLEDB.4.0;Data Source="' + filepath + '";'); | |
}else if( filepath.match(/(\.html|\.html)$/i) ){ | |
connect.push('Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="HTML Import;DATABASE=' + filepath + '";'); | |
}else{ | |
alert('Unsupported File!'); | |
} | |
document.all.result.innerHTML = ''; | |
for(var i in connect){ | |
document.all.result.innerHTML += 'Connect String:<br /><pre>' + connect[i] + '</pre>'; | |
try{ | |
ado.Open(connect[i]); | |
setStatusText('Connected Successfully.'); | |
break; | |
}catch(e){ | |
// alert(e.message); | |
document.all.result.innerHTML += 'Failed:<pre>' + e.message + '</pre>'; | |
setStatusText('Connection Failed.'); | |
} | |
} | |
} | |
function executeSQL( sql ){ | |
try{ | |
var times = [new Date()]; | |
setStatusText(''); | |
var rs = ado.Execute(sql); | |
times.push( new Date() ); | |
var buf2 = createTableFromRecordSet( rs ); | |
times.push( new Date() ); | |
document.all.result.innerHTML = ''; | |
document.all.result.appendChild( buf2.dom_table ); | |
window.result_tsv = buf2.tsv; | |
times.push( new Date() ); | |
setStatusText("Records: " + buf2.count | |
+ ",\tSQL Execution Time: " + (times[1] - times[0]) | |
+ "ms,\tTable Construction Time: " + (times[2] - times[1]) | |
+ "ms,\tDrawing Time: " + (times[3] - times[2]) + "ms"); | |
shell.RegWrite("HKCU\\SQLEXEC\\SQLLOG", sql, "REG_SZ"); | |
shell.RegWrite("HKCU\\SQLEXEC\\LASTDB", document.all.filename.value, "REG_SZ") | |
}catch( e ){ | |
alert(e.message); | |
} | |
} | |
function getTableStructure(){ | |
try{ | |
//var rs = ado.OpenSchema(20); | |
var rs = ado.OpenSchema(4); | |
document.all.result.innerHTML = ''; | |
document.all.result.appendChild( createTableFromRecordSet( rs ).dom_table ); | |
}catch( e ){ | |
alert(e.message); | |
} | |
} | |
function copyTable(){ | |
clipboardData.setData("Text", window.result_tsv); } function setStatusText( mes ){ | |
document.all.status.innerHTML = mes; | |
} | |
function closeADO(){ | |
try{ | |
ado.Close(); | |
}catch( e ){ | |
} | |
ado = null; | |
setStatusText('Disconnected Successfully.'); | |
} | |
function createTableFromRecordSet( rs ){ | |
var table = document.createElement('table'); | |
var tbody = document.createElement('tbody'); | |
var row; | |
var cell; | |
var buf_tsv = []; | |
var buf_tsv_row = []; | |
var count = 0; | |
if( !rs.Eof ){ | |
var fc = rs.Fields.Count; | |
row = document.createElement('tr'); | |
for( var i = 0; i < fc; i++ ){ | |
cell = document.createElement('th'); | |
cell.innerText = rs.Fields(i).name; | |
cell.title = rs.Fields(i).Type; | |
row.appendChild(cell); | |
buf_tsv_row.push( escapeTSV(rs.Fields(i).name) ); | |
} | |
tbody.appendChild(row); | |
buf_tsv.push( buf_tsv_row.join('\t') ); | |
while( !rs.Eof ){ | |
row = document.createElement('tr'); | |
buf_tsv_row = []; | |
for( var i = 0; i < fc; i++ ){ | |
var v = rs.Fields(i).value; | |
cell = document.createElement('td'); | |
cell.innerText = v; | |
row.appendChild(cell); | |
buf_tsv_row.push( escapeTSV(v) ); | |
} | |
tbody.appendChild(row); | |
buf_tsv.push( buf_tsv_row.join('\t') ); | |
count++; | |
rs.MoveNext(); | |
} | |
table.appendChild(tbody); | |
} | |
return {"count":count,"dom_table":table,"tsv":buf_tsv.join('\n')} | |
} | |
function escapeTSV(data){ | |
if(typeof data == 'string'){ | |
data = '"' + data.replace(/"/g,'""') + '"'; | |
} | |
return data; | |
} | |
</script> | |
<style type="text/css"> | |
* { margin: 0; padding: 0; font-family: sans-serif; font-size: 10pt; } | |
/*table { table-layout: fixed; }*/ | |
/*td {}*/ | |
textarea { font-family: FixedSys,monospace; font-size: 10pt; } | |
button { width: 100px; font-size: 9pt;} | |
#result table { border-collapse: collapse; border: 1px solid black; } | |
#result td,th { border: 1px solid black; vertical-align: bottom; word-break: keep-all; } | |
#result th { background: rgb(192,192,192); } | |
pre { font-family: monospace; } | |
</style> | |
<body> | |
<form id="f1" onsubmit="return false;" action="#"> | |
<table id="t1" style="width:100%;height:100px;padding:10px;"> | |
<tr> | |
<td> | |
Select Database(*.xls, *.xlsx or *.csv) File | |
<label for="readonly"><input type="checkbox" id="readonly" checked="checked" />Read Only</label> | |
<label for="hdr"><input type="checkbox" id="hdr" checked="checked" />Use First Row as Headers</label><br /> | |
<input type="text" id="filename" style="width:80%;" readonly="readonly" /><span><input type="file" id="fileselector" onchange="loadDatabase();" style="width:20%;" /></span> | |
</td> | |
<td style="width:100px;"> | |
<button onclick="loadDatabase();" style="height:50%;">Reopen</button><br /> | |
<button onclick="closeADO();" style="height:50%;">Close</button> | |
</td> | |
</tr> | |
<tr> | |
<td> | |
SQL<br /> | |
<textarea id="sql" style="width:100%;height:100px;scroll:auto;" wrap="off">select * from [Sheet1$]</textarea> | |
</td> | |
<td> | |
<button onclick="executeSQL(this.form.sql.value);">Execute SQL</button><br /> | |
<button onclick="copyTable();">Copy Table</button><br /> | |
<button onclick="getTableStructure();">Meta Information</button> | |
</td> | |
</tr> | |
</table> | |
</form> | |
<div id="result" style="width:100%;height:500px;overflow:scroll;background:white;padding:10px;border-top:1px solid gray;border-bottom:1px solid threedhighlight;"></div> <div id="status" style="width:1980px;height:20px;overflow:hidden;background:buttonface;"></div> | |
</body> | |
</html> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment