将文件保存到Oracle数据库时出错

[tangligang]

错误信息：ORA-01036: 非法的变量名/编号

代码：请大家帮我看看
public string SaveReportToDatabase(Byte[] FileByteArray,string ReportName,string SaveDate,string MoreInfo)
{
String SqlCmd = "INSERT INTO ReportsList (report,filename,moreinfo,savedate) "+
"values (@FileByteArray,@ReportName,@MoreInfo,@SaveDate)";

OracleConnection MyConn = new OracleConnection ("Data Source=ORACLE8;user=aa;password=aa;");

OracleCommand CmdObj = new OracleCommand (SqlCmd,MyConn );

System.Data .OracleClient .OracleParameter op1 = new OracleParameter ();
op1.OracleType = OracleType.LongRaw ;
op1.Value = FileByteArray;

System.Data .OracleClient .OracleParameter op2 = new OracleParameter ();
op2.OracleType = OracleType.VarChar ;
op2.Value = ReportName;

System.Data .OracleClient .OracleParameter op3 = new OracleParameter ();
op3.OracleType = OracleType.VarChar ;
op3.Value = MoreInfo;

System.Data .OracleClient .OracleParameter op4 = new OracleParameter ();
op4.OracleType = OracleType.DateTime ;
op4.Value = Convert.ToDateTime (SaveDate);

CmdObj.Parameters .Add (op1);
CmdObj.Parameters .Add (op2);
CmdObj.Parameters .Add (op3);
CmdObj.Parameters .Add (op4);

//打开数据库连接更新数据库
try
{
MyConn.Open ();

CmdObj.ExecuteNonQuery();

}
catch(InvalidOperationException ee)
{
return ee.Message ;
}
finally
{
MyConn.Close ();

}
return "保存成功";
}

[gOODiDEA]

String SqlCmd = "INSERT INTO ReportsList (report,filename,moreinfo,savedate) "+
"values (@FileByteArray,@ReportName,@MoreInfo,@SaveDate)";

==〉

String SqlCmd = "INSERT INTO ReportsList (report,filename,moreinfo,savedate) "+
"values (:FileByteArray,:ReportName,:MoreInfo,:SaveDate)";

[tangligang]

to gOODiDEA(无语)：问题依旧

[酋长]

你的数据库表中是否有自增长的列ID?

[tangligang]

to dattotzy(酋长) ：没有，只有report(LONG RAW),filename(VARCHAR2(30)),moreinfo(VARCHAR2(100)),savedate(DATE)列

[酋长]

OK, this is to some extent a limitation of Oracle (or maybe of
> >> cx_Oracle, I don't know how Oracle OCI works at this low a level) -
> >> I'm passing bind variables which aren't used by the SQL statement. But
> >
> > This is definitely a limitation of Oracle. Oracle does not allow you to
> > specify variables that are not part of the statement. As far as I know
> > there is no way around this. Oracle does provide a way of returning to
> > you the bind variable names once the statement is prepared but cx_Oracle
> > does not (currently) provide that information. I have another workaround
> > for now but if you really think this would be helpful, feel free to ask
> > me for it -- this is definitely beyond the scope of the DB API but I
> > have long since stopped feeling restricted by the limitations of the API
> > (which has to accommodate each of the different database management
> > systems out there).
>
> The behaviour of cursor.execute(sql, parameters) when there are
> variables specified in `parameters` which are not needed by `sql` is
> undefined in the DB-API. However, the more I think about it, the more
> I feel that ignoring "extra" parameters is the more user-friendly
> option.


我在网上找到的同样的问题的一个人的分析，你自己看一下，对于你这个问题，许多人都遇到过，但是都没有一个很好的解决办法。

[酋长]

ORA-01036 illegal variable name/number

Cause: Unable to find bind context on user side.

Action: Make sure that the variable being bound is in the SQL statement.

[酋长]

你查看一下你所有的值是否都存在，有可能是你的某个值没有取到或者为空，特别是你的那个long raw字段

[tangligang]

首先明确一点，如果没有问题的话这段代码能否 实现将任何文件以二进制的形式保存到数据库中，这段代码是我拷贝别人的

[酋长]

至少你应该在Byte[] FileByteArray中有值才可以的

[tangligang]

FileByteArray中肯定有值，是这样得到的：
HttpPostedFile UpFile = file1.PostedFile;

Int32 FileLength = 0;

FileLength = UpFile.ContentLength;
//图象文件临时储存Byte数组
Byte[] FileByteArray = new Byte[FileLength];

[gOODiDEA]

Byte[] FileByteArray = new Byte[FileLength];

==>

Stream imgDataStream = file1.PostedFile.InputStream;
int FileLength = file1.PostedFile.ContentLength;
byte[] FileByteArray = new byte[FileLength];
imgDataStream.Read( FileByteArray, 0, FileLength );



System.Data .OracleClient .OracleParameter op1 = new OracleParameter ();
op1.OracleType = OracleType.LongRaw ;
op1.Value = FileByteArray;

...
＝＝》


CmdObj.Parameters.Add( ":FileByteArray", System.Data.OracleClient.OracleType.LongRaw, FileLength, "report" ).Value = FileByteArray;

[tangligang]

谢谢dattotzy(酋长)、gOODiDEA(无语) ，成功了

下面是完整的代码：格式不是很好，恕不整理了
HttpPostedFile UpFile = file1.PostedFile;

//文件临时储存Byte数组
Stream imgDataStream = file1.PostedFile.InputStream;

int FileLength = file1.PostedFile.ContentLength;

byte[] FileByteArray = new byte[FileLength];

imgDataStream.Read( FileByteArray, 0, FileLength );

string message = _DO.SaveReportToDatabase (FileByteArray,this.ddlName .SelectedItem .Text ,this.calComplete .Text ,this.txtMoreInfo .Text , FileLength);

this.labInfo .Text = message;
///////////////////////////////////////////////////////////////////////////////
public string SaveReportToDatabase(Byte[] FileByteArray,string ReportName,string SaveDate,string MoreInfo,int FileLength)
{
String SqlCmd = "INSERT INTO ReportsList (report,filename,moreinfo,savedate) "+
"values (:FileByteArray,:ReportName,:MoreInfo,:SaveDate)";

OracleConnection MyConn = new OracleConnection ("Data Source=ORACLE8;user=weboa;password=oa;");

OracleCommand CmdObj = new OracleCommand (SqlCmd,MyConn );

CmdObj.Parameters.Add( ":FileByteArray", System.Data.OracleClient.OracleType.LongRaw, FileLength, "report" ).Value = FileByteArray;
CmdObj.Parameters.Add( ":ReportName", System.Data.OracleClient.OracleType.VarChar , ReportName.Length , "filename" ).Value = ReportName;
CmdObj.Parameters.Add( ":MoreInfo", System.Data.OracleClient.OracleType.VarChar, MoreInfo.Length , "moreinfo" ).Value = MoreInfo;
CmdObj.Parameters.Add( ":SaveDate", System.Data.OracleClient.OracleType.DateTime ).Value = Convert.ToDateTime (SaveDate);


//打开数据库连接更新数据库
try
{
MyConn.Open ();

CmdObj.ExecuteNonQuery();

}
catch(InvalidOperationException ee)
{
return ee.Message ;
}
finally
{

MyConn.Close ();

}
return "保存成功";
}