how to connect ESP with ASE in-memory database ?

[Former Member]

Hi,

I would like to send data from ESP to an ASE in-memory database via the ESP-ASE output adapter, but encountered some problems. Could anyone who has such experience before please give me some help? 

I created an in memory database named YZTest in ASE. I then created a table in this database. 

create table STOCK_QUOTE_R (

INSTRUMENT_ID int not null,

QUOTE_DATE date not null,

QUOTE_SEQ_NBR int not null,

TRADING_SYMBOL varchar(60) not null,

QUOTE_TIME bigdatetime null,

ASK_PRICE decimal(18,4) null,

ASK_SIZE bigint null,

BID_PRICE decimal(18,4) null,

BID_SIZE int null,

SYS_ID bigint null)

The CCL project I used is like this:

CREATE SCHEMA QUOTE_SCHEMA (
        INSTRUMENT_ID INTEGER,
        QUOTE_DATE DATE,
        QUOTE_SEQ_NBR INTEGER,
        TRADING_SYMBOL STRING,
        QUOTE_TIME BIGDATETIME,
        ASK_PRICE MONEY(4),
        ASK_SIZE INTEGER,
        BID_PRICE MONEY(4),
        BID_SIZE INTEGER,
        SYS_ID LONG
        );

CREATE INPUT STREAM STOCK_QUOTE SCHEMA QUOTE_SCHEMA;

ATTACH OUTPUT ADAPTER ASEOut TYPE sybase_ase_out TO STOCK_QUOTE PROPERTIES
service='MYASE',
table='STOCK_QUOTE_R';

I did not create special users for the in-memory database. I connected to the ASE server with the default root user "sa" and a password that I set at the installation time. I also specified the database name in the services.xml file (<Parameter Name="Database">YZTest</Parameter>).

When trying to run this project from the studio, I got the following error message:

...

ATTACH OUTPUT ADAPTER ASEOut TYPE sybase_ase_out TO STOCK_QUOTE PROPERTIES
service='MYASE',
table='dbo.STOCK_QUOTE_R';

The table still cannot be found.

Does anyone know what might be the issue? BTW, the table STOCK_QUOTE_R does exist in the database, and I can query it with isql.

Cheers,

Yuanzhen

2013-02-18 19:09:37.969 | 11952 | container | [SP-3-100005] (1.868) sp(4900) Server Error: LS9286, Msg 208, Severity 16, Status 6 -- "STOCK_QUOTE_R" not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).

2013-02-18 19:09:37.969 | 11952 | container | [SP-4-148011] (1.868) sp(4900) ConnectionWriter(ASEOut) connection reset error: 2013-02-18 18:09:37 Unable to get the number of columns for target DB table "STOCK_QUOTE_R".

Adapter failed to start because initialization failed.

2013-02-18 19:09:37.969 | 2580 | container | [SP-1-114079] (1.868) sp(4900) Platform()::run -- Sybase Event Stream Processor Server alive & ready for services

It seems that the ASE server was connected without problem, but the in-memory table cannot be found. I also tried the following adapter specification:

[claudia_gosav]

Hi Yuanzhen and Neal,

The issue might be with the driver you're using. The documentation (I also confirmed this with development) states that when using the ASE Output adapter to connect to the ASE database, you need to use an Open Client™ driver. See

http://dcx-pubs.sybase.com/index.html#esp514/en/com.sybase.infocenter.dc01611.0514/doc/html/skh12978...

and an example of an OCS entry in the service.xml file can be found in the ASE adapter topic in the Adapters Guide here:

http://dcx-pubs.sybase.com/index.html#esp514/en/com.sybase.infocenter.dc01615.0514/doc/html/cgo13335...

or here:

http://dcx-pubs.sybase.com/index.html#esp514/en/com.sybase.infocenter.dc01611.0514/doc/html/cgo13104...

Try updating your service.xml file and redeploying your project to see if the error still displays.

Also, could you please confirm that you posted all error messages that were displayed in your original post? I'd like to add a troubleshooting topic to the doc and want to make sure I have all the info I need for it.

Thanks and hope this helps,

Claudia

[Former Member]

Hi Claudia,

Sorry that I did that test about half a year ago and I do not use the ASE + ESP setup anymore. I cannot confirm that I have posted all error messages. It would take me quite some time if I want to reproduce the problem...

But if I remember correctly, I posted the same question in the ESP Interest Group. The solution that I got there was not to change the driver, and it did work for me.

Cheers,

Yuanzhen

[claudia_gosav]

Found the post you're refering to. Thanks, Yuanzhen!

[Former Member]

Hello,

Sorry it took so long to respond.  I don't think the parameter "Database" works with ODBC drivers in the service.xml file.  I think it works with JDBC drivers.

There is an outstanding bug for this issue:

733931 - Make database parameter useable for ODBC drivers in service.xml file

So I would suggest:

1) In your "service.xml" file, use a user who's default database is where the table was created rather than the "sa" user.

2) If the user's default database is different than the database containing the table, fully qualify the location of the table in your CCL:

table='my_database_name.dbo.STOCK_QUOTE_R';

3) If your ODBC driver supports setting the database, you can set the it in the ".odbc.ini" entry:

[RAPCache]

Description             = Sybase ODBC Data Source

UserID          = RAP_USER

Password                = rap_user

Driver         = /software/ESP51/DataAccess64/ODBC/lib/libsybdrvodb.so

Server          = archer.xyz.com

Port            = 5055

Database                = rapTAQ

Various ODBC drivers have different configuration parameters so check what yours supports.

Thanks,

  Neal