CHARTOROWID (extension)
Syntax
CHARTOROWID ( char_expression )
Description
The scalar function CHARTOROWID returns a ROWID contained in the input argument in character form.
Example
The following example shows the character-string format for a row identifier supplied as an argument to CHARTOROWID. In this example, the format for a row identifier is an integer (delimited as a character string by single quotes).
SELECT ROWID, FLD FROM SYSCALCTABLE;
ROWID FLD
----- ---
0 100
1 record selected
-- CHARTOROWID requires single quotes around its argument
SELECT * FROM SYSCALCTABLE WHERE ROWID = CHARTOROWID ('0');
FLD
---
100
1 record selected
Notes
- The argument to the function must be of type character.
- The result is of internal ROWID type.
- If the argument char_expression evaluates to null, the result is null.
- The c-treeSQL statement execution returns error if the result of the input character expression does not contain a character string in the proper format for a row identifier.
CURRENT_USER SQL Function
Syntax
CURRENT_USER
Description
CURRENT_USER returns a character string identifier for the database user as specified in the current connection. It returns a character in the database character set. If the current connection did not specify an user, CURRENT_USER returns the login name as determined by the host operating system.
FairCom DB SQL statements can refer to CURRENT_USER anywhere they can refer to a character string expression.
Example
ISQL>SELECT CURRENT_USER FROM admin.syscalctable ;
FRED
---------------
fred
1 record selected
Note: SQL-99 compatible
DATABASE (ODBC compatible)
Syntax
DATABASE [ ( ) ]
Description
The scalar function DATABASE returns the name of the database corresponding to the current connection name. This function takes no arguments, and the trailing parentheses are optional.
Example
select database() from t2;
DATABASE
--------
steel
1 record selected
DATALENGTH function
Syntax
DATALENGTH( expr )
Description
Returns the number of bytes used to represent any expression. DATALENGTH is especially useful with VARCHAR, VARBINARY, LVARCHAR and LVARBINARY data types because these data types can store variable-length data.
Arguments
- expr - An expression of any type.
Example
SELECT DATALENGTH(tbl) FROM systables WHERE tbl='systables';
DATALENGTH(TBL)
--
9
DB_NAME (extension)
Syntax
DB_NAME ( )
Description
The scalar function DB_NAME returns the name of the database corresponding to the current connection name. It provides compatibility with the Sybase SQL Server function db_name.
Example
SELECT DB_NAME() FROM T2;
DB_NAME
-------
ctreev4
1 record selected
LAST_IDENT function (extension)
Syntax
LAST_IDENT ( )
Description
The scalar function LAST_IDENT() returns the last inserted IDENTITY value for the session.
Example
SELECT LAST_IDENT();
Notes
- The result is of type NUMERIC(32,0)
LAST_ROWID function
Syntax
LAST_ROWID ( )
Description
The scalar function LAST_ROWID() returns the last inserted ROWID value for the session.
Example
SELECT LAST_ROWID();
OBJECT_ID function (extension)
Syntax
OBJECT_ID ('table_name')
Description
The scalar function OBJECT_ID returns the value of the id column in the admin.systables, plus one. This function provides compatibility with the Sybase SQL Server function object_id.
Arguments
table_name
The name of the table for which OBJECT_ID returns an identification value.
Example
select id, object_id(tbl), tbl from admin.systables
1 where owner = 'admin';
ID OBJECT_ID(TB TBL
-- ------------ ---
0 1 systblspaces
1 2 systables
2 3 syscolumns
3 4 sysindexes
4 5 systsfiles
5 6 syslogfiles
6 7 sysdbbackup
7 8 syslogbackup
8 9 sysdbsyncpt
9 10 sysdbsuuid
10 11 syssyssvr
11 12 sysusrsvr
SESSION_USER SQL Function
Syntax
SESSION_USER
Description
The scalar function SESSION_USER returns the value of the FairCom DB SQL session identifier. c-treeACE SQLFairCom DB SQL returns the user.
Example
ISQL>SELECT SESSION_USER FROM admin.syscalctable;
FRED
-------
fred
1 record selected
Note: SQL-99 compatible
SUSER_NAME function (extension)
Syntax
SUSER_NAME ( [user_id] )
Description
The scalar function SUSER_NAME returns the user login name for the user_id specified in the input argument. If no user_id is specified, SUSER_NAME returns the name of the current user.
This function provides compatibility with the Sybase SQL Server function suser_name. It is identical to the USER_NAME function.
Example
select suser_name() from admin.syscalctable;
SUSER_NAME
----------
searle
1 record selected
select suser_name(104) from admin.syscalctable;
SUSER_NAME(104)
---------------
dbp
1 record selected
select id, tbl, owner from admin.systables
1 where owner = suser_name();
ID TBL OWNER
-- --- -----
41 test searle
42 t2 searle
43 t1 searle
3 records selected
USER function (ODBC compatible)
Syntax
USER [ ( ) ]
Description
USER returns a character-string identifier for the database user, as specified in the current connection. If the current connection did not specify a user, USER returns the login name as determined by the host operating system. This function takes no arguments, and the trailing parentheses are optional.
c-treeSQL statements can refer to USER anywhere they can refer to a character string expression.
Example
The following interactive c-treeSQL example shows connecting to a database as the user fred. Queries on two system tables illustrate the USER scalar function and retrieve the names of any tables owned by the user fred:
% isql -u fred tstdb
ISQL> select user from admin.syscalctable;
FRED
----
fred
1 record selected
ISQL> select tbl, owner from adminadmin.systables where owner = user();
TBL OWNER
--- -----
flab fred
1 record selected
USER_NAME function (extension)
Syntax
USER_NAME ( [user_id] )
Description
The scalar function USER_NAME returns the user login name for the user_id specified in the input argument. If no user_id is specified, USER_NAME returns the name of the current user.
The scalar function USER_NAME is identical to SUSER_NAME.