Need help for database BasicConnection ...

mgordon 48 posts since
Oct 17, 2007

1. Re: Need help for database BasicConnection Apr 1, 2008 11:33 AM

Yes, this is tricky. Search for cdbc in the Search tab of the online documentation and be sure you've followed all five steps.

It sounds like you're there already though. To answer your specific question, search for the string <GlobalNamingResources> in your server.xml file and insert the Environment tags right after that line.

Here are some other things to watch out for. I'm using apache-tomcat-6.0.16 and mysql version 5.0.45. For my JDBC driver I used mysql-connector-java-3.1.12-bin.jar and I put it in the directory apache-tomcat-6.0.16\lib.

You'll want to create a database user for the CDBC server. Here's how.

Go to the mysql\bin directory and log on to mysql as root.

c:\mysql\bin> mysql -u root -p
root-password

If you haven't set a root password, leave out the -p flag and you won't be asked for one. (You should set a password though.)

Now you should see the mysql> prompt. To create a user, type or paste in a command like this at the prompt:

grant select, insert, update, delete, create, drop on cdbc_test.* 
to 'cdbc_user'@'localhost' identified by 'secret';

Use the password of your choice, and you can also use a different database and user name if you want to. These items must agree with the server.xml file. Note that the host name (localhost here) must match the host name that comes after jdbc:mysql:// in the cdbc-mysql resource element.

You also need to create the database:

create database cdbc_test;

You might also want to create a simple table (again, paste this in at the mysql prompt)

connect cdbc_test;
DROP TABLE IF EXISTS namenumber;
CREATE TABLE namenumber (
  name VARCHAR(64) NOT NULL,
  number INT NOT NULL,
  PRIMARY KEY (name)
);
INSERT INTO namenumber VALUES('washington', 100);
INSERT INTO namenumber VALUES('georgia', 200);

If you create additional databases that you want to make available via CDBC, you need to grant privileges to cdbc_user for them as well, using the grant command again. You can of course restrict the privileges as appropriate.

You can see all of the database users with the following commands:

connect mysql;
select host, user, password from user;

If you've done all of this, try the test requests given in the documentation. For example, if everything is running and you paste this request into a browser

http://localhost:8080/cdbc-server/CdbcServlet?op=getdbinfo

You should be able to find this line containing namenumber in the output, where the table names are listed:

<FIELD NAME="TABLE_NAME">namenumber</FIELD>

If you run into more problems, let us know what your operating system is, the version of mysql, etc., and what error message you're getting.

URPradhan 166 posts since
Mar 6, 2008

2. Re: Need help for database BasicConnection Apr 1, 2008 9:53 PM

in response to: mgordon

Thank you very much Mr mgordon.
Your replies are always easy to understand and correct.

//Thank you

Harry 20 posts since
Jun 19, 2008

3. Re: Need help for database BasicConnection Jun 27, 2008 9:51 AM

For Oracle developers:

In ..\Apache Software Foundation\Tomcat 6.0\conf\server.xml



<Environment name="cdbc/database"

type="java.lang.String"

value="oracle"

/>

<Environment name="cdbc/transaction-control"

type="java.lang.String"

value="oracle"

/>

<Environment name="cdbc/table-type"

type="java.lang.String"

value="TABLE,VIEW,SYNONYM"

/>

<Resource name="cdbc-mysql"

auth="Container"

type="javax.sql.DataSource"

factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"

driverClassName="oracle.jdbc.OracleDriver"

url="jdbc:oracle:thin:@<ip-adress>:<port>:SID"

username="scott"

password="tiger"

maxActive="20"

maxIdle="10"

/>

In ..\Apache Software Foundation\Tomcat 6.0\lib

Oracle driver: „ojdbc14.jar"

??? Ressource name

<Resource name="cdbc-oracle" produces the following error:

com.curl.cdbc.CdbcException: javax.naming.NameNotFoundException: Name cdbc-mysql is not bound in this ContextAlso for use with Oracle it has to be "cdbc-mysql". A bug?

???

With Resource name "dbs-mysql" it works for Oracle and outputs:

http://localhost:8080/cdbc-server/CdbcServlet

This is class com.curl.cdbc.CdbcServlet
Servlets: 0
transaction-control: oracle

Init param:null
<code>op=null</code>

CDBC 1.1.0 2007-05-01 0000

http://localhost:8080/cdbc-server/CdbcServlet?op=getdbinfo

This is class com.curl.cdbc.CdbcServlet
Servlets: 0
transaction-control: oracle

