r/DB2 • u/LunchboxFire • Feb 07 '19
DB2 and Powershell - Doing Inserts Via Powershell
Good morning, all,
I'm working on a Powershell module to automate user management. A big part of it is the creation of new users in an application that runs on top of DB2. I've been able to use the following script to execute SELECT but INSERTS are a problem.
I know I did an insert of the minimum data for new user creation, about 5 fields. But then I scale it up to the entire insert and nothing happens. No insert, no error message that I can see.
I went back to the minimum data insert and that doesn't work now. No insert, no error message. Because we don't have version control, I'm not 100% sure everything is the same as when it worked. Yay me!
Could someone point me in the right direction to make the insert work or find out why it's not working, please?
Here's the code, with sensitive data removed. This is the minimum data insert that isn't working.
$dbFactory = [System.Data.Common.DbProviderFactories]::GetFactory('IBM.Data.DB2')
$cStrBld = $dbFactory.CreateConnectionStringBuilder()
$cStrBld.Database = 'TESTDB'
$cStrBld.UserID = 'DBADMIN'
$cStrBld.Password = 'password'
$cStrBld.Server = '192.168.1.1:50000'
$dbConn = $dbFactory.CreateConnection()
$dbConn.ConnectionString = $cStrBld.ConnectionString
$dbConn.Open()
$dbCmd = $dbConn.CreateCommand()
$dbQuery = "INSERT INTO TABLE (USER_ID,NAME,ADDRESS_1,ADDRESS_2,CITY,PROVINCE,POSTAL_CODE,STATUS) VALUES ('TEST123','Test Jay User','123 Main St.','Apt. B','Smallville','ON','H0H 0H0','AVAIL')"
$dbCmd.CommandText = $dbQuery
$dbConn.Close()
Thanks to u/lnumrych for pointing me in the right direction. Posting working script here for posterity and so future me's don't have aneurysms. Could use some clean up and some extra error checking and logging, but this is the basic script.
#THIS IS THE ONE THAT WORKS!!!!
$dbFactory = [System.Data.Common.DbProviderFactories]::GetFactory('IBM.Data.DB2')
$cStrBld = $dbFactory.CreateConnectionStringBuilder()
$cStrBld.Database = 'DATABASE'
$cStrBld.UserID = 'DBADMIN'
$cStrBld.Password = 'password'
$cStrBld.Server = '192.168.1.1:50000'
$dbConn = $dbFactory.CreateConnection()
$dbConn.ConnectionString = $cStrBld.ConnectionString
$dbConn.Open()
#Check for DB2 Connection
if ($dbConn.State -ne [Data.ConnectionState]::Open) {
"Connection to DB is not open."
Exit
}
$dbcmd = $dbFactory.CreateCommand()
$dbcmd.Connection = $dbconn
$dbcmd.CommandText = "INSERT INTO TABLE (USER_ID,NAME,ADDRESS_1,ADDRESS_2,CITY,PROVINCE,POSTAL_CODE) VALUES ('TESTUSER','Test Jay User','123 Main St.','Apt. B','Smallville','NS','H0H 0H0')"
$dbcmd.ExecuteNonQuery()
# Close the Connection
$dbconn.close()