Execute Command with CommandText from external file

[adshocker]

Hi all,

Can anyone show or direct me as to where I can learn how to execute a command in which its commandtext was loaded from an external file (i.e. text file).

I've done the basics like the ff:

Dim sqlText As String = File.IO.ReadAllText("myScript.txt")
Dim conn As New OracleConnection(myConnString)
Dim cmd As New OracleCommand
With cmd
  .Connection=conn
  .Connection.Open()
  .CommandType = CommandType.Text
  .CommandText = sqlText
  .ExecuteNonQuery()
End With

myScript.txt contents

CREATE TABLE myTable
(
  col1 NUMBER,
  col2 VARCHAR2(10)
);

From experience, this will give an error. The command object's commandtext somehow does not recognize the linefeed and tab characters. Also, in this case, CommandType is CommandType.Text. Using CommandType.StoredPro is another story.

Anyways, just wanna know if anybody else has done something like this or if anyone is familiar to what I'm trying to do. I have posted a thread somewhat similar to this a few months ago. This time I'm having a different problem. Please help.

Thanks

 

[MattP]

If it is a couple of characters that are causing you problems can't you just remove them from your string?

sqlText.Replace(ControlChars.Lf, String.Empty).Replace(ControlChars.Tab, String.Empty)

 

[adshocker]

If it is a couple of characters that are causing you problems can't you just remove them from your string?

sqlText.Replace(ControlChars.Lf, String.Empty).Replace(ControlChars.Tab, String.Empty)

hi,

thanks for the tip.

now, i did try this and the next problem i'm having is it gives an error where there are more that 2000+ characters.

my sample external script here only contains a few lines and characters. but if i try a file with about more than the mentioned above, it doesn't work anymore.

what else can i do?

thanks.

 

[cjard]

semicolons are not allowed in oracle commandtexts

remove the semicolons and you'll be fine. all other characters can stay (including the LFs and tabs youre attempting to remove.. infact removing those will probably screw it up! dont do it!) but make sure ou read the file in using a string encoding that the file is stored in!

No point reading a unicode text file in using a reader set to ASCII; the .CommandText will be full of junk

 

[adshocker]

thanks a bunch.