How can I browse Apache Derby Embedded databases?

[Mihai Baboi]

Hello,

Iv'e got a question about the default Apache Derby Embedded datasources that come with a new installation of ColdFusion. I want to use one of them (like cfartgallery), for some demo code, but I'm not familiar with the structure of the database so I don't know how to write the queries. Is there any way to "browse" the database to see it's structure? I tried using <cfquery> with a show tables query, but it threw an error. I guess you can only use it for SELECT, INSERT, UPDATE, etc.

What am I doing wrong? Is there a tool to visually inspect the database, or a method to do that via ColdFusion that I'm missing?

Thanks

P.S. I think Dreamweaver has a tool like that built in (somewhere in the Bindings menu), but I'm running ColdFusion on an Ubuntu machine.

[Guest]

In Coldfusion Bulder open RDS Dataview tab located at lower right unless you customized your workspace.

RDS Dataview will present a tree diagram of the  datasources. Open the datasource and you will see the tables and will be able to view the structure.

[Mihai Baboi]

Thanks, but I'm on a Linux machine and ColdFusion Builder isn't available on this platform. Is there any way to do this via code? I was thinking about something like this:

<cfquery name="tables" datasource="cfargallery">

     SHOW TABLES;

</cfquery>

<cfdump var="#tables#" />

I tried this code and it throws an error. Am I doing something wrong or is <cfquery> just not made for this?

[Owainnorth]

Personally I've never used Derby, but it sounds like it has Oracle equivalent commands.

In Oracle SQL*Plus (the command-line tool) you can use "desc mytable;" to describe a table, similar to what you're doing here. However, that is SQL*Plus syntax, not SQL syntax; therefore it will never work through cfquery.

Are there any views you can use to show this? In SQLServer you can run queries against the master database to list your tables, in Oracle you can do "select table_name from all_tables"; these are valid SQL queries which will return the results you need. Whether there's a Derby equivalent or not I cannot say.

[Mihai Baboi]

Hmm, I never thought I could access Derby from the terminal. I'll have to look into that...

[Dave Watts]

I suspect that the version of Derby embedded with CF doesn't support SHOW TABLES. I know that was an issue with older versions of Derby in general. However, you should be able to use ANSI-92 standard queries to provide schema details, using the INFORMATION_SCHEMA table.

In addition, there are external tools that will work with Derby. There are command-line tools (lj, for example) and third-party tools like Aqua Data Studio.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on

GSA Schedule, and provides the highest caliber vendor-authorized

instruction at our training centers, online, or onsite.

Read this before you post:

http://forums.adobe.com/thread/607238

[Mihai Baboi]

Small update.

I went in /opt/coldfusion9/db and found a file named console. It's content is:

#!/bin/sh

# If this command doesn't work for you, please see

# the PointBase documentation for different JVM examples.

# When prompted, use jdbc:pointbase:server://localhost:9192/sample as the URL for sample database,

#     jdbc:pointbase:server://localhost:9292/compass as the URL for the compass travel database,

#     jdbc:pointbase:server://localhost:9392/smarticket as the URL for the smarticket database,

#     or jdbc:pointbase:server://localhost:9692/worldmusic as the URL for the worldmusic database.

java -classpath "../servers/lib/pbclient42RE.jar:./lib/pbtools42RE.jar" com.pointbase.tools.toolsConsole com.pointbase.jdbc.jdbcUniversalDriver

I tried running it with "sudo ./console" and it throws an error. Any ideas...? (The coldfusion install was pretty standard. All I did was configure Apache instead of jRun).

[Owainnorth]

Do you have RDS enabled on the server? If so, just use the RDS Dataview browser through CFBuilder or Eclipse, those'll be much simpler.

O.

[Mihai Baboi]

I don't have CFBuilder because I'm on Linux and it's not available for this platform. But I will look for an equivalent in Eclipse, maybe I'll get lucky

[Adam Cameron. · Accepted solution]

Did you google "Apache Derby GUI client"?  The first match includes this info:

http://db.apache.org/derby/faq.html#derby_gui

Failing that, you could use <cfdbinfo>, could you not?

--

Adam

[Mihai Baboi]

Boy, is my face red... I never knew about <cfdbinfo> It works great, and I don't need any external tools. Thanks a lot to everyone for your help.

[gbstack08]

You can use SQuirreL to browse derby database, it's an open source database browser and it's cross-platform because it's written in java. You can check this post to see how to use it to browse Derby database: Apache Derby Database Browser