• State Verified Answer
  • Locked Locked
  • Replies 5 replies
  • Subscribers 94 subscribers
  • Views 4437 views
  • Users 0 members are here
Options
Related
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Query external DB and store results in a variable

kinshasa
over 7 years ago

Hi Experts,

I'm connecting to an external system table which gives me a list of manager's and their reportees (one line for manager and their reportee).

For various reasons, I need to process this table for every manager separately - after processing all reportees for each manager I need to update a specific field for that manager on the Person object which is why a simple ORDER BY won't help.

What I think I need to do is 2 steps:

1) Perform a SELECT DISTINCT on the external table to get a list of all managers

2) Run SELECT queries in a for loop iterating over the managers in the where clause 

Question is, how do I store the list of returned values from SELECT DISTINCT into a variable so that I can iterate over it? I tried SQLExecutor but that gives me an integer value to indicate success or failure. 

So, to summarize, any way to run a query and store the results in a local variable inside a script?

Thanks in advance

Kin

6.1.4

Parents
  • +1 over 7 years ago
    Hi,

    Perhaps this code snippet might help .... sorry I lost the formatting:

    Public Sub CCC_DataReader_Test()
    Dim con As New SqlConnection(GetMsSqlConnectionString())
    Dim cmd As New SqlCommand("getUser", con)
    cmd.CommandType = Data.CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@accountID", "310211")
    Dim x As Integer = 0
    Try
    con.Open()
    Dim reader As SqlDataReader = cmd.ExecuteReader()
    While reader.Read()
    For x = 0 To reader.FieldCount - 1
    If Not IsDbNull(reader.getValue(x)) Then
    VID_Write2Log("C:\RPTest.log",reader.getName(x) & ": " & CStr(reader.getValue(x)))
    Else
    VID_Write2Log("C:\RPTest.log",reader.getName(x) & ": " & "null")
    End If
    Next
    'Exit While
    'count += 1
    'VID_Write2Log("C:\RPTest.log", "Count: " & count)
    End While
    Catch ex As SqlException
    Finally
    con.Close()
    End Try
    End Sub

    HTH, Barry.
Reply
  • +1 over 7 years ago
    Hi,

    Perhaps this code snippet might help .... sorry I lost the formatting:

    Public Sub CCC_DataReader_Test()
    Dim con As New SqlConnection(GetMsSqlConnectionString())
    Dim cmd As New SqlCommand("getUser", con)
    cmd.CommandType = Data.CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@accountID", "310211")
    Dim x As Integer = 0
    Try
    con.Open()
    Dim reader As SqlDataReader = cmd.ExecuteReader()
    While reader.Read()
    For x = 0 To reader.FieldCount - 1
    If Not IsDbNull(reader.getValue(x)) Then
    VID_Write2Log("C:\RPTest.log",reader.getName(x) & ": " & CStr(reader.getValue(x)))
    Else
    VID_Write2Log("C:\RPTest.log",reader.getName(x) & ": " & "null")
    End If
    Next
    'Exit While
    'count += 1
    'VID_Write2Log("C:\RPTest.log", "Count: " & count)
    End While
    Catch ex As SqlException
    Finally
    con.Close()
    End Try
    End Sub

    HTH, Barry.
Children
No Data