Init param:null
<code>op=getdbinfo</code>

<CDBC> <DBATTR NAME="allProceduresAreCallable">false</DBATTR>

...

The following CURL hangs in the browser without error messages.

{curl 7.0 applet}

{curl-file-attributes character-encoding = "iso-latin-2"}

{import * from CURL.DATA-ACCESS.BASE}

{import * from CURL.DATA-ACCESS.CONNECTED}

{let connection:Connection =

{BasicConnection {url "http://localhost:8080/cdbc-server/CdbcServlet"}}

}

{RecordGrid

record-source = {connection.create-record-set "cdbc-mysql","select * from emp"}}

Don't know if "cdbc-mysql" is the correct database-name in the CURL notation. Tried also "oracle" and the Oracle SID without success.

Any ideas?

hokada 12 posts since
Jun 29, 2008

4. Re: Need help for database BasicConnection Jun 29, 2008 6:23 PM

in response to: Harry

In case of Oracle, you need to set the schema name to the database-name.

e.g)
{RecordGrid record-source =
{connection.create-record-set "scott", "select * from emp"}}

If you have more problems, please let me know.

Harry 20 posts since
Jun 19, 2008

5. Re: Need help for database BasicConnection Jun 30, 2008 5:04 AM

in response to: hokada

Thanks ,

changed to your advice:

{connection.create-record-set "scott", "select * from emp"}}

After
waiting 10 minutes the applet throws an error:

Call Stack

CommitFailed:
StandardDateDomain failed to parse String: '12/17/2080 0:0:0'.

….

RecordGrid.default
(constructor) ??:??

Applet:
c:\Curl\Ora1.curl

CommitFailed:
StandardDateDomain failed to parse String: '12/17/2080 0:0:0'.

#0
BasicConnection.fill-record-set-from-stream ??:??

#1
BasicConnection.fill-record-set-using-xml ??:??

#2
BasicConnection.fill-record-set ??:??

#3
BasicConnectedRecordSet.load ??:??

#4
RecordSetDisplay.default (constructor) ??:??

#5
RecordGrid.default (constructor) ??:??

#6
Ora1.curl:11

After
"Continue" got message in Browser:

<Error>

An error occurred while loading this applet.

CommitFailed:
StandardDateDomain failed to parse String: '12/17/2080 0:0:0'.

Call Stack: Applet is running

Applet Manager: State = active

RTE Console: no messages

What’s about: server.xml

<Resource
name="cdbc-oracle" produces the following error:

com.curl.cdbc.CdbcException: javax.naming.NameNotFoundException: Name cdbc-mysql is not bound in this Context

Here my environment:

CURL 7.0.1000 beta

Build: 7.0.0. beta kendall/0-34

Supported API Versions: 7.0.0,6.0.2

Debuggebale API Versions: 7.0

Apache Tomcat 6.0.16

JAVA_HOME: C:\Programme\Java\jre1.6.0_05

MS Windows XP Pro, Ver. 2002, Service Pack 2

hokada 12 posts since
Jun 29, 2008

6. Re: Need help for database BasicConnection Jun 30, 2008 3:55 AM

in response to: Harry

com.curl.cdbc.CdbcException: javax.naming.NameNotFoundException: Name cdbc-mysql is not bound in this Context

Is there the context file(cdbc-server.xml) in $TOMCAT_HOME/conf/Cataline/localhost? This file might disappear if you removed the .war file without stopping the Tomcat service. (Probably Tomcat removes the context file automatically.)

Harry 20 posts since
Jun 19, 2008

7. Re: Need help for database BasicConnection Jun 30, 2008 5:00 AM

in response to: hokada

Yes, installed is:

C:\Programme\Apache Software Foundation\Tomcat 6.0\conf\Catalina\localhost\cdbc-server.xml

and reads:

<Context path="/cdbc-server" reloadable="false">

<Logger className="org.apache.catalina.logger.SystemOutLogger"
verbosity="4" timestamp="true"/>

<ResourceLink name="cdbc/transaction-control"
global="cdbc/transaction-control"
type="java.lang.String" />

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

<ResourceLink name="jdbc/cdbc" global="cdbc-mysql"
type="javax.sql.DataSource" />

<ResourceLink name="jdbc/cdbc-mysql" global="cdbc-mysql"
type="javax.sql.DataSource" />

<ResourceLink name="jdbc/cdbc-oracle" global="cdbc-oracle"
type="javax.sql.DataSource" />

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

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

</Context>

