|
Example of inserting a Struct() into a database using keys |
||||||||
A while ago a colleague and I were working on a timesheet application in Flex. The idea was that you could commit a custom timebar object, generated in flex, and it would update the dataset in the back end using the ColdFusion flex gateway.
I came across the code recently, and decided to tidy it up a bit, and make the query dynamic, based on the Struct contents. The obvious limitation to this is that your Struct and your database schema have to match exactly.
I won't go into the Flex application here, but I've emulated its input arguments here with a pre-populated structure.
2 timesheetTask = StructNew();
3 StructInsert(timesheetTask, "employeeid", '36');
4 StructInsert(timesheetTask, "timesheetDT", '0');
5 StructInsert(timesheetTask, "projectid", '6');
6 StructInsert(timesheetTask, "weekid", '25');
7 StructInsert(timesheetTask, "taskid", '39');
8 StructInsert(timesheetTask, "hours", '8');
9 StructInsert(timesheetTask, "comment", 'Comments for this task live here');
10 StructInsert(timesheetTask, "szStatus", '1');
11 StructInsert(timesheetTask, "iFirstLineApproval", '23');
12 StructInsert(timesheetTask, "iSecondLineApproval", '34');
13 StructInsert(timesheetTask, "iCurrentApprover", '');
14 StructInsert(timesheetTask, "szRejectReason", '');
15 StructInsert(timesheetTask, "szDescription", '');
16
17 updateTimesheet = createObject("component", "timesheet");
18 updateTimesheet.updateTask(timesheetTask);
19</cfscript>
Notice that this code also calls the CFC object at the end. The data itself isn't massively important, it's a time object for recording tasks.
Next we have the function, which accepts a Struct() argument called 'taskStruct'. I then loop through the structure, and populate a SQL query using the keys from a collection. The only logic is a check to see if it is the last structure element, as this controls the ',' placement.
2 <cfargument name="taskStruct" type="struct" required="yes">
3 <cfset var count = 0>
4
5 <cfdump var="#arguments.taskStruct#">
6 <cfset variables.structSize = structCount(arguments.taskStruct)>
7
8 <cfquery datasource="#application.dsn#">
9 INSERT INTO [dbo].[timesheet]
10 (<cfloop collection="#arguments.taskStruct#" item="key">
11 [#key#]
12 <cfset count = count + 1>
13 <cfif count LT variables.structSize>,</cfif>
14 </cfloop>)
15
16 <cfset count = 0>
17
18 VALUES(<cfloop collection="#arguments.taskStruct#" item="key">
19 '#arguments.taskStruct[key]#'
20 <cfset count = count + 1>
21 <cfif count LT variables.structSize>,</cfif>
22 </cfloop>)
23 </cfquery>
24
25 <cfreturn true>
26 </cffunction>
That will insert your Struct into a database, in small and tidy manner. It was somewhere around here that we started using cfproperty tags, and creating strongly typed objects for Flex.
|
Connecting Select form fields based on data selections Pt 3 |
||||||||
I've been exploring various methods of using JQuery to populate Select form fields. In the previous article I used an intermediary file to act as a handler for the JSON returned object. In this last article I have removed the extra handler template, interfacing directly with the CFC.
Firstly there are some small changes to the JQuery url call.
2<scr/ipt src="select-chain.js" type="text/javascript" charset="utf-8"></script>
3<scr/ipt type="text/javascript">
4 jQuery.noConflict();
5 jQuery(function () {
6 var type = jQuery('#series');
7 var sp = jQuery('#issueno');
8 var selectedSeries = jQuery('#series').val();
9
10 type.selectChain({
11 target: sp,
12 url: 'jquerySeries.cfc?method=getIssueNos',
13 type: 'post',
14 data: { ajax: true, returnformat: 'plain', series: selectedSeries }
15 }).trigger('change');
16
17 });
18</script>
Now we are directly referencing the CFC. Simply append the function name as a url parameter. In the "DATA" element we place the data we are passing in the form of key value pairs. This will build all the values into the JQuery url, so the final URL would be:
At this point I was getting an parse error, until I found the 'returnFormat' value. Coldfusion will return WDDX encoded packets by default, problem was my handler was looking for Json. Adding this value will stop this.
Next we have the same form as before, there are no changes at all: (here for completeness really)
2 <form action="" method="post">
3 <select name="series" id="series">
4 <cfoutput query="variables.series">
5 <option value="#variables.series.intId#">#variables.series.varName#</option>
6 </cfoutput>
7 </select>
8 <select name="issueno" id="issueno">
9 <option></option>
10 </select>
11 <button type="submit">Submit</button>
12 </form>
Lastly there are one or two small changes to our CFC function.
2 <cfargument name="series" type="numeric" required="false" hint="Id of the publication">
3 <cfset var result = "">
4
5 <cfquery name="result" datasource="#application.ds#">
6 SELECT [intIssueNo]
7 FROM [dbo].[tbl_cn_series_issueNos]
8 where intSeriesId = <cfqueryparam value="#arguments.series#" cfsqltype="cf_sql_integer">
9 </cfquery>
10
11 <cfsetting showdebugoutput="false">
12 <cfset ojson = createObject("component","cfjson")>
13 <cfset theresults = ojson.encode(listToArray(valuelist(result.intIssueNo)))>
14 <cfsetting enablecfoutputonly="true">
15 <cfreturn theresults>
16 </cffunction>
The access method has changed, so we add an "access=remote" value to expose the function as a service. Then we serialize the query result into JSON, and return it. This seems like a much more succinct way of doing this.
|
Connecting Select form fields based on data selections Pt 2 |
||||||||
I want to be able to dynamically change a second select field based on the value of the first select field.
Following on from the cfajaxproxy example I wrote last week, where I discovered I could not use ColdFusion 8 functionality on my live server, I have arrived at a variant solution.
In this example I am using a similar JQuery url request to process the output from a CFC. The first template is the form itself. This includes references to the JQuery libraries and the binding of the response to the form elements. It also builds the url request to the 'request_processor.cfm' file, which handles the CFC.
2<scr/ipt src="select-chain.js" type="text/javascript" charset="utf-8"></script>
3<scr/ipt type="text/javascript">
4 jQuery.noConflict();
5 jQuery(function () {
6 var type = jQuery('#series');
7 var sp = jQuery('#issueno');
8
9 type.selectChain({
10 target: sp,
11 url: 'request_processor.cfm',
12 type: 'post',
13 data: { ajax: true }
14 }).trigger('change');
15
16 });
17</script>
Next build a simple form, and populate the first select field with data from a method.
2 <cfdump var="#form#">
3<cfelse>
4 <cfset variables.series = createObject("component","jquerySeries"). getSeries()>
5 <form action="" method="post">
6 <select name="series" id="series">
7 <cfoutput query="variables.series">
8 <option value="#variables.series.intId#">#variables.series.varName#</option>
9 </cfoutput>
10 </select>
11 <select name="issueno" id="issueno">
12 <option></option>
13 </select>
14 <button type="submit">Submit</button>
15 </form>
16</cfif>
Next is the intermediately file that handles the data manipulation, this is triggered when the user chooses an option from the first select field. It passes through the selected value, and returns the query object. This is then serialised using the cfJson object, to return the data in Json. If you do anything like this remember to watch your debug output, it was destroying my Json response for a good ten minutes before I remembered to turn it off. Doh!
2<cfsetting enablecfoutputonly="true">
3<cfparam name="form.series" default="8">
4<cfset variables.issues = createObject("component","jquerySeries").getIssueNos(series=form.series)>
5<cfset ojson = createObject("component","cfjson")>
6<cfset theresults = ojson.encode(listToArray(valuelist(variables.issues.intIssueNo)))>
7<cfoutput>#theresults#</cfoutput>
Finally a CFC that performs the database functions. This is a pretty straight forward CFC that performs two database queries, the second based on an id passed in from the first.
2
3 <cffunction name="getSeries" output="false" hint="Returns publication series">
4 <cfset var result = "">
5
6 <cfquery name="result" datasource="#application.ds#">
7 SELECT [intId],[varName]
8 FROM [dbo].[table]
9 Where intActive = '1'
10 Order by varName
11 </cfquery>
12
13 <cfreturn result>
14 </cffunction>
15
16 <cffunction name="getIssueNos" output="false" hint="returns related series issue numbers">
17 <cfargument name="series" type="numeric" required="false" hint="Id of the publication">
18 <cfset var result = "">
19
20 <cfquery name="result" datasource="#application.ds#">
21 SELECT [intIssueNo]
22 FROM [dbo].[table]
23 where intSeriesId = <cfqueryparam value="#arguments.series#" cfsqltype="cf_sql_integer">
24 </cfquery>
25
26 <cfreturn result>
27 </cffunction>
28</cfcomponent>
Once you have these elements hooked up you'll see that the response from the first select field changes the values in the second field. You can download a rar'd version of the code base here.
This works well, but I'm not massively happy about the 'remote_processor' file. I think I'll see if there is a way of directly calling the CFC, and moving the JSON serialisation into the functions.
|
Importing large datasets into MySQL - Packet too large errors |
||||||||
I was recently migrating some data from one application to another and came across a stumbling block in MySQL. If you are trying to run a large SQL file backup restoration and the source file is quite large you may encounter the error : 'Packet too large' or 'MySQL server has gone away'. This is cuased by a MySQL server memory error.
If you look in the mySQLserver installation path directory you should see a 'my.ini' file. This controls the default settings for SQL server when you start the service.
Add in a line of code like this:
under the value of [mysqld]. Save the file and then restart mySQL server.
You should now be able to run:
show variables like 'max_allowed_packet';
And have it return your ini value.
As long as the value you have set it to is greater than the file size it should run successfully.
You can also run it as a set session variable command in your SQL environment, like this:
This will set the same value for that instance of the sql server.
Update:
There also seems to be a massive difference in performance in running the imports directly into a GUI environment, or through a shell command. I've had a 600mb file fail several times in the GUI, but running:
It has imported within around 10 to 15 minutes.
