populate field from SQL lookup?

amccollough

I'm working on a data generator project where I would like to be able to do a query against a table containing lookup data.

You know how when scripting data generation fields, other fields are represented like $[Division]? I'd like to be able to do a SQL Query like this: "

SELECT BusinessUnit FROM tblBusinessUNITS where Divsion = '$[Division]'

" and have the result (presuming there would be one record returned) be the data for that column/row instance.

Is this possible?

Brian Donahue

Hello,

SQL Data Generator has the ability to run a Python script, so if you have a Python script, variable declarations typically begin with the dollar-sign ($). The question must be, how to get a generated column value and use that in a SQL query. This sounds like you would need a combination of two generators - cross-column and SQL statement.

The best solution I can think of for this is to use the Python generator, get the column name into a variable and use Python to get the data.

Brian Donahue

I came up with this, but it needs to make a connection to the server for every record.

# Basic generator template

def main(config):
    # config["column_name"] is the column name
    # config["column_type"] is the column datatype
    # config["column_size"] is the column size
    # config["n_rows"] is the number of rows
    # config["seed"] is the current random seed
    import System
    import System.Data
    import System.Data.SqlClient
    str_connection="Data Source=localhost;Initial Catalog=Database2;Integrated Security=SSPI"
    sql_connection=System.Data.SqlClient.SqlConnection(str_connection)
    str_command="SELECT TextField FROM Table1 WHERE TextField LIKE '%"
    str_command+=COUNTRYNAME # Where COUNTRYNAME is a field from the table
    str_command+="%'"
    sql_command=System.Data.SqlClient.SqlCommand(str_command)
    sql_command.Connection=sql_connection
    sql_connection.Open()
    sql_datareader=sql_command.ExecuteReader()
    a_value=""
    while sql_datareader.Read():
        a_value=sql_datareader.GetString(0)
    sql_datareader.Close()
    sql_connection.Close()
    return [a_value]

Aliraza153

Say I have a table participants with PK part_id and another table DeliveryAddress with foreign key part_id.

If i setup the foreign key on DeliveryAddress back to the participants table then it will only select unique part_id when a participant could have multiple delivery addresses.

There is a hint to the right of the "Population method" on the Foreign key generator UI stating: If the foreign key references a single column, each value in the referenced column is only used once. I have tested this and it creates a one to one foreign key.