How to handle result of OracleCommand.GetRowId()?

How to handle result of OracleCommand.GetRowId()?

Postby 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]
sprinter252
 
Posts: 23
Joined: Fri 16 Nov 2007 20:10
Location: Germany

Postby 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.
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby 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
sprinter252
 
Posts: 23
Joined: Fri 16 Nov 2007 20:10
Location: Germany

Postby 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.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby 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
sprinter252
 
Posts: 23
Joined: Fri 16 Nov 2007 20:10
Location: Germany

Postby 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!
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby 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
sprinter252
 
Posts: 23
Joined: Fri 16 Nov 2007 20:10
Location: Germany

Postby Alexey.mdr » Tue 08 Jan 2008 15:04

I hope that the information in this post has helped you.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby 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...
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18


Return to dotConnect for Oracle