Application.cfc datasource definition by type

[Dave Merrill]

The docs say you can define a datasource in Application.cfc like this:

this.datasources["myds"] = {
         // required
        type: 'mysql'
        , host: 'localhost'
        , database: 'test'
        , port: 3306
        , username: 'root'
        , password: "encrypted:5120611ea34c6123fd85120a0c27ab23fd81ea34cb854"

Besides host, port, database, username, pw, etc, I've tried this, and the datasource doesn't get defined:

        , type: "MSSQL"

This works:

        , class: 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
        , connectionString: 'jdbc:sqlserver://localhost:1433;DATABASENAME=mydb;sendStringParametersAsUnicode=true;SelectMethod=direct'

Am I missing something? Is the 'type' syntax MySQL only? Anyone else using this functionality?

This is Lucee 5.1.0.34 running under CommandBox, if that matters.

Thanks.

[Jedihomer Townend]

I have

```

type: "MSSQL",

class: "com.microsoft.jdbc.sqlserver.SQLServerDriver",

```

And that works with MS SQL...

--
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/c8319b90-015c-481f-9beb-871591f6c9f0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jedihomer Townend]

And just checking some of my other Apps, some don't have the Class defined and still work...

[Dave Merrill]

Thanks for checking.

So you have SQL Server DSNs that work with type only, no class?

What version of Luceee are you running where that's the case? I can't think of any other reasons why it'd work for you and not me.

Here, both class and connectionString are required, omitting either one leaves the datasource undefined.

Interestingly, your class string is different from mine, which came from Lucee admin, but either one appears to work.

Odd.

To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.

[Jedihomer Townend]

Yeah, running both, I believe without the class string it uses the non-Microsoft jTDS version. Although I could be wrong on that.

We're running with versions 5.1.0.34 and version 4 in a Docker container...

My full-ish code is:

this.datasources[Request.DB.DSN.Reports] = {

type: "MSSQL",

host: Request.DB.Host,

database: Request.DB.DB.Reports,

port: 1433,

username: Request.DB.DB.Username,

password: Request.DB.DB.Password,

custom: {

characterEncoding: "UTF-8",

useUnicode: true,

connectionTimeout: 5,

validate: true,

clob: true

}

}

and some with class: "com.microsoft.jdbc.sqlserver.SQLServerDriver" added  

I also have some defined as:

this.datasources[Request.DB.DSN.Reports] = {

class: "com.microsoft.jdbc.sqlserver.SQLServerDriver",

connectionString: "jdbc:sqlserver://" & Request.DB.Host & ":1433;DatabaseName=" & Request.DB.DB.Reports & ";SelectMethod=direct",

username: Request.DB.Username,

password: Request.DB.PasswordEnc,

clob:true,

useUnicode: true,

connectionTimeout:5,

characterEncoding: "UTF-8",

validate: true,

custom: {

characterEncoding: "UTF-8",

useUnicode: true,

connectionTimeout: 5,

validate: true,

clob: true

}

};

At some point I'll update everything to a standard... But at the moment those are the configurations I'm using.

And confirm they are in the pseudo constructor portion of Application.cfc an not onApplication/RequestStart functions.

 

To unsubscribe from this group and stop receiving emails from it, send an email to lucee+unsubscribe@googlegroups.com.

To post to this group, send email to lu...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/9dd153e4-63d4-4cbc-af4f-bc398364190a%40googlegroups.com.

[Dave Merrill]

Arg. Well I can't explain it, but it's working now, no changes. Very weird.

Also, though I've seen on the net that, as you said, datasources need to be defined in the Application.cfc pseudo constructor, not in onRequestStart(), it's working in onRequestStart() too. Which is good, because I need to set one of the two datasources in response to the URL being accessed, which is much more cleanly read as arguments.targetPage in that context than through CGI scope vars in the pseudo constructor. Maybe you only need to do it then if you're using the default datasource, and not specifiying one in your queries, which doesn't apply here.

As I said, weird. Hope it still works tomorrow :)

Thanks for helping investigate.

Dave

[Jedihomer Townend]

As far as I know it shouldn't work in the onRequestStart etc functions.

In the psuedo constructor you still have access to the URL/Form/CGI scope.  In my code, the request variables giving it a name/db etc are first defined from the listFirst(CGI.Script_Name, "/") and other environmental variables as different folders require different databases.

HTH

To unsubscribe from this group and stop receiving emails from it, send an email to lucee+unsubscribe@googlegroups.com.

To post to this group, send email to lu...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/4fde299e-c43a-4abc-8847-cb0aeda9ba58%40googlegroups.com.

[Dave Merrill]

Thanks for your info.

I've given up on this, at least for now. Regardless of whether the calls were in the constructor or not, they simply weren't reliable, for whatever reason. The datasources sometimes got created, sometimes not.

In any case, we can use the admin API to create permanent datasources, then just set application vars with the names of the ones to use. There's no reason to incur the overhead of recreating the same datasources over and over on every request.

[Joseph Gooch]

I was personally wondering how connection pooling would work (if at all) with datasources in the appcfc.... We've always used permanent datasources.

-G

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/5b81f471-9010-47e2-9a1a-63128b92a51b%40googlegroups.com.