/*
Version: 1.01 General Public Release
Date: 09/27/2007
Authors (including external documentation): Win-Bin Huang and Eric Matthews
www.SQLINFO.net
www.dbOrchestra.com
Copyright 2007 DataExpressions Inc. All Rights Reserved. This script is free to
use for commercial or non-commercial purposes. The user assumes full liability
for use of this script. DataExpressions is not responsible for the use or
misuse of this script. You may not reverse engineer or re-engineer this script
in any way. You may reuse this script in any application or environment provided
that you leave everything contained within this script file and any external
files it references. This includes the code, the comments, and any other
verbage contained within.
*/
var g_connection_log_fileStream = null;
var g_connection_echo_SQL_command = 0;
var g_server_dict = null;
var g_connection_file = "config_connection_file.txt";
var TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0;
var ForReading = 1 , ForWriting = 2 , ForAppending = 8;
var adLongVarChar = 201; //Column datatype is Text.
var adLongVarBinary = 205; //Column datatype is Image.
var adBinary = 128; //Column datatype is Binary.
var g_server ;
var g_port ;
var g_dbname ;
var g_username ;
var g_password ;
//If port number is 1433, then this is optional.
function Connection(server, dbname, username, password, port)
{
if ( typeof(username) == "undefined" || typeof(password) == "undefined" )
{
return new Connection_WIN_AUTH( server, dbname ) ;
}
if ( typeof(port) != "undefined" )
{
server += "," + port ;
}
//properties
this._servername = server ;
this._port = port ;
this._username = username ;
this._password = password ;
this._dbname = dbname ;
this._timeout = 0 ;
this._conn = null ;
this._report_exectime_flag = 1 ;
this._bcp_lock_table_flag = 1 ;
this._logfile = "WS_log_file_" + get_today_string() + ".txt";
this._logstring = "" ;
this._logflag = 1 ;
this._fso = new ActiveXObject("Scripting.FileSystemObject");
//public methods BEGIN----------------------------------------
this.RecurInstallSQLDir = RecurInstallSQLDir ;
this.InstallSQLDir = InstallFolderSQLFileList ;
this.InstallSQLFile = InstallSQLFile ;
//Exec the SQL file and Store the output in the specified file.
this.ExecuteSQLFileAndGenerate = GenerateInstallSQLFile ; // ( sql_file, SQL_output_file )
this.ExecuteSQLAndGenerateFile = ExecAndGenSQLFile ; // ( sql_cmd, SQL_output_file )
this.RecurBulkInsertDir = RecurBulkInsertDir ; // ( bcp_dir_path )
this.BulkInsertDir = BulkInsertDir ; // ( bcp_dir_path )
this.RecurBulkInsertDir2 = RecurBulkInsertDir2 ; // ( table_name, bcp_dir_path )
this.BulkInsertDir2 = BulkInsertDir2 ; // ( table_name, bcp_dir_path )
this.RecurBulkInsertDir3 = RecurBulkInsertDir3 ; // ( bcp_dir_path , field_delimiter_str,
this.BulkInsertDir3 = BulkInsertDir3 ; // row_delimiter_str, suffix_str )
// e.g. field_delimiter_str could be $()$.
// e.g. row_delimiter_str could be $##$.
// e.g. suffix_str could be dat or csv.
this.RecurBulkInsertDir4 = RecurBulkInsertDir4 ; // ( table_name , bcp_dir_path ,
this.BulkInsertDir4 = BulkInsertDir4 ; // field_delimiter_str, row_delimiter_str,
// suffix_str )
this.RecurBulkInsertDirGen = RecurBulkInsertDirGen ; // ( bcp_dir_path )
this.BulkInsertDirGen = BulkInsertDirGen ; // ( bcp_dir_path )
this.BulkInsertTabFile = BulkInsertTabFile ; // ( table_name, data_file_path )
this.BulkInsertCommaFile = BulkInsertCommaFile ; // ( table_name, data_file_path )
this.SetBulkcopyOptionOn = SetBulkcopyOptionOn ;
this.SetBulkcopyOptionOff = SetBulkcopyOptionOff ;
this.SetSingleUserOptionOn = SetSingleUserOptionOn ;
this.SetSingleUserOptionOff = SetSingleUserOptionOff ;
this.ExecSQL_if_error_quit = ExecSQL_if_error_quit ;
this.ExecSQL_if_error_noquit = ExecSQL_if_error_noquit ;
//public methods END------------------------------------------
//private methods
this.Connect = Connect ;
this.InstallFolderSQLFileList = InstallFolderSQLFileList ;
this.ExecuteQuery = ExecuteQuery ;
this.GenerateExecuteQuery = GenerateExecuteQuery ;
this.InstallSQLFile2 = InstallSQLFile2 ;
this.check_go_str = check_go_str ;
this.RecurCollect_SQLFile = RecurCollect_SQLFile ;
this.CollectFolder_SQLFile = CollectFolder_SQLFile ;
this.Install_dictionary_SQLFile = Install_dictionary_SQLFile ;
this.Display_dictionary_key = Display_dictionary_key ;
this.Connect();
}
function Connection_WIN_AUTH(server, dbname, port)
{
if ( typeof(port) != "undefined" )
{
server += "," + port ;
}
//properties
this._servername = server ;
this._port = port ;
this._dbname = dbname ;
this._timeout = 0 ;
this._conn = null ;
this._report_exectime_flag = 1 ;
this._bcp_lock_table_flag = 1 ;
this._logfile = "WS_log_file_" + get_today_string() + ".txt";
this._logstring = "" ;
this._logflag = 1 ;
this._fso = new ActiveXObject("Scripting.FileSystemObject");
//public methods BEGIN----------------------------------------
this.RecurInstallSQLDir = RecurInstallSQLDir ;
this.InstallSQLDir = InstallFolderSQLFileList ;
this.InstallSQLFile = InstallSQLFile ;
//Exec the SQL file and Store the output in the specified file.
this.ExecuteSQLFileAndGenerate = GenerateInstallSQLFile ; // ( sql_file, SQL_output_file )
this.ExecuteSQLAndGenerateFile = ExecAndGenSQLFile ; // ( sql_cmd, SQL_output_file )
this.RecurBulkInsertDir = RecurBulkInsertDir ; // ( bcp_dir_path )
this.BulkInsertDir = BulkInsertDir ; // ( bcp_dir_path )
this.RecurBulkInsertDir2 = RecurBulkInsertDir2 ; // ( table_name, bcp_dir_path )
this.BulkInsertDir2 = BulkInsertDir2 ; // ( table_name, bcp_dir_path )
this.RecurBulkInsertDir3 = RecurBulkInsertDir3 ; // ( bcp_dir_path , field_delimiter_str,
this.BulkInsertDir3 = BulkInsertDir3 ; // row_delimiter_str, suffix_str )
// e.g. field_delimiter_str could be $()$.
// e.g. row_delimiter_str could be $##$.
// e.g. suffix_str could be dat or csv.
this.RecurBulkInsertDir4 = RecurBulkInsertDir4 ; // ( table_name , bcp_dir_path ,
this.BulkInsertDir4 = BulkInsertDir4 ; // field_delimiter_str, row_delimiter_str,
// suffix_str )
this.RecurBulkInsertDirGen = RecurBulkInsertDirGen ; // ( bcp_dir_path )
this.BulkInsertDirGen = BulkInsertDirGen ; // ( bcp_dir_path )
this.BulkInsertTabFile = BulkInsertTabFile ; // ( table_name, data_file_path )
this.BulkInsertCommaFile = BulkInsertCommaFile ; // ( table_name, data_file_path )
this.SetBulkcopyOptionOn = SetBulkcopyOptionOn ;
this.SetBulkcopyOptionOff = SetBulkcopyOptionOff ;
this.SetSingleUserOptionOn = SetSingleUserOptionOn ;
this.SetSingleUserOptionOff = SetSingleUserOptionOff ;
this.ExecSQL_if_error_quit = ExecSQL_if_error_quit ;
this.ExecSQL_if_error_noquit = ExecSQL_if_error_noquit ;
//public methods END------------------------------------------
//private methods
this.Connect_WIN_AUTH = Connect_WIN_AUTH ;
this.InstallFolderSQLFileList = InstallFolderSQLFileList ;
this.ExecuteQuery = ExecuteQuery ;
this.GenerateExecuteQuery = GenerateExecuteQuery ;
this.InstallSQLFile2 = InstallSQLFile2 ;
this.check_go_str = check_go_str ;
this.RecurCollect_SQLFile = RecurCollect_SQLFile ;
this.CollectFolder_SQLFile = CollectFolder_SQLFile ;
this.Install_dictionary_SQLFile = Install_dictionary_SQLFile ;
this.Display_dictionary_key = Display_dictionary_key ;
this.Connect_WIN_AUTH();
}
function Connect()
{
this._conn = WScript.CreateObject("ADODB.Connection");
var conn_str = "uid=" + this._username + ";pwd=" + this._password +
";driver={SQL Server};server=" + this._servername +
";database=" + this._dbname + ";dsn=''" ;
try
{
this._conn.open(conn_str);
WScript.echo( "Connected to server = " + this._servername + " database = " + this._dbname +
" user = " + this._username + "\r\n");
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( "Connected to server = " + this._servername + " database = " +
this._dbname + " user = " + this._username + "\r\n\r\n" );
}
catch (e)
{
var error_str ;
error_str = "Can not connect to server = " + this._servername + " database = " + this._dbname +
"\r\n\r\nThe error messages are:\r\n\r\n" + e.description ;
save_text_to_file("Connection_Error.log", error_str) ;
WScript.echo( error_str );
this._conn = null;
return 1;
}
this._conn.execute("SET QUOTED_IDENTIFIER OFF");
this._conn.CommandTimeout = this._timeout;
if (this._logflag != 1 || g_connection_log_fileStream)
return 0;
try
{
if (g_connection_log_fileStream == null)
g_connection_log_fileStream = this._fso.OpenTextFile(this._logfile, ForAppending, true);
}
catch (e)
{
WScript.echo( e.description );
return 1;
}
return 0;
}
function Connect_WIN_AUTH()
{
this._conn = WScript.CreateObject("ADODB.Connection");
var conn_str = "Provider=sqloledb;Data Source=" + this._servername + ";" +
"Initial Catalog=" + this._dbname + ";Integrated Security=SSPI;" ;
try
{
this._conn.open(conn_str);
WScript.echo( "Connected to server = " + this._servername + " database = " + this._dbname + "\r\n");
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( "Connected to server = " + this._servername + " database = " +
this._dbname + "\r\n\r\n" );
}
catch (e)
{
var error_str ;
error_str = "Can not connect to server = " + this._servername + " database = " + this._dbname +
"\r\n\r\nThe error messages are:\r\n\r\n" + e.description ;
save_text_to_file("Connection_Error.log", error_str) ;
WScript.echo( error_str );
this._conn = null;
return 1;
}
this._conn.execute("SET QUOTED_IDENTIFIER OFF");
this._conn.CommandTimeout = this._timeout;
if (this._logflag != 1 || g_connection_log_fileStream)
return 0;
try
{
if (g_connection_log_fileStream == null)
g_connection_log_fileStream = this._fso.OpenTextFile(this._logfile, ForAppending, true);
}
catch (e)
{
WScript.echo( e.description );
return 1;
}
return 0;
}
function RecurInstallSQLDir(folderspec)
{
var fso = this._fso;
if ( !fso.FolderExists(folderspec) )
{
WScript.echo( "Folder = " + folderspec + " does not exist !" );
return 1;
}
var sql_file_list = new ActiveXObject("Scripting.Dictionary");
RecurCollect_SQLFile(fso, folderspec, sql_file_list);
//Display_dictionary_key(sql_file_list);
Install_dictionary_SQLFile(sql_file_list, this._fso, this._conn, this._report_exectime_flag);
return 0;
}
function InstallFolderSQLFileList(folderspec)
{
var fso = this._fso;
var f, fc, extname;
if ( !fso.FolderExists(folderspec) )
{
WScript.echo( "Folder = " + folderspec + " does not exist !" );
return 1;
}
f = fso.GetFolder(folderspec);
fc = new Enumerator(f.files) ;
for (; !fc.atEnd(); fc.moveNext())
{
extname = fso.GetExtensionName( fc.item() );
if ( extname.toLowerCase() == "sql" )
{
InstallSQLFile2( fc.item(), fso, this._conn, this._report_exectime_flag );
}
}
return 0;
}
function InstallSQLFile(sql_file)
{
if ( !this._fso.FileExists(sql_file) )
{
WScript.echo( "File = " + sql_file + " does not exist !" );
return 1;
}
InstallSQLFile2(sql_file, this._fso, this._conn, this._report_exectime_flag);
return 0;
}
function InstallSQLFile2(sql_file, fso, conn, report_exectime_flag)
{
var file, fileStream, line, pos, sql_str = "";
var len , temp_str , temp_str2, temp_str3 ;
var begin_pos , end_pos ;
var got_comment = 0 , got_go ;
var got_error = 0 , ret_no ;
var d;
var total_line_no = 0, temp_line_no = 0;
if (report_exectime_flag > 0)
{
d = new Date();
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( d.toString() + "\r\n" ) ;
WScript.echo( d );
}
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( "Installing SQL file name ==> " + sql_file + "\r\n" ) ;
WScript.echo( "Installing SQL file name ==> " + sql_file );
try
{
file = fso.GetFile(sql_file);
fileStream = file.OpenAsTextStream(ForReading, TristateUseDefault);
}
catch (e)
{
error_exit( 1, e.description ) ;
return 1;
}
while (!fileStream.atEndOfStream)
{
line = fileStream.ReadLine();
total_line_no++;
temp_line_no++;
if (line.indexOf("/*",0) >= 0)
got_comment++;
if (line.length > 1)
got_go = check_go_str( line );
else
got_go = 0;
if (got_comment > 0)
{
pos = line.indexOf("*/",0);
if (pos >= 0)
got_comment--;
if (got_comment == 0)
{
if (got_go > 0 && got_go > pos)
{
//Send sql_str to be executed.
if (got_go == 2)
{
//WScript.echo(sql_str);
ret_no = ExecuteQueryAndEcho2(conn, sql_str, sql_file, got_error, total_line_no - temp_line_no + 1);
got_error += ret_no;
sql_str = "";
temp_line_no = 0;
}
else
sql_str += line + "\r\n";
//WScript.echo("###GO###\r\n");
}
else
sql_str += line + "\r\n";
}
else
{
sql_str += line + "\r\n";
}
}
else
if (got_go > 0)
{
//Send sql_str to be executed.
if (got_go == 2)
{
//WScript.echo(sql_str);
ret_no = ExecuteQueryAndEcho2(conn, sql_str, sql_file, got_error, total_line_no - temp_line_no + 1);
got_error += ret_no;
sql_str = "";
temp_line_no = 0;
}
else
sql_str += line + "\r\n";
//WScript.echo("###GO###\r\n");
}
else
sql_str += line + "\r\n";
}
if (sql_str.length > 0)
{
//Send sql_str to be executed.
//WScript.echo(sql_str);
ret_no = ExecuteQueryAndEcho2(conn, sql_str, sql_file, got_error, total_line_no - temp_line_no + 1);
got_error += ret_no;
temp_line_no = 0;
}
fileStream.Close();
if (report_exectime_flag > 0)
{
d = new Date();
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( d.toString() + "\r\n" ) ;
WScript.echo( d + "\r\n");
}
return got_error;
}
/***
There are 2 different comments, one is C comments pair, the other one is --.
If return 0 means there are no go strings.
If return 1 means this is comment line.
If return = 2 means we got go string.
Note: we also detect the following (All should return 0):
---------------------------------------------------------
select db_name(), "
go "
select db_name(), '
go '
select ' go
'
select " GO "
select go = getdate()
select getdate() go
---------------------------------------------------------
***/
function check_go_str( line )
{
var temp_str, temp_str3 = line.replace(/ */g,"");
temp_str3 = temp_str3.replace(/\t*/g,"");
temp_str3 = temp_str3.replace(/\r*/g,"");
if (temp_str3.substr(0,2) == "--")
return 1;
if (temp_str3.length < 2)
return 0;
temp_str = temp_str3.substr(0,2).toLowerCase();
if ( temp_str == "go" )
{
if (temp_str3.length > 2)
return 0;
else
return 2;
}
return 0;
}
function ExecuteQuery(conn, sql_cmd, sql_file, got_error)
{
ExecuteAndFormat2(conn, sql_cmd, sql_file, got_error, 0);
}
function ExecuteQueryAndEcho2(conn, sql_cmd, sql_file, got_error, begin_line_no)
{
ExecuteAndFormat2(conn, sql_cmd, sql_file, got_error, begin_line_no);
}
function ExecuteQueryAndEcho(conn, sql_cmd, sql_file, got_error)
{
try
{
if ( g_connection_echo_SQL_command != 0 )
{
if ( g_connection_log_fileStream != null )
g_connection_log_fileStream.Write( "\r\n<===\r\n" + sql_cmd + "\r\n===>\r\n") ;
WScript.echo( "\r\n<===\r\n" + sql_cmd + "\r\n===>\r\n");
}
var rs = conn.execute(sql_cmd);
while (rs != null)
{
var fields = rs.Fields;
while (!rs.EOF)
{
for (var i = 0; i < fields.count; i++)
{
if ( g_connection_log_fileStream != null )
g_connection_log_fileStream.Write( fields(i).Value + "\r\n" ) ;
WScript.echo( fields(i).Value );
}
rs.MoveNext();
}
rs = rs.NextRecordSet();
}
return 0;
}
catch (e)
{
displayError( conn, sql_cmd, sql_file, got_error, 0);
}
}
function GenerateInstallSQLFile(sql_file, SQL_output_file)
{
var fso = this._fso;
var conn = this._conn;
var file, fileStream, line, pos, sql_str = "";
var len , temp_str , temp_str2, temp_str3 ;
var begin_pos , end_pos ;
var got_comment = 0 , got_go ;
var d;
var SQL_output_fileStream;
if (this._report_exectime_flag > 0)
{
d = new Date();
if ( g_connection_log_fileStream != null )
g_connection_log_fileStream.Write( d.toString() + "\r\n" ) ;
WScript.echo( d );
}
if ( g_connection_log_fileStream != null )
g_connection_log_fileStream.Write( "Installing SQL file name ==> " + sql_file + "\r\n" ) ;
WScript.echo( "Installing SQL file name ==> " + sql_file );
try
{
file = fso.GetFile(sql_file);
fileStream = file.OpenAsTextStream(ForReading, TristateUseDefault);
SQL_output_fileStream = fso.OpenTextFile(SQL_output_file, ForWriting, true);
}
catch (e)
{
error_exit( 1, e.Description ) ;
return 1;
}
while (!fileStream.atEndOfStream)
{
line = fileStream.ReadLine();
if (line.indexOf("/*",0) >= 0)
got_comment++;
if (line.length > 1)
got_go = check_go_str( line );
else
got_go = 0;
if (got_comment > 0)
{
pos = line.indexOf("*/",0);
if (pos >= 0)
got_comment--;
if (got_comment == 0)
{
if (got_go > 0 && got_go > pos)
{
//Send sql_str to be executed.
if (got_go == 2)
{
//WScript.echo(sql_str);
GenerateExecuteQuery(conn, sql_str, sql_file, SQL_output_fileStream);
sql_str = "";
}
//WScript.echo("###GO###\r\n");
}
else
sql_str += line + "\r\n";
}
else
{
sql_str += line + "\r\n";
}
}
else
if (got_go > 0)
{
//Send sql_str to be executed.
if (got_go == 2)
{
//WScript.echo(sql_str);
GenerateExecuteQuery(conn, sql_str, sql_file, SQL_output_fileStream);
sql_str = "";
}
//WScript.echo("###GO###\r\n");
}
else
sql_str += line + "\r\n";
}
if (sql_str.length > 0)
{
//Send sql_str to be executed.
//WScript.echo(sql_str);
GenerateExecuteQuery(conn, sql_str, sql_file, SQL_output_fileStream);
}
fileStream.Close();
SQL_output_fileStream.Close();
if (this._report_exectime_flag > 0)
{
d = new Date();
if ( g_connection_log_fileStream != null )
g_connection_log_fileStream.Write( d.toString() + "\r\n" ) ;
WScript.echo( d + "\r\n");
}
}
function ExecAndGenSQLFile(sql_cmd, SQL_output_file)
{
var SQL_output_fileStream;
if ( g_connection_log_fileStream != null )
g_connection_log_fileStream.Write( "Generating SQL file name ==> " + SQL_output_file + "\r\n" ) ;
WScript.echo( "Generating SQL file name ==> " + SQL_output_file );
try
{
SQL_output_fileStream = fso.OpenTextFile(SQL_output_file, ForWriting, true);
}
catch (e)
{
error_exit( 1, e.Description ) ;
return 1;
}
GenerateExecuteQuery(this._conn, sql_cmd, SQL_output_file, SQL_output_fileStream);
SQL_output_fileStream.Close();
}
function GenerateExecuteQuery(conn, sql_str, sql_file, SQL_output_fileStream)
{
try
{
if ( g_connection_echo_SQL_command != 0 )
{
if ( g_connection_log_fileStream != null )
g_connection_log_fileStream.Write( "\r\n<===\r\n" + sql_str + "\r\n===>\r\n") ;
WScript.echo( "\r\n<===\r\n" + sql_str + "\r\n===>\r\n");
}
var rs = conn.execute(sql_str);
while (rs != null)
{
var fields = rs.Fields;
while (!rs.EOF)
{
for (var i = 0; i < fields.count; i++)
{
SQL_output_fileStream.Write(fields(i).Value);
}
rs.MoveNext();
}
rs = rs.NextRecordSet();
}
return 0;
}
catch (e)
{
var error_obj = conn.Errors;
var error_no;
for (var i = 0; i < error_obj.count; i++)
{
if (error_obj(i).Number == 0 || error_obj(i).SQLState == "01000")
{
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( " Info Text: " + error_obj(i).Description + "\r\n" ) ;
WScript.echo( " Info Text: " + error_obj(i).Description + "\r\n" );
}
else
{
var error_str = "\r\n Error messages from SQL file name ==> " + sql_file +
"\r\n Error Text: " + error_obj(i).Description + "\r\n" ;
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( error_str ) ;
WScript.echo( error_str );
}
error_no = error_obj(i).Number;
}
// When we send one or more blank lines to SQL server, even though we got no error messages,
// we will catch it and we have to do the following in order to continue using the conection.
if (error_no != 0)
{
var conn_str = conn.ConnectionString;
conn.close();
conn.open(conn_str);
conn.execute("SET QUOTED_IDENTIFIER OFF");
conn.CommandTimeout = 0;
return 1;
}
else
return 0;
}
}
function SetBulkcopyOptionOn()
{
var sql_str = " exec master..sp_dboption '" + this._dbname + "' ,'" +
"select into/bulkcopy', 'TRUE' ";
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( "SET select into/bulkcopy option on.\r\n\r\n" ) ;
WScript.echo( "SET select into/bulkcopy option on.\r\n\r\n" );
ExecuteQuery(this._conn, sql_str, null, 0);
}
function SetBulkcopyOptionOff()
{
var sql_str = " exec master..sp_dboption '" + this._dbname + "' ,'" +
"select into/bulkcopy', 'FALSE' ";
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( "SET select into/bulkcopy option off.\r\n\r\n" ) ;
WScript.echo( "SET select into/bulkcopy option off.\r\n\r\n" );
ExecuteQuery(this._conn, sql_str, null, 0);
}
function SetSingleUserOptionOn( db_name )
{
var sql_str = " exec master..sp_dboption '" + db_name + "' ,'" + "single user', 'TRUE' ";
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( "SET single user option on.\r\n\r\n" ) ;
WScript.echo( "SET single user option on.\r\n\r\n" );
ExecuteQuery(this._conn, sql_str, null, 0);
}
function SetSingleUserOptionOff( db_name )
{
var sql_str = " exec master..sp_dboption '" + db_name + "' ,'" + "single user', 'FALSE' ";
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( "SET single user option off.\r\n\r\n" ) ;
WScript.echo( "SET single user option off.\r\n\r\n" );
ExecuteQuery(this._conn, sql_str, null, 0);
}
function RecurCollect_SQLFile(fso, folderspec, sql_file_list)
{
var f, fc, len, pos;
f = fso.GetFolder(folderspec);
fc = new Enumerator(f.SubFolders);
for (; !fc.atEnd(); fc.moveNext())
{
RecurCollect_SQLFile( fso, fc.item(), sql_file_list );
}
CollectFolder_SQLFile( fso, f, sql_file_list );
}
function CollectFolder_SQLFile(fso, folderspec, sql_file_list)
{
var f, fc, tablename, extname, filename, len, pos;
f = fso.GetFolder(folderspec);
fc = new Enumerator(f.files);
folderspec = new String(folderspec);
len = folderspec.length;
pos = folderspec.lastIndexOf("\\");
if (pos >= 0 && len > pos + 1)
{
folderspec += "\\";
}
for (; !fc.atEnd(); fc.moveNext())
{
extname = fso.GetExtensionName( fc.item() );
if ( extname.toLowerCase() == "sql" )
{
filename = fso.GetFileName( fc.item() );
sql_file_list.add(folderspec + filename, folderspec + filename);
}
}
}
function Install_dictionary_SQLFile(sql_file_list, fso, conn, report_exectime_flag)
{
var a, i;
a = (new VBArray(sql_file_list.Keys())).toArray(); // Get the keys.
a.sort();
for (i in a)
{
InstallSQLFile2( a[i], fso, conn, report_exectime_flag );
}
}
function Display_dictionary_key(sql_file_list)
{
var a, i;
a = (new VBArray(sql_file_list.Keys())).toArray(); // Get the keys.
a.sort();
for (i in a)
{
WScript.echo( "SQL File Name = " + a[i] );
}
}
function BulkInsertTabFile(table_name, data_file_path)
{
pri_BulkInsertTabFile(this._conn , table_name, data_file_path ,
this._bcp_lock_table_flag, this._report_exectime_flag ) ;
}
function BulkInsertCommaFile(tablename, data_file_path)
{
pri_BulkInsertCommaFile(this._conn , table_name, data_file_path ,
this._bcp_lock_table_flag, this._report_exectime_flag ) ;
}
//CODEPAGE = RAW is very important, without it some column that contains char like { or } will generate errors.
//Details see Book Online. Also don't add ROWTERMINATOR = '\r\n', we need to use default.
//CODEPAGE = OEM is for if we have nvarchar.
function pri_BulkInsertTabFile(conn, table_name, data_file_path, tablockflag, report_exectime_flag)
{
var sql_cmd, d, new_table_name;
new_table_name = "[" + table_name + "]";
sql_cmd = " if exists (select * from sysobjects where name = '" + table_name +
"' and type in ('U','V') ) begin " ;
if (tablockflag)
sql_cmd += "BULK INSERT " + new_table_name +
" FROM '" + data_file_path + "' WITH " +
" (CODEPAGE = 'OEM', DATAFILETYPE = 'CHAR', FIELDTERMINATOR = '\t', TABLOCK, KEEPIDENTITY ) ";
else
sql_cmd += "BULK INSERT " + new_table_name +
" FROM '" + data_file_path + "' WITH " +
" (CODEPAGE = 'OEM', DATAFILETYPE = 'CHAR', FIELDTERMINATOR = '\t', KEEPIDENTITY ) " ;
sql_cmd += " select 'The number of records inserted is ' + convert(varchar,\@\@rowcount) end else " +
" select 'The table or view name = " + table_name + " does not exist in the database.' " ;
if (report_exectime_flag > 0)
{
d = new Date();
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( d.toString() + "\r\n" ) ;
WScript.echo( d );
}
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( "Bulk inserting file name ==> " + data_file_path + "\r\n" ) ;
WScript.echo ( "Bulk inserting file name ==> " + data_file_path );
ExecuteQueryAndEcho(conn, sql_cmd, data_file_path, 0);
if (report_exectime_flag > 0)
{
d = new Date();
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( d.toString() + "\r\n" ) ;
WScript.echo( d + "\r\n");
}
}
function pri_BulkInsertCommaFile(conn, table_name, data_file_path, tablockflag, report_exectime_flag)
{
var sql_cmd, d, new_table_name;
new_table_name = "[" + table_name + "]";
sql_cmd = " if exists (select * from sysobjects where name = '" + table_name +
"' and type in ('U','V') ) begin " ;
if (tablockflag)
sql_cmd += "BULK INSERT " + new_table_name +
" FROM '" + data_file_path + "' WITH " +
" (CODEPAGE = 'OEM', DATAFILETYPE = 'CHAR', FIELDTERMINATOR = ',', TABLOCK, KEEPIDENTITY) " ;
else
sql_cmd += "BULK INSERT " + new_table_name +
" FROM '" + data_file_path + "' WITH " +
" (CODEPAGE = 'OEM', DATAFILETYPE = 'CHAR', FIELDTERMINATOR = ',', KEEPIDENTITY) " ;
sql_cmd += " select 'The number of records inserted is ' + convert(varchar,\@\@rowcount) end else " +
" select 'The table or view name = " + table_name + " does not exist in the database.' " ;
if (report_exectime_flag > 0)
{
d = new Date();
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( d.toString() + "\r\n" ) ;
WScript.echo( d );
}
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( "Bulk inserting file name ==> " + data_file_path + "\r\n" ) ;
WScript.echo ( "Bulk inserting file name ==> " + data_file_path );
ExecuteQueryAndEcho(conn, sql_cmd, data_file_path, 0);
if (report_exectime_flag > 0)
{
d = new Date();
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( d.toString() + "\r\n" ) ;
WScript.echo( d + "\r\n");
}
}
function RecurBulkInsertDir(folderspec)
{
var fso = this._fso;
var bcp_file_list = new ActiveXObject("Scripting.Dictionary");
RecurCollect_BCPFile(fso, folderspec, bcp_file_list);
//Display_dictionary_key(bcp_file_list);
BulkInsert_dictionary_BCPFile(bcp_file_list, fso, this._conn, this._report_exectime_flag);
}
function RecurCollect_BCPFile(fso, folderspec, file_list)
{
var f, fc, len, pos;
f = fso.GetFolder(folderspec);
fc = new Enumerator(f.SubFolders);
for (; !fc.atEnd(); fc.moveNext())
{
RecurCollect_BCPFile( fso, fc.item(), file_list );
}
CollectFolder_BCPFile( fso, f, file_list );
}
function CollectFolder_BCPFile(fso, folderspec, file_list)
{
var f, fc, tablename, extname, filename, len, pos;
f = fso.GetFolder(folderspec);
fc = new Enumerator(f.files);
folderspec = new String(folderspec);
len = folderspec.length;
pos = folderspec.lastIndexOf("\\");
if (pos >= 0 && len > pos + 1)
{
folderspec += "\\";
}
for (; !fc.atEnd(); fc.moveNext())
{
extname = fso.GetExtensionName( fc.item() );
if ( extname.toLowerCase() == "dat" || extname.toLowerCase() == "csv" )
{
filename = fso.GetFileName( fc.item() );
file_list.add(folderspec + filename, folderspec + filename);
}
}
}
function BulkInsert_dictionary_BCPFile(file_list, fso, conn, report_exectime_flag)
{
var a, i, pos, tablename, extname, tablockflag = 1;
a = (new VBArray(file_list.Keys())).toArray(); // Get the keys.
a.sort();
for (i in a)
{
pos = a[i].indexOf(".");
tablename = a[i].substr(0,pos);
pos++;
extname = a[i].substr(pos,pos + 3).toLowerCase();
pos = tablename.lastIndexOf("\\");
pos++;
tablename = tablename.substr(pos, pos + 128);
//WScript.echo("table = " + tablename + " extname = " + extname);
if (extname == "dat")
pri_BulkInsertTabFile(conn , tablename, a[i], tablockflag, report_exectime_flag);
else
pri_BulkInsertCommaFile(conn, tablename, a[i], tablockflag, report_exectime_flag);
}
}
function BulkInsertDir(folderspec)
{
var fso = this._fso;
var bcp_file_list = new ActiveXObject("Scripting.Dictionary");
CollectFolder_BCPFile(fso, folderspec, bcp_file_list)
//Display_dictionary_key(bcp_file_list);
BulkInsert_dictionary_BCPFile(bcp_file_list, fso, this._conn, this._report_exectime_flag);
}
function RecurBulkInsertDir2(tablename, folderspec)
{
var fso = this._fso;
var bcp_file_list = new ActiveXObject("Scripting.Dictionary");
RecurCollect_BCPFile(fso, folderspec, bcp_file_list);
//Display_dictionary_key(bcp_file_list);
BulkInsert_dictionary_BCPFile2(tablename, bcp_file_list, fso, this._conn, this._report_exectime_flag);
}
function BulkInsertDir2(tablename, folderspec)
{
var fso = this._fso;
var bcp_file_list = new ActiveXObject("Scripting.Dictionary");
CollectFolder_BCPFile(fso, folderspec, bcp_file_list)
//Display_dictionary_key(bcp_file_list);
BulkInsert_dictionary_BCPFile2(tablename, bcp_file_list, fso, this._conn, this._report_exectime_flag);
}
function BulkInsert_dictionary_BCPFile2(tablename, file_list, fso, conn, report_exectime_flag)
{
var a, i, pos, extname, tablockflag = 1;
a = (new VBArray(file_list.Keys())).toArray(); // Get the keys.
a.sort();
for (i in a)
{
pos = a[i].indexOf(".") + 1;
extname = a[i].substr(pos,pos + 3).toLowerCase();
if (extname == "dat")
pri_BulkInsertTabFile (conn, tablename, a[i], tablockflag, report_exectime_flag);
else
pri_BulkInsertCommaFile(conn, tablename, a[i], tablockflag, report_exectime_flag);
}
}
// WScript.Quit will not exit the application.
function error_exit( error_no, message )
{
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( message + "\r\n\r\n" ) ;
WScript.Quit( error_no );
}
function ExecSQL_if_error_noquit(conn, sql_cmd)
{
try
{
var rs = conn.execute(sql_cmd);
while (rs != null)
{
var fields = rs.Fields;
while (!rs.EOF)
{
rs.MoveNext();
}
rs = rs.NextRecordSet();
}
}
catch (e)
{
display_error_message(conn);
}
}
function ExecSQL_if_error_quit(conn, sql_cmd)
{
try
{
var rs = conn.execute(sql_cmd);
while (rs != null)
{
var fields = rs.Fields;
while (!rs.EOF)
{
rs.MoveNext();
}
rs = rs.NextRecordSet();
}
}
catch (e)
{
if ( display_error_message(conn) != 0 )
WScript.quit(1);
}
}
function ExecSQL_Echo_result(conn, sql_cmd)
{
var line,header;
try
{
var rs = conn.execute(sql_cmd);
while (rs != null)
{
var fields = rs.Fields;
header = "";
while (!rs.EOF)
{
line = "\r\n";
if (header == "")
{
for (var i = 0; i < fields.count; i++)
{
header += fields(i).name + "\t";
}
}
for (var i = 0; i < fields.count; i++)
{
line += fields(i).Value + "\t";
}
if ( g_connection_log_fileStream != null )
g_connection_log_fileStream.Write( header + line + "\r\n\r\n" ) ;
WScript.echo( header + line + "\r\n" );
rs.MoveNext();
}
rs = rs.NextRecordSet();
}
}
catch (e)
{
display_error_message(conn);
}
}
function display_error_message(conn)
{
var error_obj = conn.Errors, error_no;
for (var i = 0; i < error_obj.count; i++)
{
var error_str = " Error Number : " + error_obj(i).Number +
"\r\n Error State : " + error_obj(i).SQLState +
"\r\n Error Text : " + error_obj(i).Description + "\r\n" ;
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( error_str ) ;
WScript.echo( error_str );
error_no = error_obj(i).Number;
}
if ( error_obj.count == 0 || error_no == 0 )
return 0;
return 1;
}
function RecurBulkInsertDir3( bcp_dir_path, field_delimiter_str, row_delimiter_str, suffix_str)
{
var fso = this._fso;
var bcp_file_list = new ActiveXObject("Scripting.Dictionary");
RecurCollect_BCPFile2(fso, bcp_dir_path, bcp_file_list, suffix_str);
//Display_dictionary_key(bcp_file_list);
BulkInsert_dictionary_BCPFile3(bcp_file_list, fso, this._conn, this._report_exectime_flag,
field_delimiter_str, row_delimiter_str);
}
function BulkInsertDir3( bcp_dir_path, field_delimiter_str, row_delimiter_str, suffix_str)
{
var fso = this._fso;
var bcp_file_list = new ActiveXObject("Scripting.Dictionary");
CollectFolder_BCPFile2(fso, bcp_dir_path, bcp_file_list, suffix_str);
//Display_dictionary_key(bcp_file_list);
BulkInsert_dictionary_BCPFile3(bcp_file_list, fso, this._conn, this._report_exectime_flag,
field_delimiter_str, row_delimiter_str);
}
function RecurCollect_BCPFile2(fso, folderspec, file_list, suffix_str)
{
var f, fc, len, pos;
f = fso.GetFolder(folderspec);
fc = new Enumerator(f.SubFolders);
for (; !fc.atEnd(); fc.moveNext())
{
RecurCollect_BCPFile2( fso, fc.item(), file_list, suffix_str );
}
CollectFolder_BCPFile2( fso, f, file_list, suffix_str );
}
// suffix_str could be dat or csv or any string.
function CollectFolder_BCPFile2(fso, folderspec, file_list, suffix_str)
{
var f, fc, extname, filename, len, pos, temp_str;
temp_str = suffix_str.toLowerCase();
f = fso.GetFolder(folderspec);
fc = new Enumerator(f.files);
folderspec = new String(folderspec);
len = folderspec.length;
pos = folderspec.lastIndexOf("\\");
if (pos >= 0 && len > pos + 1)
{
folderspec += "\\";
}
for (; !fc.atEnd(); fc.moveNext())
{
extname = fso.GetExtensionName( fc.item() );
if ( extname.toLowerCase() == temp_str )
{
filename = fso.GetFileName( fc.item() );
file_list.add(folderspec + filename, folderspec + filename);
}
}
}
function BulkInsert_dictionary_BCPFile3(file_list, fso, conn, report_exectime_flag,
field_delimiter_str, row_delimiter_str)
{
var a, i, pos, tablename, extname, tablockflag = 1;
a = (new VBArray(file_list.Keys())).toArray(); // Get the keys.
a.sort();
for (i in a)
{
pos = a[i].indexOf(".");
tablename = a[i].substr(0,pos);
pos++;
extname = a[i].substr(pos,pos + 128).toLowerCase();
pos = tablename.lastIndexOf("\\");
pos++;
tablename = tablename.substr(pos, pos + 128);
//WScript.echo("table = " + tablename + " extname = " + extname);
generic_BulkInsertFile(conn , tablename, a[i], tablockflag, report_exectime_flag,
field_delimiter_str, row_delimiter_str);
}
}
function generic_BulkInsertFile(conn, table_name, data_file_path, tablockflag, report_exectime_flag,
field_delimiter_str, row_delimiter_str)
{
var sql_cmd, d, new_table_name;
new_table_name = "[" + table_name + "]";
sql_cmd = " if exists (select * from sysobjects where name = '" + table_name +
"' and type in ('U','V') ) begin " ;
if (tablockflag)
sql_cmd += "BULK INSERT " + new_table_name +
" FROM '" + data_file_path + "' WITH " +
" (CODEPAGE = 'OEM', DATAFILETYPE = 'CHAR', FIELDTERMINATOR = '" +
field_delimiter_str + "', ROWTERMINATOR = '" + row_delimiter_str + "', TABLOCK, KEEPIDENTITY) ";
else
sql_cmd += "BULK INSERT " + new_table_name +
" FROM '" + data_file_path + "' WITH " +
" (CODEPAGE = 'OEM', DATAFILETYPE = 'CHAR', FIELDTERMINATOR = '" +
field_delimiter_str + "', ROWTERMINATOR = '" + row_delimiter_str + "', KEEPIDENTITY) " ;
sql_cmd += " select 'The number of records inserted is ' + convert(varchar,\@\@rowcount) end else " +
" select 'The table or view name = " + table_name + " does not exist in the database.' " ;
if (report_exectime_flag > 0)
{
d = new Date();
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( d.toString() + "\r\n" ) ;
WScript.echo( d );
}
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( "Bulk inserting file name ==> " + data_file_path + "\r\n" ) ;
WScript.echo ( "Bulk inserting file name ==> " + data_file_path );
ExecuteQueryAndEcho(conn, sql_cmd, data_file_path, 0);
if (report_exectime_flag > 0)
{
d = new Date();
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( d.toString() + "\r\n" ) ;
WScript.echo( d + "\r\n");
}
}
function RecurBulkInsertDir4( tablename, bcp_dir_path, field_delimiter_str, row_delimiter_str, suffix_str)
{
var fso = this._fso;
var bcp_file_list = new ActiveXObject("Scripting.Dictionary");
RecurCollect_BCPFile2(fso, folderspec, bcp_file_list, suffix_str);
//Display_dictionary_key(bcp_file_list);
BulkInsert_dictionary_BCPFile4(tablename, bcp_file_list, fso, this._conn, this._report_exectime_flag,
field_delimiter_str, row_delimiter_str);
}
function BulkInsertDir4( tablename, bcp_dir_path, field_delimiter_str, row_delimiter_str, suffix_str)
{
var fso = this._fso;
var bcp_file_list = new ActiveXObject("Scripting.Dictionary");
CollectFolder_BCPFile2(fso, folderspec, bcp_file_list, suffix_str)
//Display_dictionary_key(bcp_file_list);
BulkInsert_dictionary_BCPFile4(tablename, bcp_file_list, fso, this._conn, this._report_exectime_flag,
field_delimiter_str, row_delimiter_str);
}
function BulkInsert_dictionary_BCPFile4(tablename, file_list, fso, conn, report_exectime_flag,
field_delimiter_str, row_delimiter_str)
{
var a, i, tablockflag = 1;
a = (new VBArray(file_list.Keys())).toArray(); // Get the keys.
a.sort();
for (i in a)
{
generic_BulkInsertFile( conn, tablename, a[i], tablockflag, report_exectime_flag,
field_delimiter_str, row_delimiter_str );
}
}
function RecurBulkInsertDirGen( bcp_dir_path, field_delimiter_str, row_delimiter_str, suffix_str)
{
RecurBulkInsertDir3( bcp_dir_path, "\t" , "\\n", "dat" );
RecurBulkInsertDir3( bcp_dir_path, "," , "\\n", "csv" );
RecurBulkInsertDir3( bcp_dir_path, ";" , "\\n", "col" );
RecurBulkInsertDir3( bcp_dir_path, "$()$", "$##$", "data" );
}
function BulkInsertDirGen( bcp_dir_path, field_delimiter_str, row_delimiter_str, suffix_str)
{
BulkInsertDir3( bcp_dir_path, "\t" , "\\n", "dat" );
BulkInsertDir3( bcp_dir_path, "," , "\\n", "csv" );
BulkInsertDir3( bcp_dir_path, ";" , "\\n", "col" );
BulkInsertDir3( bcp_dir_path, "$()$", "$##$", "data" );
}
function displayError( conn, sql_cmd, sql_file, got_error, begin_line_no)
{
var error_obj = conn.Errors;
var error_no;
var show_error_text = 0;
var temp_str;
for (var i = 0; i < error_obj.count; i++)
{
temp_str = error_obj(i).Description;
if ( temp_str.indexOf("Duplicate key was ignored", 0) >= 0 )
{
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( "\r\n===>Warning : " + temp_str + "\r\n\r\n");
WScript.echo( "\r\n===>Warning : " + temp_str + "\r\n");
}
else
if (error_obj(i).Number != 0)
{
if (show_error_text == 0)
{
if ( g_connection_log_fileStream != null )
g_connection_log_fileStream.Write("\r\n Error messages from SQL file name ==> " + sql_file + "\r\n");
WScript.echo("\r\n Error messages from SQL file name ==> " + sql_file + "\r\n");
if ( g_connection_log_fileStream != null )
g_connection_log_fileStream.Write( "\r\nText Begin Line number = " + begin_line_no +
" File = " + sql_file + "\r\n" + sql_cmd );
WScript.echo( "\r\nText Begin Line number = " + begin_line_no + " File = " + sql_file + "\r\n" + sql_cmd);
show_error_text = 1;
}
var error_str = "===>Error Text: " + error_obj(i).Description + "\r\n";
if (g_connection_log_fileStream != null)
g_connection_log_fileStream.Write( error_str );
WScript.echo( error_str );
}
else
{
if ( g_connection_log_fileStream != null )
g_connection_log_fileStream.Write( " Info Text: " + error_obj(i).Description + "\r\n" );
WScript.echo( " Info Text: " + error_obj(i).Description );
}
error_no = error_obj(i).Number;
}
// When we send one or more blank lines to SQL server, even though we got no error messages,
// we will catch it and we have to do the following in order to continue using the conection.
if (error_no != 0)
{
var conn_str = conn.ConnectionString;
conn.close();
conn.open(conn_str);
conn.execute("SET QUOTED_IDENTIFIER OFF");
conn.CommandTimeout = 0;
return 1;
}
else
return 0;
}
function get_max_column_length( col_array )
{
var max_len = col_array[1];
for (var i = 2; i <= col_array[0]; i++)
{
if ( col_array[i] > max_len )
max_len = col_array[i];
}
return max_len;
}
function FormatResult( col_array, title_str, result_str, rowNum )
{
var max_column_len = get_max_column_length( col_array );
var len_count = max_column_len / 30;
if ( (max_column_len % 30) > 0 )
len_count++;
var space_str = "";
for (var i = 0; i < len_count; i++)
space_str += " ";
space_str += " ";
var new_line = "\n";
var max_len = 0 ;
var begin_pos ;
var end_pos ;
var len ;
var col_str ;
var dash_len ;
var title_str_buf = "";
begin_pos = 0;
max_len = 0;
for (var i = 0; i < col_array[0]; i++)
{
end_pos = title_str.indexOf("\t", begin_pos);
col_str = title_str.substring(begin_pos, end_pos);
begin_pos = end_pos + 1;
max_len += col_array[i + 1];
len = col_array[i + 1] - col_str.length;
if (len > 0)
{
title_str_buf += col_str + space_str.substring(0, len + 2);
}
else
{
title_str_buf += col_str + " ";
}
}
max_len += 2 * ( col_array[0] - 1 );
title_str_buf += new_line;
var dash_str = "------------------------------";
while ( true )
{
if ( max_len > 30 )
{
title_str_buf += dash_str;
max_len -= 30;
}
else
{
title_str_buf += dash_str.substring(0, max_len);
break;
}
}
if ( g_connection_log_fileStream != null )
g_connection_log_fileStream.Write( title_str_buf );
WScript.echo( title_str_buf );
var result_str_buf = "";
begin_pos = 0;
for (var j = 0; j < rowNum; j++)
{
for (var i = 0; i < col_array[0]; i++)
{
end_pos = result_str.indexOf("\t", begin_pos);
col_str = result_str.substring(begin_pos, end_pos);
begin_pos = end_pos + 1;
len = col_array[i + 1] - col_str.length;
if (len > 0)
{
result_str_buf += col_str + space_str.substring(0, len + 2);
}
else
{
result_str_buf += col_str + " ";
}
}
result_str_buf += new_line;
}
if ( g_connection_log_fileStream != null )
g_connection_log_fileStream.Write( result_str_buf );
WScript.echo( result_str_buf );
if ( g_connection_log_fileStream != null )
g_connection_log_fileStream.Write( new_line + "Total number of rows = " + rowNum + new_line );
WScript.echo( new_line + "Total number of rows = " + rowNum + new_line );
}
function check_sql_command( sql_cmd )
{
var ret_sql_cmd = sql_cmd.replace(/\n/g, " ");
ret_sql_cmd = ret_sql_cmd.replace(/\r/g, " ");
ret_sql_cmd = trim_trailing_space( ret_sql_cmd );
if ( ret_sql_cmd.length <= 6 )
return null;
return sql_cmd;
}
function ExecuteAndFormat(conn, sql_cmd)
{
try
{
if ( g_connection_echo_SQL_command != 0 )
{
if ( g_connection_log_fileStream != null )
g_connection_log_fileStream.Write( "\r\n<===\r\n" + sql_cmd + "\r\n===>\r\n") ;
WScript.echo( "\r\n<===\r\n" + sql_cmd + "\r\n===>\r\n");
}
sql_cmd = check_sql_command( sql_cmd );
if ( sql_cmd == null )
return;
var temp_str;
var len;
var rowNumber;
var adStateClosed = 0;
var rs = conn.execute(sql_cmd);
if (rs.State == adStateClosed )
return;
while (rs != null)
{
var fields = rs.Fields;
var col_array = new Array();
var header = "" ;
var result_str = "" ;
while (!rs.EOF)
{
if (header == "")
{
rowNumber = 0;
col_array[0] = fields.count;
for (var i = 0; i < fields.count; i++)
{
header += fields(i).name + "\t";
temp_str = fields(i).name;
col_array[i+1] = temp_str.length;
}
}
rowNumber++;
for (var i = 0; i < fields.count; i++)
{
if (fields(i).type == adLongVarChar)
{
if ( fields(i).ActualSize > 0 )
{
temp_str = new String( fields(i).GetChunk( fields(i).ActualSize ) ) ;
temp_str = temp_str.replace(/\t/g, " ");
temp_str = temp_str.replace(/\n/g, " ");
temp_str = temp_str.replace(/\r/g, " ");
len = fields(i).ActualSize;
if ( len > col_array[i + 1] )
col_array[i + 1] = len;
result_str += temp_str + "\t";
}
else
{
if ( 4 > col_array[i + 1] )
col_array[i + 1] = 4;
result_str += "NULL\t";
}
}
else if (fields(i).type == adLongVarBinary)
{
if ( fields(i).ActualSize > 0 )
{
temp_str = fields(i).Value;
len = fields(i).ActualSize;
if ( len > col_array[i + 1] )
col_array[i + 1] = len;
result_str += temp_str + "\t";
}
else
{
if ( 4 > col_array[i + 1] )
col_array[i + 1] = 4;
result_str += "NULL\t";
}
}
else if (fields(i).type == adBinary)
{
if ( fields(i).ActualSize > 0 )
{
temp_str = fields(i).Value ;
len = fields(i).ActualSize;
if ( len > col_array[i + 1] )
col_array[i + 1] = len;
result_str += temp_str + "\t";
}
else
{
if ( 4 > col_array[i + 1] )
col_array[i + 1] = 4;
result_str += "NULL\t";
}
}
else if (fields(i).Value == null)
{
if ( 4 > col_array[i + 1] )
col_array[i + 1] = 4;
result_str += "NULL\t";
}
else
{
temp_str = new String(fields(i).Value) ;
temp_str = temp_str.replace(/\t/g, " ");
temp_str = temp_str.replace(/\n/g, " ");
temp_str = temp_str.replace(/\r/g, " ");
len = temp_str.length;
if ( len > col_array[i + 1] )
col_array[i + 1] = len;
result_str += temp_str + "\t";
}
}
rs.MoveNext();
}
FormatResult( col_array, header, result_str, rowNumber );
rs = rs.NextRecordSet();
}
return 0;
}
catch (e)
{
WScript.echo(e.description);
}
}
function ExecuteAndFormat2(conn, sql_cmd, sql_file, got_error, begin_line_no)
{
try
{
if ( g_connection_echo_SQL_command != 0 )
{
if ( g_connection_log_fileStream != null )
g_connection_log_fileStream.Write( "\r\n<===\r\n" + sql_cmd + "\r\n===>\r\n") ;
WScript.echo( "\r\n<===\r\n" + sql_cmd + "\r\n===>\r\n");
}
sql_cmd = check_sql_command( sql_cmd );
if ( sql_cmd == null )
return;
var temp_str;
var len;
var rowNumber;
var adStateClosed = 0;
var rs = conn.execute(sql_cmd);
if (rs.State == adStateClosed )
return;
while (rs != null)
{
var fields = rs.Fields;
var col_array = new Array();
var header = "" ;
var result_str = "" ;
while (!rs.EOF)
{
if (header == "")
{
rowNumber = 0;
col_array[0] = fields.count;
for (var i = 0; i < fields.count; i++)
{
header += fields(i).name + "\t";
temp_str = fields(i).name;
col_array[i+1] = temp_str.length;
}
}
rowNumber++;
for (var i = 0; i < fields.count; i++)
{
if (fields(i).type == adLongVarChar)
{
if ( fields(i).ActualSize > 0 )
{
temp_str = new String( fields(i).GetChunk( fields(i).ActualSize ) ) ;
temp_str = temp_str.replace(/\t/g, " ");
temp_str = temp_str.replace(/\n/g, " ");
temp_str = temp_str.replace(/\r/g, " ");
len = fields(i).ActualSize;
if ( len > col_array[i + 1] )
col_array[i + 1] = len;
result_str += temp_str + "\t";
}
else
{
if ( 4 > col_array[i + 1] )
col_array[i + 1] = 4;
result_str += "NULL\t";
}
}
else if (fields(i).type == adLongVarBinary)
{
if ( fields(i).ActualSize > 0 )
{
temp_str = fields(i).Value;
len = fields(i).ActualSize;
if ( len > col_array[i + 1] )
col_array[i + 1] = len;
result_str += temp_str + "\t";
}
else
{
if ( 4 > col_array[i + 1] )
col_array[i + 1] = 4;
result_str += "NULL\t";
}
}
else if (fields(i).type == adBinary)
{
if ( fields(i).ActualSize > 0 )
{
temp_str = fields(i).Value ;
len = fields(i).ActualSize;
if ( len > col_array[i + 1] )
col_array[i + 1] = len;
result_str += temp_str + "\t";
}
else
{
if ( 4 > col_array[i + 1] )
col_array[i + 1] = 4;
result_str += "NULL\t";
}
}
else if (fields(i).Value == null)
{
if ( 4 > col_array[i + 1] )
col_array[i + 1] = 4;
result_str += "NULL\t";
}
else
{
temp_str = new String(fields(i).Value) ;
temp_str = temp_str.replace(/\t/g, " ");
temp_str = temp_str.replace(/\n/g, " ");
temp_str = temp_str.replace(/\r/g, " ");
len = temp_str.length;
if ( len > col_array[i + 1] )
col_array[i + 1] = len;
result_str += temp_str + "\t";
}
}
rs.MoveNext();
}
FormatResult( col_array, header, result_str, rowNumber );
rs = rs.NextRecordSet();
}
return 0;
}
catch (e)
{
displayError( conn, sql_cmd, sql_file, got_error, begin_line_no );
}
}
function ExecuteAndFormatVertical(conn, sql_cmd)
{
var space_str = " " +
" " ;
try
{
if ( g_connection_echo_SQL_command != 0 )
{
if ( g_connection_log_fileStream != null )
g_connection_log_fileStream.Write( "\r\n<===\r\n" + sql_cmd + "\r\n===>\r\n") ;
WScript.echo( "\r\n<===\r\n" + sql_cmd + "\r\n===>\r\n");
}
sql_cmd = check_sql_command( sql_cmd );
if ( sql_cmd == null )
return;
var temp_str, line_str;
var len, max_len;
var rowNumber;
var rs = conn.execute(sql_cmd);
while (rs != null)
{
var fields = rs.Fields;
var header = "" ;
var result_str = "" ;
var col_array = new Array();
while (!rs.EOF)
{
if (header == "")
{
rowNumber = 0;
max_len = 0;
for (var i = 0; i < fields.count; i++)
{
temp_str = fields(i).name;
col_array[i] = temp_str;
len = temp_str.length;
if (len > max_len)
max_len = len;
}
for (var i = 0; i < fields.count; i++)
{
col_array[i] += space_str.substring(0, max_len - col_array[i].length);
}
header = "\t";
}
rowNumber++;
if ( g_connection_log_fileStream != null )
g_connection_log_fileStream.Write( "\r\n<=== Row Number " + rowNumber + " ===>\r\n\r\n" );
WScript.echo("\n<=== Row Number " + rowNumber + " ===>\n");
for (var i = 0; i < fields.count; i++)
{
if (fields(i).type == adLongVarChar)
{
if ( fields(i).ActualSize > 0 )
{
temp_str = new String( fields(i).GetChunk( fields(i).ActualSize ) ) ;
temp_str = temp_str.replace(/\n/g, " ");
temp_str = temp_str.replace(/\r/g, " ");
line_str = col_array[i] + " = " + temp_str ;
}
else
{
line_str = col_array[i] + " = NULL" ;
}
}
else if (fields(i).type == adLongVarBinary)
{
if ( fields(i).ActualSize > 0 )
{
line_str = col_array[i] + " = " + fields(i).Value ;
}
else
{
line_str = col_array[i] + " = NULL" ;
}
}
else if (fields(i).type == adBinary)
{
if ( fields(i).ActualSize > 0 )
{
line_str = col_array[i] + " = " + fields(i).Value ;
}
else
{
line_str = col_array[i] + " = NULL" ;
}
}
if (fields(i).Value == null)
{
line_str = col_array[i] + " = NULL" ;
}
else
{
line_str = col_array[i] + " = " + fields(i).Value ;
}
if ( g_connection_log_fileStream != null )
g_connection_log_fileStream.Write( line_str + "\r\n" );
WScript.echo( line_str );
}
rs.MoveNext();
}
rs = rs.NextRecordSet();
}
return 0;
}
catch (e)
{
WScript.echo(e.description);
}
}
Submit an article or tip