I would love to hear more about this driver that lets you access SQL Server 7 data. I
am migrating to a NetBSD box and would like to have access to the SQL Server 7
database until it is migrated to postgresql.
As far as transactions, I am currently using nsodbc on a Win32 box to talk to SQL
Server 7. If I need a transaction I use a stored procedure, or I just code it into
the sql string that I send to the database via a dml call. Sometimes a combination of
the two is required, taking advantage of an error code returned by the SP. Here is an
example...
proc processUpdateProgramFunding {db formdata} {
set planYear [checkCookie planYear]
set totalCount [ns_set get $formdata totalCount]
set PROGRAMNO [ns_set get $formdata PROGRAMNO]
# Build the sql string and wrap it in a transaction. I wish there was
# a prettier way to do this. This is a stupid programmer trick, but I
# can't see how else to do it in the context of HTML.
set sqlString "DECLARE @ROWS int, @ID int, @ERRORCODE int SELECT @ROWS=0, @ERRORCODE =
0 "
append sqlString "BEGIN TRANSACTION SET NOCOUNT ON "
set varList {DELETE INSERT UPDATE FUNDSOURCEID PROGFUNDEFFDATE PROGFUNDTERMDATE
PROGFUNDAMOUNT}
for {set i 1} {$i<=$totalCount} {incr i} {
foreach var $varList {
set $var [ns_set get $formdata $var$i]
}
# A bad hack to make sure the dates are within the plan year before they go to the
# database. Database does not know what plan year the user is logged in for. I
# could do it in an insert stored procedure, if I could figure out how to parse
# the input in a stored procedure. Sigh...
if {[string length $DELETE] && [string length $INSERT] && [string length
$UPDATE]>0} {
if {[expr [clock scan $PROGFUNDEFFDATE]<[clock scan $planYear-01-01]]} {
ns_puts "<FONT COLOR=RED>Start date is outside current logged in plan
year: $planYear</FONT>"
ns_adp_break
}
if {[expr [clock scan $PROGFUNDTERMDATE]>[clock scan $planYear-12-31]]} {
ns_puts "<FONT COLOR=RED>End date is outside current logged in plan year:
$planYear</FONT>"
ns_adp_break
}
}
if {[string length $DELETE]>0} {
append sqlString "IF @ERRORCODE = 0 EXEC @ERRORCODE = vsp_DeleteProgFund
'$DELETE' \n"
} elseif {[string length $INSERT]>0} {
append sqlString "IF @ERRORCODE = 0 EXEC @ERRORCODE = vsp_InsertProgFund "
append sqlString "'$PROGFUNDEFFDATE', '$PROGFUNDTERMDATE', "
append sqlString "'$FUNDSOURCEID', $PROGFUNDAMOUNT, '$PROGRAMNO' \n"
} elseif {[string length $UPDATE]>0} {
append sqlString "IF @ERRORCODE = 0 EXEC @ERRORCODE = vsp_UpdateProgFund "
append sqlString "$UPDATE, '$PROGFUNDEFFDATE', '$PROGFUNDTERMDATE', "
append sqlString "'$FUNDSOURCEID', $PROGFUNDAMOUNT, '$PROGRAMNO' "
}
}
append sqlString "EXEC @ERRORCODE = vsp_CheckProgFundRanges $PROGRAMNO, $planYear IF
@ERRORCODE <> 0 BEGIN "
append sqlString "ROLLBACK TRANSACTION RETURN "
append sqlString "END COMMIT TRANSACTION"
# Send the string and see what happens...
catch [set res [ns_db dml $db "$sqlString"]] error
if [string length $error]>0 {
ns_puts "<FONT COLOR=RED>$error</FONT>"
updateProgramFunding $db $formdata
} else {
ns_puts "<FONT COLOR=BLUE>Update Successful.</FONT>"
updateProgramFunding $db $formdata
}
}
Since each call to nsodbc is a separate 'connection' as far as the database is
concerned, you have to begin and end the transaction in the same call. As I said,
most times a simple call to a stored procedure is best, but the input form that the
above lame code parses can do one of 3 different things for each group of form data.
Here is one of the stored procedures it calls...
CREATE PROCEDURE vsp_InsertProgFund
@PROGFUNDEFFDATE datetime,
@PROGFUNDTERMDATE datetime,
@FUNDSOURCEID int,
@PROGFUNDAMOUNT money,
@PROGRAMNO char(10)
AS
DECLARE
@PROGFUNDRANGEID int,
@ERRORCODE int
SELECT @ERRORCODE = 0
-- See if there is an existing parent record
IF EXISTS(SELECT * FROM ProgramFundRanges WHERE PROGFUNDEFFDATE = @PROGFUNDEFFDATE
AND PROGFUNDTERMDATE = @PROGFUNDTERMDATE AND PROGRAMNO = @PROGRAMNO)
BEGIN
-- Get his ID number for the insert
SELECT @PROGFUNDRANGEID = PROGFUNDRANGEID FROM ProgramFundRanges WHERE
PROGFUNDEFFDATE = @PROGFUNDEFFDATE
AND PROGFUNDTERMDATE = @PROGFUNDTERMDATE AND PROGRAMNO = @PROGRAMNO
END
ELSE
BEGIN
-- If not, insert a new one and get the id
INSERT INTO ProgramFundRanges (PROGRAMNO, PROGFUNDEFFDATE, PROGFUNDTERMDATE)
VALUES (@PROGRAMNO, @PROGFUNDEFFDATE, @PROGFUNDTERMDATE)
SELECT @ERRORCODE = @@ERROR, @PROGFUNDRANGEID = @@IDENTITY
END
-- Do the insert
IF @ERRORCODE = 0
BEGIN
INSERT INTO ProgramFund (PROGFUNDRANGEID, FUNDSOURCEID, PROGFUNDAMOUNT)
VALUES (@PROGFUNDRANGEID, @FUNDSOURCEID, @PROGFUNDAMOUNT)
SELECT @ERRORCODE = @@ERROR
END
RETURN @ERRORCODE
This one does not use a transaction, since I always call it from the kludge above. It
simply returns the error code that tells the whole bloody thing to roll back if
something went wrong. If it is a standalone stored procedure I use a transaction and
issue nice descriptive error codes with the rollback and display them to the user
directly.
I hope this helps.
Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
mailto: [EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 04/30/01 05:34PM >>>
I've been using nsodbc a bit and I wonder how you nsodbc users implement
transactions?
It appears that nsodbc seems to default to autocommit mode, and I can't
figure how to issue a "begin transaction" against it. I just get dml
errors back (when working with SQL Server 7)
What are you guys doing?
Thanks,
Jerry
P.S. I am working with Rob Mayoff's db2 driver and modifying it to be a
more compliant ODBC driver. It appears to be working nicely with SQL
Server 7, and I can commit/rollback transactions using his driver....
=====================================================
Jerry Asher [EMAIL PROTECTED]
1678 Shattuck Avenue Suite 161 Tel: (510) 549-2980
Berkeley, CA 94709 Fax: (877) 311-8688