Changed:
<ResourceLink name="jdbc/cdbc" global="cdbc-mysql"
to
<ResourceLink name="jdbc/cdbc" global="cdbc-oracle"
but same error.

With RDBMS ORACLE the CURL documentation should better read:
create-record-set (method)
public
{BasicConnection.create-record-set
schema_name:String,
query:String,
request-modifiable?:bool = false,
size-limit:int = -1,
...:EventHandler
}:BasicConnectedRecordSet

<schema_name> instead of <database_name>
ORACLE <schema_name> is identical to <user_name>

Our Database environment:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options

Database Server on:
HP-UX oracle B.11.23 U ia64 (ta)

Do you have CURL installations with DBMS Oracle 10gR2 ???

Sorry for the:
  and ...
I pasted the text from MS WinWord 2000 and did no preview.

hokada 12 posts since
Jun 29, 2008

8. Re: Need help for database BasicConnection Jun 30, 2008 7:12 AM

in response to: Harry

CommitFailed:StandardDateDomain failed to parse String: '12/17/2080 0:0:0'.

Probably, StandardDateDomain/StandardDateTimeDomain might not be able to parse this string format as DateTime.
( see "Curl Developer's Guide > Core Language - Libraries > Date and Time Library > Representing a Moment in Time > DateTiem String parsing" in the Curl documantation. )

Can you try to add "serialize?" to the BasicConnection argument? This option means loding RecordSet using Curl serialization(not String(XML) format). *And also, it's very faster than XML format.
e.g ) def conn = {BasicConnection {url "..."}, serialize? = true}

If it doesn't work, you can change NLS_xx parameters and try again?

But I'm not sure, because I don't have the CDBC test environment now.

Do you have CURL installations with DBMS Oracle 10gR2 ???

I tried to do the following environment before.
OS: CentOS4
DB: Oracle10g R2 XE
AP: Tomcat6.0.x(?)
CurlRTE 6.0

Harry 20 posts since
Jun 19, 2008

9. Re: Need help for database BasicConnection Jun 30, 2008 9:34 AM

in response to: hokada

