MSSQL Linked Servers. Samples for article (WSF:VBS)

Article    

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Linked server properties
' Provider        : IBProvider [v2]
' Name            : IB_EMPL
' Data Source     : path to employee.gdb
' Provider String : user=usr;password=pswd;ctype=win1251;
'                   truncate_char=false;
'                   support_odbc_query=true;
'                   odbc_call_sp=0;

' Provider Options: Dynamic Parameters + Allow InProcess
' Server Options  : Data Access + RPC + RPC Out + Use Remote Collation

' Use gfix.exe for set database employee.gdb in 3 dialect

'
' correct mssql.udl - choose your MSSQL server
'

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
option explicit

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
call sample_mssql_linked_server()

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
sub sample_mssql_linked_server
 dim cn

 set cn=WScript.CreateObject("ADODB.Connection")

 call cn.open ("file name=mssql.udl")

 call print_connection_info(cn)

 dim funcs,func

 funcs=Array("test_001","test_002","test_003","test_004","test_005","test_006","test_007")

 for each func in funcs
  p_echo "--------------------------------------------"

  getref(func)(cn)
 next 'func

end sub ' sample_mssql_linked_server

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
sub test_001(cn)
 p_echo "simple select"

 call begin_distr_trans(cn,"A")

 dim rs

 set rs=cn_exec(cn,"select * from IB_EMPL...EMPLOYEE")

 call print_recordset(rs)

 set rs=nothing

 call commit_distr_trans(cn,"A")
end sub 'test_001

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
sub test_002(cn)
 p_echo "select with parameter"

 call begin_distr_trans(cn,"A")

 dim cmd

 set cmd=WScript.CreateObject("ADODB.Command")

 cmd.activeconnection=cn

 call cmd_set_text(cmd,"select * from IB_EMPL...EMPLOYEE empl " + _
                       "where empl.FIRST_NAME=?")

 cmd.parameters.refresh
 cmd(0)="Scott"

 dim rs

 set rs=cmd_execute(cmd)

 call print_recordset(rs)

 call commit_distr_trans(cn,"A")
end sub 'test_002

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
sub test_003(cn)
 p_echo "insert new row"

 call cn_exec(cn,"set xact_abort on")

 call begin_distr_trans(cn,"A")

 call cn_exec(cn,"insert into IB_EMPL...COUNTRY (COUNTRY,CURRENCY) values ('Mars','Snickers')")

 p_echo "---------------------------------------------"
 p_echo "get insert row"

 dim rs

 set rs=cn_exec(cn,"select * from IB_EMPL...COUNTRY cntr where cntr.COUNTRY='Mars'")

 call print_recordset(rs)

 call commit_distr_trans(cn,"A")
end sub 'test_003

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
sub test_004(cn)
 p_echo "update new row"

 call begin_distr_trans(cn,"A")

 call cn_exec(cn,"set xact_abort on")

 dim cmd

 set cmd=WScript.CreateObject("ADODB.Command")

 cmd.activeconnection=cn

 call cmd_set_text(cmd,"update IB_EMPL...COUNTRY set CURRENCY='Beer' where COUNTRY=?")

 call cmd.parameters.refresh()

 cmd(0)="Mars"

 call cmd_execute(cmd)

 p_echo "-------------------------"
 p_echo "get updated row"

 dim rs

 set rs=cn_exec(cn,"select * from IB_EMPL...COUNTRY cntr where cntr.COUNTRY='Mars'")

 call print_recordset(rs)

 call commit_distr_trans(cn,"A")
end sub 'test_004

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
sub test_005(cn)
 p_echo "delete new row"

 call cn_exec(cn,"set xact_abort on")

 dim cmd

 set cmd=WScript.CreateObject("ADODB.Command")

 cmd.activeconnection=cn

 call begin_distr_trans(cn,"A")

 call cmd_set_text(cmd,"delete from IB_EMPL...COUNTRY where COUNTRY=?")

 call cmd.parameters.refresh()

 cmd(0)="Mars"

 call cmd_execute(cmd)

 p_echo "-------------------------"
 p_echo "verify deleting"

 dim rs

 set rs=cn_exec(cn,"select * from IB_EMPL...COUNTRY cntr where cntr.COUNTRY='Mars'")

 call print_recordset(rs)

 call commit_distr_trans(cn,"A")
end sub 'test_005

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
sub test_006(cn)
 p_echo "stored procedure exec (open recordset)"

 call begin_distr_trans(cn,"A")

 dim rs

 set rs=cn_exec(cn,"exec IB_EMPL...SUB_TOT_BUDGET '100'")

 call print_recordset(rs)

 call commit_distr_trans(cn,"A")
end sub 'test_006

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
sub test_007(cn)
 p_echo "stored procedure call (open recordset)"

 call begin_distr_trans(cn,"A")

 dim rs

 set rs=cn_exec(cn,"{call IB_EMPL...SUB_TOT_BUDGET('100')}")

 call print_recordset(rs)

 set rs=nothing

 call commit_distr_trans(cn,"A")
end sub 'test_007
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++