SQL command to PowerShell

This topic contains 5 replies, has 4 voices, and was last updated by  Sergiu-Andrei Creifelean 10 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

    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

    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

    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.