Set Parameter Array of OTYPE_ROWID - OO4O and Wizards(Archived)

I have tried to adds a parameter array to a database as OTYPE_ROWID.
the sample codes are as follow:
{
//Use oo4o to connect DB
CString strRowIDArrayName = "U1";
long ParamRecordCount = 5;
ODatabase db;
db.open("DSN","UID","PWD");
OParameterCollection ParamColl = db.GetParameters();
ParamArray1=ParamColl.AddTable(strRowIDArrayName,OPARAMETER_INVAR,OTYPE_ROWID,ParamRecordCount);
BOOL bln = ParamArray1.IsOpen();
db.close();
}
Question:Why the bln is 0
and failure in use the ParamArray1 to execute SQL statement .
Please help me.
my email address: morigen_2001#yahoo.co.jp
thank a lot

Related

PreparedStatement only returning 256 results

Hi!I'm working on a project where the user can enter up to 1000 ids to fetch the corresponding objects from our Oracle database, connected using the 11.1.0.7.0 jdbc drivers. However, only 256 rows were available in the result. So I made a simple test, like so:public static void main(String[] args) throws Exception {         Class.forName("oracle.jdbc.OracleDriver");         Connection connection = DriverManager.getConnection("jdbc:oracle:thin:myuser/mypassword#myurl:1521:mydb");     PreparedStatement select = connection.prepareStatement("SELECT id FROM t01abc_regenh WHERE id in (?, ?,..., ?)"); // 1000 ?s         String[] idArray = new String[] {"010000010", "010000026", ..., "010000981"}; // 1000 ids         for(int i = 0; i<idArray.length; i++) {       select.setString(i+1, idArray); } ResultSet result = select.executeQuery(); boolean hasNext = result.next(); for(int i = 1; hasNext; i++) { System.out.println(i + " : " + result.getString(1)); hasNext = result.next(); } result.close(); select.close(); connection.close();}
The database logs show that the query is using the 1000 ids, but only the first 256 results were printed. So I tried again, using a non-prepared statement:
public static void main(String[] args) throws Exception { Class.forName("oracle.jdbc.OracleDriver"); Connection connection = DriverManager.getConnection("jdbc:oracle:thin:myuser/mypassword#myurl:1521:mydb"); Statement select = connection.createStatement(); String[] idArray = new String[] {"010000010", "010000026", ..., "010000981"}; // 1000 ids StringBuilder query = new StringBuilder("SELECT id FROM t01abc_regenh WHERE id in ("); for(int i = 0; i<idArray.length; i++) { query.append('\'').append(idArray[i]).append("', "); } query.delete(query.length() - 2, query.length()); query.append(')'); ResultSet result = select.executeQuery(query.toString()); boolean hasNext = result.next(); for(int i = 1; hasNext; i++) { System.out.println(i + " : " + result.getString(1)); hasNext = result.next(); } result.close(); select.close(); connection.close();}
This time the full 1000 results where printed. Is this a general thing with prepared statements or is it an issue with the Oracle database or the jdbc drivers? Is there a way to increase the number of returned results? Please help!                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
>
Is this a general thing with prepared statements or is it an issue with the Oracle database or the jdbc drivers?
>
None of the above - it is an issue with your code. You are comparing apples to oranges; an invalid comparison.
The queries are not identical so you should not expect identical results.
You are using this data
    String[] idArray = new String[] {"010000010", "010000026", ..., "010000981"}; // 1000 idsin two different queries.
The first test constructs a query of the form
    "SELECT id FROM t01abc_regenh WHERE id in (010000010, 010000026,..., ?)"); // 1000 ?sAnd the second constructs a query of the form
    "SELECT id FROM t01abc_regenh WHERE id in ('010000010', '010000026',..., ?)"); // 1000 ?sThat is the first query uses numeric values for the IN clause and the second query uses string values.
