How to insert null in C++ Class library - OO4O and Wizards(Archived)

I am using oo4o Class library. I am inserting arrays into the database
This is my code (it is simplified):
// C++ Code
OStartup();
oRes = m_o_Session.Open(); // m_o_Session is OSession // oRes is oresult
oRes = m_o_db.Open(m_o_Session,sSource,sUser,sPassword); // m_o_db is ODatabase
oRes = m_o_db.BeginTrans();
// the insert proccess
// Add Params
OParameterCollection oParamCol = oDB.GetParameters();
OParamArray o_NUM_INDEX_Ary = oParamCol.AddTable("xi_lv_NUM_INDEX",OPARAMETER_INVAR,OTYPE_NUMBER,10);
OParamArray o_TEXT_Ary = oParamCol.AddTable("xi_sv_TEXT",OPARAMETER_INVAR,OTYPE_VARCHAR2,10,250);
OParamArray o_NUMBER_OPT_Ary = oParamCol.AddTable("xi_dv_NUMBER_OPT",OPARAMETER_INVAR,OTYPE_NUMBER,10);
for(int i =0;i<10;i++){
o_NUM_INDEX_Ary.SetValue(i,i);
o_TEXT_Ary.SetValue("asdasd",i);
o_NUMBER_OPT_Ary.SetValue(??????,i);
}
oRes = oDB.ExecuteSQL("INSERT INTO GEN_TABLE_TEST_T (NUM_INDEX,TEXT,NUMBER_OPT) VALUES (:xi_lv_NUM_INDEX,:xi_sv_TEXT,:xi_dv_NUMBER_OPT)");
oParamCol.Remove("xi_lv_NUM_INDEX");
oParamCol.Remove("xi_sv_TEXT");
oParamCol.Remove("xi_dv_NUMBER_OPT");
oRes = m_o_db.CommitTrans();
oRes = m_o_db.Close();
m_o_Session.Close();
OShutdown();
my question is where I marked ?????? I want to insert null to a numeric field
I tried to put NULL instead of ?????? - does not work
I tried to put OValue initialized with null instead of ?????? - does not work
and I need to send him in the insert Sql because sometimes it does not null and i have to give him value.
thanks is advance
Yoav

Just create an empty OValue object:
OValue nullv; // construct null value
then call the appropriate SetValue method...
xxx.SetValue(nullv);
Should do it.

Related

Extending Entlib for ODP.NET to use ArrayBindCount

I've modified Microsoft's Enterprise Library to use ODP.NET (new beta version) but it doesn't implement all the features, specifically passing arrays. I want to be able to set the OracleCommand parameter ArrayBindCount but this isn't implemented in the EntLib I can't find an obvious way to do it.
Has anyone extended the Entlib to use the ODP.NET and implemented the ArrayBindCount property on the OracleCommand?
Here's a link to what I did to get ODP.NET working with the EntLib:
http://www.gotdotnet.com/codegallery/messageboard/thread.aspx?id=295a464a-6072-4e25-94e2-91be63527327&mbid=aaae04ae-6f95-4162-980a-9ae55cddff40&threadid=70c21f60-c1df-411a-b2e1-6c4d5054524f 
Create a parameter type factory and use that type to dispatch the creation of respective parameter.
Create a hierarchy called simple parameter and collection parameter. Collection type parameter can be further divided into array type and bulk type.
Put the following code into a collection base. And the array and bulk will differ in CollectionType property of the command object.
// OracleCommandWrapper oraCmd is the already extended code
ArrayList arr = p.value; // get an array list of values
int arraySize = arr.Count;
// first create the command with basic information
oraCmd.AddInParameter(p.name, DbType.<the type>);
// get the internal object
OracleCommand cmd = (OracleCommand)oraCmd.Command;
OracleParameter param = cmd.Parameters[p.name];
param.OracleDbType = OracleDbType.<the type>;
param.Direction = p.direction;
// convert the arraylist to basic array. This is used to bind the value
if (arraySize > 0)
{
     param.ArrayBindSize = (int[])p.dataSize.ToArray(typeof(int));
     param.Value = p.value.ToArray(typeof(<the type>));
     
     if ( (p.direction == ParameterDirection.Input) ||
          (p.direction == ParameterDirection.InputOutput) )
     {
          ICollection c = (int[])p.status.ToArray(typeof(int));
          param.ArrayBindStatus = (OracleParameterStatus[])c;
     }
}
else
{
     param.Value = null;
}
// to the created parameter, the array Parameter will look like the following
param.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
// to the created parameter, the bulk parameter will look like the following
param.CollectionType = OracleCollectionType.None;

