If you are using Oracle as your database there are some quirks that you have to work around. Here is what I discovered. I will post some sample files when I get time.

1. As mentioned earlier, use serialize? = true when you create your connection.

      let nc:BasicConnection = 
          {BasicConnection {url server}, serialize? = true}

2. cdbc-server.xml (in tomcat/conf/Catalina/localhost)

The following ResourceLink was left out of the example given in the documentation. Without it, the "cdbc/database" item defined in server.xml will not be found, and then the "cdbc-mysql" resource will be used instead of "cdbc-oracle".

      <ResourceLink name="cdbc/database"
          global="cdbc/database"
          type="java.lang.String" />

The default-schema resource links in the sample cdbc-server.xml were incorrect. Use one like this (leaving off the trailing /ja or /en).

      <ResourceLink name="cdbc/default-schema/oracle"
            global="cdbc/default-schema/oracle"
            type="java.lang.String" />

3. Setting up the JDBC driver (in tomcat/conf/server.xml)

Every JDBC driver has different requiremnts for configuration. The XML syntax for the Oracle JDBC driver looks like this. Note that you'll need to change the username, password, and possibly localhost to some other host name or IP address.

    <Resource name="cdbc-oracle" 
      auth="Container" type="javax.sql.DataSource"
      username="CDBC_USER"
      password="your-secret-password"
      driverClassName="oracle.jdbc.driver.OracleDriver"
      url="jdbc:oracle:thin:@localhost:1521:XE"
      connectionProperties="SetBigStringTryClob=true"
    />

(I'm actually not sure about the connectionProperties value but this worked for me.)

4. Other changes to server.xml

You must use jdbc transaction control, or none. The example uses "mysql" but then the server will attempt execute nonstandard SQL statements START TRANSACTION and COMMIT. If you specify "jdbc", the server will use the JDBC connnection.commit() call to commit each update.

    <Environment name="cdbc/transaction-control"
        type="java.lang.String"
        value="jdbc"
    />

Here is the "default-schema" that I used.

    <Environment name="cdbc/default-schema/oracle"
        type="java.lang.String"
        value="CDBC_USER"
    />

5. Use upper case table names

It seems that if a table name is not all uppercase then it must be quoted in the SQL statements. The current version of the BasicConnection does not use quotes around table names, so your table names must be all upper case if you are using Oracle.

Call Oracle packages from Curl?

Implementing getter-setter functions/methods in CURL by use of Oracle database packages.

Advantages:

• hides tablenames, columns and other details in Curl application.
• no DML or DLL calls in Curl program -> conn.execute("select ...")
• all SQL (i.e. ref cursor, bulk select/insert/update/delete/commit/rollback, ...) in the backend database:-)
• no restriction of SQL-complexity (joins, hierarchical queries: connect by/start with, ...).
• use of remote database DDL functionality (alter, create, ...)
• use of Oracle DBMS packages, Job control.
• use of native/compilable Oracle PL/SQL language in the remote database.

Only first thoughts with tables scott.EMP and DEPT, very uncomplete:

1. {conn.callStoredProcedure("SCOTT","MyPackage.getDataEmpDept (dataArray[])
using bind_variable1, bind_variable2, readonly_var /* i.e. empno, deptno, ... */
return status, count, ...")}

2. {conn.callStoredProcedure("SCOTT","MyPackage.setDataEmpDept (dataArray[])
return status, count, ...")}

3. {conn.callStoredProcedure("SCOTT","MyPackage.commit (MyPackage.rollback, MyPackage.set_transaction)
return status ...")}
4. ...

In C notation (i can't do it yet in Curl)

int status; /* ORA-NNNN (DBMS return code, success, no success, ...) */
int count; /* number of rows: selected, inserted, updated, deleted */
int readonly_var; /* readonly or lock records (select ... {for update of}) */

Array for get/set data (one of many).
Mabye a second array only for setting.

struct dataArray_
{
int empno,
char ename 10,
int deptno,
char dname 14,
...
int siud, /* 0=from DB selected, 1=from user input, 3=updated, 4=deleted */
} dataArray MAX_ROWS;

or dynamic dataArray[] like malloc() in C (don't know for Curl)

Curl for presentation and user interaction.

Flow:
A. Call 1 could load parts of DataGrids, TreeViews, Forms or internal Curl variables, or ...
B. user interaction (events)
C. Call 2 would insert, updated and delete data in the database.

Please Curl experts.

Any comments?

Harry,

If you want to execute the stored procedure, you will be able to use the BasicConnection.execute method. I tried following steps, and then it does work.

1. create the table and the stored procedure.

CREATE TABLE cdbc_test ( COL1 NUMBER(10), COL2 VARCHAR2(10), COL3 DATE ); CREATE OR REPLACE PROCEDURE cdbc_proc (col1 IN VARCHAR2, col2 IN NUMBER) AS BEGIN INSERT INTO cdbc_test VALUES (col1, col2, sysdate); COMMIT; END; /

2. run the following applet.

{curl 6.0 applet} {import * from CURL.DATA-ACCESS.BASE} {import * from CURL.DATA-ACCESS.CONNECTED} {value def conn = {BasicConnection {url "http://localhost:8080/cdbc-server/CdbcServlet"}, serialize? = true } {conn.execute"your schema name","CALL cdbc_proc('Foo', 1);"} def rs = {conn.create-record-set"your schema name","select * from CDBC_TEST", request-modifiable? = true } {RecordGrid record-source = rs} }

Great, after changing into

CREATE OR REPLACE PROCEDURE cdbc_proc (col1 IN NUMBER, col2 IN VARCHAR2)
AS
BEGIN
INSERT INTO cdbc_test VALUES (col1, col2, sysdate);
COMMIT;
END;

{conn.execute"SCOTT","CALL cdbc_proc(1,'Foo');"}

IT WORKS !

Another small but very good example what curl has to offer

How to give back values (also arrays - type Record/Table in Oracle) from the stored procedure?

PROCEDURE cdbc_proc (col1 IN NUMBER, col2 IN VARCHAR2, col3 OUT varchar2)