SQL command to PowerShell

Welcome Forums General PowerShell Q&A SQL command to PowerShell

This topic contains 5 replies, has 4 voices, and was last updated by

 
Participant
2 years, 3 months ago.

  • Author
    Posts
  • #60106

    Participant
    Points: 0
    Rank: Member

    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

    Keymaster
    Points: 1,811
    Helping HandTeam Member
    Rank: Community Hero

    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
    Points: 2
    Rank: Member

    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

    Participant
    Points: 639
    Helping Hand
    Rank: Major Contributor

    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

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

    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

The topic ‘SQL command to PowerShell’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort