Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
Is there a way to test to see if a data source exists?
Author: Taco Fleur
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25130#126472
I guess you would just make a call to the datasource within a cftry
block and catch the error with cfcatch if it does not exist..
>I'm not sure why it would be an "obvious" - are you talking about a
>specific database with known tables, or just an
unknown datasource?
Yes. I really can't imagine anybody building a web app that will give a
user the option of connecting to some random unknown datasource. Know
what I mean? I would imagine that there is some sort of idea which
datasources will be given as options, and appropriate actions can be
taken.
E
Author: CF Dude
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25130#126463
>I'm not sure why it would be an "obvious" - are you talking about a specific
database with known tables, or just an
unknown datasource?
Yes. I really can't imagine anybody building a web app that will give a user the
option of connecting to some random
unknown datasource. Know what I mean? I would imagine that there is some sort
of idea which datasources will be given
as options, and appropriate actions can be taken.
E
Author: Stan Winchester
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25130#126438
Thank you for all the suggestions!
Using the following cfcatch block I get the below messages returned
<cfcatchtype="Database">
<cfoutput>#cfcatch.message#</cfoutput>
</cfcatch>
CF5 & ODBC & SQL 2K: "ODBC Error Code = IM002 (Data source not found and no
default driver specified)"
CFMX & SQL 2K: "Data source foo could not be found."
I have not as yet tested the following:
1. CF5 with OLE-DB (though if don't specify dbtype, then it falls back to
ODBC, and there may be no need to test OLE-DB?)
2. CF4.5
3. Access
4. CF4 (is there enough of a user base to justify developing for CF4?)
I am hoping to have this application compatible with CF4.5 - CFMX, SQL Server, MS
Access. I suppose I may try later to look at testing MYSQL, but for now just SQL
Server & Access will do.
Stan Winchester
stan@aftershockweb.com
http://www.aftershockweb.com/
Tel. 503-244-3440
Fax 503-244-3454
----- Excess quoted text cut - see Original Post for more -----
Author: Bryan Love
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25130#126436
on Windows you can check the registry:
<CFREGISTRY ACTION=GETALL NAME="DS_ODBC_List"
TYPE="ANY" BRANCH="HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data
Sources">
<cfset DSNnames = valueList(DS_ODBC_LIST.entry)>
<cfif listFind(DSNnames,DSN)>
<!--- a datasource with that name already
exists, so do whatever --->
</cfif>
<!--- Make sure that this DSN does not exist as a an
existing CF DSN --->
<CFREGISTRY ACTION=GETALL NAME="DS_Native_List"
TYPE="KEY"
BRANCH="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSo
urces">
<cfset DSNnames = valueList(DS_ODBC_LIST.entry)>
<cfif listFind(DSNnames,DSN)>
<!--- a datasource with that name already
exists, so do whatever --->
</cfif>
Is there a way to test to see if a data source exists? I am creating a setup
routine where the user will enter the data source name, and I would like to
check to see if it exists before using it.
Stan Winchester
stan@aftershockweb.com
http://www.aftershockweb.com/
Tel. 503-244-3440
Fax 503-244-3454
Author: Jim Davis
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25130#126434
> I consider the "right" query as an obvious, but now I do have another
> question.
I'm not sure why it would be an "obvious" - are you talking about a specific
database with known tables, or just an unknown datasource? Furthermore are
you talking about a known DB system or any system?
I was assuming unknown databases and DBMSs - if we're limiting things to
known databases or systems then the problem gets a LOT easier.
For example in SQL Server you can say "SELECT name from SYSOBJECTS WHERE
name = ''"
That query will return no rows without error in pretty much every version of
SQL Server. But it will fail in Access (or Oracle or Sysbase) even if the
Datasource exists.
You can also do (in SQL Server) "SELECT name from SYSDatabases WHERE name =
'databasename'" to see if a specific database exists.
As for systems if know, for example, that you're only interested in ODBC
datasources then you can easily easily query the ODBC key in the Windows
Registery - it has a list of all ODBC datasources.
For other datasources (JDBC for example) there really isn't a registry
component to reference - CF itself maintains the information.
If a simple works for you, use it (who wouldn't!?) but if you're trying to
be more generic (any connection, any DB) then the problem's a little larger
and you should consider using something like the CF.Factory (and that's
actually pretty easy - easy enough that you may want to use it regardless
just for possible expansion later).
Jim Davis
Author: CF Dude
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25130#126433
That makes sense.
You get a "no SQL specificied" error - CF won't try to make a connection for no
reason.
Jim
Davis
Author: Jim Davis
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25130#126432
You get a "no SQL specificied" error - CF won't try to make a connection for
no reason.
Jim Davis
----- Excess quoted text cut - see Original Post for more -----
Author: CF Dude
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25130#126424
I consider the "right" query as an obvious, but now I do have another question.
What would happen if, in this scenario, you just used an empty <cfquery>
tag?
EG :
<cftry>
<cfquery datasource=#doesThisExist#> </cfquery>
<cfcatch type="database">
What would happen if no query was specified?
</cfcatch>
</cftry>
I'd like to assume that cfServer still tries to make a physical connection before
running the query. Or does it check
for the existence of a query first, and then connect?
Eric
The "right" query to use may different with database and connection type.
For example in ODBC the message "data source not found" us returned and you
can search for it, but this message may not come back for other drivers
(such as OLE DB or JDBC).
Author: Jim Davis
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25130#126420
Assuming CFMX (version matters) you could use the stuff described here to do
it quite easily:
http://spike.oli.tudelft.nl/jochemd/index.cfm?PageID=12
Using TRY/CATCH is a good idea as well, but you'll need to determine a good
query to use: you want to make sure that the datasource doesn't exist but
the catch will activate for ANY error (if the table doesn't exist or if the
column doesn't exist for example).
The "right" query to use may different with database and connection type.
For example in ODBC the message "data source not found" us returned and you
can search for it, but this message may not come back for other drivers
(such as OLE DB or JDBC).
In SQL server you can query the system tables but you can't in Access and so
on.
If you're using CF 5 (let us know) there are a few undocumented functions to
do this as well - very easy.
Jim
----- Excess quoted text cut - see Original Post for more -----
Author: CF Dude
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25130#126419
I'm sure you would have come up with it. If this is cf 4x, you could use
BenForta's connection tag that does a reg
query and find the currently created dsns. I wish I knew what that was called.
CF Dude,
I should have thought of that; for some reason my thinking was down another path.
Author: Stan Winchester
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25130#126418
CF Dude,
I should have thought of that; for some reason my thinking was down another path.
Thanks,
Stan
----- Excess quoted text cut - see Original Post for more -----
Author: Lofback, Chris
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25130#126416
These are CF 5 solutions.
Try the undocumented <CFIF CF_IsColdFusionDataSource("MyDSN")> or if you
prefer a supported option you could try something like this:
<CFSET DSN = "Foo">
<CFSET DSNType = "ODBC">
<CFTRY>
<!--- Query should always fail and go to CFCATCH --->
<CFQUERY NAME="Test" DATASOURCE="#DSN#" MAXROWS="1" DBTYPE="#DSNType#">
SELECT Foo FROM Bar
</CFQUERY>
<!--- Just in case --->
DSN exists
<CFCATCH TYPE="Any">
<CFIF FindNoCase("Data source not found",CFCATCH.Message)>
DSN does not exist!
<CFELSE>
DSN exists
</CFIF>
</CFCATCH>
</CFTRY>
Chris
----- Excess quoted text cut - see Original Post for more -----
Author: Barney Boisvert
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25130#126415
You should be able to run a query that you know will fail on the DSN within
a CFTRY..CFCATCH block, and then look at the error message to determine what
kind of failure it was: invalid DSN or something else.
barneyb
---
Barney Boisvert, Senior Development Engineer
AudienceCentral
bboisvert@audiencecentral.com
voice : 360.756.8080 x12
fax : 360.647.5351
www.audiencecentral.com
----- Excess quoted text cut - see Original Post for more -----
Author: CF Dude
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25130#126414
Couldn't you do something like this? (not exact code)
<cftry>
<cfquery datasource=#DoesThisExist#>
select * from table where 1 = 2
</cfquery>
<cfcatch type="Database">
Something broker
</cfcatch>
</cftry>
Just an idea.
E
Is there a way to test to see if a data source exists? I am creating a setup
routine where the user will enter the data source name, and I would like to
check to see if it exists before using it.
Author: Stan Winchester
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25130#126413
Is there a way to test to see if a data source exists? I am creating a setup
routine where the user will enter the data source name, and I would like to
check to see if it exists before using it.
Stan Winchester
stan@aftershockweb.com
http://www.aftershockweb.com/
Tel. 503-244-3440
Fax 503-244-3454
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
