Home
Guides
Sign in

SYBASE-DB Connector

Suggest Edits

The Sybase DB connector can access the Sybase database by calling stored procedures (SP) or executing prepared statements. The following options determine which access method is used:

  • Stored Procedures: Set the --executable option to the name and location of the stored procedure, or
  • Prepared Statements: Set the --query option to the SQL statement.

Default and advanced stored procedures

The Sybase connector provides you with two ways to create assets for stored procedures. 

  • Default:  the connector generates assets by analyzing the stored procedure or function parameters without invoking the stored procedures or functions.
  • Advanced: the connector generates assets by invoking the stored procedure or function you specify in the --advanced-result-set-query option and analyzing the result set.

Examples

ol test connection

[module folder]> ol test connection --url jdbc:jtds:sybase://52.206.218.139:5000 --username sa --password Open1egacy! --query-timeout 3000 --pool-max-size 5

[module folder]$ ol test connection --url jdbc:jtds:sybase://52.206.218.139:5000 --username sa --password Open1egacy! --query-timeout 3000 --pool-max-size 5

ol add for prepared statement

[module folder]> ol add --query "SELECT * FROM ACCOUNTS WHERE ID = ?" --operation-name account-by-id

[module folder]$ ol add --query "SELECT * FROM ACCOUNTS WHERE ID = ?" --operation-name account-by-id

ol add for stored-procedure in a default mode

[module folder]> ol add --executable demo_db.dbo.proc_get_employee_by_id --temp-table-query "SELECT * FROM ACCOUNTS WHERE ID = 'A100000001'"--temp-table-query "CREATE TABLE #CUSTOMER_BANK_ACCOUNTS (ACCOUNT_ID varchar(12), IBAN varchar(32), BALANCE numeric(12))"

[module folder]$ ol add --executable demo_db.dbo.proc_get_employee_by_id --temp-table-query "SELECT * FROM ACCOUNTS WHERE ID = 'A100000001'"--temp-table-query "CREATE TABLE #CUSTOMER_BANK_ACCOUNTS (ACCOUNT_ID varchar(12), IBAN varchar(32), BALANCE numeric(12))"

ol add for stored-procedure in an advanced mode

[module folder]> ol add --executable demo_db.dbo.proc_get_employee_by_id --advanced-result-set-query "EXEC proc_get_employee_by_id @val = 1;"

[module folder]$ ol add --executable demo_db.dbo.proc_get_employee_by_id --advanced-result-set-query "EXEC proc_get_employee_by_id @val = 1;"

Properties

--query (required for prepared-statement data fetching)

SQL prepared statement

--query "SELECT * FROM ACCOUNTS WHERE ID = ?"

--operation-name (required for prepared-statement DB access)

The name of the generated query-based asset

--operation-name mySpecialName

--executable (required for a stored procedure-based DB access)

Database path to a stored procedure or function

--executable DEMO.BANK_EXAMPLE.GET_ACCOUNT_BY_ID

--advanced-result-set-query (for a stored procedure-based DB access)

The connector invokes the stored procedure or function that you specify in the --advanced-result-set-query option and analyzes the result set for output metadata.

If you leave --advanced-result-set-query unspecified, the connector generates input and output metadata based on procedure or function parameters without invoking the stored procedures or functions.

--advanced-result-set-query "EXEC proc_get_employee_by_id @val = 1;"

--temp-table-query (for a stored procedure-based DB access)

Formulate an SQL query that creates a table with specified columns. The connector will derive the asset input metadata structure from the query's column composition.

--temp-table-query "CREATE TABLE #CUSTOMER_BANK_ACCOUNTS (ACCOUNT_ID varchar(12), IBAN varchar(32), BALANCE numeric(12))"

--detect-db-default-values (for a stored procedure-based DB access)

Detects db default values if possible

--db-default-values (for a stored procedure-based DB access)

JSON array of name/type/value JSON object

--url

The JDBC url

--url jdbc:jtds:sybase://52.206.218.139:5000

--driver-class-name

The database driver fully qualified Java class name

--driver-class-name net.sourceforge.jtds.jdbc.Driver

--username

The database user

--username demo

--password

The database password

--password 12345

--query-timeout

The query timeout (in milliseconds) of callable statements for stored procedures.

--query-timeout 5000

--timezone

The timezone of the client executing the query for displaying the correct query timestamp at the client location.

--timezone America/New_York

--pool-max-size

Thread pool maximal size

--pool-max-size 5

Updated 4 months ago