Professional Web Applications Themes

Testing for a table - Coldfusion - Advanced Techniques

In a utility I created, I'm allowing the users to specify a table name through a form which runs a cfc. My problem is, if the table doesn't exist it gives them a CF error. I know I can use CFERROR, or onError, but am curious how I can describe the error to the user elegantly so they'll know that the table is not valid and re-enter it. Thanks...

Sponsored Links
  1. #1

    Default Testing for a table

    In a utility I created, I'm allowing the users to specify a table name through
    a form which runs a cfc. My problem is, if the table doesn't exist it gives
    them a CF error. I know I can use CFERROR, or onError, but am curious how I can
    describe the error to the user elegantly so they'll know that the table is not
    valid and re-enter it.

    Thanks

    Sponsored Links
    blastbeat Guest

  2. #2

    Default Re: Testing for a table

    At the very least, you could wrap a <cftry> around a <cfquery>Select * From #TheTableName#</cfquery>. If the <cftry> catches a database error, it should be because the table doesn't exist.

    Josh
    Josho Guest

  3. #3

    Default Re: Testing for a table

    I would recommend that you give them a drop-down list of available tables to
    prevent the problem up-front.

    You can also test for the existence of the table before taking further action
    (as suggested previously).

    You can also use my free http://www.bryantwebconsulting.com/cfcs/ to help in
    determining existence of a table or creating one as needed (among other things).

    SteveBryant Guest

  4. #4

    Default Re: Testing for a table

    I think I got it going, but here's a new issue. This is in a CFC, and I the
    cftry/catch is working and displaying the error properly, however it continues
    to try and process the rest of the page, which errors as well because there are
    no query results. How can I simply stop processing the page once the try catch
    has found an error. I've tried cfabort.

    Here's my code.

    <cfcomponent>
    <cffunction name="compareColumns" access="public" returntype="struct"
    hint="Returns the differences between columns in tables.">

    <cfargument name="tableOne" type="string" />
    <cfargument name="tableTwo" type="string" />
    <cfargument name="dsnOne" type="string" />
    <cfargument name="dsnTwo" type="string" />

    <cftry>
    <cfquery name="queryOne" datasource="#dsnOne#">
    select *
    from #tableOne#
    </cfquery>
    <cfcatch type="Database">
    <cfoutput>
    <strong>ERROR</strong><br />There was a problem accessing the data for the
    table <strong>#tableOne#</strong> in datasource
    <strong>#dsnOne#</strong>.<p>Possible causes for this could be:</p>
    <ul>
    <li> Invalid table name
    <li> Invalid datasource name
    <li> Invalid authorization to the table or datasource
    </ul>
    <strong>SERVER ERROR MESSAGE</strong><br />#cfcatch.message#
    </cfoutput>
    </cfcatch>
    </cftry>

    <cfquery name="queryTwo" datasource="#dsnTwo#">
    select *
    from #tableTwo#
    </cfquery>

    <cfparam name="listOne" default="" />
    <cfparam name="listTwo" default="" />

    <cfloop index="i" list="#queryOne.ColumnList#" delimiters=",">
    <cfif listFind(queryTwo.ColumnList, i) is 0>
    <cfset listOne = listAppend(listOne, i) />
    </cfif>
    </cfloop>

    <cfloop index="i" list="#queryTwo.ColumnList#" delimiters=",">
    <cfif listFind(queryOne.ColumnList, i) is 0>
    <cfset listTwo = listAppend(listTwo, i) />
    </cfif>
    </cfloop>

    <cfset tableCols = StructNew() />
    <cfset tableCols.tableOne = listOne />
    <cfset tableCols.tableTwo = listTwo />

    <cfreturn tableCols>

    </cffunction>
    </cfcomponent>


    blastbeat Guest

  5. #5

    Default Re: Testing for a table

    You shouldn't display anything from your CFC. Use cfthrow and then display the
    exception in the code that calls the CFC.

    Keep in mind that your .cfm pages should be communicating to the user (by
    displaying information to the browser). Your CFC should be communicating to
    your .cfm page (by returning data or throwing exceptions). This will give you
    maximum flexibility for minimum effort in the long run.

    I would also recommend adding an output="no" attribute to each cffunction (in
    part to enforce previously mentioned guideline).

    You will also want to get in the habit of VARing any variables that you set in
    your method, but that is a tale for another day.

    SteveBryant Guest

  6. #6

    Default Re: Testing for a table

    Hey steve, this makes sense.

    Thanks for the tip!
    blastbeat Guest

  7. #7

    Default Re: Testing for a table

    Steve, is there a way to get columtype information, such as primary key, type of column, etc.

    I'd like to display that as well.
    blastbeat Guest

  8. #8

    Default Re: Testing for a table

    It really depends on the database. It isn't really possible to do in MS Access
    without using COM (though you can guess, which is what DataMgr does with
    Access). It can certainly be done with SQL Server, MySQL and PostGreSQL. I
    don't know about with other databases (though I am sure it is possible with
    Oracle as well).

    The easiest way to get that information (IMHO) is to use the
    getDBTableStruct() method of my free http://www.bryantwebconsulting.com/cfcs/.
    If you decide to use that, feel free to use my contact form to inquire on any
    help with use.

    Even if you don't use it directly, feel free to download it to see the syntax
    for getting that information for the databases previously listed.

    SteveBryant Guest

  9. #9

    Default Re: Testing for a table

    If cfabort did not work, you have a logic problem somewhere.

    Originally posted by: blastbeat
    I think I got it going, but here's a new issue. This is in a CFC, and I the
    cftry/catch is working and displaying the error properly, however it continues
    to try and process the rest of the page, which errors as well because there are
    no query results. How can I simply stop processing the page once the try catch
    has found an error. I've tried cfabort.




    Dan Guest

  10. #10

    Default Re: Testing for a table

    Blastbeat,

    Move the <cfcatch>...<c/fcatch> block all the way down, to just before </cffunction>



    BKBK Guest

Similar Threads

  1. Testing if a mysql table exists
    By sam in forum PHP Development
    Replies: 1
    Last Post: October 16th, 11:10 PM
  2. Replies: 0
    Last Post: September 16th, 04:37 PM
  3. Replies: 0
    Last Post: September 15th, 05:39 AM
  4. Replies: 0
    Last Post: September 10th, 10:49 PM
  5. Replies: 2
    Last Post: August 12th, 07:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139