8 Replies Latest reply on Jun 26, 2007 8:34 AM by hellodnt

    Oracle Datasource on the fly

    hellodnt
      hi there,

      can you someone help me with code on create "oracle" datasource on the fly? I want the cf code to query any database I specify on the cf page and not the CF administrator page.

      Thanks
        • 1. Re: Oracle Datasource on the fly
          Grizzly9279 Level 1
          I'm not really sure why you would need to do this, or what you're really trying to accomplish. I have to wonder if you're perhaps approaching the problem from the wrong angle to begin with...

          For starters, which driver do you intend to use? OLE DB, generic ODBC, the JDBC "thin" client, or perhaps the Oracle JDBC OCI client?
          • 2. Re: Oracle Datasource on the fly
            hellodnt Level 1
            I have no problem setting up Oracle using CF administrator. However, I want to able connection to any database in CF page without going through CF administrator. I was told there's a way. But I can't figure it out.
            • 3. Re: Oracle Datasource on the fly
              hellodnt Level 1
              Sorry, I'm using "Oracle" driver
              • 4. Re: Oracle Datasource on the fly
                Level 7
                hellodnt wrote:
                > I have no problem setting up Oracle using CF administrator. However, I want to
                > able connection to any database in CF page without going through CF
                > administrator. I was told there's a way. But I can't figure it out.
                >

                Not without going through the administrator. Older versions of CF
                allowed one to define a DSN in the <cfquery...> tag. But that has gone
                away. The later versions of CF now have an administrator API that
                allows one to programmatically set DSN and other settings.

                I have never done this, but there is an entire document about it in the
                CF documentation.
                • 6. Re: Oracle Datasource on the fly
                  hellodnt Level 1
                  Hi Simon,

                  Yes. I have looked at this doc but can't figure it out for Oracle setting.
                  • 7. Re: Oracle Datasource on the fly
                    Stressed_Simon Level 1
                    If you view the CFC with the CFC viewer you can get the documentation:-

                    setOracle
                    public void setOracle ( string vendor="oracle", string type="ddtek", required string name, required string host, required string sid, string originaldsn="", string port="1521", string driver="Oracle", string class="macromedia.jdbc.MacromediaDriver", string username="", string password="", boolean encryptpassword="true", string description="", string args, numeric MaxPooledStatements, numeric timeout, numeric interval, numeric login_timeout, numeric buffer, numeric blob_buffer, boolean enablemaxconnections, numeric maxconnections, boolean pooling, boolean disable, boolean disable_clob, boolean disable_blob, boolean select, boolean create, boolean grant, boolean insert, boolean drop, boolean revoke, boolean update, boolean alter, boolean storedproc, boolean delete )

                    Creates or modifies an Oracle data source.

                    Output: suppressed
                    Parameters:
                    vendor: string, optional, vendor - Always Oracle.
                    type: string, optional, type - Always ddtek.
                    name: string, required, name - ColdFusion datasource name.
                    host: string, required, host - Database server host name or IP address.
                    sid: string, required, sid - The Oracle System Identifier that refers to the instance of the Oracle database software running on the server. ORCL is the default.
                    originaldsn: string, optional, originaldsn - Original ColdFusion datasource name, if you are renaming this dsn.
                    port: string, optional, port - Port that is used to access the database server. (default 1521)
                    driver: string, optional, driver - JDBC driver.
                    class: string, optional, class - JDBC class file.
                    username: string, optional, username - Database username.
                    password: string, optional, password - Database password.
                    encryptpassword: boolean, optional, encryptpassword - Indicates whether to encrypt the password when storing it in the neo-query.xml file:

                    * True – Encrypt the password before storing it.
                    * False - Store the password in clear text.

                    Note: If you are updating a data source that already has an encrypted password, you must set this argument to false to avoid re-encrypting an encrypted password.


                    description: string, optional, description - A description of this data source connection.
                    args: string, optional, args - Connection string arguments, formatted (arg1=argvalue;arg2=argvalue).
                    MaxPooledStatements: numeric, optional, MaxPooledStatements - The maximum number of pooled statements.
                    timeout: numeric, optional, timeout - The number of minutes that ColdFusion maintains an unused connection before destroying it.
                    interval: numeric, optional, interval - The time (in minutes) that the server waits between cycles to check for expired data source connections to close.
                    login_timeout: numeric, optional, login_timeout - The number of seconds before ColdFusion times out the data source connection login attempt.
                    buffer: numeric, optional, buffer - The default buffer size, used if disable_clob is not specified or True. Default is 64000 bytes.
                    blob_buffer: numeric, optional, blob_buffer - The default buffer size, used if disable_blob is not specified or True. Default is 64000 bytes.
                    enablemaxconnections: boolean, optional, enablemaxconnections - Enables the maxconnections setting.
                    maxconnections: numeric, optional, maxconnections - Limit connections to this maximum amount.
                    pooling: boolean, optional, pooling - Enable server connection pooling for your data source.
                    disable: boolean, optional, disable - Suspends all client connections to the data source.
                    disable_clob: boolean, optional, disable_clob - Specify False to return the entire contents of any CLOB/Text columns in the database. If you specify False, ColdFusion MX retrieves up to the amount specified in the buffer argument.
                    disable_blob: boolean, optional, disable_blob - Specify False to return the entire contents of any BLOB/Image columns in the database. If you specify False, ColdFusion MX retrieves up to the amount specified in the blob_buffer setting.
                    select: boolean, optional, select - Allow SQL SELECT statements.
                    create: boolean, optional, create - Allow SQL CREATE statements.
                    grant: boolean, optional, grant - Allow SQL GRANT statements.
                    insert: boolean, optional, insert - Allow SQL INSERT statements.
                    drop: boolean, optional, drop - Allow SQL DROP statements.
                    revoke: boolean, optional, revoke - Allow SQL REVOKE statements.
                    update: boolean, optional, update - Allow SQL UPDATE statements.
                    alter: boolean, optional, alter - Allow SQL ALTER statements.
                    storedproc: boolean, optional, storedproc - Allow SQL stored procedure calls.
                    delete: boolean, optional, delete - Allow SQL DELETE statements.
                    • 8. Re: Oracle Datasource on the fly
                      hellodnt Level 1
                      Thanks Simon

                      I will try those parameters and let you know for the result. Again thanks for inputs and help.