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