Update Quoted Field in ORACLE Using ADO

BrianG
BrianG used Ask the Experts™
on
Hello all,

I have a database table that has a field named "Source".  I created the field with quoted identifiers since Source is a reserved word.  I have a VB app that selects rows from this table and performs an update on the "Source" field using an ordinal position in the recordset.

sql = "SELECT ""Source"" FROM ""DSRACTVYGUIControls"" WHERE ""ControlName"" = 'MyNewComponent_Src1Agg'"
rs.Open sql, conn, , , adCmdText
While Not rs.EOF
     rs(0) = Replace(rs(0),"SEARCH", "REPLACE")
     rs.Update
     rs.MoveNext
Wend

The problem comes when I call the update.  I receive the error:
[Oracle][ODBC][Ora]ORA-00904: invalid column name

It would seem that ADO is not using the quoted identifiers when it trys to perform the update.  Does anyone have any thoughts on this?  Is there an option in the connection or recordset object that I need to set?

I know that I could just use an update query, but I would really like for the update to be performed through the ado recordset since this works with other databases and I have this trype of scenario many places in my code.

Thanks for your help,

brian
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I haven't worked in Oracle in a while, but have you tried:
sql = "SELECT [Source] FROM [DSRACTVYGUIControls] WHERE [ControlName] = 'MyNewComponent_Src1Agg'"
Of course, that last posting could be shoing my SQL Server side.  If Oracle needs doublequoted field/table names for reserved keywords, use:
sql = "SELECT " & chr$(34) & "Source" & chr$(34) & " FROM " & chr$(34) & "DSRACTVYGUIControls" & chr$(34) & " WHERE " & chr$(34) & "ControlName" & chr$(34) & " = 'MyNewComponent_Src1Agg'"

Author

Commented:
Just performing the select and retrieving the values works with the quotes around the table and column names.  However when I attempt to update a field in the recordset I get an error.  I can assigna value to the field ie: rs(0) = "New Value" But when I do rs.Update I receive an error from ORACLE about an invalid field.  I think that ADO is either removing the quotes that I placed around the table and fields or adding another set around them.

It is beginning to look like it is a bug in ADO.

Thanks,
Brian
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dirk HaestProject manager

Commented:
Try this: rs(0) = replace(newvalue,"'","''")

Author

Commented:
Just performing the select and retrieving the values works with the quotes around the table and column names.  However when I attempt to update a field in the recordset I get an error.  I can assigna value to the field ie: rs(0) = "New Value" But when I do rs.Update I receive an error from ORACLE about an invalid field.  I think that ADO is either removing the quotes that I placed around the table and fields or adding another set around them.

It is beginning to look like it is a bug in ADO.

Thanks,
Brian
Commented:
You should not require quotes around your column names for starters, as creating a table with a column called SOURCE is legal:

DEV SQL> create table mytemp
  2  (source varchar2(10));

Table created.

DEV SQL> desc mytemp
 Name                        Null?    Type
 ------------------------ -------- ------------
 SOURCE                            VARCHAR2(10)

DEV SQL> create table mytemp2
  2  ("SOURCE" varchar2(10));

Table created.

DEV SQL> desc mytemp2
 Name                        Null?    Type
 ------------------------ -------- ------------
 SOURCE                            VARCHAR2(10)

As you can see, thereis no difference to Oracle.

Second, I think with ADO and Oracle you need to investigate the recordset parameters more closely, namely CursorType, as I think you need to open it up as a Dynamic Recordset for that to work properly.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdobjodbrec.asp

Also, I have a sneaking suspicion that something in my past tells me Oracle does not support that type of updating, but I could be wrong.




Author

Commented:
markag,

I have found that creating the column with all upper case does solve part of my problem.  However, this brings up a new one.  I also have a column named Comment.  It looks like I must use quotes around this column when I create it and refer to it, even if I use upper case.

Are there any tricks to getting this field to work?

Thanks,
Brian

Commented:
Brian,

You need to get away from using keywords/reserved words as column names! COMMENT is an oracle reserved word. If you are the guy creating the table, then that is a major no-no, and give it another name. You got 30 characters to work with for naming an Oracle object, and having to use a keyword is simply a bad idea and very unecessary. Call it MY_COMMENT or something ELSE other than COMMENT.  

If you have a DBA that is doing that, then he needs to be lashed 39 times and subsequently forced to work on Sql Server 6.0.

Author

Commented:
Mark,

The problem is that we have an application that was developed for SQL Server and Access.  We are trying to make the code generic enough so that it will work with clients using SQL and with a new client that is using ORACLE.

When we developed the system we only took into account SQL reserved words.  You have to admit that some of the reserved words in ORACLE are not that intuitive.  What database has Comment as a reserved word besides ORACLE?

Unfortunately, changing the field name just is not a viable option.

Thanks,
Brian

Commented:
Brian,

You need to get away from using keywords/reserved words as column names! COMMENT is an oracle reserved word. If you are the guy creating the table, then that is a major no-no, and give it another name. You got 30 characters to work with for naming an Oracle object, and having to use a keyword is simply a bad idea and very unecessary. Call it MY_COMMENT or something ELSE other than COMMENT.  

If you have a DBA that is doing that, then he needs to be lashed 39 times and subsequently forced to work on Sql Server 6.0.

Author

Commented:
Since changing the column names is not an option, the best solution seems to be creating the columns with all uppercase and referring to "COMMENT" with quotes.

Here is a link to the ORACLE area where I asked the same question.

http://www.experts-exchange.com/Databases/Oracle/Tools_Development/Q_20402559.html

Thanks for your help!
Brian

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial