FreeTDS Development Group

[James Cameron <cameron AT fax.sno.cpqcorp.net>]

G'day,

My task is to try to replicate the functions of a GUI client using a PHP
script on top of FreeTDS to a Microsoft SQL Server 7 system.

Using SQL Query Analyser, someone else in my team has given me the SQL
queries being sent to the database server by the GUI client.

However, I'm not sure I understand some of what I see, particularly as
it relates to literal strings within a command.

A small query extracted from the analysis appears below.  It apparently
increments an object number in a table according to a type code, and
then SELECTs it again to read it back, all in one transaction.

- while I know what "begin tran" and "commit tran" do, what does "go"
do?
- what does the "exec sp_executesql N'" sequence mean?
- why are the literal strings segregated from the command?
- while not relevant to this example, if I don't see a "with (nolock)"
clause in a query, does this prove it is not done?


begin tran
exec sp_executesql N'  update dbo.adp_tbl_oid
 set obj_num = obj_num + @U1
 where type_id = @U2
 select obj_num from dbo.adp_tbl_oid
 where type_id = @U3
', N'@U1 varchar(255),@U2 varchar(255),@U3 varchar(255)',
@U1="1",@U2="77",@U3="77"
commit tran

go


Now, the complex transaction ... marks a customer case as accepted by a
customer support specialist ... much more is done here, but it raises
more questions:

- is it possible to get FreeTDS to operate in this way in which multiple
SQL commands are issued in a transaction and 'return' statements in the
SQL prevent execution of following commands?

- can the value of @@error after execution of this be exposed to a PHP
script?


begin tran
exec sp_executesql N' update dbo.table_condition
 set condition = @U1,wipbin_time = @U2,title = @U3
 where objid = @U4
AND (  (condition & @U5) != 0 ) ', N'@U1 varchar(255),@U2
varchar(255),@U3 varchar(255),@U4 varchar(255),@U5 varchar(255)',
@U1="16386",@U2="1/7/2002
03:07:50:000",@U3="Open",@U4="268738414",@U5="552"

if @@ROWCOUNT <=0
BEGIN
RAISERROR 91001 'The record may have been changed, please refresh and
try again.'
ROLLBACK TRANSACTION
RETURN
END

if((@@error=1205)or(@@error=916)or(@@error=2601)or((@@error>=90000)and(@@error<=91000)))return

exec rl_curr_owner2case 268437052, 268738403

if((@@error=1205)or(@@error=916)or(@@error=2601)or((@@error>=90000)and(@@error<=91000)))return

exec sp_executesql N' update dbo.table_case set case_currq2queue = NULL
where objid = @U1
', N'@U1 varchar(255)', @U1="268738403"

exec rl_wipbin2case 268437533, 268738403

if((@@error=1205)or(@@error=916)or(@@error=2601)or((@@error>=90000)and(@@error<=91000)))return

exec sp_executesql N' insert into dbo.table_act_entry
(objid,act_code,entry_time,addnl_info,proxy,removed,entry_name2gbst_elm,act_entry2case,act_entry2user)
 values(@U1,@U2,@U3,@U4,@U5,@U6,@U7,@U8,@U9)
', N'@U1 varchar(255),@U2 varchar(255),@U3 varchar(255),@U4
varchar(255),@U5 varchar(255),@U6 varchar(255),@U7 varchar(255),@U8
varchar(255),@U9 varchar(255)', 
@U1="277027838",@U2="100",@U3="1/7/2002 03:07:50:000",@U4="from Queue
SPR_SW_VMS to WIP
default.",@U5="sa",@U6="0",@U7="268435523",@U8="268738403",@U9="268437052"

if((@@error=1205)or(@@error=916)or(@@error=2601)or((@@error>=90000)and(@@error<=91000)))return

exec sp_executesql N' insert into dbo.table_time_bomb
(objid,title,escalate_time,end_time,focus_lowid,focus_type,suppl_info,time_period,flags,left_repeat,report_title,property_set,users,cmit_creator2employee)
values(@U1,@U2,@U3,@U4,@U5,@U6,@U7,@U8,@U9,@U10,@U11,@U12,@U13,@U14)
', N'@U1 varchar(255),@U2 varchar(255),@U3 varchar(255),@U4
varchar(255),@U5 varchar(255),@U6 varchar(255),@U7 text,@U8
varchar(255),@U9 varchar(255),@U10 varchar(255),@U11 varchar(255),@U12
varchar(255),@U13 varchar(255),@U14 varchar(255)'
, @U1="276509234",@U2="",@U3="1/1/1753 00:00:00:000",@U4="1/7/2002
03:07:50:000",@U5="268738403",@U6="0",@U7="",@U8="277027838",@U9="2",@U10="0",@U11="",@U12="",@U13="",@U14="268437052"

if((@@error=1205)or(@@error=916)or(@@error=2601)or((@@error>=90000)and(@@error<=91000)))return

commit tran

go

-- 
James Cameron