XMLType Insertion and ORA-01704 error

I am trying to insert a XML string from C# into a XMLType column. However, when I use OracleDataAdapter.Fill , I get the ORA-01704: string literal too long. I was wondering why I am getting this error since I am inserting into XML Type, which should have enough space for the XML. 
Hi,
It doesnt seem that "inserting" and "datadatapter.fill" go together. You're inserting by filling a data adapter?
Anyway, usually that error occurs when a hard coded sql statement is too long. Try bind variables instead.
Cheers,
Greg 
Actually, I am inserting, and at the same time getting the DataSet with the information. independent of that, I think I am getting the error because my sql statement created contains the XML which is definitely more than 4000 characters. Can you point me to how I can use bind variables? And how that can be achieved using the .NET environment?
Thanks! 
Following is a common code to create an oracle parameter....
// OracleCommand oraCmd
OracleParameter param = oraCmd.CreateParameter();
param.ParameterName = name;
param.DbType = dbType;
param.Size = size;
param.Value = (value == null) ? DBNull.Value : value;
// modify parameter type and value for special cases
switch (dbType)
{
// for Guid, change to value to byte array
case DbType.Guid:
guidParameters.Add(param.ParameterName, "System.Guid");
param.OracleDbType = OracleDbType.Raw;
param.Size = 16;
// convert Guid value to byte array only if not null
if ((value is DBNull) || (value == null))
{
param.Value = Convert.DBNull;
}
else
{
param.Value = ((Guid)value).ToByteArray();
}
break;
default:
break;
}
param.Direction = direction;
param.IsNullable = nullable;
param.Precision = precision;
param.Scale = scale;
param.SourceColumn = sourceColumn;
param.SourceVersion = sourceVersion;
// add the created parameter to the command object
oraCmd.Parameters.Add(param)

[Solved] ODP 10.2.0.2.20 and Oracle 8.1.7.0.0 Update not working