If the actual data is VARCHAR2 these will not match the same values.
A string value of '010000010' will not match a numeric value of 010000010 because when 010000010 is converted back to a string for the comparison there will be no leading zeroes so the converted value will be '10000010'
So the first query tries to match '010000010' with '10000010' and they do not match. 
aznan wrote:
I'm working on a project where the user can enter up to 1000 ids to fetch the corresponding objects
...
Please help!Besides the above you should note that that code is SPECIFICALLY open to sql injection attacks.
The solution is to use replacement parameters ('?') with a Prepared Statement.
And a side effect of that would be that it would solve your current problem too.

[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...

OutOfMemoryException - CLOB in C#

I am creating a program using the compact framework with c#. I don't know if some of you can hep me.
When I try to read a Clob value an OutOfMemoryException is thrown.
the code is:
String helpString = odc.DataReader.GetString(i);
TextBoxList(i).Text = helpString;
Besides this I am using the Oracle.DataAccess.Lite
Thanks in advance for the support. 
Hi
The code I'm using for clob contents is the same I'm using for blob contents. Somehow the clob field in Oracle Lite (as difference from .net oracle driver, or ODP) should be accessed as blob.
Here is the code sample:
OracleCommand cmd = new OracleCommand();
///here we have some cmd code
OracleDataReader rd = (OracleDataReader)cmd.ExecuteReader();
while (rd.Read())
{
object[] values = new object[rd.FieldCount];
rd.GetValues(values);
if (values[rd.GetOrdinal("ColumnName")] != DBNull.Value)
{
try
{
OracleBlob b = (OracleBlob)values[rd.GetOrdinal("ColumnName")];
System.Text.ASCIIEncoding enc = new System.Text.ASCIIEncoding();
byte[] content = b.GetBytes(0, (int)b.Length);
string clobContent = enc.GetString(content, 0, content.Length);
}
catch (InvalidCastException castEx)
{
//the column is not blob or clob - you may check db structure
}
catch (Exception ex)
{
//other possible exception
}
}
}
Another thing is to use object array when playing with tables that contain blob/clob fields. Somehow the oracle lite data reader is getting corrupt if looping through the values. Taking the values out from the beginning (in an object array).
If still the problem with out of memory is there, this means that your clob is too large and moving directly to one value cannot be done from memory limits perspective. Think of async read or reduce the size.
Hope it helps 
i am using blob fields for images but i guess that pretty much the same could be changed for characters,like a text file for instace
Oracle.Lite.Data.LiteDataAdapter mytempadapter = new Oracle.Lite.Data.LiteDataAdapter();
mytempadapter.SelectCommand=new Oracle.DataAccess.Lite.OracleCommand( "Select * from mytable where ..."OraCon);
DataSet dttempset = new DataSet("dsettemp");
mytempadapter.Fill(dttempset);
DataTable dtable=new DataTable();
dtable = dttempset.Tables[0];
FileStream FS = new FileStream(mytempfile, FileMode.Create);
DataRow dataRow =dtable.Rows[0];
long lngOffset = 0;
int chunk;
string ttypetemptemptemp=dataRow[1].GetType().ToString();
Oracle.DataAccess.Lite.OracleBlob mylob = (Oracle.DataAccess.Lite.OracleBlob)dataRow[1];
while(true)
{
byte[] temp=mylob.GetBytes(lngOffset, 256);
lngOffset = lngOffset + temp.Length;
if (temp.Length == 0) { break; }
else
{
FS.Write(temp, 0, temp.Length);
}
}
FS.Close();
FS = null;
tempfile now has the blob field in bytes, use fopen to read it.
of course you dont have to write it in the file you can just retrieve it in the FS.Write... line
can you adjust this a little to work with clob?
by the way why not save the characters as bytes in blob and use clob?

rs.getString()  sometime return NullPointerException

Dear All, Please help, I 'd got NullpointerException on rs.getString() about every 1 of 100 transaction. Sometime it work , sometime it fail , event if same record. as you see my code , the bold line is raise the error. for(rs = dao.connect().executeQuery(query); rs.next();) { banID = rs.getString("banid"); billMonth = rs.getString("billmonth"); paymentAmt = rs.getString("paymentamt"); IP = rs.getString("ip"); onlineKey = rs.getString("onlinekey"); transtatus = rs.getString("trans_status"); } ----- This is an sql SQL : select a.trans_status trans status, a.transban banid, a.trans_ip ip, a.trans_cksum onlinekey, b.total_amt paymentamt, c.service_desc billmonth from payment_trans a, orders_hist b, orders_item c where a.trans_id = '000547123' and b.trans_id = a.trans_id and c.trans_id = a.trans_id and c.item_no = (select min(item_no) from orders_item where trans_id = '000547123') *(total_amt is numeric(7,2) )* ----- When error occour: java.lang.NullPointerException at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.java:381) at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:1572) at th.co.truecorp.dao.PaymentSQL.qryForEndPayment(PaymentSQL.java:268) at th.co.truecorp.action.EndPaymentBGAction.execute(EndPaymentBGAction.java:81) I check all class12.jar (in web-inf/lib), obdc14dms.jar ( in oc4j sharedlib) but cannot find co-related jar there. ----- When success: banId : 190012079959 onlinekey : I11pOSlj billMonth : 032009 paymentAmt : 1202.79 {color:#ff0000}The upper success & fail case is query with same sql! {color}
Do you have several threads using the same Connection, Statement or ResultSet? You can't do that. Don't store any of those objects anywhere except local variables; if you only keep them in local variables other threads can't get at them. 
Thanks for response ,
I 'd checked my code that there are no thread or static object sharing. 
Judging by this
onlineKey = rs.getString("onlinekey");I assume this is some sort of web app?
Is this part of a Servlet/JSP app? If so, you are, of course, using threads, even if you did not program them yourself.
Some more info and/or code may help with this.
For the info, is this a web app (i.e. servlet/jsp)?
For the code, show where and how the "parts" (connections, statements, resultsets) are declared and defined. 
Dear ,
After you request sourcode , I found {color:#ff0000}Connection conn in PaymentDAO is static{color}
This should be fixed?
-----
This story comming from struts's Action class:
in execute() function:
PaymentSQL sql = new PaymentSQL();
Hashtable endPaymentData = sql.qryForEndPayment(id);
-----
in PaymentSQL.java
public class PaymentSQL
{
private PaymentDAO dao;
public PaymentSQL()
{
dao = new PaymentDAO();
}
public Hashtable qryForEndPayment(String transactionID)
{
String query = "";
query = query +" select a.trans_status trans_status, a.trans_ban banid, a.trans_ip ip, a.trans_c"+
"ksum onlinekey, b.total_amt paymentamt, c.service_desc billmonth , oc.companycode companycode "
;
query = query +" from payment_trans a, orders_hist b, orders_item c ,orders_contact oc ";+
query = query " where a.trans_id = '" transactionID "' ";
query = query +" and oc.trans_id = a.trans_id ";+
query = query " and b.trans_id = a.trans_id ";
query = query +" and c.trans_id = a.trans_id ";+
query = query " and c.item_no = (select min(item_no) from orders_item where trans_id = '" transactionID "') ";
String banID = "";
String billMonth = "";
String paymentAmt = "";
String IP = "";
String onlineKey = "";
String companycode="";
Hashtable result = null;
log.info("SQL : " + query);
String transtatus = null;
try
{
for(rs = dao.connect().executeQuery(query); rs.next();)
{
banID = rs.getString("banid");
billMonth = rs.getString("billmonth");
paymentAmt = rs.getString("paymentamt");
IP = rs.getString("ip");
onlineKey = rs.getString("onlinekey");
transtatus = rs.getString("trans_status");
companycode=rs.getString("companycode");
}
result = new Hashtable();
result.put("trans_status", transtatus);
result.put("banID", banID);
result.put("billMonth", billMonth);
result.put("paymentAmt", paymentAmt);
result.put("IP", IP);
result.put("onlineKey", onlineKey);
result.put("companycode",companycode);
rs.close();
dao.close();
}
catch(SQLException ex)
{
log.error(ex.getMessage(), ex);
}
catch(Exception ex)
{
log.error(ex.getMessage(), ex);
}
return result;
}
}
-----
public class PaymentDAO
{
private static Connection conn;
private Statement stmt;
public Statement connect(){
try{
conn = ConnectionManager.getInstance().getConnection();
stmt = conn.createStatement();
}catch(ConnectDBExcetion ce){
log.error(ce.getMessage(), ce);
}catch(SQLException se){
log.error(se.getMessage(), se);
}
return stmt;
}
} 
It might help to see what is behind
dao.close();but i assume it is only
stmt.close()and does not include
conn.close()If it does close the connection, however, that definately is a problem. Also, how it defines conn can also, of course, be a definite problem, depending on how that is synchronised.
The connection being static is probably not a good thing, no. The Statement being an instance variable is also, definately, not a good thing.
If this is a standalone app, then it can be completely acceptable that Connection is an instance variable (depending on how you handle transactions), and, in the same boat can be completely acceptable that it is a class variable (i.e. static). Neither is desirable, but not necessarily a real problem (although I would recommend against it without some compelling reasons).
Any smaller components (Statements, ResultSets, %MetaDatas) as instance (and even more so as class) variables is just begging for problems, though. Now, if you were to instantiate your "dao" object inside your methods, rather than inside your constructor, you could easily get away with it, however. Which, of course, also means that "dao" should be a local, and not an instance, variable.
Edit: And you still haven't said whether this is a web app or not. 
hlex wrote:
public class PaymentDAO
{
private static Connection conn;
private Statement stmt;There's the problem. Connection is static and that is far from threadsafe. Statement is an instance variable and thus also not threadsafe, which proves that you absolutely don't know where you're talking about:
hlex wrote:
Thanks for response ,
I 'd checked my code that there are no thread or static object sharing.You should always acquire and close external resources in the shortest possible scope. Redo the DAO. You may get new insights out of this article: [http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html]. 
I fix the static problem and wait to see the result.
Million Thanks to your helpfulness,Kindness , carefulness. At first I don't except anyone will really interest to help me like you.
Sorry for misleading about thread at first. There 's my fault cause this coding is not mine and I didn't analyst all of it.

OATS - Open Script: How to capture data from the SQL query

Hi Everyone,
I am trying to gather data for the Open Script tool using SQL query.
As an example:
SELECT CONTAINER_ID FROM available_containers WHERE CONTAINER_TYPE = ? AND STATUS = ?
The next step of the script will be using the value of the CONTAINER_ID for inputting it in a Form.
Can anyone tell me how I can script that?
Thank you! 
First Step connect to your DB either by using the below function or can use the code simply, but do not forget to pass HOSTNAME, ServiceName, DBUserName, DBPassword from your testing database. public Connection dbconnect() throws Exception{     java.sql.Connection dbconnection = null;     java.sql.SQLException jsqlexcep;try{     Class.forName("oracle.jdbc.driver.OracleDriver");     dbconnection = java.sql.DriverManager.getConnection("jdbc:oracle:thin:#"<<HOSTNAME (replace)>>":1521/"<<ServiceName(replace)>>"", "<<DBUserName (replace)>>",""+     <<DBPassword (replace)>>");}     catch (SQLException e){     System.err.print(e.getMessage());}     finally{     return dbconnection;} } Second step is to execute the query, you can pass your above SQL statement as a string, which returns result set. public ResultSet executequery(Connection connection, String sql_stmt) throws Exception{     try     {          Statement stmt;          ResultSet rset;          stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);          rset = stmt.executeQuery(sql_stmt);          return rset;     }     catch (SQLException e)     {          System.err.print(e + "\n" + sql_stmt);         return null;     }} Third loop through your result setExample:ResultSet.next();CONTAINER_ID = ResultSet.getString(1);

Categories

Resources