tried:
def conn =
{BasicConnection {url "http://localhost:8080/cdbc-server/CdbcServlet"}, serialize?=true}
{RecordGrid record-source =
{conn.create-record-set ...

throws exception:
def conn = ~~BasicConnection @0x04271030~~
<Error> - An error occurred while loading this applet.
SyntaxError: file:Ora1.curl:10[5]: 'conn' is not recognized.

The following program worked, but showed (also with serialize) the RecordGrid in Browser firefox 2.0.0.14
only after 10 minutes:
{curl 7.0 applet}
{curl-file-attributes character-encoding = "windows-latin-1"}
{import * from CURL.DATA-ACCESS.BASE}
{import * from CURL.DATA-ACCESS.CONNECTED}
{let conn =
{BasicConnection {url "http://localhost:8080/cdbc-server/CdbcServlet"}, serialize?=true}
}
{RecordGrid record-source =
{conn.create-record-set "scott", "select empno,ename from emp"}}

To simplify no DATE column was selected.

Tried also to select a modifiable Grid-Data:
{RecordGrid record-source =
{conn.create-record-set "scott", "select empno,ename from emp"}, request-modifiable?=true}
but got message:
Error: Option name 'request-modifiable?' is not defined for ~~RecordGrid @0x04137204~~.

Documentation reads:
request-modifiable? (field)
public-get protected-set request-modifiable?:bool
Class: ConnectedRecordSet
Package: CURL.DATA-ACCESS.CONNECTED
If true, an attempt will be made to return a modifiable RecordSet.
Some subclasses may be unable to create a modifiable RecordSet if
the query is too complex or if the Connection does not allow it for any reason.

Does this mean that CURL has no updateable RecordGrid?

A simple query running against an Oracle database already with these problems
1. select Date problem
2. Time problem, waiting 10 minutes to see the Grid, also after serialize?=true
3. Grid data not modifiable
4. CPU utilization with 99% by surge.exe, for the whole 10 minutes (other programs are nearly unusable)
After Grid display CPU utilization is again normal (surge.exe falls below 1%).

Please read about your "fit-client".
http://developers.curl.com/blogs/community_blog/2008/06/30/curl-customer-presents-at-catalyst-conference-san-diego-june-26-2008
Curl customer presents at Catalyst Conference, San Diego, June 26, 2008

Are there any complete ORACLE CRUD-examples with a modifiable DataGrid and modifiable TreeView ?
(C)reate INSERT
(R)ead SELECT
(U)pdate UPDATE
(D)elete DELETE

Thanks for your patience.

Duke 179 posts since
Oct 17, 2007

10. Re: Need help for database BasicConnection Jun 30, 2008 11:52 AM

in response to: Harry

Your syntax error is because you did not put curly braces {} around the "def conn" which must have been at the top level of an applet for the error to occur the way you report.

hokada 12 posts since
Jun 29, 2008

11. Re: Need help for database BasicConnection Jun 30, 2008 7:14 PM

in response to: Harry

1. select Date problem

Probably, it will work, if you set serialize? = true.

2. Time problem, waiting 10 minutes to see the Grid, also after serialize?=true

As Oracle has large datas, which are data dictionaries and such, in just one database/instance, you should set "default-schema" to filter datas. You will should add a few elements in the server.xml and cdbc-server.xml like this:

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

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

3. Grid data not modifiable
{RecordGrid record-source = {conn.create-record-set "scott", "select empno,ename from emp"}, request-modifiable?=true}

request-modifiable? is not the argument of RecordGrid, it is the argument of create-record-set method. So you have to write the following.

{RecordGrid record-source = {conn.create-record-set "scott", "select empno,ename from emp", request-modifiable?=true}}

4. CPU utilization with 99% by surge.exe, for the whole 10 minutes (other programs are nearly unusable)

Probably, this problem is the same as No.2 issue.

CRUD examples

You can use BasicConneciton.execute like this:
{conn.execute "SCOTT", "UPDATE EMP SET COL = 1"}

And also, you can modify records on the RecordGrid and call commit method.

Harry 20 posts since
Jun 19, 2008

12. Re: Need help for database BasicConnection Jul 1, 2008 5:33 AM

in response to: hokada

Thanks to your help, now it works!

Load time is ok.

No more Date problem .

RecordGrid is modifiable (all fields in the Grid could be edited).

{def conn=} works like {let conn=}

Here the applet:

{curl 7.0 applet}
{curl-file-attributes character-encoding = "windows-latin-1"}

{import * from CURL.DATA-ACCESS.BASE}
{import * from CURL.DATA-ACCESS.CONNECTED}
{let conn =
{BasicConnection {url "http://localhost:8080/cdbc-server/CdbcServlet"}, serialize?=true}
}

{RecordGrid record-source =
{conn.create-record-set "scott", "select * from emp", request-modifiable?=true}}

{CommandButton
label = "commit",
style = "standard",
{on Action at btn:CommandButton do
{conn.execute "SCOTT","update emp set ename = 'New Name' where empno=1"}
{conn.execute "SCOTT","commit"}
}
}

Above commit by Button-event does nothing .

The "commit changes" in context menu of the Grid gives back "ConnectedDataException: No keys found for query 'update emp set empno= ...' "

Seems that the where clause is missing.

There is a primary key constraint on empno.

What do do mean by "call commit method"?

CURL commit method or a {conn.execute "SCOTT","commit"}

These are all beginner questions/problems and therefore it would help if the CURL documentation gives more information/examples how to work with remote databases.

hokada 12 posts since
Jun 29, 2008

13. Re: Need help for database BasicConnection Jul 1, 2008 6:19 AM

in response to: Harry

Above commit by Button-event does nothing

All your codes are on top level, so I think that you need to change like this:

{value
{let conn =
{BasicConnection {url "http://localhost:8080/cdbc-server/CdbcServlet"}, serialize? = true}}

{spaced-vbox
{RecordGrid record-source =
{conn.create-record-set "scott", "select * from emp", request-modifiable? = true}
},
{CommandButton
label = "commit",
style = "standard",
{on Action at btn:CommandButton do
{conn.execute "SCOTT","update emp set ename = 'New Name' where empno=1"}
{conn.execute "SCOTT","commit"}
}
}
}
}

What do do mean by "call commit method"? CURL commit method or a {conn.execute "SCOTT","commit"}

You can use BasicConnectedRecordSet.commit method, after you modified cell datas on RecordGrid.

def rs = {conn.create-record-set "scott", "select * from emp", request-modifiable? = true}
.....
{CommandButton label = "commit",
{on Action do
{rs.commit} || After you modified cell datas, please press this button.
}
}

hokada 12 posts since
Jun 29, 2008

14. Re: Need help for database BasicConnection Jul 1, 2008 6:22 AM

in response to: hokada

And also, you can do like this:

{conn.execute "SCOTT", "update emp set ename = 'New Name' where empno=1;commit"}

This Question is Answered

Need help for database BasicConnection

Apr 1, 2008 2:02 AM

Actions

More Like This