xReporter

I started evaluation of this project. The installation instructions are taylored for MySQL database. I need to make this work with Sybase. So, I'll throw in my modifications here.

First, Start with a new configuration has a table creation script, which must be modified. The reason is that "role" and "function" are keywords in Sybase, and they are used as column names in the script. Here's the corrected script:

----------------------

Now, when change /sarconfig/config.xml so to replace "role" and "function" column names with "role1" and "function" in order to comply with above script.
IF OBJECT_ID('users') IS NOT NULL
BEGIN
DROP TABLE users
END
go

IF OBJECT_ID('roles') IS NOT NULL
BEGIN
DROP TABLE roles
END
go

IF OBJECT_ID('authcodes') IS NOT NULL
BEGIN
DROP TABLE authcodes
END
go

IF OBJECT_ID('customers') IS NOT NULL
BEGIN
DROP TABLE customers
END
go

IF OBJECT_ID('userprops') IS NOT NULL
BEGIN
DROP TABLE userprops
END
go

IF OBJECT_ID('accesscontrol') IS NOT NULL
BEGIN
DROP TABLE accesscontrol
END
go

IF OBJECT_ID('userentrystore') IS NOT NULL
BEGIN
DROP TABLE userentrystore
END
go

IF OBJECT_ID('reportstore') IS NOT NULL
BEGIN
DROP TABLE reportstore
END
go

IF OBJECT_ID('reportstore_detail') IS NOT NULL
BEGIN
DROP TABLE reportstore_detail
END
go

create table users (
username varchar(50) primary key,
password varchar(50) not null
)
go

create table roles(
username varchar(50) not null,
role1 varchar(20) not null
)
go
create index roles_username_idx on roles (username)

create table authcodes (
username varchar(50) primary key,
authcode varchar(20) not null
)
go

create table customers (
username varchar(50) primary key,
customer varchar(50) not null
)
go

create table userprops (
username varchar(50) not null,
propname varchar(50) not null,
propvalue varchar(255) not null,
primary key(username, propname)
)
go

create table accesscontrol(
report_id varchar(50),
user_role varchar(50)
)
go

create index ac_reportid_idx on accesscontrol (report_id)
create index ac_userrole_idx on accesscontrol (user_role)

insert into users values('jef', 'bigsecret')
insert into customers values('jef','jeftechnologies')
insert into roles values('jef', 'chief')
insert into authcodes values('jef', 'sample-auth-code')
insert into accesscontrol values('firstreport', 'chief')

create table userentrystore (
username varchar(50) not null,
storage_id varchar(50) not null,
function1 varchar(10) not null,
sequence numeric(5) not null,
type varchar(10) not null,
value varchar(255) not null,
primary key(username, storage_id, function1, sequence)
)

create table reportstore (
id VARCHAR(50) not null,
username VARCHAR(50) not null,
name VARCHAR(255) not null,
datasource_id VARCHAR(255) not null,
reportdef_id VARCHAR(255) not null,
primary key (id),
unique (name, username)
)

create index rs_name_idx on reportstore (name)
create index rs_username_idx on reportstore (username)

create table reportstore_detail (
id VARCHAR(50) not null,
function1 VARCHAR(15) not null,
name VARCHAR(255) not null,
value VARCHAR(255) not null,
primary key(id, function1, name)
)
--------------------

Now, we have to modify /sarconf/config.xml accordingly, i.e. replace "role" and "function" column references with "role1" and "function1".

Next thing to do is to change connection properties in config.xml and datasources. I have an earlier blog entry on setting up Sybase connections.

If you want to call stored procedures, which return result sets, then the trick is to replace query strings like "select f1, f2 from t1" with something like "exec sp1". Where sp1 could be:
-------------
CREATE PROCEDURE sp1
AS
BEGIN
select f1, f2 from t1
END
-------------
Sybase JDBC driver will treat this as a "select ..." query. Parameters are apssed in xReporter in the same way as in the query. I'm using stored procedures extensively, so I'm glad this works.

Cheers!