Joe
Below is the Java source, originally caalled DBViewer.
This is working code that I modified to use the CFMX cfsnippets db (The
PointBase
database shipped with the Linux distro).
I want to accomplish the same thing within CFMX, and generalize it a
bit so it will
work with any JDBC driver and database,
remote or local, on any platform.
For remote dbs, there will be a stub program that determines the
platform, CF
version, etc. and Uses COM objects or the Java interface as needed.
Requests and data are exchanged via WDDX packets.
For local dbs the function could be included inline (for performance)
or via the
stub (for convenience)
The problem statements are shown at: 30, 38, and 45.
It is fairly easy to program equivalent CF code, but you can't pass
nulls from CF.
Given more time, I would probably do this:
Use a Java program (similar to this) to do the actual manipulation
of the JDBC driver.
Use a CF routine to interface the Java program: providing input
paramaters for the desired db request; and presentation of the
results
Use an alias (such as 'MyNull'), to exchange psuedo nulls between
CF and Java, as necessary
Any help will be greatly appreciated.
TIA
Dick
1 // public abstract ResultSet getIndexInfo(String catalog, String
schema, String table, boolean unique, boolean approximate)
throws SQLException;
2 // public abstract ResultSet getColumns(String catalog, String
schemaPattern, String tableNamePattern, String columnNamePattern)
throws SQLException;
3
4 import java.sql.*;
5 import java.util.StringTokenizer;
6 public class DBViewerPB {
7 final static String jdbcURL =
"jdbc:pointbase:cfsnippets,database.home=/opt/coldfusionmx/db";
8 final static String jdbcDriver =
"com.pointbase.jdbc.jdbcUniversalDriver";
9 final static String username = "PBPUBLIC";
10 final static String password = "PBPUBLIC";
11 public static void main(java.lang.String[] args) {
12 System.out.println("--- Database Viewer ---");
13
14 try {
15 Class.forName(jdbcDriver);
16 Connection con = DriverManager.getConnection(jdbcURL,
username, password);
17 DatabaseMetaData dbmd = con.getMetaData( );
18 System.out.println("Driver Name: " + dbmd.getDriverName( ));
19 System.out.println("Database Product: " +
dbmd.getDatabaseProductName( ));
20 System.out.println("Database Version: " +
dbmd.getDatabaseProductVersion( ));
21 System.out.println("SQL Keywords Supported:");
22 //StringTokenizer st = new
StringTokenizer(dbmd.getSQLKeywords( ), ",");
23 //while(st.hasMoreTokens( ))
24 // System.out.println(" " + st.nextToken( ));
25
26 // Get a ResultSet that contains all of the tables in this
database
27 // We specify a table_type of "TABLE" to prevent seeing system
tables,
28 // views and so forth
29 String[] tableTypes = { "TABLE" };
30 ResultSet allTables =
dbmd.getTables(null,null,null,tableTypes);
31 while(allTables.next( )) {
32 String table_name = allTables.getString("TABLE_NAME");
33 System.out.println("Table Name: " + table_name);
34 System.out.println("Table Type: " +
allTables.getString("TABLE_TYPE"));
35 System.out.println("Indexes: ");
36 // Get a list of all the columns for this table
37 ResultSet columnList =
38 dbmd.getColumns(null,null,table_name,null);
39 while(columnList.next( )) {
40 System.out.println(" Column Name:
"+columnList.getString("COLUMN_NAME"));
41 }
42 columnList.close( );
43 // Get a list of all the indexes for this table
44 ResultSet indexList =
45
dbmd.getIndexInfo(null,null,table_name,false,false);
46 while(indexList.next( )) {
47 System.out.println(" Index Name:
"+indexList.getString("INDEX_NAME"));
48 System.out.println(" Column Name:
"+indexList.getString("COLUMN_NAME"));
49 }
50 indexList.close( );
51 }
52 allTables.close( );
53 con.close( );
54 }
55 catch (ClassNotFoundException e) {
56 System.out.println("Unable to load database driver class");
57 }
58 catch (SQLException e) {
59 System.out.println("SQL Exception: " + e.getMessage( ));
60 }
61 }
62 }
On Monday, November 25, 2002, at 08:42 AM, Joe Eugene wrote:
> Dick,
> Can we see your code? Cant you have a method that converts CF String
> "null" to
> Java String=null?
>
>> tried to invoke it with cfobject. We could not
>> make the interface work
>> because we could not pass Nulls between CF and
>> Java.
>
> Here is an example
>
> public class StringType{
> private String str;
> public String getString(String s){
> String val="";
> str=s;
> if(str.equals("null")){
> val="Your String was null, setting to null now<br>";
> str = null;
> val= val+ " " +"Now Java value is : <b>"+ str +"</b>";
> }
> return val;
> }
> }
>
> You can invoke it with
> <cfobject action="CREATE" class="StringType" name="chkNull"
> type="JAVA">
> <cfoutput>
> #chkNull.getString("null")#
> </cfoutput>
>
> if you can post your code, we can try figure it out. Let me know.
>
> Joe
>
> On Mon, 25 Nov 2002 06:54:03 -0800 Dick Applebaum <[EMAIL PROTECTED]>
> wrote:
>
>> On Monday, November 25, 2002, at 01:43 AM,
>> Jochem van Dieten wrote:
>>
>>> Quoting Dave Carabetta :
>>>>
>>>> While I understand this isn't a feature that
>> everybody would use, I
>>>> would personally like to see MM focus on
>> encapsulating some more Java
>>>> features into easy-to-use black-box CF tags
>> rather than having to code
>>>> my own Java.
>>>
>>> I agree. For instance, it would be far better
>> if CF had a tag to get at
>>> the DatabaseMetaData interface instead of
>> making it marginally easier
>>> to
>>> write it yourself by allowing inline Java.
>>
>> This is an excellent example & I expect that
>> this will be one of the
>> most-requested capabilities -- to be able to
>> get DatabaseMetaData into
>> CF. I tried to do this, with help from Sean
>> Corfield -- without
>> success. I found a working Java program that
>> extracts metadata, and
>> tried to invoke it with cfobject. We could not
>> make the interface work
>> because we could not pass Nulls between CF and
>> Java.
>>
>> This is for a general-purpose developer utility
>> that I use to
>> manipulate databases. It is especially useful
>> on remote (shared) sites
>> where you don't have administrative
>> privileges.
>>
>> I have been doing this a long time with CF 4.5
>> and CF 5 on win
>> platforms using cfobject to manipulate COM
>> objects.
>>
>> But, I would like to be able to do the same
>> thing with CFMX on
>> non-windows platforms.
>>
>> Here's the difficulty:
>>
>> With CFMX:
>>
>> I can get at the equivalent of DatabaseMetaData
>> on a remote windows
>> box, using cfobject and COM objects.
>>
>> But, I can't get at the DatabaseMetaData on my
>> local Unix (Mac OS X)
>> developer machine -- you can't use COM objects
>> and can't pass the Nulls
>> to the Java program that gets the
>> DatabaseMetaData.
>>
>> I suppose there is a way to circumvent the need
>> to pass Nulls between
>> CF and Java, but I have not had time to
>> investigate this.
>>
>>> And especially from the point of view of
>> security built-in tags are
>>> better. All those JSP tags and Java classes
>> are nice, but on a shared
>>> server you need to disable them anyway
>> because the same mechanism that
>>> is used to access them can be used to break
>> out of the sandbox.
>>>
>>
>> Is this true for CFMXJ2ee on JRun, Websphere or
>> whatever?
>>
>> I thought that one of the advantages of
>> CFMXJ2ee on a J2ee-compliant
>> app server, is the ability to interoperate
>> between CF and Java programs.
>>
>> Will this be possible with Java access
>> disabled?
>>
>> For the DatabaseMetaData example, I would
>> prefer the CF tag approach.
>>
>> But, I still think it is valid to use Java,
>> where warranted, on a
>> developer machine.
>>
>>
>> Dick
>>
>>
>>> Jochem
>>>
>>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
