source: OpenRLabs-Git/deploy/rlabs-docker/web2py-rlabs/gluon/contrib/pypyodbc.py

main
Last change on this file was 42bd667, checked in by David Fuertes <dfuertes@…>, 4 years ago

Historial Limpio
  • Property mode set to 100755
File size: 104.4 KB
Line 
1# -*- coding: utf-8 -*-
2
3# PyPyODBC is develped from RealPyODBC 0.1 beta released in 2004 by Michele Petrazzo. Thanks Michele.
4
5# The MIT License (MIT)
6#
7# Copyright (c) 2014 Henry Zhou <jiangwen365@gmail.com> and PyPyODBC contributors
8# Copyright (c) 2004 Michele Petrazzo
9
10# Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated
11# documentation files (the "Software"), to deal in the Software without restriction, including without limitation
12# the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software,
13# and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
14#
15# The above copyright notice and this permission notice shall be included in all copies or substantial portions
16# of the Software.
17#
18# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO
19# THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
20# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF
21# CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
22# DEALINGS IN THE SOFTWARE.
23
24pooling = True
25apilevel = '2.0'
26paramstyle = 'qmark'
27threadsafety = 1
28version = '1.3.3'
29lowercase=True
30
31DEBUG = 0
32# Comment out all "if DEBUG:" statements like below for production
33#if DEBUG:print 'DEBUGGING'
34
35import sys, os, datetime, ctypes, threading
36from decimal import Decimal
37
38
39py_ver = sys.version[:3]
40py_v3 = py_ver >= '3.0'
41
42if py_v3:
43    long = int
44    unicode = str
45    str_8b = bytes
46    buffer = memoryview
47    BYTE_1 = bytes('1','ascii')
48    use_unicode = True
49else:
50    str_8b = str
51    BYTE_1 = '1'
52    use_unicode = False
53if py_ver < '2.6':
54    bytearray = str
55   
56
57if not hasattr(ctypes, 'c_ssize_t'):
58    if ctypes.sizeof(ctypes.c_uint) == ctypes.sizeof(ctypes.c_void_p):
59        ctypes.c_ssize_t = ctypes.c_int
60    elif ctypes.sizeof(ctypes.c_ulong) == ctypes.sizeof(ctypes.c_void_p):
61        ctypes.c_ssize_t = ctypes.c_long
62    elif ctypes.sizeof(ctypes.c_ulonglong) == ctypes.sizeof(ctypes.c_void_p):
63        ctypes.c_ssize_t = ctypes.c_longlong
64
65
66lock = threading.Lock()
67shared_env_h = None
68SQLWCHAR_SIZE = ctypes.sizeof(ctypes.c_wchar)
69
70#determin the size of Py_UNICODE
71#sys.maxunicode > 65536 and 'UCS4' or 'UCS2'
72UNICODE_SIZE = sys.maxunicode > 65536 and 4 or 2
73
74
75# Define ODBC constants. They are widly used in ODBC documents and programs
76# They are defined in cpp header files: sql.h sqlext.h sqltypes.h sqlucode.h
77# and you can get these files from the mingw32-runtime_3.13-1_all.deb package
78SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC2, SQL_OV_ODBC3 = 200, 2, 3
79SQL_DRIVER_NOPROMPT = 0
80SQL_ATTR_CONNECTION_POOLING = 201; SQL_CP_ONE_PER_HENV = 2
81
82SQL_FETCH_NEXT, SQL_FETCH_FIRST, SQL_FETCH_LAST = 0x01, 0x02, 0x04
83SQL_NULL_HANDLE, SQL_HANDLE_ENV, SQL_HANDLE_DBC, SQL_HANDLE_STMT = 0, 1, 2, 3
84SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR = 0, 1, -1
85SQL_NO_DATA = 100; SQL_NO_TOTAL = -4
86SQL_ATTR_ACCESS_MODE = SQL_ACCESS_MODE = 101
87SQL_ATTR_AUTOCOMMIT = SQL_AUTOCOMMIT = 102
88
89SQL_MODE_DEFAULT = SQL_MODE_READ_WRITE = 0; SQL_MODE_READ_ONLY = 1
90SQL_AUTOCOMMIT_OFF, SQL_AUTOCOMMIT_ON = 0, 1
91SQL_IS_UINTEGER = -5
92SQL_ATTR_LOGIN_TIMEOUT = 103; SQL_ATTR_CONNECTION_TIMEOUT = 113;SQL_ATTR_QUERY_TIMEOUT = 0
93SQL_COMMIT, SQL_ROLLBACK = 0, 1
94
95SQL_INDEX_UNIQUE,SQL_INDEX_ALL = 0,1
96SQL_QUICK,SQL_ENSURE = 0,1
97SQL_FETCH_NEXT = 1
98SQL_COLUMN_DISPLAY_SIZE = 6
99SQL_INVALID_HANDLE = -2
100SQL_NO_DATA_FOUND = 100; SQL_NULL_DATA = -1; SQL_NTS = -3
101SQL_HANDLE_DESCR = 4
102SQL_TABLE_NAMES = 3
103SQL_PARAM_INPUT = 1; SQL_PARAM_INPUT_OUTPUT = 2
104SQL_PARAM_TYPE_UNKNOWN = 0
105SQL_RESULT_COL = 3
106SQL_PARAM_OUTPUT = 4
107SQL_RETURN_VALUE = 5
108SQL_PARAM_TYPE_DEFAULT = SQL_PARAM_INPUT_OUTPUT
109
110SQL_RESET_PARAMS = 3
111SQL_UNBIND = 2
112SQL_CLOSE = 0
113
114
115
116
117# Below defines The constants for sqlgetinfo method, and their coresponding return types
118SQL_QUALIFIER_LOCATION = 114
119SQL_QUALIFIER_NAME_SEPARATOR = 41
120SQL_QUALIFIER_TERM = 42
121SQL_QUALIFIER_USAGE = 92
122SQL_OWNER_TERM = 39
123SQL_OWNER_USAGE = 91
124SQL_ACCESSIBLE_PROCEDURES = 20
125SQL_ACCESSIBLE_TABLES = 19
126SQL_ACTIVE_ENVIRONMENTS = 116
127SQL_AGGREGATE_FUNCTIONS = 169
128SQL_ALTER_DOMAIN = 117
129SQL_ALTER_TABLE = 86
130SQL_ASYNC_MODE = 10021
131SQL_BATCH_ROW_COUNT = 120
132SQL_BATCH_SUPPORT = 121
133SQL_BOOKMARK_PERSISTENCE = 82
134SQL_CATALOG_LOCATION = SQL_QUALIFIER_LOCATION
135SQL_CATALOG_NAME = 10003
136SQL_CATALOG_NAME_SEPARATOR = SQL_QUALIFIER_NAME_SEPARATOR
137SQL_CATALOG_TERM = SQL_QUALIFIER_TERM
138SQL_CATALOG_USAGE = SQL_QUALIFIER_USAGE
139SQL_COLLATION_SEQ = 10004
140SQL_COLUMN_ALIAS = 87
141SQL_CONCAT_NULL_BEHAVIOR = 22
142SQL_CONVERT_FUNCTIONS = 48
143SQL_CONVERT_VARCHAR = 70
144SQL_CORRELATION_NAME = 74
145SQL_CREATE_ASSERTION = 127
146SQL_CREATE_CHARACTER_SET = 128
147SQL_CREATE_COLLATION = 129
148SQL_CREATE_DOMAIN = 130
149SQL_CREATE_SCHEMA = 131
150SQL_CREATE_TABLE = 132
151SQL_CREATE_TRANSLATION = 133
152SQL_CREATE_VIEW = 134
153SQL_CURSOR_COMMIT_BEHAVIOR = 23
154SQL_CURSOR_ROLLBACK_BEHAVIOR = 24
155SQL_DATABASE_NAME = 16
156SQL_DATA_SOURCE_NAME = 2
157SQL_DATA_SOURCE_READ_ONLY = 25
158SQL_DATETIME_LITERALS = 119
159SQL_DBMS_NAME = 17
160SQL_DBMS_VER = 18
161SQL_DDL_INDEX = 170
162SQL_DEFAULT_TXN_ISOLATION = 26
163SQL_DESCRIBE_PARAMETER = 10002
164SQL_DM_VER = 171
165SQL_DRIVER_NAME = 6
166SQL_DRIVER_ODBC_VER = 77
167SQL_DRIVER_VER = 7
168SQL_DROP_ASSERTION = 136
169SQL_DROP_CHARACTER_SET = 137
170SQL_DROP_COLLATION = 138
171SQL_DROP_DOMAIN = 139
172SQL_DROP_SCHEMA = 140
173SQL_DROP_TABLE = 141
174SQL_DROP_TRANSLATION = 142
175SQL_DROP_VIEW = 143
176SQL_DYNAMIC_CURSOR_ATTRIBUTES1 = 144
177SQL_DYNAMIC_CURSOR_ATTRIBUTES2 = 145
178SQL_EXPRESSIONS_IN_ORDERBY = 27
179SQL_FILE_USAGE = 84
180SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1 = 146
181SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES2 = 147
182SQL_GETDATA_EXTENSIONS = 81
183SQL_GROUP_BY = 88
184SQL_IDENTIFIER_CASE = 28
185SQL_IDENTIFIER_QUOTE_CHAR = 29
186SQL_INDEX_KEYWORDS = 148
187SQL_INFO_SCHEMA_VIEWS = 149
188SQL_INSERT_STATEMENT = 172
189SQL_INTEGRITY = 73
190SQL_KEYSET_CURSOR_ATTRIBUTES1 = 150
191SQL_KEYSET_CURSOR_ATTRIBUTES2 = 151
192SQL_KEYWORDS = 89
193SQL_LIKE_ESCAPE_CLAUSE = 113
194SQL_MAX_ASYNC_CONCURRENT_STATEMENTS = 10022
195SQL_MAX_BINARY_LITERAL_LEN = 112
196SQL_MAX_CATALOG_NAME_LEN = 34
197SQL_MAX_CHAR_LITERAL_LEN = 108
198SQL_MAX_COLUMNS_IN_GROUP_BY = 97
199SQL_MAX_COLUMNS_IN_INDEX = 98
200SQL_MAX_COLUMNS_IN_ORDER_BY = 99
201SQL_MAX_COLUMNS_IN_SELECT = 100
202SQL_MAX_COLUMNS_IN_TABLE = 101
203SQL_MAX_COLUMN_NAME_LEN = 30
204SQL_MAX_CONCURRENT_ACTIVITIES = 1
205SQL_MAX_CURSOR_NAME_LEN = 31
206SQL_MAX_DRIVER_CONNECTIONS = 0
207SQL_MAX_IDENTIFIER_LEN = 10005
208SQL_MAX_INDEX_SIZE = 102
209SQL_MAX_PROCEDURE_NAME_LEN = 33
210SQL_MAX_ROW_SIZE = 104
211SQL_MAX_ROW_SIZE_INCLUDES_LONG = 103
212SQL_MAX_SCHEMA_NAME_LEN = 32
213SQL_MAX_STATEMENT_LEN = 105
214SQL_MAX_TABLES_IN_SELECT = 106
215SQL_MAX_TABLE_NAME_LEN = 35
216SQL_MAX_USER_NAME_LEN = 107
217SQL_MULTIPLE_ACTIVE_TXN = 37
218SQL_MULT_RESULT_SETS = 36
219SQL_NEED_LONG_DATA_LEN = 111
220SQL_NON_NULLABLE_COLUMNS = 75
221SQL_NULL_COLLATION = 85
222SQL_NUMERIC_FUNCTIONS = 49
223SQL_ODBC_INTERFACE_CONFORMANCE = 152
224SQL_ODBC_VER = 10
225SQL_OJ_CAPABILITIES = 65003
226SQL_ORDER_BY_COLUMNS_IN_SELECT = 90
227SQL_PARAM_ARRAY_ROW_COUNTS = 153
228SQL_PARAM_ARRAY_SELECTS = 154
229SQL_PROCEDURES = 21
230SQL_PROCEDURE_TERM = 40
231SQL_QUOTED_IDENTIFIER_CASE = 93
232SQL_ROW_UPDATES = 11
233SQL_SCHEMA_TERM = SQL_OWNER_TERM
234SQL_SCHEMA_USAGE = SQL_OWNER_USAGE
235SQL_SCROLL_OPTIONS = 44
236SQL_SEARCH_PATTERN_ESCAPE = 14
237SQL_SERVER_NAME = 13
238SQL_SPECIAL_CHARACTERS = 94
239SQL_SQL92_DATETIME_FUNCTIONS = 155
240SQL_SQL92_FOREIGN_KEY_DELETE_RULE = 156
241SQL_SQL92_FOREIGN_KEY_UPDATE_RULE = 157
242SQL_SQL92_GRANT = 158
243SQL_SQL92_NUMERIC_VALUE_FUNCTIONS = 159
244SQL_SQL92_PREDICATES = 160
245SQL_SQL92_RELATIONAL_JOIN_OPERATORS = 161
246SQL_SQL92_REVOKE = 162
247SQL_SQL92_ROW_VALUE_CONSTRUCTOR = 163
248SQL_SQL92_STRING_FUNCTIONS = 164
249SQL_SQL92_VALUE_EXPRESSIONS = 165
250SQL_SQL_CONFORMANCE = 118
251SQL_STANDARD_CLI_CONFORMANCE = 166
252SQL_STATIC_CURSOR_ATTRIBUTES1 = 167
253SQL_STATIC_CURSOR_ATTRIBUTES2 = 168
254SQL_STRING_FUNCTIONS = 50
255SQL_SUBQUERIES = 95
256SQL_SYSTEM_FUNCTIONS = 51
257SQL_TABLE_TERM = 45
258SQL_TIMEDATE_ADD_INTERVALS = 109
259SQL_TIMEDATE_DIFF_INTERVALS = 110
260SQL_TIMEDATE_FUNCTIONS = 52
261SQL_TXN_CAPABLE = 46
262SQL_TXN_ISOLATION_OPTION = 72
263SQL_UNION = 96
264SQL_USER_NAME = 47
265SQL_XOPEN_CLI_YEAR = 10000
266
267
268aInfoTypes = {
269SQL_ACCESSIBLE_PROCEDURES : 'GI_YESNO',SQL_ACCESSIBLE_TABLES : 'GI_YESNO',SQL_ACTIVE_ENVIRONMENTS : 'GI_USMALLINT',
270SQL_AGGREGATE_FUNCTIONS : 'GI_UINTEGER',SQL_ALTER_DOMAIN : 'GI_UINTEGER',
271SQL_ALTER_TABLE : 'GI_UINTEGER',SQL_ASYNC_MODE : 'GI_UINTEGER',SQL_BATCH_ROW_COUNT : 'GI_UINTEGER',
272SQL_BATCH_SUPPORT : 'GI_UINTEGER',SQL_BOOKMARK_PERSISTENCE : 'GI_UINTEGER',SQL_CATALOG_LOCATION : 'GI_USMALLINT',
273SQL_CATALOG_NAME : 'GI_YESNO',SQL_CATALOG_NAME_SEPARATOR : 'GI_STRING',SQL_CATALOG_TERM : 'GI_STRING',
274SQL_CATALOG_USAGE : 'GI_UINTEGER',SQL_COLLATION_SEQ : 'GI_STRING',SQL_COLUMN_ALIAS : 'GI_YESNO',
275SQL_CONCAT_NULL_BEHAVIOR : 'GI_USMALLINT',SQL_CONVERT_FUNCTIONS : 'GI_UINTEGER',SQL_CONVERT_VARCHAR : 'GI_UINTEGER',
276SQL_CORRELATION_NAME : 'GI_USMALLINT',SQL_CREATE_ASSERTION : 'GI_UINTEGER',SQL_CREATE_CHARACTER_SET : 'GI_UINTEGER',
277SQL_CREATE_COLLATION : 'GI_UINTEGER',SQL_CREATE_DOMAIN : 'GI_UINTEGER',SQL_CREATE_SCHEMA : 'GI_UINTEGER',
278SQL_CREATE_TABLE : 'GI_UINTEGER',SQL_CREATE_TRANSLATION : 'GI_UINTEGER',SQL_CREATE_VIEW : 'GI_UINTEGER',
279SQL_CURSOR_COMMIT_BEHAVIOR : 'GI_USMALLINT',SQL_CURSOR_ROLLBACK_BEHAVIOR : 'GI_USMALLINT',SQL_DATABASE_NAME : 'GI_STRING',
280SQL_DATA_SOURCE_NAME : 'GI_STRING',SQL_DATA_SOURCE_READ_ONLY : 'GI_YESNO',SQL_DATETIME_LITERALS : 'GI_UINTEGER',
281SQL_DBMS_NAME : 'GI_STRING',SQL_DBMS_VER : 'GI_STRING',SQL_DDL_INDEX : 'GI_UINTEGER',
282SQL_DEFAULT_TXN_ISOLATION : 'GI_UINTEGER',SQL_DESCRIBE_PARAMETER : 'GI_YESNO',SQL_DM_VER : 'GI_STRING',
283SQL_DRIVER_NAME : 'GI_STRING',SQL_DRIVER_ODBC_VER : 'GI_STRING',SQL_DRIVER_VER : 'GI_STRING',SQL_DROP_ASSERTION : 'GI_UINTEGER',
284SQL_DROP_CHARACTER_SET : 'GI_UINTEGER', SQL_DROP_COLLATION : 'GI_UINTEGER',SQL_DROP_DOMAIN : 'GI_UINTEGER',
285SQL_DROP_SCHEMA : 'GI_UINTEGER',SQL_DROP_TABLE : 'GI_UINTEGER',SQL_DROP_TRANSLATION : 'GI_UINTEGER',
286SQL_DROP_VIEW : 'GI_UINTEGER',SQL_DYNAMIC_CURSOR_ATTRIBUTES1 : 'GI_UINTEGER',SQL_DYNAMIC_CURSOR_ATTRIBUTES2 : 'GI_UINTEGER',
287SQL_EXPRESSIONS_IN_ORDERBY : 'GI_YESNO',SQL_FILE_USAGE : 'GI_USMALLINT',
288SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1 : 'GI_UINTEGER',SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES2 : 'GI_UINTEGER',
289SQL_GETDATA_EXTENSIONS : 'GI_UINTEGER',SQL_GROUP_BY : 'GI_USMALLINT',SQL_IDENTIFIER_CASE : 'GI_USMALLINT',
290SQL_IDENTIFIER_QUOTE_CHAR : 'GI_STRING',SQL_INDEX_KEYWORDS : 'GI_UINTEGER',SQL_INFO_SCHEMA_VIEWS : 'GI_UINTEGER',
291SQL_INSERT_STATEMENT : 'GI_UINTEGER',SQL_INTEGRITY : 'GI_YESNO',SQL_KEYSET_CURSOR_ATTRIBUTES1 : 'GI_UINTEGER',
292SQL_KEYSET_CURSOR_ATTRIBUTES2 : 'GI_UINTEGER',SQL_KEYWORDS : 'GI_STRING',
293SQL_LIKE_ESCAPE_CLAUSE : 'GI_YESNO',SQL_MAX_ASYNC_CONCURRENT_STATEMENTS : 'GI_UINTEGER',
294SQL_MAX_BINARY_LITERAL_LEN : 'GI_UINTEGER',SQL_MAX_CATALOG_NAME_LEN : 'GI_USMALLINT',
295SQL_MAX_CHAR_LITERAL_LEN : 'GI_UINTEGER',SQL_MAX_COLUMNS_IN_GROUP_BY : 'GI_USMALLINT',
296SQL_MAX_COLUMNS_IN_INDEX : 'GI_USMALLINT',SQL_MAX_COLUMNS_IN_ORDER_BY : 'GI_USMALLINT',
297SQL_MAX_COLUMNS_IN_SELECT : 'GI_USMALLINT',SQL_MAX_COLUMNS_IN_TABLE : 'GI_USMALLINT',
298SQL_MAX_COLUMN_NAME_LEN : 'GI_USMALLINT',SQL_MAX_CONCURRENT_ACTIVITIES : 'GI_USMALLINT',
299SQL_MAX_CURSOR_NAME_LEN : 'GI_USMALLINT',SQL_MAX_DRIVER_CONNECTIONS : 'GI_USMALLINT',
300SQL_MAX_IDENTIFIER_LEN : 'GI_USMALLINT',SQL_MAX_INDEX_SIZE : 'GI_UINTEGER',
301SQL_MAX_PROCEDURE_NAME_LEN : 'GI_USMALLINT',SQL_MAX_ROW_SIZE : 'GI_UINTEGER',
302SQL_MAX_ROW_SIZE_INCLUDES_LONG : 'GI_YESNO',SQL_MAX_SCHEMA_NAME_LEN : 'GI_USMALLINT',
303SQL_MAX_STATEMENT_LEN : 'GI_UINTEGER',SQL_MAX_TABLES_IN_SELECT : 'GI_USMALLINT',
304SQL_MAX_TABLE_NAME_LEN : 'GI_USMALLINT',SQL_MAX_USER_NAME_LEN : 'GI_USMALLINT',
305SQL_MULTIPLE_ACTIVE_TXN : 'GI_YESNO',SQL_MULT_RESULT_SETS : 'GI_YESNO',
306SQL_NEED_LONG_DATA_LEN : 'GI_YESNO',SQL_NON_NULLABLE_COLUMNS : 'GI_USMALLINT',
307SQL_NULL_COLLATION : 'GI_USMALLINT',SQL_NUMERIC_FUNCTIONS : 'GI_UINTEGER',
308SQL_ODBC_INTERFACE_CONFORMANCE : 'GI_UINTEGER',SQL_ODBC_VER : 'GI_STRING',SQL_OJ_CAPABILITIES : 'GI_UINTEGER',
309SQL_ORDER_BY_COLUMNS_IN_SELECT : 'GI_YESNO',SQL_PARAM_ARRAY_ROW_COUNTS : 'GI_UINTEGER',
310SQL_PARAM_ARRAY_SELECTS : 'GI_UINTEGER',SQL_PROCEDURES : 'GI_YESNO',SQL_PROCEDURE_TERM : 'GI_STRING',
311SQL_QUOTED_IDENTIFIER_CASE : 'GI_USMALLINT',SQL_ROW_UPDATES : 'GI_YESNO',SQL_SCHEMA_TERM : 'GI_STRING',
312SQL_SCHEMA_USAGE : 'GI_UINTEGER',SQL_SCROLL_OPTIONS : 'GI_UINTEGER',SQL_SEARCH_PATTERN_ESCAPE : 'GI_STRING',
313SQL_SERVER_NAME : 'GI_STRING',SQL_SPECIAL_CHARACTERS : 'GI_STRING',SQL_SQL92_DATETIME_FUNCTIONS : 'GI_UINTEGER',
314SQL_SQL92_FOREIGN_KEY_DELETE_RULE : 'GI_UINTEGER',SQL_SQL92_FOREIGN_KEY_UPDATE_RULE : 'GI_UINTEGER',
315SQL_SQL92_GRANT : 'GI_UINTEGER',SQL_SQL92_NUMERIC_VALUE_FUNCTIONS : 'GI_UINTEGER',
316SQL_SQL92_PREDICATES : 'GI_UINTEGER',SQL_SQL92_RELATIONAL_JOIN_OPERATORS : 'GI_UINTEGER',
317SQL_SQL92_REVOKE : 'GI_UINTEGER',SQL_SQL92_ROW_VALUE_CONSTRUCTOR : 'GI_UINTEGER',
318SQL_SQL92_STRING_FUNCTIONS : 'GI_UINTEGER',SQL_SQL92_VALUE_EXPRESSIONS : 'GI_UINTEGER',
319SQL_SQL_CONFORMANCE : 'GI_UINTEGER',SQL_STANDARD_CLI_CONFORMANCE : 'GI_UINTEGER',
320SQL_STATIC_CURSOR_ATTRIBUTES1 : 'GI_UINTEGER',SQL_STATIC_CURSOR_ATTRIBUTES2 : 'GI_UINTEGER',
321SQL_STRING_FUNCTIONS : 'GI_UINTEGER',SQL_SUBQUERIES : 'GI_UINTEGER',
322SQL_SYSTEM_FUNCTIONS : 'GI_UINTEGER',SQL_TABLE_TERM : 'GI_STRING',SQL_TIMEDATE_ADD_INTERVALS : 'GI_UINTEGER',
323SQL_TIMEDATE_DIFF_INTERVALS : 'GI_UINTEGER',SQL_TIMEDATE_FUNCTIONS : 'GI_UINTEGER',
324SQL_TXN_CAPABLE : 'GI_USMALLINT',SQL_TXN_ISOLATION_OPTION : 'GI_UINTEGER',
325SQL_UNION : 'GI_UINTEGER',SQL_USER_NAME : 'GI_STRING',SQL_XOPEN_CLI_YEAR : 'GI_STRING',
326}
327
328#Definations for types
329BINARY = bytearray
330Binary = bytearray
331DATETIME = datetime.datetime
332Date = datetime.date
333Time = datetime.time
334Timestamp = datetime.datetime
335STRING = str
336NUMBER = float
337ROWID = int
338DateFromTicks = datetime.date.fromtimestamp
339TimeFromTicks = lambda x: datetime.datetime.fromtimestamp(x).time()
340TimestampFromTicks = datetime.datetime.fromtimestamp
341
342
343#Define exceptions
344class OdbcNoLibrary(Exception):
345    def __init__(self, value):
346        self.value = value
347    def __str__(self):
348        return repr(self.value)
349class OdbcLibraryError(Exception):
350    def __init__(self, value):
351        self.value = value
352    def __str__(self):
353        return repr(self.value)
354class OdbcInvalidHandle(Exception):
355    def __init__(self, value):
356        self.value = value
357    def __str__(self):
358        return repr(self.value)
359class OdbcGenericError(Exception):
360    def __init__(self, value):
361        self.value = value
362    def __str__(self):
363        return repr(self.value)
364class Warning(Exception):
365    def __init__(self, error_code, error_desc):
366        self.value = (error_code, error_desc)
367        self.args = (error_code, error_desc)
368class Error(Exception):
369    def __init__(self, error_code, error_desc):
370        self.value = (error_code, error_desc)
371        self.args = (error_code, error_desc)
372class InterfaceError(Error):
373    def __init__(self, error_code, error_desc):
374        self.value = (error_code, error_desc)
375        self.args = (error_code, error_desc)
376class DatabaseError(Error):
377    def __init__(self, error_code, error_desc):
378        self.value = (error_code, error_desc)
379        self.args = (error_code, error_desc)
380class InternalError(DatabaseError):
381    def __init__(self, error_code, error_desc):
382        self.value = (error_code, error_desc)
383        self.args = (error_code, error_desc)
384class ProgrammingError(DatabaseError):
385    def __init__(self, error_code, error_desc):
386        self.value = (error_code, error_desc)
387        self.args = (error_code, error_desc)
388class DataError(DatabaseError):
389    def __init__(self, error_code, error_desc):
390        self.value = (error_code, error_desc)
391        self.args = (error_code, error_desc)
392class IntegrityError(DatabaseError):
393    def __init__(self, error_code, error_desc):
394        self.value = (error_code, error_desc)
395        self.args = (error_code, error_desc)
396class NotSupportedError(Error):
397    def __init__(self, error_code, error_desc):
398        self.value = (error_code, error_desc)
399        self.args = (error_code, error_desc)
400class OperationalError(DatabaseError):
401    def __init__(self, error_code, error_desc):
402        self.value = (error_code, error_desc)
403        self.args = (error_code, error_desc)
404
405       
406       
407       
408############################################################################       
409#
410#       Find the ODBC library on the platform and connect to it using ctypes
411#
412############################################################################
413# Get the References of the platform's ODBC functions via ctypes
414
415odbc_decoding = 'utf_16'
416odbc_encoding = 'utf_16_le'
417ucs_length = 2
418
419if sys.platform in ('win32','cli'):
420    ODBC_API = ctypes.windll.odbc32
421    # On Windows, the size of SQLWCHAR is hardcoded to 2-bytes.
422    SQLWCHAR_SIZE = ctypes.sizeof(ctypes.c_ushort)
423else:
424    # Set load the library on linux
425    try:
426        # First try direct loading libodbc.so
427        ODBC_API = ctypes.cdll.LoadLibrary('libodbc.so')
428    except:
429        # If direct loading libodbc.so failed
430        # We try finding the libodbc.so by using find_library
431        from ctypes.util import find_library
432        library = find_library('odbc')
433        if library is None:
434            # If find_library still can not find the library
435            # we try finding it manually from where libodbc.so usually appears
436            lib_paths = ("/usr/lib/libodbc.so","/usr/lib/i386-linux-gnu/libodbc.so","/usr/lib/x86_64-linux-gnu/libodbc.so","/usr/lib/libiodbc.dylib")
437            lib_paths = [path for path in lib_paths if os.path.exists(path)]
438            if len(lib_paths) == 0 :
439                raise OdbcNoLibrary('ODBC Library is not found. Is LD_LIBRARY_PATH set?')
440            else:
441                library = lib_paths[0]
442
443        # Then we try loading the found libodbc.so again
444        try:
445            ODBC_API = ctypes.cdll.LoadLibrary(library)
446        except:
447            # If still fail loading, abort.
448            raise OdbcLibraryError('Error while loading ' + library)
449           
450        # only iODBC uses utf-32 / UCS4 encoding data, others normally use utf-16 / UCS2
451        # So we set those for handling.
452        if 'libiodbc.dylib' in library:
453            odbc_decoding = 'utf_32'
454            odbc_encoding = 'utf_32_le'
455            ucs_length = 4
456
457
458    # unixODBC defaults to 2-bytes SQLWCHAR, unless "-DSQL_WCHART_CONVERT" was
459    # added to CFLAGS, in which case it will be the size of wchar_t.
460    # Note that using 4-bytes SQLWCHAR will break most ODBC drivers, as driver
461    # development mostly targets the Windows platform.
462    if py_v3:
463        from subprocess import getstatusoutput
464    else:
465        from commands import getstatusoutput
466
467    status, output = getstatusoutput('odbc_config --cflags')
468    if status == 0 and 'SQL_WCHART_CONVERT' in output:
469        SQLWCHAR_SIZE = ctypes.sizeof(ctypes.c_wchar)
470    else:
471        SQLWCHAR_SIZE = ctypes.sizeof(ctypes.c_ushort)
472
473
474create_buffer_u = ctypes.create_unicode_buffer
475create_buffer = ctypes.create_string_buffer
476wchar_pointer = ctypes.c_wchar_p
477UCS_buf = lambda s: s
478def UCS_dec(buffer):
479    i = 0
480    uchars = []
481    while True:
482        uchar = buffer.raw[i:i + ucs_length].decode(odbc_decoding)
483        if uchar == unicode('\x00'):
484            break
485        uchars.append(uchar)
486        i += ucs_length
487    return ''.join(uchars)
488from_buffer_u = lambda buffer: buffer.value
489
490# This is the common case on Linux, which uses wide Python build together with
491# the default unixODBC without the "-DSQL_WCHART_CONVERT" CFLAGS.
492if sys.platform not in ('win32','cli'):
493    if UNICODE_SIZE >= SQLWCHAR_SIZE:
494        # We can only use unicode buffer if the size of wchar_t (UNICODE_SIZE) is
495        # the same as the size expected by the driver manager (SQLWCHAR_SIZE).
496        create_buffer_u = create_buffer
497        wchar_pointer = ctypes.c_char_p
498
499        def UCS_buf(s):
500            return s.encode(odbc_encoding)
501
502        from_buffer_u = UCS_dec
503
504    # Exoteric case, don't really care.
505    elif UNICODE_SIZE < SQLWCHAR_SIZE:
506        raise OdbcLibraryError('Using narrow Python build with ODBC library '
507            'expecting wide unicode is not supported.')
508
509           
510           
511           
512           
513           
514           
515           
516           
517           
518           
519           
520############################################################
521# Database value to Python data type mappings
522
523
524SQL_TYPE_NULL = 0
525SQL_DECIMAL = 3
526SQL_FLOAT = 6
527SQL_DATE = 9
528SQL_TIME = 10
529SQL_TIMESTAMP = 11
530SQL_VARCHAR = 12
531SQL_LONGVARCHAR = -1
532SQL_VARBINARY = -3
533SQL_LONGVARBINARY = -4
534SQL_BIGINT = -5
535SQL_WVARCHAR = -9
536SQL_WLONGVARCHAR = -10
537SQL_ALL_TYPES = 0
538SQL_SIGNED_OFFSET = -20
539SQL_SS_VARIANT = -150
540SQL_SS_UDT = -151
541SQL_SS_XML = -152
542SQL_SS_TIME2 = -154
543
544SQL_C_CHAR =            SQL_CHAR =          1
545SQL_C_NUMERIC =         SQL_NUMERIC =       2
546SQL_C_LONG =            SQL_INTEGER =       4
547SQL_C_SLONG =           SQL_C_LONG + SQL_SIGNED_OFFSET
548SQL_C_SHORT =           SQL_SMALLINT =      5
549SQL_C_FLOAT =           SQL_REAL =          7
550SQL_C_DOUBLE =          SQL_DOUBLE =        8
551SQL_C_TYPE_DATE =       SQL_TYPE_DATE =     91
552SQL_C_TYPE_TIME =       SQL_TYPE_TIME =     92
553SQL_C_BINARY =          SQL_BINARY =        -2
554SQL_C_SBIGINT =         SQL_BIGINT + SQL_SIGNED_OFFSET       
555SQL_C_TINYINT =         SQL_TINYINT =       -6
556SQL_C_BIT =             SQL_BIT =           -7
557SQL_C_WCHAR =           SQL_WCHAR =         -8
558SQL_C_GUID =            SQL_GUID =          -11 
559SQL_C_TYPE_TIMESTAMP =  SQL_TYPE_TIMESTAMP = 93
560SQL_C_DEFAULT = 99
561
562SQL_DESC_DISPLAY_SIZE = SQL_COLUMN_DISPLAY_SIZE
563
564def dttm_cvt(x):
565    if py_v3:
566        x = x.decode('ascii')
567    if x == '': return None
568    x = x.ljust(26,'0')
569    return datetime.datetime(int(x[0:4]),int(x[5:7]),int(x[8:10]),int(x[10:13]),int(x[14:16]),int(x[17:19]),int(x[20:26]))
570
571def tm_cvt(x):
572    if py_v3:
573        x = x.decode('ascii')
574    if x == '': return None
575    x = x.ljust(15,'0')
576    return datetime.time(int(x[0:2]),int(x[3:5]),int(x[6:8]),int(x[9:15]))
577
578def dt_cvt(x):
579    if py_v3:
580        x = x.decode('ascii')
581    if x == '': return None
582    else:return datetime.date(int(x[0:4]),int(x[5:7]),int(x[8:10]))
583
584def Decimal_cvt(x):
585    if py_v3:
586        x = x.decode('ascii')   
587    return Decimal(x)
588   
589bytearray_cvt = bytearray
590if sys.platform == 'cli':
591    bytearray_cvt = lambda x: bytearray(buffer(x))
592   
593# Below Datatype mappings referenced the document at
594# http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sdk_12.5.1.aseodbc/html/aseodbc/CACFDIGH.htm
595
596SQL_data_type_dict = { \
597#SQL Data TYPE        0.Python Data Type     1.Default Output Converter  2.Buffer Type     3.Buffer Allocator   4.Default Size  5.Variable Length
598SQL_TYPE_NULL       : (None,                lambda x: None,             SQL_C_CHAR,         create_buffer,      2     ,         False         ),
599SQL_CHAR            : (str,                 lambda x: x,                SQL_C_CHAR,         create_buffer,      2048  ,         False         ),
600SQL_NUMERIC         : (Decimal,             Decimal_cvt,                SQL_C_CHAR,         create_buffer,      150   ,         False         ),
601SQL_DECIMAL         : (Decimal,             Decimal_cvt,                SQL_C_CHAR,         create_buffer,      150   ,         False         ),
602SQL_INTEGER         : (int,                 int,                        SQL_C_CHAR,         create_buffer,      150   ,         False         ),
603SQL_SMALLINT        : (int,                 int,                        SQL_C_CHAR,         create_buffer,      150   ,         False         ),
604SQL_FLOAT           : (float,               float,                      SQL_C_CHAR,         create_buffer,      150   ,         False         ),
605SQL_REAL            : (float,               float,                      SQL_C_CHAR,         create_buffer,      150   ,         False         ),
606SQL_DOUBLE          : (float,               float,                      SQL_C_CHAR,         create_buffer,      200   ,         False         ),
607SQL_DATE            : (datetime.date,       dt_cvt,                     SQL_C_CHAR,         create_buffer,      30    ,         False         ),
608SQL_TIME            : (datetime.time,       tm_cvt,                     SQL_C_CHAR,         create_buffer,      20    ,         False         ),
609SQL_SS_TIME2        : (datetime.time,       tm_cvt,                     SQL_C_CHAR,         create_buffer,      20    ,         False         ),
610SQL_TIMESTAMP       : (datetime.datetime,   dttm_cvt,                   SQL_C_CHAR,         create_buffer,      30    ,         False         ),
611SQL_VARCHAR         : (str,                 lambda x: x,                SQL_C_CHAR,         create_buffer,      2048  ,         False         ),
612SQL_LONGVARCHAR     : (str,                 lambda x: x,                SQL_C_CHAR,         create_buffer,      20500 ,         True          ),
613SQL_BINARY          : (bytearray,           bytearray_cvt,              SQL_C_BINARY,       create_buffer,      5120  ,         True          ),
614SQL_VARBINARY       : (bytearray,           bytearray_cvt,              SQL_C_BINARY,       create_buffer,      5120  ,         True          ),
615SQL_LONGVARBINARY   : (bytearray,           bytearray_cvt,              SQL_C_BINARY,       create_buffer,      20500 ,         True          ),
616SQL_BIGINT          : (long,                long,                       SQL_C_CHAR,         create_buffer,      150   ,         False         ),
617SQL_TINYINT         : (int,                 int,                        SQL_C_CHAR,         create_buffer,      150   ,         False         ),
618SQL_BIT             : (bool,                lambda x:x == BYTE_1,       SQL_C_CHAR,         create_buffer,      2     ,         False         ),
619SQL_WCHAR           : (unicode,             lambda x: x,                SQL_C_WCHAR,        create_buffer_u,    2048  ,         False          ),
620SQL_WVARCHAR        : (unicode,             lambda x: x,                SQL_C_WCHAR,        create_buffer_u,    2048  ,         False          ),
621SQL_GUID            : (str,                 str,                        SQL_C_CHAR,         create_buffer,      2048  ,         False         ),
622SQL_WLONGVARCHAR    : (unicode,             lambda x: x,                SQL_C_WCHAR,        create_buffer_u,    20500 ,         True          ),
623SQL_TYPE_DATE       : (datetime.date,       dt_cvt,                     SQL_C_CHAR,         create_buffer,      30    ,         False         ),
624SQL_TYPE_TIME       : (datetime.time,       tm_cvt,                     SQL_C_CHAR,         create_buffer,      20    ,         False         ),
625SQL_TYPE_TIMESTAMP  : (datetime.datetime,   dttm_cvt,                   SQL_C_CHAR,         create_buffer,      30    ,         False         ),
626SQL_SS_VARIANT      : (str,                 lambda x: x,                SQL_C_CHAR,         create_buffer,      2048  ,         True         ),
627SQL_SS_XML          : (unicode,             lambda x: x,                SQL_C_WCHAR,        create_buffer_u,    20500 ,         True          ),
628SQL_SS_UDT          : (bytearray,           bytearray_cvt,              SQL_C_BINARY,       create_buffer,      5120  ,         True          ),
629}
630
631
632"""
633Types mapping, applicable for 32-bit and 64-bit Linux / Windows / Mac OS X.
634
635SQLPointer -> ctypes.c_void_p
636SQLCHAR * -> ctypes.c_char_p
637SQLWCHAR * -> ctypes.c_wchar_p on Windows, ctypes.c_char_p with unixODBC
638SQLINT -> ctypes.c_int
639SQLSMALLINT -> ctypes.c_short
640SQMUSMALLINT -> ctypes.c_ushort
641SQLLEN -> ctypes.c_ssize_t
642SQLULEN -> ctypes.c_size_t
643SQLRETURN -> ctypes.c_short
644"""
645
646# Define the python return type for ODBC functions with ret result.
647funcs_with_ret = [
648    "SQLAllocHandle",
649    "SQLBindParameter",
650    "SQLBindCol",
651    "SQLCloseCursor",
652    "SQLColAttribute",
653    "SQLColumns",
654    "SQLColumnsW",
655    "SQLConnect",
656    "SQLConnectW",
657    "SQLDataSources",
658    "SQLDataSourcesW",
659    "SQLDescribeCol",
660    "SQLDescribeColW",
661    "SQLDescribeParam",
662    "SQLDisconnect",
663    "SQLDriverConnect",
664    "SQLDriverConnectW",
665    "SQLDrivers",
666    "SQLDriversW",
667    "SQLEndTran",
668    "SQLExecDirect",
669    "SQLExecDirectW",
670    "SQLExecute",
671    "SQLFetch",
672    "SQLFetchScroll",
673    "SQLForeignKeys",
674    "SQLForeignKeysW",
675    "SQLFreeHandle",
676    "SQLFreeStmt",
677    "SQLGetData",
678    "SQLGetDiagRec",
679    "SQLGetDiagRecW",
680    "SQLGetInfo",
681    "SQLGetInfoW",
682    "SQLGetTypeInfo",
683    "SQLMoreResults",
684    "SQLNumParams",
685    "SQLNumResultCols",
686    "SQLPrepare",
687    "SQLPrepareW",
688    "SQLPrimaryKeys",
689    "SQLPrimaryKeysW",
690    "SQLProcedureColumns",
691    "SQLProcedureColumnsW",
692    "SQLProcedures",
693    "SQLProceduresW",
694    "SQLRowCount",
695    "SQLSetConnectAttr",
696    "SQLSetEnvAttr",
697    "SQLStatistics",
698    "SQLStatisticsW",
699    "SQLTables",
700    "SQLTablesW",
701    "SQLSetStmtAttr"
702]
703
704for func_name in funcs_with_ret:
705    getattr(ODBC_API, func_name).restype = ctypes.c_short
706
707if sys.platform not in ('cli'):
708    #Seems like the IronPython can not declare ctypes.POINTER type arguments
709    ODBC_API.SQLAllocHandle.argtypes = [
710        ctypes.c_short, ctypes.c_void_p, ctypes.POINTER(ctypes.c_void_p),
711    ]
712
713    ODBC_API.SQLBindParameter.argtypes = [
714        ctypes.c_void_p, ctypes.c_ushort, ctypes.c_short,
715        ctypes.c_short,  ctypes.c_short,  ctypes.c_size_t,
716        ctypes.c_short,  ctypes.c_void_p, ctypes.c_ssize_t, ctypes.POINTER(ctypes.c_ssize_t),
717    ]
718
719    ODBC_API.SQLColAttribute.argtypes = [
720        ctypes.c_void_p, ctypes.c_ushort, ctypes.c_ushort,
721        ctypes.c_void_p, ctypes.c_short, ctypes.POINTER(ctypes.c_short), ctypes.POINTER(ctypes.c_ssize_t),
722    ]
723
724    ODBC_API.SQLDataSources.argtypes = [
725        ctypes.c_void_p, ctypes.c_ushort, ctypes.c_char_p,
726        ctypes.c_short, ctypes.POINTER(ctypes.c_short),
727        ctypes.c_char_p,  ctypes.c_short, ctypes.POINTER(ctypes.c_short),
728    ]
729
730    ODBC_API.SQLDescribeCol.argtypes = [
731        ctypes.c_void_p, ctypes.c_ushort, ctypes.c_char_p, ctypes.c_short,
732        ctypes.POINTER(ctypes.c_short), ctypes.POINTER(ctypes.c_short),
733        ctypes.POINTER(ctypes.c_size_t), ctypes.POINTER(ctypes.c_short), ctypes.POINTER(ctypes.c_short),
734    ]
735
736    ODBC_API.SQLDescribeParam.argtypes = [
737        ctypes.c_void_p, ctypes.c_ushort,
738        ctypes.POINTER(ctypes.c_short), ctypes.POINTER(ctypes.c_size_t),
739        ctypes.POINTER(ctypes.c_short), ctypes.POINTER(ctypes.c_short),
740    ]
741
742    ODBC_API.SQLDriverConnect.argtypes = [
743        ctypes.c_void_p, ctypes.c_void_p, ctypes.c_char_p,
744        ctypes.c_short, ctypes.c_char_p, ctypes.c_short,
745        ctypes.POINTER(ctypes.c_short), ctypes.c_ushort,
746    ]
747
748    ODBC_API.SQLDrivers.argtypes = [
749        ctypes.c_void_p, ctypes.c_ushort,
750        ctypes.c_char_p, ctypes.c_short, ctypes.POINTER(ctypes.c_short),
751        ctypes.c_char_p, ctypes.c_short, ctypes.POINTER(ctypes.c_short),
752    ]
753
754    ODBC_API.SQLGetData.argtypes = [
755        ctypes.c_void_p, ctypes.c_ushort, ctypes.c_short,
756        ctypes.c_void_p, ctypes.c_ssize_t, ctypes.POINTER(ctypes.c_ssize_t),
757    ]
758
759    ODBC_API.SQLGetDiagRec.argtypes = [
760        ctypes.c_short, ctypes.c_void_p, ctypes.c_short,
761        ctypes.c_char_p, ctypes.POINTER(ctypes.c_int),
762        ctypes.c_char_p, ctypes.c_short, ctypes.POINTER(ctypes.c_short),
763    ]
764
765    ODBC_API.SQLGetInfo.argtypes = [
766        ctypes.c_void_p, ctypes.c_ushort, ctypes.c_void_p,
767        ctypes.c_short, ctypes.POINTER(ctypes.c_short),
768    ]
769
770    ODBC_API.SQLRowCount.argtypes = [
771        ctypes.c_void_p, ctypes.POINTER(ctypes.c_ssize_t),
772    ]
773
774    ODBC_API.SQLNumParams.argtypes = [
775        ctypes.c_void_p, ctypes.POINTER(ctypes.c_short),
776    ]
777
778    ODBC_API.SQLNumResultCols.argtypes = [
779        ctypes.c_void_p, ctypes.POINTER(ctypes.c_short),
780    ]
781
782
783ODBC_API.SQLCloseCursor.argtypes = [ctypes.c_void_p]
784
785ODBC_API.SQLColumns.argtypes = [
786    ctypes.c_void_p, ctypes.c_char_p, ctypes.c_short,
787    ctypes.c_char_p, ctypes.c_short, ctypes.c_char_p,
788    ctypes.c_short, ctypes.c_char_p, ctypes.c_short,
789]
790
791ODBC_API.SQLConnect.argtypes = [
792    ctypes.c_void_p, ctypes.c_char_p, ctypes.c_short,
793    ctypes.c_char_p, ctypes.c_short, ctypes.c_char_p, ctypes.c_short,
794]
795
796ODBC_API.SQLDisconnect.argtypes = [ctypes.c_void_p]
797
798ODBC_API.SQLEndTran.argtypes = [
799    ctypes.c_short, ctypes.c_void_p, ctypes.c_short,
800]
801
802ODBC_API.SQLExecute.argtypes = [ctypes.c_void_p]
803
804ODBC_API.SQLExecDirect.argtypes = [
805    ctypes.c_void_p, ctypes.c_char_p, ctypes.c_int,
806]
807
808ODBC_API.SQLFetch.argtypes = [ctypes.c_void_p]
809
810ODBC_API.SQLFetchScroll.argtypes = [
811    ctypes.c_void_p, ctypes.c_short, ctypes.c_ssize_t,
812]
813
814ODBC_API.SQLForeignKeys.argtypes = [
815    ctypes.c_void_p, ctypes.c_char_p, ctypes.c_short,
816    ctypes.c_char_p, ctypes.c_short, ctypes.c_char_p,
817    ctypes.c_short, ctypes.c_char_p, ctypes.c_short,
818    ctypes.c_char_p, ctypes.c_short, ctypes.c_char_p, ctypes.c_short,
819]
820
821ODBC_API.SQLFreeHandle.argtypes = [
822    ctypes.c_short, ctypes.c_void_p,
823]
824
825ODBC_API.SQLFreeStmt.argtypes = [
826    ctypes.c_void_p, ctypes.c_ushort,
827]
828
829
830ODBC_API.SQLGetTypeInfo.argtypes = [
831    ctypes.c_void_p, ctypes.c_short,
832]
833
834ODBC_API.SQLMoreResults.argtypes = [ctypes.c_void_p]
835
836
837ODBC_API.SQLPrepare.argtypes = [
838    ctypes.c_void_p, ctypes.c_char_p, ctypes.c_int,
839]
840
841ODBC_API.SQLPrimaryKeys.argtypes = [
842    ctypes.c_void_p, ctypes.c_char_p, ctypes.c_short,
843    ctypes.c_char_p, ctypes.c_short, ctypes.c_char_p, ctypes.c_short,
844]
845
846ODBC_API.SQLProcedureColumns.argtypes = [
847    ctypes.c_void_p, ctypes.c_char_p, ctypes.c_short,
848    ctypes.c_char_p, ctypes.c_short, ctypes.c_char_p,
849    ctypes.c_short, ctypes.c_char_p, ctypes.c_short,
850]
851
852ODBC_API.SQLProcedures.argtypes = [
853    ctypes.c_void_p, ctypes.c_char_p, ctypes.c_short,
854    ctypes.c_char_p, ctypes.c_short, ctypes.c_char_p, ctypes.c_short,
855]
856
857
858ODBC_API.SQLSetConnectAttr.argtypes = [
859    ctypes.c_void_p, ctypes.c_int, ctypes.c_void_p, ctypes.c_int,
860]
861
862ODBC_API.SQLSetEnvAttr.argtypes = [
863    ctypes.c_void_p, ctypes.c_int, ctypes.c_void_p,  ctypes.c_int,
864]
865
866ODBC_API.SQLStatistics.argtypes = [
867    ctypes.c_void_p, ctypes.c_char_p, ctypes.c_short,
868    ctypes.c_char_p, ctypes.c_short, ctypes.c_char_p,
869    ctypes.c_short, ctypes.c_ushort, ctypes.c_ushort,
870]
871
872ODBC_API.SQLTables.argtypes = [
873    ctypes.c_void_p, ctypes.c_char_p, ctypes.c_short,
874    ctypes.c_char_p, ctypes.c_short, ctypes.c_char_p,
875    ctypes.c_short, ctypes.c_char_p,  ctypes.c_short,
876]
877
878def to_wchar(argtypes):
879    if argtypes: # Under IronPython some argtypes are not declared
880        result = []
881        for x in argtypes:
882            if x == ctypes.c_char_p:
883                result.append(wchar_pointer)
884            else:
885                result.append(x)
886        return result
887    else:
888        return argtypes
889
890ODBC_API.SQLColumnsW.argtypes = to_wchar(ODBC_API.SQLColumns.argtypes)
891ODBC_API.SQLConnectW.argtypes = to_wchar(ODBC_API.SQLConnect.argtypes)
892ODBC_API.SQLDataSourcesW.argtypes = to_wchar(ODBC_API.SQLDataSources.argtypes)
893ODBC_API.SQLDescribeColW.argtypes = to_wchar(ODBC_API.SQLDescribeCol.argtypes)
894ODBC_API.SQLDriverConnectW.argtypes = to_wchar(ODBC_API.SQLDriverConnect.argtypes)
895ODBC_API.SQLDriversW.argtypes = to_wchar(ODBC_API.SQLDrivers.argtypes)
896ODBC_API.SQLExecDirectW.argtypes = to_wchar(ODBC_API.SQLExecDirect.argtypes)
897ODBC_API.SQLForeignKeysW.argtypes = to_wchar(ODBC_API.SQLForeignKeys.argtypes)
898ODBC_API.SQLPrepareW.argtypes = to_wchar(ODBC_API.SQLPrepare.argtypes)
899ODBC_API.SQLPrimaryKeysW.argtypes = to_wchar(ODBC_API.SQLPrimaryKeys.argtypes)
900ODBC_API.SQLProcedureColumnsW.argtypes = to_wchar(ODBC_API.SQLProcedureColumns.argtypes)
901ODBC_API.SQLProceduresW.argtypes = to_wchar(ODBC_API.SQLProcedures.argtypes)
902ODBC_API.SQLStatisticsW.argtypes = to_wchar(ODBC_API.SQLStatistics.argtypes)
903ODBC_API.SQLTablesW.argtypes = to_wchar(ODBC_API.SQLTables.argtypes)
904ODBC_API.SQLGetDiagRecW.argtypes = to_wchar(ODBC_API.SQLGetDiagRec.argtypes)
905ODBC_API.SQLGetInfoW.argtypes = to_wchar(ODBC_API.SQLGetInfo.argtypes)
906
907# Set the alias for the ctypes functions for beter code readbility or performance.
908ADDR = ctypes.byref
909c_short = ctypes.c_short
910c_ssize_t = ctypes.c_ssize_t
911SQLFetch = ODBC_API.SQLFetch
912SQLExecute = ODBC_API.SQLExecute
913SQLBindParameter = ODBC_API.SQLBindParameter
914SQLGetData = ODBC_API.SQLGetData
915SQLRowCount = ODBC_API.SQLRowCount
916SQLNumResultCols = ODBC_API.SQLNumResultCols
917SQLEndTran = ODBC_API.SQLEndTran
918# Set alias for beter code readbility or performance.
919NO_FREE_STATEMENT = 0
920FREE_STATEMENT = 1
921BLANK_BYTE = str_8b()
922
923def ctrl_err(ht, h, val_ret, ansi):
924    """Classify type of ODBC error from (type of handle, handle, return value)
925    , and raise with a list"""
926   
927    if ansi:
928        state = create_buffer(22)
929        Message = create_buffer(1024*4)
930        ODBC_func = ODBC_API.SQLGetDiagRec
931        if py_v3:
932            raw_s = lambda s: bytes(s,'ascii')
933        else:
934            raw_s = str_8b
935    else:
936        state = create_buffer_u(22)
937        Message = create_buffer_u(1024*4)
938        ODBC_func = ODBC_API.SQLGetDiagRecW
939        raw_s = unicode
940    NativeError = ctypes.c_int()
941    Buffer_len = c_short()
942    err_list = []
943    number_errors = 1
944   
945    while 1:
946        ret = ODBC_func(ht, h, number_errors, state, \
947            ADDR(NativeError), Message, 1024, ADDR(Buffer_len))
948        if ret == SQL_NO_DATA_FOUND:
949            #No more data, I can raise
950            #print(err_list[0][1])
951            state = err_list[0][0]
952            err_text = raw_s('[')+state+raw_s('] ')+err_list[0][1]
953            if state[:2] in (raw_s('24'),raw_s('25'),raw_s('42')):
954                raise ProgrammingError(state,err_text)
955            elif state[:2] in (raw_s('22')):
956                raise DataError(state,err_text)
957            elif state[:2] in (raw_s('23')) or state == raw_s('40002'):
958                raise IntegrityError(state,err_text)
959            elif state == raw_s('0A000'):
960                raise NotSupportedError(state,err_text)
961            elif state in (raw_s('HYT00'),raw_s('HYT01')):
962                raise OperationalError(state,err_text)
963            elif state[:2] in (raw_s('IM'),raw_s('HY')):
964                raise Error(state,err_text)
965            else:
966                raise DatabaseError(state,err_text)
967            break
968        elif ret == SQL_INVALID_HANDLE:
969            #The handle passed is an invalid handle
970            raise ProgrammingError('', 'SQL_INVALID_HANDLE')
971        elif ret == SQL_SUCCESS:
972            if ansi:
973                err_list.append((state.value, Message.value, NativeError.value))
974            else:
975                err_list.append((from_buffer_u(state), from_buffer_u(Message), NativeError.value))
976            number_errors += 1
977        elif ret == SQL_ERROR:
978            raise ProgrammingError('', 'SQL_ERROR')
979
980
981
982def check_success(ODBC_obj, ret):
983    """ Validate return value, if not success, raise exceptions based on the handle """
984    if ret not in (SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA):
985        if isinstance(ODBC_obj, Cursor):
986            ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
987        elif isinstance(ODBC_obj, Connection):
988            ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)
989        else:
990            ctrl_err(SQL_HANDLE_ENV, ODBC_obj, ret, False)
991           
992           
993def AllocateEnv():
994    if pooling:
995        ret = ODBC_API.SQLSetEnvAttr(SQL_NULL_HANDLE, SQL_ATTR_CONNECTION_POOLING, SQL_CP_ONE_PER_HENV, SQL_IS_UINTEGER)
996        check_success(SQL_NULL_HANDLE, ret)
997
998    '''
999    Allocate an ODBC environment by initializing the handle shared_env_h
1000    ODBC enviroment needed to be created, so connections can be created under it
1001    connections pooling can be shared under one environment
1002    '''
1003    global shared_env_h
1004    shared_env_h  = ctypes.c_void_p()
1005    ret = ODBC_API.SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, ADDR(shared_env_h))
1006    check_success(shared_env_h, ret)
1007
1008    # Set the ODBC environment's compatibil leve to ODBC 3.0
1009    ret = ODBC_API.SQLSetEnvAttr(shared_env_h, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, 0)
1010    check_success(shared_env_h, ret)
1011   
1012
1013"""
1014Here, we have a few callables that determine how a result row is returned.
1015
1016A new one can be added by creating a callable that:
1017- accepts a cursor as its parameter.
1018- returns a callable that accepts an iterable containing the row values.
1019"""
1020
1021def TupleRow(cursor):
1022    """Normal tuple with added attribute `cursor_description`, as in pyodbc.
1023
1024    This is the default.
1025    """
1026    class Row(tuple):
1027        cursor_description = cursor.description
1028       
1029        def get(self, field):
1030            if not hasattr(self, 'field_dict'):
1031                self.field_dict = {}
1032                for i,item in enumerate(self):
1033                    self.field_dict[self.cursor_description[i][0]] = item
1034            return self.field_dict.get(field)
1035           
1036        def __getitem__(self, field):
1037            if isinstance(field, (unicode,str)):
1038                return self.get(field)
1039            else:
1040                return tuple.__getitem__(self,field)
1041           
1042    return Row
1043
1044
1045def NamedTupleRow(cursor):
1046    """Named tuple to allow attribute lookup by name.
1047
1048    Requires py2.6 or above.
1049    """
1050    from collections import namedtuple
1051
1052    attr_names = [x[0] for x in cursor._ColBufferList]
1053
1054    class Row(namedtuple('Row', attr_names, rename=True)):
1055        cursor_description = cursor.description
1056
1057        def __new__(cls, iterable):
1058            return super(Row, cls).__new__(cls, *iterable)
1059
1060    return Row
1061
1062
1063def MutableNamedTupleRow(cursor):
1064    """Mutable named tuple to allow attribute to be replaced. This should be
1065    compatible with pyodbc's Row type.
1066
1067    Requires 3rd-party library "recordtype".
1068    """
1069    from recordtype import recordtype
1070
1071    attr_names = [x[0] for x in cursor._ColBufferList]
1072
1073    class Row(recordtype('Row', attr_names, rename=True)):
1074        cursor_description = cursor.description
1075
1076        def __init__(self, iterable):
1077            super(Row, self).__init__(*iterable)
1078
1079        def __iter__(self):
1080            for field_name in self.__slots__:
1081                yield getattr(self, field_name)
1082
1083        def __getitem__(self, index):
1084            if isinstance(index, slice):
1085                return tuple(getattr(self, x) for x in self.__slots__[index])
1086            return getattr(self, self.__slots__[index])
1087
1088        def __setitem__(self, index, value):
1089            setattr(self, self.__slots__[index], value)
1090
1091    return Row
1092
1093# When Null is used in a binary parameter, database usually would not
1094# accept the None for a binary field, so the work around is to use a
1095# Specical None that the pypyodbc moudle would know this NULL is for
1096# a binary field.
1097class BinaryNullType(): pass
1098BinaryNull = BinaryNullType()
1099
1100# The get_type function is used to determine if parameters need to be re-binded
1101# against the changed parameter types
1102# 'b' for bool, 'U' for long unicode string, 'u' for short unicode string
1103# 'S' for long 8 bit string, 's' for short 8 bit string, 'l' for big integer, 'i' for normal integer
1104# 'f' for float, 'D' for Decimal, 't' for datetime.time, 'd' for datetime.datetime, 'dt' for datetime.datetime
1105# 'bi' for binary
1106def get_type(v):   
1107
1108    if isinstance(v, bool):
1109        return ('b',)
1110    elif isinstance(v, unicode):
1111        if len(v) >= 255:
1112            return  ('U',(len(v)//1000+1)*1000)
1113        else:
1114            return ('u',)
1115    elif isinstance(v, (str_8b,str)):
1116        if len(v) >= 255:
1117            return  ('S',(len(v)//1000+1)*1000)
1118        else:
1119            return  ('s',)
1120    elif isinstance(v, (int, long)):
1121        #SQL_BIGINT defination: http://msdn.microsoft.com/en-us/library/ms187745.aspx
1122        if v > 2147483647 or v < -2147483648:
1123            return  ('l',)
1124        else:
1125            return  ('i',)
1126    elif isinstance(v, float):
1127        return ('f',)
1128    elif isinstance(v, BinaryNullType):
1129        return ('BN',)
1130    elif v is None:
1131        return ('N',)
1132    elif isinstance(v, Decimal):
1133        t = v.as_tuple() #1.23 -> (1,2,3),-2 , 1.23*E7 -> (1,2,3),5
1134        return  ('D',(len(t[1]),0 - t[2])) # number of digits, and number of decimal digits
1135
1136       
1137    elif isinstance (v, datetime.datetime):
1138        return ('dt',)
1139    elif isinstance (v, datetime.date):
1140        return ('d',)
1141    elif isinstance(v, datetime.time):
1142        return ('t',)
1143    elif isinstance (v, (bytearray, buffer)):
1144        return ('bi',(len(v)//1000+1)*1000)
1145       
1146    return type(v)
1147
1148
1149# The Cursor Class.
1150class Cursor:
1151    def __init__(self, conx, row_type_callable=None):
1152        """ Initialize self.stmt_h, which is the handle of a statement
1153        A statement is actually the basis of a python"cursor" object
1154        """
1155        self.stmt_h = ctypes.c_void_p()
1156        self.connection = conx
1157        self.ansi = conx.ansi
1158        self.row_type_callable = row_type_callable or TupleRow
1159        self.statement = None
1160        self._last_param_types = None
1161        self._ParamBufferList = []
1162        self._ColBufferList = []
1163        self._row_type = None
1164        self._buf_cvt_func = []
1165        self.rowcount = -1
1166        self.description = None
1167        self.autocommit = None
1168        self._ColTypeCodeList = []
1169        self._outputsize = {}
1170        self._inputsizers = []
1171        self.arraysize = 1
1172        ret = ODBC_API.SQLAllocHandle(SQL_HANDLE_STMT, self.connection.dbc_h, ADDR(self.stmt_h))
1173        check_success(self, ret)
1174   
1175        self.timeout = conx.timeout
1176        if self.timeout != 0:
1177            self.set_timeout(self.timeout)
1178       
1179        self._PARAM_SQL_TYPE_LIST = []
1180        self.closed = False     
1181
1182    def set_timeout(self, timeout):
1183        self.timeout = timeout
1184        ret = ODBC_API.SQLSetStmtAttr(self.stmt_h, SQL_ATTR_QUERY_TIMEOUT, self.timeout, 0)
1185        check_success(self, ret)
1186       
1187    def prepare(self, query_string):
1188        """prepare a query"""
1189       
1190        #self._free_results(FREE_STATEMENT)
1191        if not self.connection:
1192            self.close()
1193           
1194        if type(query_string) == unicode:
1195            c_query_string = wchar_pointer(UCS_buf(query_string))
1196            ret = ODBC_API.SQLPrepareW(self.stmt_h, c_query_string, len(query_string))
1197        else:
1198            c_query_string = ctypes.c_char_p(query_string)
1199            ret = ODBC_API.SQLPrepare(self.stmt_h, c_query_string, len(query_string))
1200        if ret != SQL_SUCCESS:
1201            check_success(self, ret)
1202           
1203       
1204        self._PARAM_SQL_TYPE_LIST = []
1205       
1206        if self.connection.support_SQLDescribeParam:
1207            # SQLServer's SQLDescribeParam only supports DML SQL, so avoid the SELECT statement
1208            if True:# 'SELECT' not in query_string.upper():
1209                #self._free_results(NO_FREE_STATEMENT)
1210                NumParams = c_short()
1211                ret = ODBC_API.SQLNumParams(self.stmt_h, ADDR(NumParams))
1212                if ret != SQL_SUCCESS:
1213                    check_success(self, ret)
1214           
1215                for col_num in range(NumParams.value):
1216                    ParameterNumber = ctypes.c_ushort(col_num + 1)
1217                    DataType = c_short()
1218                    ParameterSize = ctypes.c_size_t()
1219                    DecimalDigits = c_short()
1220                    Nullable = c_short()
1221                    ret = ODBC_API.SQLDescribeParam(
1222                        self.stmt_h,
1223                        ParameterNumber,
1224                        ADDR(DataType),
1225                        ADDR(ParameterSize),
1226                        ADDR(DecimalDigits),
1227                        ADDR(Nullable),
1228                    )
1229                    if ret != SQL_SUCCESS:
1230                        try:
1231                            check_success(self, ret)
1232                        except DatabaseError:
1233                            if sys.exc_info()[1].value[0] == '07009':
1234                                self._PARAM_SQL_TYPE_LIST = []
1235                                break
1236                            else:
1237                                raise sys.exc_info()[1]
1238                        except:
1239                            raise sys.exc_info()[1]
1240
1241                    self._PARAM_SQL_TYPE_LIST.append((DataType.value,DecimalDigits.value))
1242       
1243        self.statement = query_string
1244
1245
1246    def _BindParams(self, param_types, pram_io_list = []):
1247        """Create parameter buffers based on param types, and bind them to the statement"""
1248        # Clear the old Parameters
1249        if not self.connection:
1250            self.close()
1251        #self._free_results(NO_FREE_STATEMENT)
1252       
1253        # Get the number of query parameters judged by database.
1254        NumParams = c_short()
1255        ret = ODBC_API.SQLNumParams(self.stmt_h, ADDR(NumParams))
1256        if ret != SQL_SUCCESS:
1257            check_success(self, ret)
1258       
1259        if len(param_types) != NumParams.value:
1260            # In case number of parameters provided do not same as number required
1261            error_desc = "The SQL contains %d parameter markers, but %d parameters were supplied" \
1262                        %(NumParams.value,len(param_types))
1263            raise ProgrammingError('HY000',error_desc)
1264       
1265       
1266        # Every parameter needs to be binded to a buffer
1267        ParamBufferList = []
1268        # Temporary holder since we can only call SQLDescribeParam before
1269        # calling SQLBindParam.
1270        temp_holder = []
1271        for col_num in range(NumParams.value):
1272            dec_num = 0           
1273            buf_size = 512
1274               
1275            if param_types[col_num][0] == 'u':
1276                sql_c_type = SQL_C_WCHAR
1277                sql_type = SQL_WVARCHAR
1278                buf_size = 255                 
1279                ParameterBuffer = create_buffer_u(buf_size)               
1280                   
1281            elif param_types[col_num][0] == 's':
1282                sql_c_type = SQL_C_CHAR
1283                sql_type = SQL_VARCHAR
1284                buf_size = 255                 
1285                ParameterBuffer = create_buffer(buf_size)
1286
1287
1288            elif param_types[col_num][0] == 'U':
1289                sql_c_type = SQL_C_WCHAR
1290                sql_type = SQL_WLONGVARCHAR
1291                buf_size = param_types[col_num][1]#len(self._inputsizers)>col_num and self._inputsizers[col_num] or 20500
1292                ParameterBuffer = create_buffer_u(buf_size)
1293
1294            elif param_types[col_num][0] == 'S':
1295                sql_c_type = SQL_C_CHAR
1296                sql_type = SQL_LONGVARCHAR
1297                buf_size = param_types[col_num][1]#len(self._inputsizers)>col_num and self._inputsizers[col_num] or 20500
1298                ParameterBuffer = create_buffer(buf_size)
1299           
1300            # bool subclasses int, thus has to go first
1301            elif param_types[col_num][0] == 'b':
1302                sql_c_type = SQL_C_CHAR
1303                sql_type = SQL_BIT
1304                buf_size = SQL_data_type_dict[sql_type][4]
1305                ParameterBuffer = create_buffer(buf_size)
1306               
1307            elif param_types[col_num][0] == 'i':
1308                sql_c_type = SQL_C_CHAR           
1309                sql_type = SQL_INTEGER   
1310                buf_size = SQL_data_type_dict[sql_type][4]             
1311                ParameterBuffer = create_buffer(buf_size)           
1312               
1313            elif param_types[col_num][0] == 'l':
1314                sql_c_type = SQL_C_CHAR           
1315                sql_type = SQL_BIGINT         
1316                buf_size = SQL_data_type_dict[sql_type][4]         
1317                ParameterBuffer = create_buffer(buf_size)
1318           
1319
1320            elif param_types[col_num][0] == 'D': #Decimal
1321                sql_c_type = SQL_C_CHAR
1322                sql_type = SQL_NUMERIC
1323                digit_num, dec_num = param_types[col_num][1]
1324                if dec_num > 0:
1325                    # has decimal
1326                    buf_size = digit_num
1327                    dec_num = dec_num
1328                else:
1329                    # no decimal
1330                    buf_size = digit_num - dec_num
1331                    dec_num = 0
1332
1333                ParameterBuffer = create_buffer(buf_size + 4)# add extra length for sign and dot
1334
1335               
1336            elif param_types[col_num][0] == 'f':
1337                sql_c_type = SQL_C_CHAR
1338                sql_type = SQL_DOUBLE               
1339                buf_size = SQL_data_type_dict[sql_type][4]
1340                ParameterBuffer = create_buffer(buf_size)
1341               
1342               
1343            # datetime subclasses date, thus has to go first
1344            elif param_types[col_num][0] == 'dt':
1345                sql_c_type = SQL_C_CHAR
1346                sql_type = SQL_TYPE_TIMESTAMP
1347                buf_size = self.connection.type_size_dic[SQL_TYPE_TIMESTAMP][0]               
1348                ParameterBuffer = create_buffer(buf_size)
1349                dec_num = self.connection.type_size_dic[SQL_TYPE_TIMESTAMP][1]
1350               
1351               
1352            elif param_types[col_num][0] == 'd':
1353                sql_c_type = SQL_C_CHAR
1354                if SQL_TYPE_DATE in self.connection.type_size_dic:
1355                    #if DEBUG:print('conx.type_size_dic.has_key(SQL_TYPE_DATE)')
1356                    sql_type = SQL_TYPE_DATE
1357                    buf_size = self.connection.type_size_dic[SQL_TYPE_DATE][0]
1358                   
1359                    ParameterBuffer = create_buffer(buf_size)
1360                    dec_num = self.connection.type_size_dic[SQL_TYPE_DATE][1]
1361                   
1362                else:
1363                    # SQL Sever <2008 doesn't have a DATE type.
1364                    sql_type = SQL_TYPE_TIMESTAMP
1365                    buf_size = 10                   
1366                    ParameterBuffer = create_buffer(buf_size)
1367                   
1368   
1369            elif param_types[col_num][0] == 't':
1370                sql_c_type = SQL_C_CHAR
1371                if SQL_TYPE_TIME in self.connection.type_size_dic:
1372                    sql_type = SQL_TYPE_TIME
1373                    buf_size = self.connection.type_size_dic[SQL_TYPE_TIME][0]                   
1374                    ParameterBuffer = create_buffer(buf_size)
1375                    dec_num = self.connection.type_size_dic[SQL_TYPE_TIME][1]                   
1376                elif SQL_SS_TIME2 in self.connection.type_size_dic:
1377                    # TIME type added in SQL Server 2008
1378                    sql_type = SQL_SS_TIME2
1379                    buf_size = self.connection.type_size_dic[SQL_SS_TIME2][0]
1380                    ParameterBuffer = create_buffer(buf_size)
1381                    dec_num = self.connection.type_size_dic[SQL_SS_TIME2][1]
1382                else:
1383                    # SQL Sever <2008 doesn't have a TIME type.
1384                    sql_type = SQL_TYPE_TIMESTAMP
1385                    buf_size = self.connection.type_size_dic[SQL_TYPE_TIMESTAMP][0]                   
1386                    ParameterBuffer = create_buffer(buf_size)
1387                    dec_num = 3
1388                   
1389            elif param_types[col_num][0] == 'BN':
1390                sql_c_type = SQL_C_BINARY
1391                sql_type = SQL_VARBINARY
1392                buf_size = 1                 
1393                ParameterBuffer = create_buffer(buf_size)               
1394           
1395            elif param_types[col_num][0] == 'N':
1396                if len(self._PARAM_SQL_TYPE_LIST) > 0:
1397                    sql_c_type = SQL_C_DEFAULT
1398                    sql_type = self._PARAM_SQL_TYPE_LIST[col_num][0]
1399                    buf_size = 1
1400                    ParameterBuffer = create_buffer(buf_size)
1401                else:
1402                    sql_c_type = SQL_C_CHAR
1403                    sql_type = SQL_CHAR
1404                    buf_size = 1                 
1405                    ParameterBuffer = create_buffer(buf_size)
1406            elif param_types[col_num][0] == 'bi':
1407                sql_c_type = SQL_C_BINARY
1408                sql_type = SQL_LONGVARBINARY
1409                buf_size = param_types[col_num][1]#len(self._inputsizers)>col_num and self._inputsizers[col_num] or 20500               
1410                ParameterBuffer = create_buffer(buf_size)
1411           
1412               
1413            else:
1414                sql_c_type = SQL_C_CHAR
1415                sql_type = SQL_LONGVARCHAR
1416                buf_size = len(self._inputsizers)>col_num and self._inputsizers[col_num] or 20500               
1417                ParameterBuffer = create_buffer(buf_size)
1418               
1419            temp_holder.append((sql_c_type, sql_type, buf_size, dec_num, ParameterBuffer))
1420
1421        for col_num, (sql_c_type, sql_type, buf_size, dec_num, ParameterBuffer) in enumerate(temp_holder):
1422            BufferLen = c_ssize_t(buf_size)
1423            LenOrIndBuf = c_ssize_t()
1424               
1425           
1426            InputOutputType = SQL_PARAM_INPUT
1427            if len(pram_io_list) > col_num:
1428                InputOutputType = pram_io_list[col_num]
1429
1430            ret = SQLBindParameter(self.stmt_h, col_num + 1, InputOutputType, sql_c_type, sql_type, buf_size,\
1431                    dec_num, ADDR(ParameterBuffer), BufferLen,ADDR(LenOrIndBuf))
1432            if ret != SQL_SUCCESS:   
1433                check_success(self, ret)
1434            # Append the value buffer and the length buffer to the array
1435            ParamBufferList.append((ParameterBuffer,LenOrIndBuf,sql_type))
1436               
1437        self._last_param_types = param_types
1438        self._ParamBufferList = ParamBufferList
1439
1440   
1441    def execute(self, query_string, params=None, many_mode=False, call_mode=False):
1442        """ Execute the query string, with optional parameters.
1443        If parameters are provided, the query would first be prepared, then executed with parameters;
1444        If parameters are not provided, only th query sting, it would be executed directly
1445        """
1446        if not self.connection:
1447            self.close()
1448           
1449        self._free_stmt(SQL_CLOSE)
1450        if params:
1451            # If parameters exist, first prepare the query then executed with parameters
1452           
1453            if not isinstance(params, (tuple, list)):
1454                raise TypeError("Params must be in a list, tuple, or Row")
1455
1456               
1457            if query_string != self.statement:
1458                # if the query is not same as last query, then it is not prepared 
1459                self.prepare(query_string)
1460           
1461   
1462            param_types = list(map(get_type, params))
1463
1464            if call_mode:
1465                self._free_stmt(SQL_RESET_PARAMS)
1466                self._BindParams(param_types, self._pram_io_list)
1467            else:
1468                if self._last_param_types is None:
1469                    self._free_stmt(SQL_RESET_PARAMS)
1470                    self._BindParams(param_types)
1471                elif len(param_types) != len(self._last_param_types):
1472                    self._free_stmt(SQL_RESET_PARAMS)
1473                    self._BindParams(param_types)
1474                elif sum([p_type[0] != 'N' and p_type != self._last_param_types[i] for i,p_type in enumerate(param_types)]) > 0:
1475                    self._free_stmt(SQL_RESET_PARAMS)
1476                    self._BindParams(param_types)
1477               
1478           
1479            # With query prepared, now put parameters into buffers
1480            col_num = 0
1481            for param_buffer, param_buffer_len, sql_type in self._ParamBufferList:
1482                c_char_buf, c_buf_len = '', 0
1483                param_val = params[col_num]
1484                if param_types[col_num][0] in ('N','BN'):
1485                    param_buffer_len.value = SQL_NULL_DATA
1486                    col_num += 1
1487                    continue
1488                elif param_types[col_num][0] in ('i','l','f'):
1489                    if py_v3:
1490                        c_char_buf = bytes(str(param_val),'ascii')
1491                    else:
1492                        c_char_buf = str(param_val)
1493                    c_buf_len = len(c_char_buf)
1494                   
1495                elif param_types[col_num][0] in ('s','S'):
1496                    c_char_buf = param_val
1497                    c_buf_len = len(c_char_buf)
1498                elif param_types[col_num][0] in ('u','U'):
1499                    c_char_buf = UCS_buf(param_val)
1500                    c_buf_len = len(c_char_buf)
1501                   
1502                elif param_types[col_num][0] == 'dt':
1503                    max_len = self.connection.type_size_dic[SQL_TYPE_TIMESTAMP][0]
1504                    datetime_str = param_val.strftime('%Y-%m-%d %H:%M:%S.%f')
1505                    c_char_buf = datetime_str[:max_len]
1506                    if py_v3:
1507                        c_char_buf = bytes(c_char_buf,'ascii')
1508                       
1509                    c_buf_len = len(c_char_buf)
1510                    # print c_buf_len, c_char_buf
1511                   
1512                elif param_types[col_num][0] == 'd':
1513                    if SQL_TYPE_DATE in self.connection.type_size_dic:
1514                        max_len = self.connection.type_size_dic[SQL_TYPE_DATE][0]
1515                    else:
1516                        max_len = 10
1517                    c_char_buf = param_val.isoformat()[:max_len]
1518                    if py_v3:
1519                        c_char_buf = bytes(c_char_buf,'ascii')
1520                    c_buf_len = len(c_char_buf)
1521                    #print c_char_buf
1522                   
1523                elif param_types[col_num][0] == 't':
1524                    if SQL_TYPE_TIME in self.connection.type_size_dic:
1525                        max_len = self.connection.type_size_dic[SQL_TYPE_TIME][0]
1526                        c_char_buf = param_val.isoformat()[:max_len]
1527                        c_buf_len = len(c_char_buf)
1528                    elif SQL_SS_TIME2 in self.connection.type_size_dic:
1529                        max_len = self.connection.type_size_dic[SQL_SS_TIME2][0]
1530                        c_char_buf = param_val.isoformat()[:max_len]
1531                        c_buf_len = len(c_char_buf)
1532                    else:
1533                        c_buf_len = self.connection.type_size_dic[SQL_TYPE_TIMESTAMP][0]
1534                        time_str = param_val.isoformat()
1535                        if len(time_str) == 8:
1536                            time_str += '.000'
1537                        c_char_buf = '1900-01-01 '+time_str[0:c_buf_len - 11]
1538                    if py_v3:
1539                        c_char_buf = bytes(c_char_buf,'ascii')
1540                    #print c_buf_len, c_char_buf
1541                   
1542                elif param_types[col_num][0] == 'b':
1543                    if param_val == True:
1544                        c_char_buf = '1'
1545                    else:
1546                        c_char_buf = '0'
1547                    if py_v3:
1548                        c_char_buf = bytes(c_char_buf,'ascii')
1549                    c_buf_len = 1
1550                   
1551                elif param_types[col_num][0] == 'D': #Decimal
1552                    sign = param_val.as_tuple()[0] == 0 and '+' or '-'
1553                    digit_string = ''.join([str(x) for x in param_val.as_tuple()[1]])
1554                    digit_num, dec_num = param_types[col_num][1]
1555                    if dec_num > 0:
1556                        # has decimal
1557                        left_part = digit_string[:digit_num - dec_num]
1558                        right_part = digit_string[0-dec_num:]
1559                    else:
1560                        # no decimal
1561                        left_part = digit_string + '0'*(0-dec_num)
1562                        right_part = ''
1563                    v = ''.join((sign, left_part,'.', right_part))
1564
1565                    if py_v3:
1566                        c_char_buf = bytes(v,'ascii')
1567                    else:
1568                        c_char_buf = v
1569                    c_buf_len = len(c_char_buf)
1570                   
1571                elif param_types[col_num][0] == 'bi':
1572                    c_char_buf = str_8b(param_val)
1573                    c_buf_len = len(c_char_buf)
1574                   
1575                else:
1576                    c_char_buf = param_val
1577           
1578   
1579                if param_types[col_num][0] == 'bi':
1580                    param_buffer.raw = str_8b(param_val)
1581                   
1582                else:
1583                    #print (type(param_val),param_buffer, param_buffer.value)
1584                    param_buffer.value = c_char_buf
1585
1586                if param_types[col_num][0] in ('U','u','S','s'):
1587                    #ODBC driver will find NUL in unicode and string to determine their length
1588                    param_buffer_len.value = SQL_NTS
1589                else:
1590                    param_buffer_len.value = c_buf_len
1591   
1592                col_num += 1
1593            ret = SQLExecute(self.stmt_h)
1594            if ret != SQL_SUCCESS:
1595                #print param_valparam_buffer, param_buffer.value
1596                check_success(self, ret)
1597           
1598
1599            if not many_mode:
1600                self._NumOfRows()
1601                self._UpdateDesc()
1602                #self._BindCols()
1603           
1604        else:
1605            self.execdirect(query_string)
1606        return self
1607   
1608   
1609    def _SQLExecute(self):
1610        if not self.connection:
1611            self.close()
1612        ret = SQLExecute(self.stmt_h)
1613        if ret != SQL_SUCCESS:
1614            check_success(self, ret) 
1615       
1616   
1617    def execdirect(self, query_string):
1618        """Execute a query directly"""
1619        if not self.connection:
1620            self.close()
1621           
1622        self._free_stmt()
1623        self._last_param_types = None
1624        self.statement = None
1625        if type(query_string) == unicode:
1626            c_query_string = wchar_pointer(UCS_buf(query_string))
1627            ret = ODBC_API.SQLExecDirectW(self.stmt_h, c_query_string, len(query_string))
1628        else:
1629            c_query_string = ctypes.c_char_p(query_string)
1630            ret = ODBC_API.SQLExecDirect(self.stmt_h, c_query_string, len(query_string))
1631        check_success(self, ret)
1632        self._NumOfRows()
1633        self._UpdateDesc()
1634        #self._BindCols()
1635        return self
1636       
1637       
1638    def callproc(self, procname, args):
1639        if not self.connection:
1640            self.close()
1641        raise Warning('', 'Still not fully implemented')
1642        self._pram_io_list = [row[4] for row in self.procedurecolumns(procedure = procname).fetchall() if row[4] not in (SQL_RESULT_COL, SQL_RETURN_VALUE)]
1643       
1644        print('pram_io_list: '+str(self._pram_io_list))
1645
1646       
1647       
1648        call_escape = '{CALL '+procname
1649        if args:
1650            call_escape += '(' + ','.join(['?' for params in args]) + ')'
1651        call_escape += '}'
1652
1653        self.execute(call_escape, args, call_mode = True)
1654       
1655        result = []
1656
1657        for buf, buf_len, sql_type in self._ParamBufferList:
1658            if buf_len.value == -1:
1659                result.append(None)
1660            else:
1661                result.append(self.connection.output_converter[sql_type](buf.value))
1662        return result
1663
1664               
1665   
1666    def executemany(self, query_string, params_list = [None]):
1667        if not self.connection:
1668            self.close()
1669           
1670        for params in params_list:
1671            self.execute(query_string, params, many_mode = True)
1672        self._NumOfRows()
1673        self.rowcount = -1
1674        self._UpdateDesc()
1675        #self._BindCols()
1676       
1677   
1678
1679    def _CreateColBuf(self):
1680        if not self.connection:
1681            self.close()
1682        self._free_stmt(SQL_UNBIND)
1683        NOC = self._NumOfCols()
1684        self._ColBufferList = []
1685        bind_data = True
1686        for col_num in range(NOC):
1687            col_name = self.description[col_num][0]             
1688            col_size = self.description[col_num][2]     
1689            col_sql_data_type = self._ColTypeCodeList[col_num] 
1690
1691            target_type = SQL_data_type_dict[col_sql_data_type][2]
1692            dynamic_length = SQL_data_type_dict[col_sql_data_type][5]
1693            # set default size base on the column's sql data type
1694            total_buf_len = SQL_data_type_dict[col_sql_data_type][4]
1695           
1696            # over-write if there's pre-set size value for "large columns"
1697            if total_buf_len > 20500:
1698                total_buf_len = self._outputsize.get(None,total_buf_len)
1699            # over-write if there's pre-set size value for the "col_num" column
1700            total_buf_len = self._outputsize.get(col_num, total_buf_len)
1701
1702            # if the size of the buffer is very long, do not bind
1703            # because a large buffer decrease performance, and sometimes you only get a NULL value.
1704            # in that case use sqlgetdata instead.
1705            if col_size >= 1024:
1706                dynamic_length = True   
1707
1708            alloc_buffer = SQL_data_type_dict[col_sql_data_type][3](total_buf_len)
1709
1710            used_buf_len = c_ssize_t()
1711           
1712            force_unicode = self.connection.unicode_results
1713   
1714            if force_unicode and col_sql_data_type in (SQL_CHAR,SQL_VARCHAR,SQL_LONGVARCHAR):
1715                target_type = SQL_C_WCHAR
1716                alloc_buffer = create_buffer_u(total_buf_len)
1717           
1718            buf_cvt_func = self.connection.output_converter[self._ColTypeCodeList[col_num]]
1719           
1720            if bind_data:
1721                if dynamic_length:
1722                    bind_data = False
1723            self._ColBufferList.append([col_name, target_type, used_buf_len, ADDR(used_buf_len), alloc_buffer, ADDR(alloc_buffer), total_buf_len, buf_cvt_func, bind_data])     
1724           
1725            if bind_data:
1726                ret = ODBC_API.SQLBindCol(self.stmt_h, col_num + 1, target_type, ADDR(alloc_buffer), total_buf_len, ADDR(used_buf_len))
1727                if ret != SQL_SUCCESS:
1728                    check_success(self, ret)
1729   
1730    def _UpdateDesc(self):
1731        "Get the information of (name, type_code, display_size, internal_size, col_precision, scale, null_ok)" 
1732        if not self.connection:
1733            self.close()
1734           
1735        force_unicode = self.connection.unicode_results
1736        if force_unicode:
1737            Cname = create_buffer_u(1024)
1738        else:
1739            Cname = create_buffer(1024)
1740       
1741        Cname_ptr = c_short()
1742        Ctype_code = c_short()
1743        Csize = ctypes.c_size_t()
1744        Cdisp_size = c_ssize_t(0)
1745        CDecimalDigits = c_short()
1746        Cnull_ok = c_short()
1747        ColDescr = []
1748        self._ColTypeCodeList = []
1749        NOC = self._NumOfCols()
1750        for col in range(1, NOC+1):
1751           
1752            ret = ODBC_API.SQLColAttribute(self.stmt_h, col, SQL_DESC_DISPLAY_SIZE, ADDR(create_buffer(10)),
1753                10, ADDR(c_short()),ADDR(Cdisp_size))
1754            if ret != SQL_SUCCESS:
1755                check_success(self, ret)
1756           
1757            if force_unicode:
1758           
1759                ret = ODBC_API.SQLDescribeColW(self.stmt_h, col, Cname, len(Cname), ADDR(Cname_ptr),\
1760                    ADDR(Ctype_code),ADDR(Csize),ADDR(CDecimalDigits), ADDR(Cnull_ok))
1761                if ret != SQL_SUCCESS:
1762                    check_success(self, ret)
1763            else:
1764               
1765                ret = ODBC_API.SQLDescribeCol(self.stmt_h, col, Cname, len(Cname), ADDR(Cname_ptr),\
1766                    ADDR(Ctype_code),ADDR(Csize),ADDR(CDecimalDigits), ADDR(Cnull_ok))
1767                if ret != SQL_SUCCESS:
1768                    check_success(self, ret)
1769           
1770            col_name = Cname.value
1771            if lowercase:
1772                col_name = col_name.lower()
1773            #(name, type_code, display_size,
1774
1775            ColDescr.append((col_name, SQL_data_type_dict.get(Ctype_code.value,(Ctype_code.value,))[0],Cdisp_size.value,\
1776                Csize.value, Csize.value,CDecimalDigits.value,Cnull_ok.value == 1 and True or False))
1777            self._ColTypeCodeList.append(Ctype_code.value)
1778       
1779        if len(ColDescr) > 0:
1780            self.description = ColDescr
1781            # Create the row type before fetching.
1782            self._row_type = self.row_type_callable(self)
1783        else:
1784            self.description = None
1785        self._CreateColBuf()
1786           
1787   
1788    def _NumOfRows(self):
1789        """Get the number of rows"""
1790        if not self.connection:
1791            self.close()
1792           
1793        NOR = c_ssize_t()
1794        ret = SQLRowCount(self.stmt_h, ADDR(NOR))
1795        if ret != SQL_SUCCESS:
1796            check_success(self, ret)
1797        self.rowcount = NOR.value
1798        return self.rowcount   
1799
1800   
1801    def _NumOfCols(self):
1802        """Get the number of cols"""
1803        if not self.connection:
1804            self.close()
1805           
1806        NOC = c_short()
1807        ret = SQLNumResultCols(self.stmt_h, ADDR(NOC))
1808        if ret != SQL_SUCCESS:
1809            check_success(self, ret)
1810        return NOC.value
1811
1812
1813    def fetchall(self):
1814        if not self.connection:
1815            self.close()
1816           
1817        rows = []
1818        while True:
1819            row = self.fetchone()
1820            if row is None:
1821                break
1822            rows.append(row)
1823        return rows
1824
1825
1826    def fetchmany(self, num = None):
1827        if not self.connection:
1828            self.close()
1829           
1830        if num is None:
1831            num = self.arraysize
1832        rows = []
1833       
1834        while len(rows) < num:
1835            row = self.fetchone()
1836            if row is None:
1837                break
1838            rows.append(row)
1839        return rows
1840
1841
1842    def fetchone(self):
1843        if not self.connection:
1844            self.close()
1845           
1846        ret = SQLFetch(self.stmt_h)
1847       
1848        if ret in (SQL_SUCCESS,SQL_SUCCESS_WITH_INFO):           
1849            '''Bind buffers for the record set columns'''
1850           
1851            value_list = []
1852            col_num = 1
1853            for col_name, target_type, used_buf_len, ADDR_used_buf_len, alloc_buffer, ADDR_alloc_buffer, total_buf_len, buf_cvt_func, bind_data in self._ColBufferList:
1854                raw_data_parts = []
1855                while 1:
1856                    if bind_data:
1857                        ret = SQL_SUCCESS
1858                    else:
1859                        ret = SQLGetData(self.stmt_h, col_num, target_type, ADDR_alloc_buffer, total_buf_len, ADDR_used_buf_len)                       
1860                    if ret == SQL_SUCCESS:
1861                        if used_buf_len.value == SQL_NULL_DATA:
1862                            value_list.append(None)                 
1863                        else:
1864                            if raw_data_parts == []:
1865                                # Means no previous data, no need to combine
1866                                if target_type == SQL_C_BINARY:
1867                                    value_list.append(buf_cvt_func(alloc_buffer.raw[:used_buf_len.value]))
1868                                elif target_type == SQL_C_WCHAR:
1869                                    value_list.append(buf_cvt_func(from_buffer_u(alloc_buffer)))
1870                                else:
1871                                    value_list.append(buf_cvt_func(alloc_buffer.value))
1872                            else:
1873                                # There are previous fetched raw data to combine
1874                                if target_type == SQL_C_BINARY:
1875                                    raw_data_parts.append(alloc_buffer.raw[:used_buf_len.value])
1876                                elif target_type == SQL_C_WCHAR:
1877                                    raw_data_parts.append(from_buffer_u(alloc_buffer))
1878                                else:
1879                                    raw_data_parts.append(alloc_buffer.value)
1880                        break                   
1881                   
1882                    elif ret == SQL_SUCCESS_WITH_INFO:
1883                        # Means the data is only partial
1884                        if target_type == SQL_C_BINARY:
1885                            raw_data_parts.append(alloc_buffer.raw)
1886                        else:
1887                            raw_data_parts.append(alloc_buffer.value) 
1888         
1889                    elif ret == SQL_NO_DATA:
1890                        # Means all data has been transmitted
1891                        break
1892                    else:
1893                        check_success(self, ret)   
1894                   
1895                if raw_data_parts != []:
1896                    if py_v3:
1897                        if target_type != SQL_C_BINARY:
1898                            raw_value = ''.join(raw_data_parts)
1899                        else:
1900                            raw_value = BLANK_BYTE.join(raw_data_parts)
1901                    else:
1902                        raw_value = ''.join(raw_data_parts)
1903
1904                    value_list.append(buf_cvt_func(raw_value))
1905                col_num += 1
1906
1907            return self._row_type(value_list)
1908       
1909        else:
1910            if ret == SQL_NO_DATA_FOUND:
1911               
1912                return None
1913            else:
1914                check_success(self, ret)
1915               
1916    def __next__(self):
1917        return self.next()
1918   
1919    def next(self):         
1920        row = self.fetchone()
1921        if row is None:
1922            raise(StopIteration)
1923        return row
1924   
1925    def __iter__(self):
1926        return self
1927
1928   
1929    def skip(self, count = 0):
1930        if not self.connection:
1931            self.close()
1932           
1933        for i in range(count):
1934            ret = ODBC_API.SQLFetchScroll(self.stmt_h, SQL_FETCH_NEXT, 0)
1935            if ret != SQL_SUCCESS:
1936                check_success(self, ret)
1937        return None   
1938   
1939   
1940       
1941    def nextset(self):
1942        if not self.connection:
1943            self.close()
1944           
1945        ret = ODBC_API.SQLMoreResults(self.stmt_h)
1946        if ret not in (SQL_SUCCESS, SQL_NO_DATA):
1947            check_success(self, ret)
1948           
1949        if ret == SQL_NO_DATA:
1950            self._free_stmt()
1951            return False
1952        else:
1953            self._NumOfRows()
1954            self._UpdateDesc()
1955            #self._BindCols()
1956        return True
1957   
1958   
1959    def _free_stmt(self, free_type = None):
1960        if not self.connection:
1961            self.close()
1962           
1963        if not self.connection.connected:
1964            raise ProgrammingError('HY000','Attempt to use a closed connection.')
1965       
1966        #self.description = None
1967        #self.rowcount = -1
1968        if free_type in (SQL_CLOSE, None):
1969            ret = ODBC_API.SQLFreeStmt(self.stmt_h, SQL_CLOSE)
1970            if ret != SQL_SUCCESS:
1971                check_success(self, ret)
1972        if free_type in (SQL_UNBIND, None):
1973            ret = ODBC_API.SQLFreeStmt(self.stmt_h, SQL_UNBIND)
1974            if ret != SQL_SUCCESS:
1975                check_success(self, ret)
1976        if free_type in (SQL_RESET_PARAMS, None):   
1977            ret = ODBC_API.SQLFreeStmt(self.stmt_h, SQL_RESET_PARAMS)
1978            if ret != SQL_SUCCESS:
1979                check_success(self, ret)
1980       
1981   
1982   
1983    def getTypeInfo(self, sqlType = None):
1984        if not self.connection:
1985            self.close()
1986           
1987        if sqlType is None:
1988            type = SQL_ALL_TYPES
1989        else:
1990            type = sqlType
1991        ret = ODBC_API.SQLGetTypeInfo(self.stmt_h, type)
1992        if ret in (SQL_SUCCESS, SQL_SUCCESS_WITH_INFO):
1993            self._NumOfRows()
1994            self._UpdateDesc()
1995            #self._BindCols()
1996            return self.fetchone()
1997   
1998   
1999    def tables(self, table=None, catalog=None, schema=None, tableType=None):
2000        """Return a list with all tables"""
2001        if not self.connection:
2002            self.close()
2003           
2004        l_catalog = l_schema = l_table = l_tableType = 0
2005       
2006        if unicode in [type(x) for x in (table, catalog, schema,tableType)]:
2007            string_p = lambda x:wchar_pointer(UCS_buf(x))
2008            API_f = ODBC_API.SQLTablesW
2009        else:
2010            string_p = ctypes.c_char_p
2011            API_f = ODBC_API.SQLTables
2012           
2013       
2014       
2015        if catalog is not None:
2016            l_catalog = len(catalog)
2017            catalog = string_p(catalog)
2018
2019        if schema is not None:
2020            l_schema = len(schema)
2021            schema = string_p(schema)
2022           
2023        if table is not None:
2024            l_table = len(table)
2025            table = string_p(table)
2026           
2027        if tableType is not None:
2028            l_tableType = len(tableType)
2029            tableType = string_p(tableType)
2030       
2031        self._free_stmt()
2032        self._last_param_types = None
2033        self.statement = None
2034        ret = API_f(self.stmt_h,
2035                                catalog, l_catalog,
2036                                schema, l_schema,
2037                                table, l_table,
2038                                tableType, l_tableType)
2039        check_success(self, ret)
2040   
2041        self._NumOfRows()
2042        self._UpdateDesc()
2043        #self._BindCols()
2044        return self
2045   
2046   
2047    def columns(self, table=None, catalog=None, schema=None, column=None):
2048        """Return a list with all columns"""   
2049        if not self.connection:
2050            self.close()
2051           
2052        l_catalog = l_schema = l_table = l_column = 0
2053       
2054        if unicode in [type(x) for x in (table, catalog, schema,column)]:
2055            string_p = lambda x:wchar_pointer(UCS_buf(x))
2056            API_f = ODBC_API.SQLColumnsW
2057        else:
2058            string_p = ctypes.c_char_p
2059            API_f = ODBC_API.SQLColumns
2060           
2061       
2062       
2063        if catalog is not None:
2064            l_catalog = len(catalog)
2065            catalog = string_p(catalog)
2066        if schema is not None:
2067            l_schema = len(schema)
2068            schema = string_p(schema)
2069        if table is not None:
2070            l_table = len(table)
2071            table = string_p(table)
2072        if column is not None:
2073            l_column = len(column)
2074            column = string_p(column)
2075           
2076        self._free_stmt()
2077        self._last_param_types = None
2078        self.statement = None
2079           
2080        ret = API_f(self.stmt_h,
2081                    catalog, l_catalog,
2082                    schema, l_schema,
2083                    table, l_table,
2084                    column, l_column)
2085        check_success(self, ret)
2086
2087        self._NumOfRows()
2088        self._UpdateDesc()
2089        #self._BindCols()
2090        return self
2091   
2092   
2093    def primaryKeys(self, table=None, catalog=None, schema=None):
2094        if not self.connection:
2095            self.close()
2096           
2097        l_catalog = l_schema = l_table = 0
2098       
2099        if unicode in [type(x) for x in (table, catalog, schema)]:
2100            string_p = lambda x:wchar_pointer(UCS_buf(x))
2101            API_f = ODBC_API.SQLPrimaryKeysW
2102        else:
2103            string_p = ctypes.c_char_p
2104            API_f = ODBC_API.SQLPrimaryKeys
2105           
2106       
2107       
2108        if catalog is not None:
2109            l_catalog = len(catalog)
2110            catalog = string_p(catalog)
2111           
2112        if schema is not None:
2113            l_schema = len(schema)
2114            schema = string_p(schema)
2115           
2116        if table is not None:
2117            l_table = len(table)
2118            table = string_p(table)
2119           
2120        self._free_stmt()
2121        self._last_param_types = None
2122        self.statement = None
2123           
2124        ret = API_f(self.stmt_h,
2125                    catalog, l_catalog,
2126                    schema, l_schema,
2127                    table, l_table)
2128        check_success(self, ret)
2129       
2130        self._NumOfRows()
2131        self._UpdateDesc()
2132        #self._BindCols()
2133        return self
2134   
2135       
2136    def foreignKeys(self, table=None, catalog=None, schema=None, foreignTable=None, foreignCatalog=None, foreignSchema=None):
2137        if not self.connection:
2138            self.close()
2139           
2140        l_catalog = l_schema = l_table = l_foreignTable = l_foreignCatalog = l_foreignSchema = 0
2141       
2142        if unicode in [type(x) for x in (table, catalog, schema,foreignTable,foreignCatalog,foreignSchema)]:
2143            string_p = lambda x:wchar_pointer(UCS_buf(x))
2144            API_f = ODBC_API.SQLForeignKeysW
2145        else:
2146            string_p = ctypes.c_char_p
2147            API_f = ODBC_API.SQLForeignKeys
2148       
2149        if catalog is not None:
2150            l_catalog = len(catalog)
2151            catalog = string_p(catalog)
2152        if schema is not None:
2153            l_schema = len(schema)
2154            schema = string_p(schema)
2155        if table is not None:
2156            l_table = len(table)
2157            table = string_p(table)
2158        if foreignTable is not None:
2159            l_foreignTable = len(foreignTable)
2160            foreignTable = string_p(foreignTable)
2161        if foreignCatalog is not None:
2162            l_foreignCatalog = len(foreignCatalog)
2163            foreignCatalog = string_p(foreignCatalog)
2164        if foreignSchema is not None:
2165            l_foreignSchema = len(foreignSchema)
2166            foreignSchema = string_p(foreignSchema)
2167       
2168        self._free_stmt()
2169        self._last_param_types = None
2170        self.statement = None
2171       
2172        ret = API_f(self.stmt_h,
2173                    catalog, l_catalog,
2174                    schema, l_schema,
2175                    table, l_table,
2176                    foreignCatalog, l_foreignCatalog,
2177                    foreignSchema, l_foreignSchema,
2178                    foreignTable, l_foreignTable)
2179        check_success(self, ret)
2180       
2181        self._NumOfRows()
2182        self._UpdateDesc()
2183        #self._BindCols()
2184        return self
2185   
2186   
2187    def procedurecolumns(self, procedure=None, catalog=None, schema=None, column=None):
2188        if not self.connection:
2189            self.close()
2190           
2191        l_catalog = l_schema = l_procedure = l_column = 0
2192        if unicode in [type(x) for x in (procedure, catalog, schema,column)]:
2193            string_p = lambda x:wchar_pointer(UCS_buf(x))
2194            API_f = ODBC_API.SQLProcedureColumnsW
2195        else:
2196            string_p = ctypes.c_char_p
2197            API_f = ODBC_API.SQLProcedureColumns
2198           
2199       
2200        if catalog is not None:
2201            l_catalog = len(catalog)
2202            catalog = string_p(catalog)
2203        if schema is not None:
2204            l_schema = len(schema)
2205            schema = string_p(schema)
2206        if procedure is not None:
2207            l_procedure = len(procedure)
2208            procedure = string_p(procedure)
2209        if column is not None:
2210            l_column = len(column)
2211            column = string_p(column)
2212           
2213       
2214        self._free_stmt()
2215        self._last_param_types = None
2216        self.statement = None
2217           
2218        ret = API_f(self.stmt_h,
2219                    catalog, l_catalog,
2220                    schema, l_schema,
2221                    procedure, l_procedure,
2222                    column, l_column)
2223        check_success(self, ret)
2224       
2225        self._NumOfRows()
2226        self._UpdateDesc()
2227        return self
2228       
2229   
2230    def procedures(self, procedure=None, catalog=None, schema=None):
2231        if not self.connection:
2232            self.close()
2233           
2234        l_catalog = l_schema = l_procedure = 0
2235       
2236        if unicode in [type(x) for x in (procedure, catalog, schema)]:
2237            string_p = lambda x:wchar_pointer(UCS_buf(x))
2238            API_f = ODBC_API.SQLProceduresW
2239        else:
2240            string_p = ctypes.c_char_p
2241            API_f = ODBC_API.SQLProcedures
2242           
2243       
2244       
2245        if catalog is not None:
2246            l_catalog = len(catalog)
2247            catalog = string_p(catalog)
2248        if schema is not None:
2249            l_schema = len(schema)
2250            schema = string_p(schema)
2251        if procedure is not None:
2252            l_procedure = len(procedure)
2253            procedure = string_p(procedure)
2254           
2255       
2256        self._free_stmt()
2257        self._last_param_types = None
2258        self.statement = None
2259           
2260        ret = API_f(self.stmt_h,
2261                    catalog, l_catalog,
2262                    schema, l_schema,
2263                    procedure, l_procedure)
2264        check_success(self, ret)
2265       
2266        self._NumOfRows()
2267        self._UpdateDesc()
2268        return self
2269
2270
2271    def statistics(self, table, catalog=None, schema=None, unique=False, quick=True):
2272        if not self.connection:
2273            self.close()
2274           
2275        l_table = l_catalog = l_schema = 0
2276       
2277        if unicode in [type(x) for x in (table, catalog, schema)]:
2278            string_p = lambda x:wchar_pointer(UCS_buf(x))
2279            API_f = ODBC_API.SQLStatisticsW
2280        else:
2281            string_p = ctypes.c_char_p
2282            API_f = ODBC_API.SQLStatistics
2283           
2284   
2285        if catalog is not None:
2286            l_catalog = len(catalog)
2287            catalog = string_p(catalog)
2288        if schema is not None:
2289            l_schema = len(schema)
2290            schema = string_p(schema)
2291        if table is not None:
2292            l_table = len(table)
2293            table = string_p(table)
2294           
2295        if unique:
2296            Unique = SQL_INDEX_UNIQUE
2297        else:
2298            Unique = SQL_INDEX_ALL
2299        if quick:
2300            Reserved = SQL_QUICK
2301        else:
2302            Reserved = SQL_ENSURE
2303       
2304        self._free_stmt()
2305        self._last_param_types = None
2306        self.statement = None
2307       
2308        ret = API_f(self.stmt_h,
2309                    catalog, l_catalog,
2310                    schema, l_schema,
2311                    table, l_table,
2312                    Unique, Reserved)
2313        check_success(self, ret)
2314   
2315        self._NumOfRows()
2316        self._UpdateDesc()
2317        #self._BindCols()
2318        return self
2319   
2320
2321    def commit(self):
2322        if not self.connection:
2323            self.close()
2324        self.connection.commit()
2325
2326    def rollback(self):
2327        if not self.connection:
2328            self.close()
2329        self.connection.rollback()
2330   
2331    def setoutputsize(self, size, column = None):
2332        if not self.connection:
2333            self.close()
2334        self._outputsize[column] = size
2335   
2336    def setinputsizes(self, sizes):
2337        if not self.connection:
2338            self.close()
2339        self._inputsizers = [size for size in sizes]
2340
2341
2342    def close(self):
2343        """ Call SQLCloseCursor API to free the statement handle"""
2344#        ret = ODBC_API.SQLCloseCursor(self.stmt_h)
2345#        check_success(self, ret)
2346#       
2347        if self.connection.connected:
2348            ret = ODBC_API.SQLFreeStmt(self.stmt_h, SQL_CLOSE)
2349            check_success(self, ret)
2350
2351            ret = ODBC_API.SQLFreeStmt(self.stmt_h, SQL_UNBIND)
2352            check_success(self, ret)
2353
2354            ret = ODBC_API.SQLFreeStmt(self.stmt_h, SQL_RESET_PARAMS)
2355            check_success(self, ret)
2356
2357            ret = ODBC_API.SQLFreeHandle(SQL_HANDLE_STMT, self.stmt_h)
2358            check_success(self, ret)
2359       
2360       
2361        self.closed = True
2362   
2363   
2364    def __del__(self): 
2365        if not self.closed:
2366            self.close()
2367   
2368    def __exit__(self, type, value, traceback):
2369        if not self.connection:
2370            self.close()
2371           
2372        if value:
2373            self.rollback()
2374        else:
2375            self.commit()
2376           
2377        self.close()
2378   
2379           
2380    def __enter__(self):
2381        return self
2382
2383
2384
2385
2386
2387   
2388# This class implement a odbc connection.
2389#
2390#
2391connection_timeout = 0
2392
2393class Connection:
2394    def __init__(self, connectString = '', autocommit = False, ansi = False, timeout = 0, unicode_results = use_unicode, readonly = False, **kargs):
2395        """Init variables and connect to the engine"""
2396        self.connected = 0
2397        self.type_size_dic = {}
2398        self.ansi = False
2399        self.unicode_results = False
2400        self.dbc_h = ctypes.c_void_p()
2401        self.autocommit = autocommit
2402        self.readonly = False
2403        # the query timeout value
2404        self.timeout = 0
2405        # self._cursors = []
2406        for key, value in list(kargs.items()):
2407            connectString = connectString + key + '=' + value + ';'
2408        self.connectString = connectString
2409
2410       
2411        self.clear_output_converters()
2412
2413        try:
2414            lock.acquire()
2415            if shared_env_h is None:
2416                #Initialize an enviroment if it is not created.
2417                AllocateEnv()
2418        finally:
2419            lock.release()
2420       
2421        # Allocate an DBC handle self.dbc_h under the environment shared_env_h
2422        # This DBC handle is actually the basis of a "connection"
2423        # The handle of self.dbc_h will be used to connect to a certain source
2424        # in the self.connect and self.ConnectByDSN method
2425       
2426        ret = ODBC_API.SQLAllocHandle(SQL_HANDLE_DBC, shared_env_h, ADDR(self.dbc_h))
2427        check_success(self, ret)
2428
2429        self.connection_timeout = connection_timeout
2430        if self.connection_timeout != 0:
2431            self.set_connection_timeout(connection_timeout)
2432
2433       
2434        self.connect(connectString, autocommit, ansi, timeout, unicode_results, readonly)
2435       
2436    def set_connection_timeout(self,connection_timeout):
2437        self.connection_timeout = connection_timeout
2438        ret = ODBC_API.SQLSetConnectAttr(self.dbc_h, SQL_ATTR_CONNECTION_TIMEOUT, connection_timeout, SQL_IS_UINTEGER);
2439        check_success(self, ret)
2440     
2441    def connect(self, connectString = '', autocommit = False, ansi = False, timeout = 0, unicode_results = use_unicode, readonly = False):
2442        """Connect to odbc, using connect strings and set the connection's attributes like autocommit and timeout
2443        by calling SQLSetConnectAttr
2444        """
2445
2446        # Before we establish the connection by the connection string
2447        # Set the connection's attribute of "timeout" (Actully LOGIN_TIMEOUT)
2448        if timeout != 0:
2449            ret = ODBC_API.SQLSetConnectAttr(self.dbc_h, SQL_ATTR_LOGIN_TIMEOUT, timeout, SQL_IS_UINTEGER);
2450            check_success(self, ret)
2451        # Create one connection with a connect string by calling SQLDriverConnect
2452        # and make self.dbc_h the handle of this connection
2453
2454
2455        # Convert the connetsytring to encoded string
2456        # so it can be converted to a ctypes c_char array object
2457
2458       
2459        self.ansi = ansi
2460        if not ansi:
2461            c_connectString = wchar_pointer(UCS_buf(self.connectString))
2462            odbc_func = ODBC_API.SQLDriverConnectW
2463        else:
2464            c_connectString = ctypes.c_char_p(self.connectString)
2465            odbc_func = ODBC_API.SQLDriverConnect
2466
2467        # With unixODBC, SQLDriverConnect will intermittently fail with error:
2468        #    [01000] [unixODBC][Driver Manager]Can't open lib '/path/to/so' : file not found"
2469        # or:
2470        #    [01000] [unixODBC][Driver Manager]Can't open lib '/path/to/so' : (null)"
2471        # when called concurrently by more than one threads. So, we have to
2472        # use a lock to serialize the calls. By the way, the error is much
2473        # less likely to happen if ODBC Tracing is enabled, likely due to the
2474        # implicit serialization caused by writing to trace file.
2475        if ODBC_API._name != 'odbc32':
2476            try:
2477                lock.acquire()
2478                ret = odbc_func(self.dbc_h, 0, c_connectString, len(self.connectString), None, 0, None, SQL_DRIVER_NOPROMPT)
2479            finally:
2480                lock.release()
2481        else:
2482            ret = odbc_func(self.dbc_h, 0, c_connectString, len(self.connectString), None, 0, None, SQL_DRIVER_NOPROMPT)
2483        check_success(self, ret)
2484           
2485       
2486        # Set the connection's attribute of "autocommit"
2487        #
2488        self.autocommit = autocommit
2489       
2490        if self.autocommit == True:
2491            ret = ODBC_API.SQLSetConnectAttr(self.dbc_h, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_ON, SQL_IS_UINTEGER)
2492        else:
2493            ret = ODBC_API.SQLSetConnectAttr(self.dbc_h, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, SQL_IS_UINTEGER)
2494        check_success(self, ret)
2495       
2496        # Set the connection's attribute of "readonly"
2497        #
2498        self.readonly = readonly
2499        if self.readonly == True:
2500            ret = ODBC_API.SQLSetConnectAttr(self.dbc_h, SQL_ATTR_ACCESS_MODE, SQL_MODE_READ_ONLY, SQL_IS_UINTEGER)
2501            check_success(self, ret)
2502       
2503        self.unicode_results = unicode_results
2504        self.connected = 1
2505        self.update_db_special_info()
2506       
2507    def clear_output_converters(self):
2508        self.output_converter = {}
2509        for sqltype, profile in SQL_data_type_dict.items():
2510            self.output_converter[sqltype] = profile[1]
2511       
2512       
2513    def add_output_converter(self, sqltype, func):
2514        self.output_converter[sqltype] = func
2515   
2516       
2517
2518    def ConnectByDSN(self, dsn, user, passwd = ''):
2519        """Connect to odbc, we need dsn, user and optionally password"""
2520        self.dsn = dsn
2521        self.user = user
2522        self.passwd = passwd
2523
2524        sn = create_buffer(dsn)
2525        un = create_buffer(user)       
2526        pw = create_buffer(passwd)
2527       
2528        ret = ODBC_API.SQLConnect(self.dbc_h, sn, len(sn), un, len(un), pw, len(pw))
2529        check_success(self, ret)
2530
2531        self.update_db_special_info()
2532        self.connected = 1
2533       
2534       
2535    def cursor(self, row_type_callable=None):
2536        #self.settimeout(self.timeout)
2537        if not self.connected:
2538            raise ProgrammingError('HY000','Attempt to use a closed connection.')
2539        cur = Cursor(self, row_type_callable=row_type_callable)
2540        # self._cursors.append(cur)
2541        return cur
2542
2543    def update_db_special_info(self):
2544        for sql_type in (
2545            SQL_TYPE_TIMESTAMP,
2546            SQL_TYPE_DATE,
2547            SQL_TYPE_TIME,
2548            SQL_SS_TIME2,
2549        ):
2550            cur = Cursor(self)
2551
2552            try:
2553                info_tuple = cur.getTypeInfo(sql_type)
2554                if info_tuple is not None:
2555                    self.type_size_dic[sql_type] = info_tuple[2], info_tuple[14]
2556            except:
2557                pass
2558            cur.close()
2559           
2560        self.support_SQLDescribeParam = False
2561        try:
2562            driver_name = self.getinfo(SQL_DRIVER_NAME)
2563            if any(x in driver_name for x in ('SQLSRV','ncli','libsqlncli')):
2564                self.support_SQLDescribeParam = True
2565        except:
2566            pass
2567   
2568    def commit(self):
2569        if not self.connected:
2570            raise ProgrammingError('HY000','Attempt to use a closed connection.')
2571       
2572        ret = SQLEndTran(SQL_HANDLE_DBC, self.dbc_h, SQL_COMMIT)
2573        if ret != SQL_SUCCESS:
2574            check_success(self, ret)
2575
2576    def rollback(self):
2577        if not self.connected:
2578            raise ProgrammingError('HY000','Attempt to use a closed connection.')
2579        ret = SQLEndTran(SQL_HANDLE_DBC, self.dbc_h, SQL_ROLLBACK)
2580        if ret != SQL_SUCCESS:
2581            check_success(self, ret)
2582       
2583   
2584   
2585    def getinfo(self,infotype):
2586        if infotype not in list(aInfoTypes.keys()):
2587            raise ProgrammingError('HY000','Invalid getinfo value: '+str(infotype))
2588       
2589       
2590        if aInfoTypes[infotype] == 'GI_UINTEGER':
2591            total_buf_len = 1000
2592            alloc_buffer = ctypes.c_ulong()
2593            used_buf_len = c_short()
2594            ret = ODBC_API.SQLGetInfo(self.dbc_h,infotype,ADDR(alloc_buffer), total_buf_len,\
2595                    ADDR(used_buf_len))
2596            check_success(self, ret)
2597            result = alloc_buffer.value
2598           
2599        elif aInfoTypes[infotype] == 'GI_USMALLINT':
2600            total_buf_len = 1000
2601            alloc_buffer = ctypes.c_ushort()
2602            used_buf_len = c_short()
2603            ret = ODBC_API.SQLGetInfo(self.dbc_h,infotype,ADDR(alloc_buffer), total_buf_len,\
2604                    ADDR(used_buf_len))
2605            check_success(self, ret)
2606            result = alloc_buffer.value
2607
2608        else:
2609            total_buf_len = 1000
2610            alloc_buffer = create_buffer(total_buf_len)
2611            used_buf_len = c_short()
2612            if self.ansi:
2613                API_f = ODBC_API.SQLGetInfo
2614            else:
2615                API_f = ODBC_API.SQLGetInfoW
2616            ret = API_f(self.dbc_h,infotype,ADDR(alloc_buffer), total_buf_len,\
2617                    ADDR(used_buf_len))
2618            check_success(self, ret)
2619            if self.ansi:
2620                result = alloc_buffer.value
2621            else:
2622                result = UCS_dec(alloc_buffer)
2623            if aInfoTypes[infotype] == 'GI_YESNO':
2624                if unicode(result[0]) == unicode('Y'):
2625                    result = True
2626                else:
2627                    result = False
2628                   
2629        return result
2630       
2631    def __exit__(self, type, value, traceback):
2632        if value:
2633            self.rollback()
2634        else:
2635            self.commit()
2636           
2637        if self.connected:
2638            self.close()
2639           
2640    def __enter__(self):
2641        return self
2642
2643    def __del__(self):
2644        if self.connected:
2645            self.close()
2646       
2647    def close(self):
2648        if not self.connected:
2649            raise ProgrammingError('HY000','Attempt to close a closed connection.')
2650        # for cur in self._cursors:
2651            # if not cur is None:
2652                # if not cur.closed:
2653                    # cur.close()
2654       
2655        if self.connected:
2656            #if DEBUG:print 'disconnect'
2657            if not self.autocommit:
2658                self.rollback()
2659            ret = ODBC_API.SQLDisconnect(self.dbc_h)
2660            check_success(self, ret)
2661        #if DEBUG:print 'free dbc'
2662        ret = ODBC_API.SQLFreeHandle(SQL_HANDLE_DBC, self.dbc_h)
2663        check_success(self, ret)
2664#        if shared_env_h.value:
2665#            #if DEBUG:print 'env'
2666#            ret = ODBC_API.SQLFreeHandle(SQL_HANDLE_ENV, shared_env_h)
2667#            check_success(shared_env_h, ret)
2668        self.connected = 0
2669       
2670odbc = Connection
2671connect = odbc
2672'''
2673def connect(connectString = '', autocommit = False, ansi = False, timeout = 0, unicode_results = False, readonly = False, **kargs):
2674    return odbc(connectString, autocommit, ansi, timeout, unicode_results, readonly, kargs)
2675'''
2676
2677def drivers():
2678    if sys.platform not in ('win32','cli'):
2679        raise Exception('This function is available for use in Windows only.')
2680    try:
2681        lock.acquire()
2682        if shared_env_h is None:
2683            AllocateEnv()
2684    finally:
2685        lock.release()
2686   
2687    DriverDescription = create_buffer_u(1000)
2688    BufferLength1 = c_short(1000)
2689    DescriptionLength = c_short()
2690    DriverAttributes = create_buffer_u(1000)
2691    BufferLength2 = c_short(1000)
2692    AttributesLength = c_short()
2693    ret = SQL_SUCCESS
2694    DriverList = []
2695    Direction = SQL_FETCH_FIRST
2696    while ret != SQL_NO_DATA:
2697        ret = ODBC_API.SQLDriversW(shared_env_h, Direction , DriverDescription , BufferLength1
2698                    , ADDR(DescriptionLength), DriverAttributes, BufferLength2, ADDR(AttributesLength))
2699        check_success(shared_env_h, ret)
2700        DriverList.append(DriverDescription.value)
2701        if Direction == SQL_FETCH_FIRST:
2702            Direction = SQL_FETCH_NEXT
2703    return DriverList
2704       
2705
2706
2707
2708def win_create_mdb(mdb_path, sort_order = "General\0\0"):
2709    if sys.platform not in ('win32','cli'):
2710        raise Exception('This function is available for use in Windows only.')
2711   
2712    mdb_driver = [d for d in drivers() if 'Microsoft Access Driver (*.mdb' in d]
2713    if mdb_driver == []:
2714        raise Exception('Access Driver is not found.')
2715    else:
2716        driver_name = mdb_driver[0].encode('mbcs')
2717
2718
2719    #CREATE_DB=<path name> <sort order>
2720    ctypes.windll.ODBCCP32.SQLConfigDataSource.argtypes = [ctypes.c_void_p,ctypes.c_ushort,ctypes.c_char_p,ctypes.c_char_p]
2721
2722    if py_v3:
2723        c_Path =  bytes("CREATE_DB=" + mdb_path + " " + sort_order,'mbcs')
2724    else:
2725        c_Path =  "CREATE_DB=" + mdb_path + " " + sort_order
2726    ODBC_ADD_SYS_DSN = 1
2727   
2728   
2729    ret = ctypes.windll.ODBCCP32.SQLConfigDataSource(None,ODBC_ADD_SYS_DSN,driver_name, c_Path)
2730    if not ret:
2731        raise Exception('Failed to create Access mdb file - "%s". Please check file path, permission and Access driver readiness.' %mdb_path)
2732   
2733   
2734def win_connect_mdb(mdb_path):
2735    if sys.platform not in ('win32','cli'):
2736        raise Exception('This function is available for use in Windows only.')
2737   
2738    mdb_driver = [d for d in drivers() if 'Microsoft Access Driver (*.mdb' in d]
2739    if mdb_driver == []:
2740        raise Exception('Access Driver is not found.')
2741    else:
2742        driver_name = mdb_driver[0]
2743
2744    return connect('Driver={'+driver_name+"};DBQ="+mdb_path, unicode_results = use_unicode, readonly = False)
2745   
2746   
2747   
2748def win_compact_mdb(mdb_path, compacted_mdb_path, sort_order = "General\0\0"):
2749    if sys.platform not in ('win32','cli'):
2750        raise Exception('This function is available for use in Windows only.')
2751   
2752   
2753    mdb_driver = [d for d in drivers() if 'Microsoft Access Driver (*.mdb' in d]
2754    if mdb_driver == []:
2755        raise Exception('Access Driver is not found.')
2756    else:
2757        driver_name = mdb_driver[0].encode('mbcs')
2758   
2759    #COMPACT_DB=<source path> <destination path> <sort order>
2760    ctypes.windll.ODBCCP32.SQLConfigDataSource.argtypes = [ctypes.c_void_p,ctypes.c_ushort,ctypes.c_char_p,ctypes.c_char_p]
2761    #driver_name = "Microsoft Access Driver (*.mdb)"
2762    if py_v3:
2763        c_Path = bytes("COMPACT_DB=" + mdb_path + " " + compacted_mdb_path + " " + sort_order,'mbcs')
2764        #driver_name = bytes(driver_name,'mbcs')
2765    else:
2766        c_Path = "COMPACT_DB=" + mdb_path + " " + compacted_mdb_path + " " + sort_order
2767
2768    ODBC_ADD_SYS_DSN = 1
2769    ret = ctypes.windll.ODBCCP32.SQLConfigDataSource(None,ODBC_ADD_SYS_DSN,driver_name, c_Path)
2770    if not ret:
2771        raise Exception('Failed to compact Access mdb file - "%s". Please check file path, permission and Access driver readiness.' %compacted_mdb_path)
2772       
2773
2774def dataSources():
2775    """Return a list with [name, descrition]"""
2776    dsn = create_buffer(1024)
2777    desc = create_buffer(1024)
2778    dsn_len = c_short()
2779    desc_len = c_short()
2780    dsn_list = {}
2781    try:
2782        lock.acquire()
2783        if shared_env_h is None:
2784            AllocateEnv()
2785    finally:
2786        lock.release()
2787    while 1:
2788        ret = ODBC_API.SQLDataSources(shared_env_h, SQL_FETCH_NEXT, \
2789            dsn, len(dsn), ADDR(dsn_len), desc, len(desc), ADDR(desc_len))
2790        if ret == SQL_NO_DATA_FOUND:
2791            break
2792        elif not ret in (SQL_SUCCESS, SQL_SUCCESS_WITH_INFO):
2793            ctrl_err(SQL_HANDLE_ENV, shared_env_h, ret)
2794        else:
2795            dsn_list[dsn.value] = desc.value
2796    return dsn_list
2797
2798
2799def monkey_patch_for_gevent():
2800    import functools, gevent
2801    apply_e = gevent.get_hub().threadpool.apply_e
2802    def monkey_patch(func):
2803        @functools.wraps(func)
2804        def wrap(*args, **kwargs):
2805            #if DEBUG:print('%s called with %s %s' % (func, args, kwargs))
2806            return apply_e(Exception, func, args, kwargs)
2807        return wrap
2808    for attr in dir(ODBC_API):
2809        if attr.startswith('SQL') and hasattr(getattr(ODBC_API, attr), 'argtypes'):
2810            setattr(ODBC_API, attr, monkey_patch(getattr(ODBC_API, attr)))
Note: See TracBrowser for help on using the repository browser.