Accessing mySQL DB

dev support
#1

On Windows 10, I’m trying to execute a simple query but I don’t understand the code structure required. I cut and pasted an example I found but how do I find what the class and connectionString need to be?

ds = { class: 'org.gjt.mm.mysql.Driver', connectionString: 'jdbc:mysql://localhost:3306/bradwood?useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=true', username: 'root', password: 'whatever'};

var qry = queryExecute( sql=‘select * from tblusers’, options={ datasource : ds } );

for( var row in qry ) {
echo( row.role & chr( 10 ) );
}

Java Version: ???
Tomcat Version: ???
Lucee Version: 5.3.7.48

#2

just use the lucee admin to configure a datasource

there is a generated code snippet at the bottom of the edit datasource page which you can copy pasta

#3

Zackster, thanks for the response, it gets me a little further. Now I have this error: key [type] doesn’t exist

for this code:
var qry = queryExecute( sql=‘select * from tblusers’, options={ datasource : this.datasources } );

What is it referring to?

#4

What name is defined as datasource? I think your definition in the queryexec line shouldn’t be “this.datasources”, instead it needs to be the defined name. Usually if the datasource is defined and set with the name “myDataSourceName”, then the string defined in the application cfc should be something like this.datasources[“myDataSourceName”]=… then the query should be like:

var qry = queryExecute( sql: "select * from tblusers", options:{ datasource="myDataSourceName"} );

See the docs for further details:

#5

Always read the error message and stack trace, it’s telling you exactly what the problem is, it may seem a little cryptic at first but all the clues are there.

Type is missing from what? The datasource? Then cfdump out this.datasources and see what you’re passing in

Lucee 5.3.8.169-SNAPSHOT Error (expression)
Message The key [type] does not exist, only the following keys are available: [A, B, C].
Stacktrace The Error Occurred in
C:\lucee\tomcat\webapps\ROOT\query.cfm: line 3

1:
2: this.datasources = {a:1,b:2,c:3};
3: var qry = queryExecute( sql=“select * from tblusers”, options={ datasource : this.datasources } );
4:
5: for( var row in qry ) {
Java Stacktrace lucee.runtime.exp.ExpressionException: The key [type] does not exist, only the following keys are available : [A, B, C].
at lucee.runtime.type.util.StructSupport.invalidKey(StructSupport.java:65)
at lucee.runtime.type.StructImpl.get(StructImpl.java:139)
at lucee.runtime.listener.AppListenerUtil.toDataSource(AppListenerUtil.java:247)
at lucee.runtime.tag.Query.toDatasource(Query.java:247)
at lucee.runtime.tag.Query.setDatasource(Query.java:242)

#6

I had used the code snippet generated by editing the datasource page but hadn’t realised it was intended for use in Application.cfc (I was including it in my program), so presumably that’s why I got the non-existent datasource message. I’m now reading up on CFC’s in Lucee and trying to understand how to set up this code snippet and then how to refer to the cfc from my program. I also don’t understand what the “this.” prefix means.

#7

I totally understand the problem you are facing, really a LOT! There is a need of a new cfml book/ressource that handles cfml in a modern way. There is plenty of stuff all over the net, but no easy step by step guide. You can get a lot of Lucee docs, but its like a index with some good guides. Best at the moment in the net s: learncfinaweek.com but it has been down often lately. We really need some easy contemt like w3schools for cfml with a modern approach. There is a lot of.work to do!!! May be you are the chance for some of us loose conributors to go for it!

For your issue, take a quick look at:

1 Like
#8

If you set the configured datasource in this.datasource=“datasourcename” in an application.cfc means you don’t need to add datasource in cfquery/queryexecute

But if you have multiple datasource means you can set default datasource by using this.defaultdatasource=“datasourcename”

#9

Very good cfmitrah and thank you. I got it to work but only by including a datasource in my cfm.

cfquery name=“qry” datasource=“dpi”
select * from tbllocks
/cfquery

When I didn’t include the datasource, I got:

attribute [datasource] is required when attribute [dbtype] is not [query] and no default datasource is defined

#10 
<cfset res = getApplicationMetadata()>
<cfdump var="#res.datasource#">

What would you get from the above code using in the .cfm file? If you configured the datasource in application.cfc by this.datasource means, the above code returns the datasource name

#11

@new2lucee

Since it worked when you added the datasource attribute in your cfquery then try the following:

// cfscript format
component{
this.datasource = "dpi";
}
// tag format
<cfcomponent>
<cfset this.datasource = "dpi">
</cfcomponent>

Save the file in the root directory of your web app as Application.cfc. Note the capital “A” in Application.cfc.

Once you’ve done that run your query again but you can omit the datasource attribute in the cfquery tag.

#12

Thanks Hugh but it still has a problem. This is my Application.cfc:

component { this.datasources["dpi"] = { class: 'com.mysql.cj.jdbc.Driver' , bundleName: 'com.mysql.cj' , bundleVersion: '8.0.19' , connectionString: 'jdbc:mysql://localhost:3306/prodndb?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=EXCEPTION&verifyServerCertificate=false&tinyInt1isBit=false&serverTimezone=Australia/Sydney&autoReconnect=true&useSSL=true&maxReconnects=3&jdbcCompliantTruncation=true&useOldAliasMetadataBehavior=true&allowMultiQueries=true&useLegacyDatetimeCode=true' , username: 'something' , password: "whatever" 
// optional settings
, connectionLimit:100 // default:-1
, alwaysSetTimeout:true // default: false
, validate:false // default: false
	};

}

and it says:
attribute [datasource] is required when attribute [dbtype] is not [query] and no default datasource is defined

#13

cfmitrah, no datasource name! There must be something wrong with the structure of my Application.cfc entry.

#14

@new2lucee just add this line in your Application.cfc

this.datasource = “dpi”

FYI, this.datasources for creating a datasource in the Application.cfc
this.datasource for defines which datasource was used by the application.

#15

@new2lucee,
Like cfmitrah has indicated don’t use the this.datasources property. Just use:

this.datasource = ‘dpi’;

The extra metadata you’ve included in your datasources property feels powerful, but it’s not necessary in most cases.

#16

Hugh, that worked just fine. Many thanks.