ColdFusion 8 DS limits?

Guys

I realize the Database Design I am working with isn’t optimal however PSQL 8 has a max field limit of 65,000 for a single DB engine. With that said I cannot overcome this limit unless I switch DB products or go with a separate DB for each client approach. My preliminary code is based off Jochem’s suggestion and it works perfectly however I am still concerned that I may run into trouble in the area of number Data Sources managed by CF 8. Does anyone have any insight on this threshold? Does one even exist that couldn’t be overcome with dare I say it :) hardware?

In response to the DSN-Less suggestion by Ksmith, I’ve been unable to get this type of connection to work. Here is a clip of what I’ve posted in the PSQL forum.
****************************************************************
POST on PSQL

Bill
Thanks for you perspective on this topic. I have been unsuccessful in setting up a DSN-Less connection in Coldfusion 8. I believe this may be due to the fact that this version of CF no longer supports native drivers and works purely off JDBC. While an option exists to use an (ODBC-JDBC bridge) and I am able to specify connection string info i.e. Driver={Pervasive Software ODBC-32};DDFPATH=C:\DDF;DATAPATH=C:\Data I have discovered that these additional parameters are completely ignored. You are correct:) it could get ugly trying to manage all the DBs thru PCC, but I happen to have a Dataflex 7 programmer on staff who should be able to write a module that should simpfy DB creation in lieu of using PCC. Perhaps I am missing something on the CF side, do you have an insight on settings up DSN-Less connections in CF8?

Here is the connection string I am attempting to use:

CF Driver: ODBC Socket
CF Data Source Name: PSQLWEB
ODBC DSN: PSQLWEB
Connection String: Driver={Pervasive Software ODBC-32};DDFPATH=C:\WEB\CLIENT_DDF;DATAPATH=C:\WEB\CLIENT_DATA

If it worked as it should I would simply swap out the CLIENT_DDF and CLIENT_DATA with a Session variable upon successful login to the site for the real location of that client's data.

Again Bill thanks for your expertise in this area:)

I am aware that a single PSQL DB has a max size of approx 65,000 fields, however my question as to how many individual DBs can be hosted by single PSQL engine has gone unanswered. Are you aware of any limits?
******************************************************************************
Here link reflecting proposed DB Design:
DB Design

Why not a single DSN that you send specific user & pwd attributes with? You could even do "use database x" type syntax for different databases. I do not see why you need 1000s of DSNs.

So, in theory, you want a differnet DSN for each user?

That is not a very healthy design structure when so many other DB servers could offer similar service under a single DSN using a different design, but similar tactic.

Your performance will depend upon the traffic occurring at the same time with simultaneous DB calls, whether they are to one DSN or hundreds of DSNs.

JMO

Jdeline thanks for your response to my post. To elaborate more on my design challenge I am working with here are some of the specifics. I am connecting to a Pervasive SQL 8 backend using the manufacturer supplied JDBC Type 4 driver. My SQL guy has asked that I develop a web-based front-end in CF using the following model.

1) Web users will be provided individual folders in which a set of five PSQL tables will reside. (These PSQL tables exist as separate entities and will not share data amongst each other)
2) All web users will access a common PSQL table at login and during step it is my goal to acquire the web user’s data source location and set it as a session variable that will be used through the site for that specific user.

So to simplify, I am looking for a way to change the target database I am working with based on information I acquire on web user during login. The solution I’ve drawn up on this so far….

1) During 1st login call the cfide.adminapi and create the data source programmatically as the PSQL tables will already exist and I will know the name of the tables based off client ID.
2) During subsequent logins the client’s ID will be stored as the data source name in a session variable which will ensure that clients only have access to the 5 tables assigned to them in there own folders.

Thanks for you input on this subject. I look forward to insight.

marty@assetresearch.com

Hmmm. Mind sharing why you want so many data sources? There may be a simple solution.