populate field from SQL lookup?

• 
  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 August 27, 2014 08:37

  - 0 +
• 
  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]
  

  Brian Donahue August 27, 2014 09:05

  - 0 +
• 
  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.

  Aliraza153 January 04, 2015 23:59

  - 0 +

Add comment

Please sign in to leave a comment.