SQL command to PowerShell

This topic contains 5 replies, has 4 voices, and was last updated by Profile photo of Sergiu-Andrei Creifelean Sergiu-Andrei Creifelean 3 months, 1 week ago.

  • Author
    Posts
  • #60106

    Hi Guys,
    I've ran into an issue that I can't seem to find any answers to so maybe you can help me out:
    I have a table in SQL
    Column1|Column2|Column3|Column4|Column5

    I am importing data from a *.csv file into a SQL table using the following code

    
    	$CsvPath = "C:\Temp\File.csv"
    	$Csv = Import-Csv $CsvPath -Delimiter ";"
    	
    	Add-Type -Path "C:\ProgramData\oracle\odp.net\bin\4\Oracle.DataAccess.dll"
    	$ConStr = New-Object Oracle.DataAccess.Client.OracleConnection("User Id=;Password=;Data Source=")
    	$CMD = $ConStr.CreateCommand()
    	$ConStr.Open()
    	foreach ($Item in $Csv)
    	{
    		$Col1 = $Item.col1
    		$Col2 = $Item.col2
    		$Col3 = $Item.col3
    		$Col4 = $Item.col4
    	        $Col5 = $Item.col5
    	
    		## Insert data in table FOLDER_PERMISSION
    		$CMD.CommandText = "INSERT INTO  (Col1 ,Col2 ,Col3 ,Col4 ,Col5 ) VALUES('{0}','{1}','{2}','{3}','{4}')" -f "$Col1", "$Col2", "$Col3 ", "$Col4", "$Col5"
    		$CMD.executenonquery()
    	}
    	$ConStr.close()
    

    This works nicely until I encounter a large string in Column5 and it throws an error, and then again I have found a workaround for this directly in SQL:

    cCol5 clob := 
    begin
    
    insert into 
      (col1,col2,col3,col4,col5)
    values
      ('Test Value',
      'Test Value2',
      'Test Value3',
       cCol5
      );
    

    So this little peace of code works in SQL and I have no clue how to translate this in a SQL powershell statement that would do the same thing from PS.

    I have tried the following with no result:

    ...
    $CMD.CommandText = "
    cCol5 clob := 
    begin
    
    insert into 
      (col1,col2,col3,col4,col5)
    values
      ('Test Value',
      'Test Value2',
      'Test Value3',
       cCol5
      );
    "
    ...
    
    
    Any and all help will be useful, please let me know if further details are required.
    Regards,
    Sergiu Creifelean
  • #60108
    Profile photo of Don Jones
    Don Jones
    Keymaster

    This is really a SQL question; there's no way to do this "in" PowerShell, but you can easily add the statement to your SQL statement string. Just separate statements with a semicolon.

  • #60114
    Profile photo of Ron
    Ron
    Participant

    If I'm following the logic, when col5 is too big, you store it in col4 instead?

    If that's correct, just test the value's length and use a different CommandText statement.

    I assume there's a way to query the table definition to read the max size of Col5 so you don't have to hard code it in your script.

  • #60115
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    In your example, I don't see you actually setting the value of cCol5. Try using a here-string as it allows you to format the SQL, the only catch is you cannot indent it:

    $CsvPath = "C:\Temp\File.csv"
    $Csv = Import-Csv $CsvPath -Delimiter ";"
    	
    Add-Type -Path "C:\ProgramData\oracle\odp.net\bin\4\Oracle.DataAccess.dll"
    $ConStr = New-Object Oracle.DataAccess.Client.OracleConnection("User Id=;Password=;Data Source=")
    $CMD = $ConStr.CreateCommand()
    $ConStr.Open()
    foreach ($Item in $Csv)
    {
    
    $cmd = @"
        insert into 
          (col1,
           col2,
           col3,
           col4,
           col5 clob )
        values
          ('$($Item.col1)',
           '$($Item.col2)',
           '$($Item.col3)',
           '$($Item.col4)',
           '$($Item.col5)',
          );
    
    "@
    	
    	## Insert data in table FOLDER_PERMISSION
    	$CMD.CommandText = $cmd
    	$CMD.executenonquery()
    }
    
    $ConStr.close()
    
  • #60166

    Hi all,

    @Ron, it was a editing mistake, I forgot to add Test Value4, thank you for pointing that out.
    @Rob Simmers
    Rob the code you added throws an oracle error:

    Exception calling "ExecuteNonQuery" with "0" argument(s): "ORA-00917: missing comma"
    At C:\Users\L1_RO10025\Documents\SIG-Data\Utils\saveSQL.ps1:28 char:5
    +     $CMD.executenonquery()
    +     ~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : OracleException
    

    and it seems that this error is related to col5 clob. Any ideas?

    @Don Jones
    Don I tried it, but it does nothing, so no errors but no data in the table as well. Any other ideas?

  • #60172

    Hi all,
    this is what works for me:

    $SQLIn =  @"
    DECLARE
    cVariable clob:= 'HUGE STRING';
    BEGIN
    INSERT INTO TableName(Col1,Col2,Col3,Col4,Col5)
    VALUES('$($Col1)','$($Col2)','$($Col3','$($Col4)',cVariable);
    END;
    "@
    

    Thank you everyone for your time and effort and I hope this will help someone else as well.
    Regards,
    Sergiu

You must be logged in to reply to this topic.