Hello, I'm use sapdb Version: 'Kernel 7.4.3 Build 027-123-048-452'
I have problem, my database crash
I sent you the source DB procedure LAB.add_usergroup, executing
which the crash is happeneing. Having tested the procedure, I've found the
reason of crash. If I delete call LAB.add_event, the crash is not
happening.
type: Error
source: SAPDB:Lab
category: Fast
code: 18245
date: 03.10.2003
time: 18:34:00
user: NT AUTHORITY\SYSTEM
computer: EMELIAN
Desc:
SAPDB - Lab :
- ERROR TID: 0x244 PID: 0x3DC MsgID: 18245
Kernel aborts,
(view diagnostic file: 'knldiag')
type: error
source: SAPDB:Lab
category: Fast
code: 18006
date: 03.10.2003
time: 18:34:00
user: NT AUTHORITY\SYSTEM
computer: EMELIAN
Desc:
SAPDB - Lab :
- ERROR TID: 0x244 PID: 0x3DC MsgID: 18006
EXCEPTION:0xc0000005 Addr:0x6a732d ( 0x1:0x658:0:0 )
I create db proc
CREATE DBPROC LAB.add_usergroup
( IN role VARCHAR(50)
, IN group_name VARCHAR(50)
, IN parent INT
, OUT group_id INT
)
AS
VAR parent_role VARCHAR(50); sql_cmd VARCHAR(255); log_msg VARCHAR (255);
log_type VARCHAR (255);
SET log_type = 'info';
SET log_msg = '';
TRY
TRY
select user_login from LAB.lab_users
where user_id = :parent;
FETCH INTO :parent_role;
CATCH
IF $rc = 100 THEN STOP(5001, 'invalid argument parent ''' || parent || '''
not found')
ELSE STOP($rc, $errmsg);
TRY
select * from LAB.lab_users
where user_name = :group_name;
IF $rc = 0 THEN STOP(5002, 'invalid argument: group name ''' || group_name
|| ''' already exists in system');
CATCH
IF $rc <> 100 THEN STOP($rc, $errmsg);
TRY
select * from DOMAIN.ROLES
where role = UPPER(:role);
IF $rc = 0 THEN STOP(5003, 'invalid argument: role name ''' || role || '''
already exists in database');
CATCH
IF $rc <> 100 THEN STOP($rc, $errmsg);
TRY
SET sql_cmd = 'create role ' || role;
execute sql_cmd;
CATCH
STOP($rc, $errmsg);
TRY
SET sql_cmd = 'grant ' || parent_role || ' to ' || role;
execute sql_cmd;
CATCH
STOP($rc, $errmsg);
TRY
SET sql_cmd = 'insert into LAB.lab_users (parent, group_flag, user_name,
user_login) values (' || parent || ' , 1 , ''' || group_name || ''' , ''' ||
role || ''')';
execute sql_cmd;
SET group_id = LAB.lab_users.CURRVAL;
SET log_type = 'info';
SET log_msg = 'The user group ''' || group_name || ''' || role || ''' ||
role || ''' parent ''' || parent || ''' with group_id ''' || group_id || '''
has been added successfully.';
call LAB.add_event(:log_type, 'add_usergroup', 'LAB.add_usergroup',
:log_msg);
CATCH
STOP($rc, $errmsg);
CATCH
BEGIN
SET log_type = 'error';
SET log_msg = 'The user group ''' || group_name || ''' || role || ''' ||
role || ''' parent ''' || parent || ''' has not been added. Error ''' || $rc
|| ''', errmsg ''' || $errmsg || '''';
call LAB.add_event(:log_type, 'add_usergroup', 'LAB.add_usergroup',
:log_msg);
STOP($rc, $errmsg);
END;
CREATE DBPROC LAB.add_event
( IN type VARCHAR(10)
, IN event VARCHAR(25)
, IN source VARCHAR(50)
, IN comment VARCHAR(2000)
)
AS
VAR node VARCHAR(50);
SET node = 'N/A';
TRY
TRY
SELECT APPLNODEID
FROM SYSDBA.TRANSACTIONS T, DOMAIN.CONNECTPARAMETERS C
WHERE T.SESSION = C.SESSION;
FETCH INTO :node;
CATCH
IF $rc <> 100 THEN STOP($rc, $errmsg);
INSERT LAB.lab_eventlog(source, type, event, event_user, computer,
event_time, comment)
values(:source, :type, :event, USER, :node, timestamp, :comment);
CATCH
STOP($rc);
CREATE TABLE LAB.lab_eventlog
(
id INT NOT NULL DEFAULT SERIAL(1) ,
record_time timestamp NOT NULL DEFAULT TIMESTAMP ,
source VARCHAR(50) UNICODE NOT NULL ,
type VARCHAR(10) UNICODE NOT NULL ,
event VARCHAR(25) UNICODE NOT NULL ,
event_user VARCHAR(50) UNICODE NOT NULL DEFAULT 'N/A' ,
computer VARCHAR(50) UNICODE NOT NULL DEFAULT 'N/A' ,
event_time timestamp NULL ,
comment VARCHAR(2000) UNICODE NULL ,
raw_data LONG UNICODE NULL ,
PRIMARY KEY(id),
CONSTRAINT type IN ('info','warning','error') ,
FOREIGN KEY ref2event (event)
REFERENCES LAB.lab_events (event)
)
///////////////////////////////////////////////////
// TABLE LAB.lab_events
CREATE TABLE LAB.lab_events
(
event VARCHAR(25) UNICODE NOT NULL ,
event_name VARCHAR(100) UNICODE NOT NULL ,
event_desc VARCHAR(255) UNICODE NULL ,
audit INT NOT NULL DEFAULT 1 ,
PRIMARY KEY(event) ,
CONSTRAINT unique_event_name
UNIQUE (event_name)
)
///////////////////////////////////////////////////
// events
insert lab_events (event, event_name, event_desc)
values ('add_usergroup' , 'add user group in system', 'add user group in
system')
insert lab_events (event, event_name, event_desc)
values ('drop_usergroup' , 'remove user group from system', 'remove user
group from system')
insert lab_events (event, event_name, event_desc)
values ('add_user' , 'add user in system', 'add user in system')
insert lab_events (event, event_name, event_desc)
values ('drop_user' , 'remove user from system', 'remove user from system')
message in eventlog
CREATE TABLE LAB.lab_users
(
user_id INT NOT NULL DEFAULT SERIAL(1),
parent INT NOT NULL ,
group_flag INT NOT NULL ,
user_name VARCHAR(50) UNICODE NOT NULL,
user_login VARCHAR(50) UNICODE NOT NULL,
PRIMARY KEY(user_id),
CONSTRAINT group_flag IN (0, 1),
CONSTRAINT unique_user_name
UNIQUE (user_name),
CONSTRAINT unique_user_login
UNIQUE (user_login)
)
insert LAB.lab_users (parent, group_flag, user_name, user_login)
values (0, 1, 'Users of laboratory', 'role_users_of_laboratory')
create role role_users_of_laboratory
create_lab_script
@echo off
rem #################################################################
rem #
rem #create example database TST with:
rem # - 20 MB data volume and 8 MB log volume
rem # - demo database user test (with password test)
rem ##################################################################
rem # IND -> independent program path
rem # DEP -> dependent path
rem # values will be set by installer, if it isn't set correctly - doit
yourself right (replace with "$")
set PATH=c:\program files\sapdb\indep_prog\bin;c:\program
files\sapdb\indep_prog\pgm;%PATH%
set INSTPATH="c:\program files\sapdb\depend"
rem name of the database
set SID=LAB
rem directory root for volumes (data,log)
set DATA=%SystemDrive%\data
rem start remote communication server
x_server stop
x_server start
rem stop and drop probably existing demo database
dbmcli -d %SID% -u dbm,dbm db_offline > NUL
dbmcli -d %SID% -u dbm,dbm db_drop > NUL
rem create new demo database
dbmcli -R %INSTPATH% db_create %SID% dbm,dbm
rem create directory where to put the database files
md %DATA% > NUL
md %DATA%\%SID% > NUL
rem setup database parameters
echo param_rmfile > param.tmp
echo param_startsession >> param.tmp
echo param_init OLTP >> param.tmp
echo param_put CAT_CACHE_SUPPLY 300 >> param.tmp
echo param_put CACHE_SIZE 3000 >> param.tmp
echo param_put MAXDATADEVSPACES 5 >> param.tmp
echo param_put RESTART_SHUTDOWN AUTO >> param.tmp
echo param_checkall >> param.tmp
echo param_commitsession >> param.tmp
echo param_addvolume 1 DATA %DATA%\%SID%\DISKD0001 F 2560 >> param.tmp
echo param_addvolume 1 LOG %DATA%\%SID%\DISKL001 F 1024 >> param.tmp
type param.tmp | dbmcli -d %SID% -u dbm,dbm
rem startup database
dbmcli -d %SID% -u dbm,dbm db_start
rem initialize database files
echo util_connect dbm,dbm > param.tmp
echo util_execute init config >> param.tmp
echo util_activate dba,dba >> param.tmp
type param.tmp | dbmcli -d %SID% -u dbm,dbm
rem load database system tables
dbmcli -d %SID% -u dbm,dbm load_systab -u dba,dba -ud domain
rem create database demo user
echo sql_connect dba,dba > param.tmp
echo sql_execute CREATE USER lab PASSWORD lab DBA NOT EXCLUSIVE >> param.tmp
type param.tmp | dbmcli -d %SID% -u dbm,dbm
echo medium_put data %DATA%\%SID%\datasave FILE DATA 0 8 YES > param.tmp
echo medium_put auto %DATA%\%SID%\autosave FILE AUTO >> param.tmp
echo util_connect dbm,dbm >> param.tmp
echo backup_save data >> param.tmp
echo autosave_on >> param.tmp
type param.tmp | dbmcli -d %SID% -u dbm,dbm
del param.tmp
perl 5.8
script
#####################################################################
#
use English;
use strict;
use subs;
use DBI qw(:sql_types);
my $dbi = DBI->connect("DBI:ODBC:LAB", "lab", "lab") or die "connecting:
$DBI::errstr";
my $sql = "call LAB.add_usergroup('role_biochemistry', 'biochemistry', 1,
?)";
my $sth = $dbi->prepare($sql)
or die "preparing: $DBI::errstr";
my $group = undef;
$sth->bind_param_inout(1, \$group, 4);
$sth->execute
or die "executing: err = $DBI::err, errstr = $DBI::errstr";
print "group = ", $group, "\n";
$sth->finish;
$dbi->disconnect;
exit;
#####################################################################
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
