How to handle result of OracleCommand.GetRowId()?

sprinter252 · Post by **sprinter252** » Wed 19 Dec 2007 17:07

Hi,

I've got the problem, that my GetRowId() returns the string "AAAN8RAAFAAAA+cAA4". The following code than leads to a System.FormatException:

Code: Select all

public Int32 GetRowID(IDbCommand cmdThis)
{
    Int32 intRet = -1;
    OracleCommand cmdOra = (OracleCommand)cmdThis;
    string strTmp = cmdOra.GetRowId();
    if (!string.IsNullOrEmpty(strTmp))
    {                
        strTmp = Convert.FromBase64String(strTmp).ToString();
        Int32.TryParse(strTmp, out intRet);
    }
    return intRet;
}

So how would I obtain the value of GetRowId()?

Thanx, Alex[/code]

Paul · Post by **Paul** » Mon 24 Dec 2007 11:57

I suppose that ROWID value is not a Base64 string and Convert.FromBase64String function will return an error. Please see Oracle documentation for more details.

sprinter252 · Post by **sprinter252** » Thu 27 Dec 2007 10:12

but the CoreLab class documentation says, it's Base64. Go to object browser and select the CoreLab.Oracle.OracleCommand-class and then click on the GetRowID()-method. "Return Values" is described as:

ROWID string in Base64 format string.

Alex

Alexey.mdr · Post by **Alexey.mdr** » Thu 27 Dec 2007 14:14

Check that the function “GetRowID” returns the result.
Method “Convert.FromBase64String” expects another Base64 string than Oracle provides. The types are slightly different. Try to add “==” to the end of the current ROWID string.
“Int32.TryParse” might fail because the length of the ROWID string is far longer than the length of Int32 itself.
Refer to the help sources and check the usage of the “ GetRowID” function. Probably you are using it in a wrong context.

sprinter252 · Post by **sprinter252** » Wed 02 Jan 2008 11:20

Hi,

thanks alot but I mixed up something. No I know that this is the physical position of the data inside the Oracle storage system. Sorry for that. I'm used to work on DBMS which are using IDENTITY-columns. In that case the ROWID would be the last primary key given by DBMS. In Oracle I gues I have to do a query against the appropriate sequence

.

Thanks anyway,

Alex

Alexey.mdr · Post by **Alexey.mdr** » Thu 03 Jan 2008 10:11

I made a small example for you.

Code: Select all

            myConn.Open();            

            OracleCommand insertIdCmd = new OracleCommand();
            insertIdCmd.Connection = ora1110_SCOTT1;
            insertIdCmd.CommandText = "INSERT INTO SCOTT.DEPT (DEPTNO) VALUES (:DEPTNO)";
            insertIdCmd.Parameters.Add("DEPTNO",18);            
            insertIdCmd.Prepare();
            insertIdCmd.ExecuteNonQuery();
            string rowIdStr = insertIdCmd.GetRowId();

            OracleCommand selRowIdCmd = new OracleCommand();
            selRowIdCmd.Connection = ora1110_SCOTT1;
            selRowIdCmd.CommandText = "SELECT * FROM DEPT WHERE(ROWID = :myRowId)";
            selRowIdCmd.Parameters.Add("myRowId", rowIdStr);                   
            selRowIdCmd.ExecuteReader();               

            myConn.Close();

Hope it will help you!

sprinter252 · Post by **sprinter252** » Tue 08 Jan 2008 11:07

Hi Alexey,

thanks a lot for the example but still I would have to send a second query towards the DBMS manually. Because I use INSERT-triggers in combination with sequences, I also can get the currval from the trigger to retrieve the needed primary key. As I mentioned before, I just mixed up the terms "ROWID" and "Primary Key" because of my experiences with other DBMS

. But thanks anyway!

Alex

Alexey.mdr · Post by **Alexey.mdr** » Tue 08 Jan 2008 15:04

I hope that the information in this post has helped you.

Alladin · Post by **Alladin** » Fri 11 Jan 2008 17:42

You can use Raw(16) as primary key data type and Guid.NewGuid() to generate unique value on the client side without bothering RDBMS...