Hi
I'm using ODP 10.2.0.2.20 with Visual Studio 2005 to access an Oracle Database 8.1.7.0.0
I want to do UPDATE on a table, but when executing the query, there are no line updated...
Here is the equivalent code of what I'm doing :
OracleConnection ocon = new OracleConnection(connectionString);
ocon.Open();
OracleTransaction otx = ocon.BeginTransaction();
OracleCommand ocom = ocon.CreateCommand();
ocom.CommandText = "UPDATE TEST SET LIB=:LIB WHERE COD=:COD";
ocom.Parameters.Add(":LIB", "A");
ocom.Parameters.Add(":COD", "X");
int ret = ocom.ExecuteNonQuery();
Console.WriteLine(ret); //Prints 0
otx.Commit();
ocon.Close();
My table is :
CREATE TABLE TEST
(
COD VARCHAR2(2) NOT NULL,
LIB VARCHAR2(100) NOT NULL
);
The data :
Insert into TEST (COD, LIB) Values ('X', 'la déclaration sommaire');
Insert into TEST (COD, LIB) Values ('Y', 'la déclaration initiale');
Insert into TEST (COD, LIB) Values ('Z', 'le document précédent');
COMMIT;
This UPDATE should update the first row, but it doesn't. When I suppress the WHERE in the query, the 3 rows are updated.
I don't see where the problem can come from...
Message was edited by: MaitreDede
Added [Solved] to subject 
I could not reproduce the problem.
But I was connecting to Oracle 8.1.7.4.1.
Sinclair 
The equivalent code is working well, but that's strange...
My original code builds dynamically the queries, because the function is to read files to update db tables.
First, I build the query and I get a query like this :
UPDATE table SET col1=:col1, col2=:col2 WHERE key1=:key1
I put it into the CommandText of my OracleCommand.
Then, I clear all previous parameters (OracleCommand.Parameters.Clear())
After, I assign parameters :
foreach(string parameterName in pList.Keys)
{
ocom.Parameters.add(parameterName, pList[parameterName]);
}
And I execute the query
ocom.ExecuteNonQuery();
But the returned value is still 0... I don't see where my code is wrong, other people does not see where the error can be...This is really strange...
Message was edited by:
MaitreDede
The INSERT, SELECT and DELETE queries are built the same way, and they work... 
I've done some tests :
The equivalent code (first post) is working. It's "hardcoded" queries and values.
The dynamically constructed queries and values code does not work (no rows updated).
The OracleConnection, OracleCommand, OracleParameters are equal (same property values) in both cases... 
Here is my full code :
private void rowUpdate(string tableBdd, OracleConnection ocon, AssociationsChamps champ, XmlElement ligne)
{
//For debug
if (Program.Debug)
{
if (!Debugger.IsAttached) Debugger.Launch();
Debugger.Break();
}
//Building UPDATE query
string pattern = "UPDATE {0} SET {1} WHERE {2}";
string[] colsSet = champ.champsNonCle(TypeChampRetour.Bdd); //DB cols to update
string[] colsWhere = champ.champsCle(TypeChampRetour.Bdd); //DB cols of clause
string set = OracleHelper.getClause(colsSet, ", "); // to format the string like X=:X, Y=:Y
string where = OracleHelper.getClause(colsWhere, " AND "); // to format the string like K=:K AND L=:L
string query = string.Format(pattern, tableBdd, set, where); //UPDATE table SET X=:X, Y=:Y WHERE K=:K AND L=:L
OracleCommand ocom = new OracleCommand(query, ocon);
//Parameter values
Dictionary<string, object> lstValues = new Dictionary<string, object>();
foreach (string champFichier in champ.ChampsFichier)
{
string champBdd = champ.ChampBdd(champFichier);
string paramName = string.Format(":{0}", champBdd);
bool isKey = champ.ChampCle(champFichier);
string paramValue = ligne[champFichier].InnerText;
if (query.Contains(paramName))
{
if (isKey && (paramValue == null || paramValue.Length == 0))
{
paramValue = " ";
}
ocom.Parameters.Add(paramName, paramValue);
}
}
int ret = ocom.ExecuteNonQuery();
System.Diagnostics.Debug.WriteLine(ret);
} 
Does set ocom.BindByName to true help?
Sinclair 
Yes !!!!
Thanks.
I haven't seen this property before...

Creating Named Query: from OracleCallableStatement

We have a great many PL/SQL Procs in an existing legacy system that return PL/SQL Boolean, that we wish to call from toplink.
Ideally we would like to create Toplink Nameded queries that wrap the SQL call statements.
Does anyone know if its possible to convert a call like the following into a Toplink named query?
String domain = "COST CENTRE";
String value = "OP";
OracleCallableStatement xcall = (OracleCallableStatement) con.prepareCall(
"BEGIN ? := SYS.SQLJUTL.bool2int(is_valid_value(?, ?)); END;");
xcall.registerOutParameter( 1, OracleTypes.INTEGER);
xcall.setString(2,domain);
xcall.setString(3,value);
xcall.execute();
System.out.println ("Status = " + xcall.getInt(1));
...
FUNCTION is_valid_value(
p_rv_domain IN CG_REF_CODES.RV_DOMAIN%TYPE,
p_rv_low_value IN CG_REF_CODES.RV_LOW_VALUE%TYPE
)
RETURN BOOLEAN
IS
....
Many thanks,
Lee. 
You can define Stored Procedure calls in TopLink through the StoredProcedureCall class. StoredProcedureCall allows you to define output parameters to access return values.
Example:
StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("is_valid_value");
call.addNamedArgumentValue("DOMAIN", domain);
call.addNamedArgumentValue("VALUE", value);
call.addNamedOutputArgument("RESULT", "RESULT", Integer.class);
List results = session.executeSelectingCall(call);
boolean value = ((Integer) ((Map) results.get(0)).get("RESULT")).intValue() == 0;
However what you seem to be accessing is a stored function, not a procedure. To access a stored function from TopLink you must use an SQLCall and select the return value through the DUAL table.
Example:
SQLCall call = new SQLCall("Select SYS.SQLJUTL.bool2int(is_valid_value(" + domain + ", " + value + ")) from dual");
List results = session.executeSelectingCall(call);
Thanks for trying.
Alas that doesn't help. For example,
you can try to convert the following func. call into Toplink :
OracleCallableStatement xcall = (OracleCallableStatement) con.prepareCall(
"BEGIN ? := SYS.SQLJUTL.bool2int(SYS.SQLJUTL.int2bool(0)); END;"
);
xcall.registerOutParameter( 1, OracleTypes.INTEGER);
xcall.execute();// which works fine in SQL
The Toplink solution as suggested is :
SQLCall xcall = new SQLCall("SELECT SYS.SQLJUTL.bool2int(SYS.SQLJUTL.int2bool(0)) FROM DUAL");
sess.executeSelectingCall(xcall);
And results in:-
// ORA-06552: PL/SQL: Statement ignored
// ORA-06553: PLS-382: expression is of wrong type
- not as simple as it seems.
If anyone has another solution, please post it.
Thanks,
Lee.

Parameter.Value proper cast with c# oracle number to c# int??

Hello,
I'm trying to get a return value from an Oracle stored function using C# and odp.net.
The function basically looks like:
function f( id in number ) return number
is
id_new number := null;
begin
select sequenct_name.nextval
into id_new
from dual;
-- do some stuff
return id_new;
end;
The C# call basically looks like
cmd.Parameters.Clear();
     cmd.CommandText = "f";
     cmd.CommandType = CommandType.StoredProcedure;
     // set up return variable
     ret = cmd.Parameters.Add( "id_new",     // name
          OracleDbType.Decimal );          // type
     ret.Direction = ParameterDirection.ReturnValue;
     // pass in original id
     cmd.Parameters.Add( "id",      // name
          OracleDbType.Decimal,     // type
          id,               // value
          ParameterDirection.Input ); // direction
     cmd.ExecuteNonQuery();
     if ( ret.Value != null ){
//          ai_id_new = (int) ret.Value;
//          ai_id_new = (OracleDbType.Decimal ret.Value;
//          ai_id_new = (int)(decimal) ret.Value;
//          ai_id_new_d = (decimal) ret.Value;
//          ai_id_new = (int)(Int32) ret.Value;
//          ai_id_new = (Int32) ret.Value;
          ai_id_new = int.Parse(ret.Value.ToString());
     }
You can see all of the casts that I had to comment out. The last line works, but I don't like it:
ai_id_new = int.Parse(ret.Value.ToString());
Does anyone know what the proper cast is for the ret.Value Object?? to get it assigned back to a C# int?
Thanks,
David
figured it out
have to do this in c# file
int ai_new_id;
OracleDecimal ora_dec;
.....
ora_dec = (OracleDecimal) ret.value;
ai_new_id = ora_dec.ToInt32();

Categories

Resources