'''
the purpose of this script is to determine if there is a memory leak associated with a 
long-running, persistent, ADO connection to a SQL Server datastore when that connection
has an associated prepared statement (WITHOUT parameters) that is executed at regular intervals

*** this script DOES NOT appear to exhibit a memory leak

-- python:  2.4.1 (#65, Mar 30 2005, 09:13:57) [MSC v.1310 32 bit (Intel)]
-- pywin32: build 209
-- Microsoft Windows XP Professional Version 2002 Service Pack 2
-- Microsoft SQL Server 2000 (8.0)
-- jim.vickroy@noaa.gov
'''

import datetime, os, win32com.client
from   time import sleep


# SQL Server ...
host_name    = 'sec-swds-sqldw.sec.noaa.gov'
catalog_name = 'jvickroy'
protocol     = 'Provider=SQLOLEDB.1;Data Source=%s;Database=%s;Trusted_Connection=yes' % (host_name,catalog_name)
table_name   = 'ado_mem_check_1'
column_name  = 'time_tag'

# SQL Server Express ...
#~ host_name     = '%s%sSQLEXPRESS' % (os.environ['COMPUTERNAME'], os.sep)
#~ catalog_name  = 'Boulder_NOAA_mag'
#~ protocol      = 'Provider=SQLNCLI;Server=%s;Database=%s;Trusted_Connection=yes' % (host_name,catalog_name)


connection = win32com.client.Dispatch('ADODB.Connection')
connection.CommandTimeout = 5 # seconds
connection.Open(protocol)


catalog = win32com.client.Dispatch('ADOX.Catalog')
catalog.ActiveConnection = connection


command                  = win32com.client.Dispatch('ADODB.Command')
command.ActiveConnection = connection
command.CommandType      = win32com.client.constants.adCmdText
command.CommandText      = 'INSERT INTO %s (%s) VALUES (DEFAULT)' % (table_name,column_name)
command.NamedParameters  = True # do not assume parameter order matches table_name columns order
command.Prepared         = True # direct the datastore backend to *prepare* (i.e., pre-compile) this command


# create the command parameters ...
#     for unknown reasons, the order the parameters are added to the command.Parameters collection must match the order
#     they appear in the command.CommandText (SQL) statement even though the command.NamedParameters attribute has been
#     set to True!  I have been unsuccessful in finding an @param substitute for the "?" placeholders that is acceptable_name
#     to the SQL Server backend.
column               = win32com.client.Dispatch('ADOX.Column')
column.Name          = column_name
column.Type          = win32com.client.constants.adDBTimeStamp
column.ParentCatalog = catalog
#~ parameter            = command.CreateParameter('@%s' % column.Name, column.Type, win32com.client.constants.adParamInput)
#~ command.Parameters.Append(parameter)


while True:
   assert connection.State == win32com.client.constants.adStateOpen, 'connection not open (host_name: %s, database: %s)' % (host_name,catalog_name)
   command.Execute()
   print '[%s] DEFAULT INSERT completed' % datetime.datetime.now()
   sleep(1)