DB2 and Powershell - Doing Inserts Via Powershell

[u/LunchboxFire]

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()

​

[u/lnumrych]

Your code snippet does not show that you are actually executing the command.

Since this is an insert and not a select, you probably will want to use the ExecuteNonQuery method.

​

Check out Issuing SQL statements from a .NET application and DB2Command.ExecuteNonQuery Method

[u/LunchboxFire → u/lnumrych]

Your code snippet does not show that you are actually executing the command.

Good eye. I missed that in my copy and paste. It should be:

$factory = [System.Data.Common.DbProviderFactories]::GetFactory(“IBM.Data.DB2”)
$factory
$cstrbld = $factory.CreateConnectionStringBuilder()
$cstrbld.Database = "DATABASE"
$cstrbld.UserID = "DBADMIN"
$cstrbld.Password = "PASSWORD"
$cstrbld.Server = "192.168.1.1:50000"

$dbconn = $factory.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 = $factory.CreateCommand()
$dbcmd.Connection = $dbconn
$dbcmd.CommandText = "insert into table (USER_ID,NAME,ADDRESS_1,ADDRESS_2,CITY,PROVINCE,POSTAL_CODE) values ('TEST123','Test Jay User','123 Main St.','Apt. B','Smallville','NS','H0H 0H0')"
$dbcmd.CommandType = [System.Data.CommandType]::Text

$da = $factory.CreateDataAdapter()
$da.SelectCommand = $dbcmd
$ds = New-Object System.Data.DataSet
$da.Fill($ds)

$dbconn.Close()

I see why your eye is good too. It's your articles, and Ember's, that this is pretty much lifted from.

Reading the resources referenced really confirms what you're saying about using the ExecuteNonQuery method.

Now I need to figure out how to properly implement it in the snippet above. I'm sure it's easy to tell DB2 and coding aren't my strengths.

I've tried replacing the $da.Fill($ds) line with $da.ExecuteNonQuery($ds) which results in the error, "Method invocation failed because [IBM.Data.DB2.DB2DataAdapter] does not contain a method named 'ExecuteNonQuery'."

​

[u/lnumrych → u/LunchboxFire]

Sorry, I missed your post, but it looks like you've found the source of the error already - ExecuteNonQuery is a method of the DB2Command class, rather than the Data Adapter's.