how to run sql query in vbscript

[126012601]

Kindly find below the vbscript which is giving problem.
I have an insert_query which takes data from the select_query. If i write the insert_query inside the function,ExecuteMigrationQuery, it works but if i define it outside the function it doesn't. i think it is because of the strings '" & resultSet("subcategory") & "'. Can anybody tell me how to resolve this. i want to keep the insert query out of the function in the starting. i think i can be doen by using replace and eval but dont know how


Dim OdbcDSN, connect, sql, resultSet
Dim OdbcDSN_target, connect_target, sql_getdate, sql_target, sql_init, resultSet_target, resultsetdate, resultdate, replacementstring

OdbcDSN = "DSN=test;UID=user1;PWD=tttt"
OdbcDSN_target = "DSN=test1;UID=user1;PWD=ttt"

delete_query = "delete from sctable"

select_query = "select Convert(datetime, close_time) close_time, number, Convert(datetime, open_time) open_time, Convert(datetime, resolved_time) resolved_time, logical_name, bp_classification category, bp_subclassification subcategory, product_type, '' stIBMmodule, open_group, contact_name, resolved_group,closed_group, priority_code, brief_description, cause_code, resolution_code, total_loss,problem_type, '' stIBMcallcode, critical_user, p.dept, d.dept_id from test.sctable p where p.status ='closed' and p.close_time > Convert(datetime, Convert(Varchar(25), getdate()-20000, 120))"

insert_query = "INSERT INTO sctable(CLOSE_TIME, NUMBERPRGN, OPEN_TIME, RESOLVED_TIME, LOGICAL_NAME, CATEGORY, SUBCATEGORY, PRODUCT_TYPE, STIBMMODULE, OPEN_GROUP, CONTACT_NAME, RESOLVED_GROUP, CLOSED_GROUP, PRIORITY_CODE, BRIEF_DESCRIPTION, CAUSE_CODE, RESOLUTION_CODE, TOTAL_LOSS, PROBLEM_TYPE, STIBMCALLCODE, CRITICAL_USER, DEPT, DEPT_ID) VALUES (to_date('" & resultSet("close_time") & "','dd/mm/yyyy hh24:mi:ss'),'" & resultSet("number") & "',to_date('" & resultSet("open_time") & "','dd/mm/yyyy hh24:mi:ss'), to_date('" & resultSet("resolved_time") & "','dd/mm/yyyy hh24:mi:ss'),'" & resultSet("logical_name") & "','" & resultSet("category") & "','" & resultSet("subcategory") & "','" & resultSet("product_type") & "','" & resultSet("stIBMmodule") & "','" & resultSet("open_group") & "','" & resultSet("contact_name") & "','" & resultSet("resolved_group") & "','" & resultSet("closed_group") & "','" & resultSet("priority_code") & "','" & replace(resultSet("brief_description"), "'", "''", 1, -1, 1) & "','" & resultSet("cause_code") & "','" & resultSet("resolution_code") & "','" & resultSet("total_loss") & "','" & resultSet("problem_type") & "','" & resultSet("stIBMcallcode") & "','" & resultSet("critical_user") & "','" & resultSet("dept") & "','" & resultSet("dept_id") & "')"


On Error Resume Next
Set connect = CreateObject("ADODB.Connection")
Set connect_target = CreateObject("ADODB.Connection")
connect.Open OdbcDSN
connect_target.Open OdbcDSN_target
' stIBMmodule and stIBMcallcode empty cause no replacement fields
' category replaced by bp_classification
' SubCategory replaced by bp_subclassification

Call ExecuteMigrationQuery(delete_query, select_query, insert_query)
Sub ExecuteMigrationQuery(delete_query, select_query, insert_query)
sql_init=delete_query
sql_getdate="select Convert(Varchar(25), getdate()-20000, 120) cur"

Call ErrorHandling()

Set resultsetdate = connect.execute(sql_getdate)
resultsetdate.MoveFirst
resultdate = resultsetdate("cur")
msgBox resultdate
Call ErrorHandling()
sql= select_query
'msgBox sql

connect_target.execute sql_init
Set resultSet = connect.Execute(sql)

resultSet.MoveFirst
Do While Not resultSet.eof
Dim tmpstr
tmpstr = insert_query
connect_target.execute tmpstr
Call ErrorHandling()
resultSet.MoveNext
Loop

End Sub

resultSet.Close
connect.Close
Set connect = Nothing

resultSet_target.Close
connect_target.Close
Set connect_target = Nothing



' CHANGE TO BE WRITING TO LOG FILE
Sub ErrorHandling()
If Err.Number <> 0 Then
MsgBox "ERROR:" & Err.Number & ", " & Err.Description
Err.Clear()
End If
End Sub

 

[ettienne]

That's not going to work, all the resultSet() fields are Null when you set insert_query = "Insert Into..."

 

[linney]

Have you tried your luck in the VBscript Forum?

http://www.tek-tips.com/threadminder.cfm?pid=329

 

[126012601]

can you tell me the solution for this. i knw it can be done using replace and eval. but dont know what to replace in the resultset.