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]

resulSet does not exist when you call ExecuteMigrationQuery, so all the resultSet fields are NULL.

 

[tsuji]

[1] At the assignment of select_query outside the function, rewrite it with two rules: (a) whereevery you see now ", change it to ""; (b) add an pair of quote at the outermost of the whole thing.
[tt]
insert_query = [highlight]"[/highlight][red]"[/red]"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('[red]"[/red]" & resultSet([red]"[/red]"close_time[red]"[/red]") & [red]"[/red]"','dd/mm/yyyy hh24:mi:ss'),'[red]"[/red]" & resultSet([red]"[/red]"number[red]"[/red]") & [red]"[/red]"',to_date('[red]"[/red]" & resultSet([red]"[/red]"open_time[red]"[/red]") & [red]"[/red]"','dd/mm/yyyy hh24:mi:ss'), to_date('[red]"[/red]" & resultSet([red]"[/red]"resolved_time[red]"[/red]") & [red]"[/red]"','dd/mm/yyyy hh24:mi:ss'),'[red]"[/red]" & resultSet([red]"[/red]"logical_name[red]"[/red]") & [red]"[/red]"','[red]"[/red]" & resultSet([red]"[/red]"category[red]"[/red]") & [red]"[/red]"','[red]"[/red]" & resultSet([red]"[/red]"subcategory[red]"[/red]") & [red]"[/red]"','[red]"[/red]" & resultSet([red]"[/red]"product_type[red]"[/red]") & [red]"[/red]"','[red]"[/red]" & resultSet([red]"[/red]"stIBMmodule[red]"[/red]") & [red]"[/red]"','[red]"[/red]" & resultSet([red]"[/red]"open_group[red]"[/red]") & [red]"[/red]"','[red]"[/red]" & resultSet([red]"[/red]"contact_name[red]"[/red]") & [red]"[/red]"','[red]"[/red]" & resultSet([red]"[/red]"resolved_group[red]"[/red]") & [red]"[/red]"','[red]"[/red]" & resultSet([red]"[/red]"closed_group[red]"[/red]") & [red]"[/red]"','[red]"[/red]" & resultSet([red]"[/red]"priority_code[red]"[/red]") & [red]"[/red]"','[red]"[/red]" & replace(resultSet([red]"[/red]"brief_description[red]"[/red]"), [red]"[/red]"'[red]"[/red]", [red]"[/red]"''[red]"[/red]", 1, -1, 1) & [red]"[/red]"','[red]"[/red]" & resultSet([red]"[/red]"cause_code[red]"[/red]") & [red]"[/red]"','[red]"[/red]" & resultSet([red]"[/red]"resolution_code[red]"[/red]") & [red]"[/red]"','[red]"[/red]" & resultSet([red]"[/red]"total_loss[red]"[/red]") & [red]"[/red]"','[red]"[/red]" & resultSet([red]"[/red]"problem_type[red]"[/red]") & [red]"[/red]"','[red]"[/red]" & resultSet([red]"[/red]"stIBMcallcode[red]"[/red]") & [red]"[/red]"','[red]"[/red]" & resultSet([red]"[/red]"critical_user[red]"[/red]") & [red]"[/red]"','[red]"[/red]" & resultSet([red]"[/red]"dept[red]"[/red]") & [red]"[/red]"','[red]"[/red]" & resultSet([red]"[/red]"dept_id[red]"[/red]") & [red]"[/red]"')[red]"[/red]"[highlight]"[/highlight]
[/tt]
[2] Replace this line.
>tmpstr = insert_query
[tt]tmpstr = [red]eval([/red]insert_query[red])[/red][/tt]

[3] Avoid defining any variables to have any of the words appeared in the right-hand-side of select_query except the recordset.

 

[126012601]

Thanks tsuji. Your method works.
Thanks a million!!!!!