Newbie Question, VB - OO4O and Wizards(Archived)

Hi All,
I have a newbie question for you....
I am using VB to access oracle via OLE and am having trouble adding records.
I am doing basically the following...
set oraDyna = oraDBA.createdynaset("select * from products",0)
oraDyna.AddNew
At that point when it runs I get the error...
"OIP-O4117: Not an updatable set."
What am I doing wrong here?
Thanks
Dean

Replace the Option of 0 which is Default with the Oracle Mode option so you can get an updatble recordset because your dynaset will have the ability to 'ReFetch' data. A better practice is to not use updatable recordsets but rather direct SQL calls via the OraDatabase.ExecuteSQL method of better employing the OraSQLStmt object.
Good Luck,
Zane E

Related

Newbie on Sptial

Hi...hope u guyz can help me out..i am totally new to spatial data....after reading the manual still dont quite understand.....
when i am retrieving from my oracle database using JDBC recordset....i shld use rst.get what?String??
another thing is tt....after i get the coordinate on a place..which function shld i use to retrieve all the buildings within 1km from it...
Please help....urgent.. Thanks in advance guyz 
You should use sdoapi.jar to fetch the sdo_geometry objects in jdbc result set. specifically look at the java doc of the JGeometry class.
you use sql query to achieve the function you mentioned; specifically the spatial operator sdo_within_distance, like this:
select location from building_table where sdo_within_distance(place_geom, ...) = 'TRUE'
run this query in jdbc, and then use JGeometry to fetch all the buildings that are returned by this query. 
icic....thx a lot....anyone haf a website that haf working examples for us to dwnload ?? 
can help ???
how can i find the mid point of a sptial data...It is stored in the column of "GBLOB" of table "Places"...What function can i use to retrieve it....

Best Practices Question

I'm working through accessing Oracle (Express HR sample) from my .Net code. I notice the examples all use the commandtype.text and the subsequent SQL string.
Our project is a web site using ntier architecture and one of the specs is to not access the database directly as in the command.text example.
Is there a reason why I should not create a Stored Procedure to return a multi row set? IF not, an example of the Procedure would help. Here is the SQL string from the command.text example: "SELECT First_Name FROM Employees"
TIA,
Bob 
You can return a resultset from a stored procedure using a ref cursor in Oracle - see http://download-west.oracle.com/docs/cd/B25329_01/doc/appdev.102/b25312/procedures_plsql.htm#sthref876 and http://download-west.oracle.com/docs/cd/B25329_01/doc/appdev.102/b25108/xedev_plsql.htm#CHDCIGHE
for how to do this from PL/SQL
You will note that it's a little more complex to get this done in Oracle than it is in SQLServer, and there is no real advantage to this approach versus just accessing the database directly via SQL (remember to use bind variables), so you may want to re-think your standard.

Is using REF CURSOR the only way to retrieve a resultset from Oracle?

I've read the official Oracle for .Net docs and several articles on using Oracle under .Net, and it seems like the only way to retrieve a rowset from Oracle is to declare an output parameter of type REF CURSOR. The problem with ref cursors is that when my application is using generic Db engines with provider factory (defined in System.Data.Common), it cannot be used with Ref Cursors since it's REQUIRED to declare a parameter of OracleDbType = RefCursor for the command. So I have to write something like this:
DbCommand comm = CreateDbCommand(helper.CreateDbConnection());
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = rsrTreeProcedure;
DbParameter parm = helper.CreateDbParameter();
parm.ParameterName = "P_ACCESS_TREE";
((OracleParameter)parm).OracleDbType = OracleDbType.RefCursor;
parm.Direction = ParameterDirection.Output;
comm.Parameters.Add(parm);
Well, then I can use standard comm.ExecuteReader, etc, and use the common as usual, but that only line makes me sad, 'cause it breaks all the Generic Db idea.
So I want to know, is there any other way to retrieve a rowset from Oracle procedure, that would allow me using generic db and don't worry? 
Hi
Yes I'm also using REF CURSOR for retrieving result sets from a Stored Procdure when the column count is different - Otherwise I use pipelined table functions.
Sven
PS: Pipelined table functions just use "SELECT * FROM table(my_function)". This should be the answer to your problem. 
I definitely understand the desire to be generic in the data access layer, but I have to question whether it makes sense in the big picture. Unless you have a very small system, it is simply not going to be possible to migrate the back-end from Oracle to another DBMS. Oracle is the only one I know of with a true programming language (PL/SQL) in the DB. Trying to replace those procs with Microsft T-SQL procs, for example, would be a royal nightmare. Our procs have grown in complexity over the years so we just can't migrate. I'm not saying we'd want to migrate, but rather it is just not an option. If we were forced to replace the back-end DB, we'd end up re-architecting large parts of our system.
I started with a generic DAL but now I have a number of casts just like yours (for BLOBs and other Oracle unique items). If I had to do it over, I would not even attempt to make the DAL database agnostic. It just doesn't gain you anything in the real world and it causes a lot of pain. You end up stretching the abstraction to the breaking point, and you're not going to support other back-ends anyway (unless your system is very small).
Eric 
marantz effective solution has scalability issues compared to using cursors as well as being less flexible. However if neither the scalability nor flexibility are at issue in your circumstance (which is very possible) go for it.
However, I tend to agree with Eric's sentiment. Writing client any-database code is fundamentally a putrid pursuit.
Michael O
http://blog.crisatunity.com 
Let me give you a trick answer here. If you do not retrieve your data using Stored Procedure then you do not have to use REF CURSOR.
Check this out for more options:
http://www.oracle.com/technology/sample_code/tech/windows/odpnet/odpbasic.html
Ming Man

Bind grid to dynaset

I need to bind a MSFlexgrid to a oo4o dynaset in runtime (without a ORADC control). With ADO the command would be:
Set MSFlexGrid1.DataSource = rsContacts.DataSource
How can this be done with oo4o?
Thanks in advance 
I don't think you can. The OO4O Dynaset is not an ADO dynaset, just like the bookmarks are not ODBC/ADO bookmarks. They are the same name, but not the same thing. The data control is speifically designed for this purpose. If you can't use that then you need to use the dta unbound and programmatically push/pull the data.

Easier DataReader setup

This is somewhat of a 'dumb' question, but is there an easier way of setting up Oracle DataReaders? By this I mean - Does one have to always declare and set up an Oracle Command object for each datareader? Couldn't we do something like (in VB.NET):
drDetails = New Oracle.DataAccess.Client.OracleCommand("Select * from details", p_Connection).ExecuteReader
Or do we ALWAYS have to predefine a command object before we can use the datareader?
P.S. The help shows an overload with parameters (sqlcommandtext, connection) - if you use this method for the datareader setup, then what do you use to actually GET the data? (i.e. there wouldn't be a command so you couldn't use cmd.ExecuteReader) - the online help is very vague on this.
Thanks.
Tom 
Tom,
It depends on your programming language, and not ODP.NET.
For examples, VB.Net and C# should be able to do that.
To get data from the reader, you should call
reader.Next() and then reader.GetXX(int) to get the
data for a particular column.
Martha

Categories

Resources