Application.cfc datasource definition by type

The docs
http://docs.lucee.org/guides/cookbooks/datasource-define-datasource.html
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.

I have

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

And that works with MS SQL…On 9 February 2017 at 14:22, Dave Merrill <@Dave_Merrill> wrote:

The docs
http://docs.lucee.org/guides/cookbooks/datasource-define-datasource.html
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:5120611ea34c6123fd85120a0c27ab
23fd81ea34cb854”

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.


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 lucee@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/
msgid/lucee/c8319b90-015c-481f-9beb-871591f6c9f0%40googlegroups.com
https://groups.google.com/d/msgid/lucee/c8319b90-015c-481f-9beb-871591f6c9f0%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

And just checking some of my other Apps, some don’t have the Class defined
and still work…On 9 February 2017 at 16:34, Jedihomer Townend <@Jedihomer_Townend> wrote:

I have

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

And that works with MS SQL…

On 9 February 2017 at 14:22, Dave Merrill <@Dave_Merrill> wrote:

The docs
http://docs.lucee.org/guides/cookbooks/datasource-define-datasource.html
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:5120611ea34c6123fd8
5120a0c27ab23fd81ea34cb854”

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:14
33;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.


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 lucee@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/ms
gid/lucee/c8319b90-015c-481f-9beb-871591f6c9f0%40googlegroups.com
https://groups.google.com/d/msgid/lucee/c8319b90-015c-481f-9beb-871591f6c9f0%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

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.On Thursday, February 9, 2017 at 5:47:14 PM UTC-5, Jedihomer Townend wrote:

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

On 9 February 2017 at 22:29, Dave Merrill <enig...@gmail.com <javascript:> wrote:

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 :slight_smile:

Thanks for helping investigate.

Dave

On Thursday, February 9, 2017 at 3:44:20 PM UTC-5, Jedihomer Townend wrote:

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.

On 9 February 2017 at 18:26, Dave Merrill enig...@gmail.com wrote:

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.

On Thursday, February 9, 2017 at 11:35:30 AM UTC-5, Jedihomer Townend wrote:

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

On 9 February 2017 at 16:34, Jedihomer Townend je...@jedihomer.net wrote:

I have

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

And that works with MS SQL…

On 9 February 2017 at 14:22, Dave Merrill enig...@gmail.com wrote:

The docs
http://docs.lucee.org/guides/cookbooks/datasource-define-datasource.html
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.


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+un...@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
https://groups.google.com/d/msgid/lucee/c8319b90-015c-481f-9beb-871591f6c9f0%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.


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+un...@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
https://groups.google.com/d/msgid/lucee/9dd153e4-63d4-4cbc-af4f-bc398364190a%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit https://groups.google.com/d/optout.


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+un...@googlegroups.com <javascript:>.
To post to this group, send email to lu...@googlegroups.com <javascript:>
.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/4fde299e-c43a-4abc-8847-cb0aeda9ba58%40googlegroups.com
https://groups.google.com/d/msgid/lucee/4fde299e-c43a-4abc-8847-cb0aeda9ba58%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit https://groups.google.com/d/optout.

I was personally wondering how connection pooling would work (if at all)
with datasources in the appcfc… We’ve always used permanent datasources.
-GOn Sat, Feb 11, 2017 at 11:55 AM Dave Merrill <@Dave_Merrill> wrote:

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.

On Thursday, February 9, 2017 at 5:47:14 PM UTC-5, Jedihomer Townend wrote:

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

On 9 February 2017 at 22:29, Dave Merrill enig...@gmail.com wrote:

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 :slight_smile:

Thanks for helping investigate.

Dave

On Thursday, February 9, 2017 at 3:44:20 PM UTC-5, Jedihomer Townend wrote:

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.

On 9 February 2017 at 18:26, Dave Merrill enig...@gmail.com wrote:

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.

On Thursday, February 9, 2017 at 11:35:30 AM UTC-5, Jedihomer Townend wrote:

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

On 9 February 2017 at 16:34, Jedihomer Townend je...@jedihomer.net wrote:

I have

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

And that works with MS SQL…

On 9 February 2017 at 14:22, Dave Merrill enig...@gmail.com wrote:

The docs
http://docs.lucee.org/guides/cookbooks/datasource-define-datasource.html
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.


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+un...@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
https://groups.google.com/d/msgid/lucee/c8319b90-015c-481f-9beb-871591f6c9f0%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.


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+un...@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
https://groups.google.com/d/msgid/lucee/9dd153e4-63d4-4cbc-af4f-bc398364190a%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit https://groups.google.com/d/optout.


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+un...@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
https://groups.google.com/d/msgid/lucee/4fde299e-c43a-4abc-8847-cb0aeda9ba58%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit https://groups.google.com/d/optout.


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 lucee@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/5b81f471-9010-47e2-9a1a-63128b92a51b%40googlegroups.com
https://groups.google.com/d/msgid/lucee/5b81f471-9010-47e2-9a1a-63128b92a51b%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.