Known Participant

Answered

How can I browse Apache Derby Embedded databases?

Forum|Forum|15 years ago
December 4, 2010
4 replies
17329 views

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.

Database access

This topic has been closed for replies.

Correct answer Adam Cameron.

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

G

gbstack08

Participant

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

A

Adam Cameron.Correct answer

Inspiring

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 BaboiAuthor

Known Participant

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.

Mihai BaboiAuthor

Known Participant

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

Inspiring

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 BaboiAuthor

Known Participant

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

A

Anonymous

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 BaboiAuthor

Known Participant

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:

SHOW TABLES;

</cfquery>

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

Owainnorth

Inspiring

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded