My favorites | Sign in
Project Home Downloads Wiki
READ-ONLY: This project has been archived. For more information see this post.
Search
for
  Advanced search   Search tips   Subscriptions

Issue 161 attachment: pyodbc_issue_161_test.py (2.1 KB)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
SQL_CREATE_DB = "CREATE DATABASE test_pyodbc_161;"

SQL_CREATE_TABLES = """\
USE test_pyodbc_161;

CREATE TABLE bill (
bill_id int,
updated datetime,
updated_by varchar(30)
);

CREATE TABLE bill_item (
bill_id int,
item_id int,
amount double precision
);

/* need to insert a row or else MAX(bill_id) gives null */
INSERT INTO bill VALUES (1, CURRENT_TIMESTAMP, 'Initial');
"""

SQL_CREATE_TRIGGER = """\
CREATE trigger [dbo].[bill_updated]
on [dbo].[bill]
for insert, update
as
begin
print 'some message'
end
"""

SQL_DROP = """
USE master;
DROP DATABASE test_pyodbc_161;
"""

ODBC_INFO = ';'.join((
'DRIVER={SQL Server Native Client 10.0}',
'SERVER=(local)',
'TRUSTED_CONNECTION=yes',
))

def do_test(cnxn1):
c1 = cnxn1.cursor()
insertSQL = """\
BEGIN
SET NOCOUNT ON;
declare @message varchar(300)
BEGIN TRANSACTION insert_bill
BEGIN TRY
declare @newBillId int
select @newBillId = MAX(bill_id)+1 from bill
insert into bill (bill_id,updated,updated_by)
values (@newBillId,CURRENT_TIMESTAMP,'PythonTool')
insert into bill_item values (@newBillId,1,123.45)
insert into bill_item values (@newBillId,2,678.90)
--print 'cause error'
COMMIT TRANSACTION insert_bill
SELECT @message=@newBillId --as 'Message'
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION insert_bill
SELECT @message='ERROR! '+ERROR_MESSAGE() --as 'Message'
END CATCH
select @message as 'message'
END"""

SQLresult = c1.execute(insertSQL)
for row in SQLresult:
print row.message

import pyodbc


cnxn = pyodbc.connect(ODBC_INFO, autocommit=True)
cnxn2 = pyodbc.connect(ODBC_INFO, autocommit=True)

try:
cnxn.execute(SQL_DROP)
finally:
print "DB certainly does not exist."

cnxn.execute(SQL_CREATE_DB)
cnxn.execute(SQL_CREATE_TABLES)
cnxn.execute(SQL_CREATE_TRIGGER)

try:
do_test(cnxn)
finally:
cnxn2.execute(SQL_DROP)

print "Done."

Powered by Google Project Hosting