obtain object_id value for executing SQL Anywhere code

[#1]

How can I obtain the object_id value for executing SQL Anywhere code. If accessible, I could then obtain the name of the executing SQL Anywhere code with:

declare my_object_id integer;
declare executing_object_name char (128);

set my_object_id = ???;
set executing_object_name = object_name (my_object_id);

If I knew what to substitute into '???', I could obtain the name of any stored procedure or trigger that is currently executing.

Is there another way to do this?

Thanks, Dan.

[MarkCulp]

Each type of object (procedure, table, etc) has an object_id column in the corresponding SYS* table (e.g. sysprocedure, systable, etc). So to get the object id of an entity you need to query the associated SYS table.

For example, to find the object id of a procedure, use

select object id from sysprocedure where proc_name = 'my_procedure';

You can also just use the OBJECT_ID() function - e.g.

select object_id( 'myuser.my_procedure' );

FWIW: The OBJECT_ID() function simply queries the sysobject table to get the answer!

HTH

[dhkom]

Thanks, but my objective was to determine the name of the object - in particular the name of the server code that is executing. Your suggestion has me know the name that I was looking to calculate.

My larger objective, if this helps, is to be able to emit debugging code from any trigger or stored procedure that includes the name of the stored procedure - without having to code the name myself.

[MarkCulp]

Ah, so then you want to use OBJECT_NAME() function - see http://dcx.sap.com/index.html#sqla170/en/html/81f83e6a6ce210148bb8a319b96e3f9a.html

[dhkom]

Indeed - but to use OBJECT_NAME(), I need an object_id value. In stored procedures I have @@Procid. Volker, below, provided a detailed solution for obtaining object_id values within Triggers and Events. He admits the solution is undocumented, and thus could stop working in a future release. It would be nice if an @@ObjectID were available in any server code which could feed into OBJECT_NAME().

Actually, I am wondering whether OBJECT_NAME() is very useful without something like an @@ObjectID global variable.

[VolkerBarth]

He admits the solution is undocumented.

No, I'm just another customer, so I would not even claim that this "solution" is one - it's just a guess that the @@procid has particular values within triggers and events...

Some SAP engineer would have to acknowledge that "solution" first before it could be claimed "undocumented" - and could then possibly tell if it's reliable or not.

[VolkerBarth]

It would be nice if an @@ObjectID were available in any server code which could feed into OBJECT_NAME().

Hm, it seems naturally that running code has a global "self reference" via @@procid. But what would you expect that requested new "@@objectid" to refer to, as that generally may be relate to tables, columns, procedures, users, ... - all kinds of objects. So within running code, it could also make sense to know what table/column/... the code is working on currently...

IMHO, in case the @@procid really does have the guessed meaning for triggers/events, it would be more helpful to

• either document it that way, or
• add a builtin function (say, "object_id_to_proc_id()") that would map the internal proc id to the object id of the according procedure/trigger/event/whatever can run code...