Computed Columns MS SQL

A table with a Totals column is a perfect use case for computed columns in MSSQL. Making the Totals column a computed column allows MSSQL to do all the preassigned math for me, eliminating the need to write SQL or ColdFusion code to sum up the monthly totals. Every time I update the table I would have to run other code or SQL to again obtain the correct total.

Here's my table, MonthlyPay.

view plain print about
1SELECT [ID]
2 ,[Year]
3 ,[January]
4 ,[February]
5 ,[March]
6 ,[April]
7 ,[May]
8 ,[June]
9 ,[July]
10 ,[August]
11 ,[September]
12 ,[October]
13 ,[November]
14 ,[December]
15 ,[Totals]
16 FROM [MonthlyPay]

By turning Totals into a computed column, my life is easier. Here's how:

view plain print about
1ALTER TABLE [MonthlyPay] DROP COLUMN [Totals];
2GO
3ALTER TABLE [MonthlyPay] ADD [Totals] AS (
4[January] + [February] + [March] + [April] + [May] + [June] + [July] + [August] + [September] + [October] + [November] + [December]) PERSISTED;

I could add even more calculations including the math to get tax percentages and including those in the total. So much easier!

Combining MSSQL Functions and Wildcards to Filter Data

We can combine SQL functions and use "LIKE" to filter data searching a string in a column. We are not using RegEx, just simple pattern matching. Here's our practical example.

We are given a table called "CityYear" with a column, "Title" that has this pattern "[city name] ([year])", e.g.;

view plain print about
1Title
2Los Angeles (2015)
3Los Angeles (2016)
4Los Angeles (2017)
5Los Angeles (2018)
6Denver (2015)
7Denver (2016)
8Denver (2017)
9Denver (2018)
10Dallas (2015)
11Dallas (2016)
12Dallas (2017)
13Dallas (2018)

Let's say we just want to pull the rows containing data for the current year only (2018 at the time of this post).

The first attempt could look like this:

view plain print about
1SELECT Title
2FROM CityYear
3WHERE Title LIKE '% + YEAR(GETDATE() )+ %'

However this returns no results. This is because we are matching to a string that literally contains "+ YEAR(GETDATE() ) +". Trying the following:

view plain print about
1SELECT Title
2FROM CityYear
3WHERE Title LIKE '%' + YEAR(GETDATE ()) + '%'

Gives us an error: Conversion failed when converting the varchar value '%' to data type int.

view plain print about
1SELECT Title
2FROM CityYear
3WHERE Title LIKE '%' + CAST(YEAR(GETDATE()) as varchar(4)) + '%'

The above query returns exactly what we want:

view plain print about
1Title
2Los Angeles (2018)
3Denver (2018)
4Dallas (2018)

The following query narrows down possible variations even more by conforming to the pattern and adding parenthesis to the comparison parameter.

view plain print about
1SELECT Title
2FROM CityYear
3WHERE Title LIKE '%(' + CAST(YEAR(GETDATE()) as varchar(4)) + ')%'

If we want to find the previous year (in this case, 2017), we subtract from the YEAR(GETDATE()) function.

view plain print about
1SELECT Title
2FROM CityYear
3WHERE Title LIKE '%(' + CAST(YEAR(GETDATE()) -1 as varchar(4)) + ')%'

view plain print about
1Title
2Los Angeles (2017)
3Denver (2017)
4Dallas (2017)

Select Distinct and Return Multiple Columns

How does one select a distinct column value and still bring back all other associated columns for the distinct value?

In this example we have repeated [Requisition_Number]s and wish to find distinct values, but we still want the other columns returned also.

This ignores the DISTINCT and returns all the rows:

view plain print about
1SELECT [ID]
2        ,DISTINCT [Requisition_Number]
3        ,[Status]
4        ,Title]
5        ,[Code]
6        ,[UID]
7        ,[Full_Name]
8        ,[City]
9        ,[State]
10        ,[Requisition_Justification]
11        ,[Date_Requisition_Opened]
12    FROM [Requisitions]
and is NOT what we want.

This returns JUST the distinct values for [Requisition_Number] and columns associated with those values.

view plain print about
1SELECT     [ID]
2        ,[Requisition_Number]
3        ,[Status]
4        ,Title]
5        ,[Code]
6        ,[UID]
7        ,[Full_Name]
8        ,[City]
9        ,[State]
10        ,[Requisition_Justification]
11        ,[Date_Requisition_Opened]
12    FROM (
13SELECT     [ID]
14        ,[Requisition_Number]
15        ,[Status]
16        ,Title]
17        ,[Code]
18        ,[UID]
19        ,[Full_Name]
20        ,[City]
21        ,[State]
22        ,[Requisition_Justification]
23        ,[Date_Requisition_Opened]
24        ,ROW_NUMBER() OVER(PARTITION BY [Requisition_Number] ORDER BY [ID]) RN
25    FROM [Requisitions]) R
26    WHERE RN = 1

not equal to null not working

In MSSQL, looking for rows that are not equal to a certain value does not return rows with null values. To retrieve all values that are not equal and are null, you have to add both clauses "not equal" and "is null" to return all values not equal.

In SQL checking for NOT EQUAL ("!=" or "<>") will not return NULL values. NULL has no value and cannot be compared using scalar value operators.

I had to change my WHERE clause from:

view plain print about
1[whatever] != 1
To
view plain print about
1([whatever] != 1 OR [whatever] is null)

CTE with Recursion Example

Common Table Expressions with Recursion using WITH. A Common Table Expression is a temporary result set upon which we can work against. It's kind of like a sub query in memory that we can manipulate. Referencing this temporary table to create a subset is an example of recursion. This example may help explain and provide reference to create your own CTE with Recursion query.

This is very easy in ColdFusion but not so in Microsoft SQL Server or TSQL. We'd like to keep it on the database side because it is so much faster with less overhead on servers and network. It is a lot faster than trying to use sub-queries too.

We are given this table called dbo.JoeTest1:

view plain print about
1EVID    BillDocket
2191    External:277
3192    External:277
4193    External:277
5194    External:277
6195    Internal:117
7197    Internal:HB2255
8206    External:4;5;7
9206    Action:123
10206    Combo:1;0;67
11206    Action:1321;5445;3545
12206    Internal:1233;1323
13207    Internal:11;22;33
14207    External:44;55;66
15211    External:729
16214    External:481

Notice EVIDs 206 and 207. They have multiple records. We want to combine those records by concatenating the BillDocket and showing one result set for each unique EVID.

We want the results to look like the following (again, notice 206 & 207):

view plain print about
1EVID    BillDocket
2191    External:277
3192    External:277
4193    External:277
5194    External:277
6195    Internal:117
7197    Internal:HB2255
8206    Action:123,Action:1321;5445;3545,Combo:1;0;67,External:4;5;7,Internal:1233;1323
9207    External:44;55;66,Internal:11;22;33
10211    External:729
11214    External:481

Compare that to the table above, looking at 206 & 207. We also have 5 less rows in the result set due to the combining of column values.

How do we get this result? Here's the query with some explanations commented inline:

view plain print about
1-- we use the 'WITH' CTE to create the 'anchor' table
2-- also define the temporary table name and columns
3WITH Bills (EVID, BillDocket)
4AS
5(
6-- this is the anchor table upon which we will recurse
7-- if run separately, this query will return the 1st table above
8SELECT J.EVID, BillDocket
9FROM [dbo].JoeTest1 J
10)
11(
12-- this does the recursion
13SELECT Bills2.EVID,
14    -- stuff command, concatenates and combines for us
15    STUFF(
16        -- we are adding the comma plus the next BillDocket to the string
17        (SELECT DISTINCT ',' + J1.BillDocket
18            FROM [dbo].JoeTest1 J1
19            -- matched the anchor to the CTE table
20            WHERE Bills2.[EVID] = J1.[EVID]
21                FOR XML PATH (''))
22            , 1, 1, '')
23        AS BillDocket
24FROM Bills Bills2
25GROUP BY Bills2.EVID
26)
27-- have to keep this to break any type of infinite loop we may cause
28OPTION (MAXRECURSION 1000);

ColdFusion CLIENT cookie issue

MSSQL error: The data types text and varchar are incompatible in the equal to operator.

In the ColdFusion administrator under there is the option to save CLIENT variables as cookies, in the registry, a datasource or not at all.

If the client variables are stored as cookies, it is fairly easy to retrieve and parse to determine things like the identity of a returning user from the cookie. This cookie is called CFCLIENT_<cfapplication name>. If the client variables are stored in the database, it's not so easy. Actually impossible, the cookie variables are NOT stored in the datasource.

First to locate the CLIENT variables, which are stored in a table called CDATA. CFGLOBALS client variables are session variables, like CFID, CFTOKEN and timestamp details and are stored in the table CGLOBAL.

So what about the error? The data types text and varchar are incompatible in the equal to operator. I ran some queries to try and see if there was some information I could glean from the client variables table.

view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] != ''
Error: The data types text and varchar are incompatible in the not equal to operator.

[data] is a text field permitting nulls. So I checked for null:

view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] is null
pulled up everything, to test, I ran this:
view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] is not null
which turned up nothing! So I added a space in the quotes:
view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] != ' '
The data types text and varchar are incompatible in the not equal to operator.
No go! But this worked:
view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] not like ''
which pulled up what I wanted to look at, but unfortunately could not use. No cookie info is stored in with the datasource client session storage option in the ColdFusion administrator.

MSSQL Remove duplicate records

MSSQL Remove duplicate records from a table leaving a single unique record.

The situation arose where we had multiple records for items where there should be only a single record. We have JobIDs which will have multiple unique TaskIDs. We found we several (lots) of JobIDs which had 2 of the same TaskIDs. It's OK to have many TaskIDs in each job, there just can't be 2 of the same TaskIDs per JobID. Many TaskIDs to one JobID and one unique TaskID to one JobID.

First we find which TaskIDs are duplicated.

view plain print about
1Select DISTINCT TaskID FROM (
2select JobID, TaskID, (*) as Count from JobTable
3Where TaskID IS NOT NULL
4Group by JobID, TaskID HAVING COUNT(*) >
1) X

We are going to remove the 2nd entry for each duplicate TaskID. The original TaskID is correct in this case. The 2nd TaskID is superfluous and will have a greater Identity Key, they were added at a later time. Let's pull up the duplicate TaskIDs with the larger Identity Key (ID).

view plain print about
1SELECT T1.ID
2FROM JobTable T1
3JOIN
4(SELECT JobID,TaskID , MAX(ID) AS ID FROM JobTable
5GROUP BY JobID,TaskID
6Having COUNT(*) >
1) AS T2
7ON T1.TaskID = T2.TaskID AND T1.Qbid = T2.QBID AND T1.ID = T2.ID

This pulls up what we want, so lets get rid of them.

view plain print about
1DELETE JobTable WHERE ID in (
2SELECT T1.ID
3FROM JobTable T1
4JOIN
5(SELECT JobID,TaskID , MAX(ID) AS ID FROM JobTable
6GROUP BY JobID,TaskID
7Having COUNT(*) >
1) AS T2
8ON T1.TaskID = T2.TaskID AND T1.Qbid = T2.QBID AND T1.ID = T2.ID
9)

Done! Now we have unique TaskIDs for each JobID.

Find Duplicates in MSSQL

Find duplicate entries in MS SQL table columns. Need to see if you have 2 or more duplicate entries in your table? Want to find out if the key is really unique and there are not multiples? Simple!

view plain print about
1SELECT [ID], COUNT([TaskID])as NumberOfDups
2FROM [dbo].[MyBigTable]
3GROUP BY ID,[TaskID]
4HAVING (COUNT([TaskID]) >
1 )

ID = the identifier, could be a name or whatever
TaskID = looking for duplicates of this

Trouble with cfloop lists and arrays with cfqueryparam

I was looping an array and and using each item in a cfqueryparam. Running a query in each loop. Each iteration was displaying a null query result, which was incorrect. The same problem occurred with a list of the same items.

The simple answer was to surround the cfqueryparam value with the Trim() function! It worked with the list loop too.

view plain print about
1<cfqueryparam value="#Trim(MSArray[j])#" cfsqltype="CF_SQL_VARCHAR">

Here is my effort:

view plain print about
1<cfset MSList = "
2010,
3020,
4004,
5075.5,
6027,
7030,
8020.5,
9021,
10023,
11041,
12041.1,
13041.5,
14041.3,
15040,
16080,
17102,
18088.1,
19095,
20104.5,
21104.1,
22104.3,
23110,
24096,
25100,
26107.1,
27112,
28124,
29025,
30101,
31143,
32143.1,
33126,
34132,
35146,
36149,
37153,
38154,
39155,
40160
41"

42/
>

43
44<cfloop list="#MSList#" index="j">
45    <cfquery datasource="#myDataSource#" name="getID">
46         SELECT [ID]
47         FROM     [dbo].[MS_Table]
48         where [Activity_ID] = 1
49         and     [MS] = <cfqueryparam value="#Trim(j)#" cfsqltype="CF_SQL_VARCHAR">
50    </cfquery>
51    <cfdump var="#getID#">
52</cfloop>

Custom Logging Template for SQL queries and Stored Procedures

We'll combine the last two blog posts, Where Does CF Debug Information Come from Mommy? Or How to use the ColdFusion Service Factory and ColdFusion Custom Debugging Template. The task is to create a template to log any database activity, specifically looking for consistently long running queries and to make a baseline for our code optimization.

Let's take a look at the data we are gathering. I created this table to keep data which I thought would be pertinent.

Hey this image will be loaded soon!

Here's a key explaining the basics on this table:
ATTUID = individual user id
RequestID = Individual ID per request (more below)
Body = the actual query
Line = line number of query or stored procedure
Name = assigned name of query or stored procedure
CFScript = not what you think, this is the script name calling the query or stored procedure
CFTimeStamp = ColdFusion's time stamp
SQLTimeStamp = MS SQL's time stamp
Type = query or stored procedure

Now the code...
First we'll utilize the ColdFusion Service Factory:

view plain print about
1<!--- initialize the CF Server's Service Factory --->
2<cfset factory = createObject("java","coldfusion.server.ServiceFactory") />
3<!--- get all the debugging data --->
4<cfset queries = factory.getDebuggingService().getDebugger().getData() />
We create the logging service by calling Illudium PU-36 generated beans and DAO (Data Access Objects). Basically these are the getters and setters.
view plain print about
1<!--- Set up Logging Service - call it once and reuse it --->
2<cfset CFLog = createObject("component","optimization.cfc.Optimization_Code_Log").init() />
3<cfset CFLogger = createObject("component","optimization.cfc.Optimization_Code_LogDAO").init(application.sqlSource) />

Add this in to help keep individual page requests identified together.

view plain print about
1<!--- Unique ID per request --->
2<!--- keep all requests per page call together --->
3<cfset RequestID = Val(Replace(Rand(),"0.","")) />
A little detail about this... I desired a random number be generated but wanted it to be a whole number. The Rand() function creates a decimal. I am treating the Rand() produced number as string, removing the preceding "0.", and converting to a number with the Val() function. The number length varied, so multiplication would not be work.

To get the data we want we are going to query the debugging data, "queries" called above, as shown below:

view plain print about
1<cfquery dbType="query" name="cfdebug_queries" debug="false">
2    SELECT *, (endTime - startTime) AS executionTime
3    FROM queries
4    WHERE type = 'SqlQuery'
5</cfquery>

Two things of note. One is the calculation of Execution Time, really what we are after, and the type = 'SqlQuery'. For Stored Procedures, we replace this with "StoredProcedure".

view plain print about
1<cfquery dbType="query" name="cfdebug_storedproc" debug="false">
2    SELECT *, (endTime - startTime) AS executionTime
3    FROM queries
4    WHERE type = 'StoredProcedure'
5</cfquery>

Loop the query containing all the debug data for queries run on your template, then set the data in the bean using the Illudium PU-36 setters, e.g. CFLog.setBlahBlah(variablescope.blahblah). Once they are all set, then we save using the generated DAO.

view plain print about
1<cfloop query="cfdebug_queries">
2
3    <cfset CFLog.setATTUID(client.ATTUID) />
4    <cfset CFLog.setRequestID(RequestID) />
5    <cfset CFLog.setBody(cfdebug_queries.Body) />
6    <cfset CFLog.setDatasource(cfdebug_queries.Datasource) />
7    <cfset CFLog.setIPAddress(CGI.REMOTE_ADDR) />
8    <cfset CFLog.setEndTime(cfdebug_queries.EndTime) />
9    <cfset CFLog.setExecutionTime(cfdebug_queries.ExecutionTime) />
10    <cfset CFLog.setLine(cfdebug_queries.Line) />
11    <cfset CFLog.setName(cfdebug_queries.Name) />
12    <cfset CFLog.setCFRowCount(cfdebug_queries.RowCount) />
13    <cfset CFLog.setStartTime(cfdebug_queries.StartTime) />
14    <cfset CFLog.setCFScript(CGI.SCRIPT_NAME) />
15    <cfset CFLog.setURLString(CGI.QUERY_STRING) />
16    <cfset CFLog.setTemplate(cfdebug_queries.Template) />
17    <cfset CFLog.setCFTimeStamp(cfdebug_queries.TimeStamp) />
18    <cfset CFLog.setSQLTimeStamp(Now()) />
19    <cfset CFLog.setType(cfdebug_queries.Type) />
20
21    <cfset CFLogger.save(CFLog) />
22</cfloop>

You can see that we are gathering more information than what was provided from the service factory, for example, the client user id, and several CGI variables.

We do the same thing for the Stored Procedures, except for one small but important change. Note also that I do not have to create another set of bean and DAO objects, I just reuse the existing ones, CFLog and CFLogger.

view plain print about
1<cfloop query="cfdebug_storedproc">
2
3    <cfset CFLog.setATTUID(client.ATTUID) />
4    <cfset CFLog.setRequestID(RequestID) />
5    <cfset CFLog.setBody(cfdebug_storedproc.Body) />
6    <cfset CFLog.setDatasource(cfdebug_storedproc.Datasource) />
7    <cfset CFLog.setIPAddress(CGI.REMOTE_ADDR) />
8    <cfset CFLog.setEndTime(cfdebug_storedproc.EndTime) />
9    <cfset CFLog.setExecutionTime(cfdebug_storedproc.ExecutionTime) />
10    <cfset CFLog.setLine(cfdebug_storedproc.Line) />
11    <cfset CFLog.setName(cfdebug_storedproc.result[cfdebug_storedproc.currentRow][1].name) />
12    <cfset CFLog.setCFRowCount(cfdebug_storedproc.RowCount) />
13    <cfset CFLog.setStartTime(cfdebug_storedproc.StartTime) />
14    <cfset CFLog.setCFScript(CGI.SCRIPT_NAME) />
15    <cfset CFLog.setURLString(CGI.QUERY_STRING) />
16    <cfset CFLog.setTemplate(cfdebug_storedproc.Template) />
17    <cfset CFLog.setCFTimeStamp(cfdebug_storedproc.TimeStamp) />
18    <cfset CFLog.setSQLTimeStamp(Now()) />
19    <cfset CFLog.setType(cfdebug_storedproc.Type) />
20
21    <cfset CFLogger.save(CFLog) />
22</cfloop>

That change is for Name. Inexplicably, in successive Stored Procedure calls in my template, the query output name remained the same for the following Stored Procs, ignoring the actual name. I had to dig down into the result column output to get the correct name for the Stored Procedure called.

view plain print about
1<cfset CFLog.setName(cfdebug_storedproc.result[cfdebug_storedproc.currentRow][1].name) />

If you pull a <cfdump> on the query output, you'll readily see from where this came, and possibly the situation in to which I ran.

Here is the entire chunk of code for my logging template. I'll not provide the Illudium PU-36 generated bean and DAO unless there are some requests. It's easy enough to generate your own.

view plain print about
1<!--- Get the Debugger Service --->
2<cfset factory = createObject("java","coldfusion.server.ServiceFactory") />
3<cfset queries = factory.getDebuggingService().getDebugger().getData() />
4
5<!--- Set up Logging Service - call it once and reuse it --->
6<cfset CFLog = createObject("component","optimization.cfc.Optimization_Code_Log").init() />
7<cfset CFLogger = createObject("component","optimization.cfc.Optimization_Code_LogDAO").init(application.sqlSource) />
8
9<!--- Unique ID per request --->
10<cfset RequestID = Val(Replace(Rand(),"0.","")) />
11
12<!--- EVENT: SQL Queries --->
13<cftry>
14    <cfquery dbType="query" name="cfdebug_queries" debug="false">
15        SELECT *, (endTime - startTime) AS executionTime
16        FROM queries
17        WHERE type = 'SqlQuery'
18    </cfquery>
19    <cfscript>
20        if( cfdebug_queries.recordCount eq 1 and not len(trim(cfdebug_queries.executionTime)) )
21        {
22            querySetCell(cfdebug_queries, "executionTime", "0", 1);
23        }
24    
</cfscript>
25    <cfcatch type="Any">
26        <cfscript>
27            cfdebug_queries = queryNew('ATTRIBUTES, BODY, CACHEDQUERY, CATEGORY, DATASOURCE, ENDTIME, EXECUTIONTIME, LINE, MESSAGE, NAME, PARENT, PRIORITY, RESULT, ROWCOUNT, STACKTRACE, STARTTIME, TEMPLATE, TIMESTAMP, TYPE, URL, et');
28        
</cfscript>
29    </cfcatch>
30</cftry>
31
32<!--- EVENT: Stored Procedures --->
33<cftry>
34    <cfquery dbType="query" name="cfdebug_storedproc" debug="false">
35        SELECT *, (endTime - startTime) AS executionTime
36        FROM queries
37        WHERE type = 'StoredProcedure'
38    </cfquery>
39    <cfscript>
40        if( cfdebug_storedproc.recordCount eq 1 and not len(trim(cfdebug_storedproc.executionTime)) )
41        {
42            querySetCell(cfdebug_storedproc, "executionTime", "0", 1);
43        }
44    
</cfscript>
45    <cfcatch type="Any">
46        <cfscript>
47            cfdebug_storedproc = queryNew('ATTRIBUTES, BODY, CACHEDQUERY, CATEGORY, DATASOURCE, ENDTIME, EXECUTIONTIME, LINE, MESSAGE, NAME, PARENT, PRIORITY, RESULT, ROWCOUNT, STACKTRACE, STARTTIME, TEMPLATE, TIMESTAMP, TYPE, URL');
48        
</cfscript>
49    </cfcatch>
50</cftry>
51
52<!--- SQL Queries Logging --->
53<cftry>
54    <cfloop query="cfdebug_queries">
55
56        <cfset CFLog.setATTUID(client.ATTUID) />
57        <cfset CFLog.setRequestID(RequestID) />
58        <cfset CFLog.setBody(cfdebug_queries.Body) />
59        <cfset CFLog.setDatasource(cfdebug_queries.Datasource) />
60        <cfset CFLog.setIPAddress(CGI.REMOTE_ADDR) />
61        <cfset CFLog.setEndTime(cfdebug_queries.EndTime) />
62        <cfset CFLog.setExecutionTime(cfdebug_queries.ExecutionTime) />
63        <cfset CFLog.setLine(cfdebug_queries.Line) />
64        <cfset CFLog.setName(cfdebug_queries.Name) />
65        <cfset CFLog.setCFRowCount(cfdebug_queries.RowCount) />
66        <cfset CFLog.setStartTime(cfdebug_queries.StartTime) />
67        <cfset CFLog.setCFScript(CGI.SCRIPT_NAME) />
68        <cfset CFLog.setURLString(CGI.QUERY_STRING) />
69        <cfset CFLog.setTemplate(cfdebug_queries.Template) />
70        <cfset CFLog.setCFTimeStamp(cfdebug_queries.TimeStamp) />
71        <cfset CFLog.setSQLTimeStamp(Now()) />
72        <cfset CFLog.setType(cfdebug_queries.Type) />
73
74        <cfset CFLogger.save(CFLog) />
75    </cfloop>
76    <cfcatch type="Any">
77        <!--- Error reporting query event --->
78    </cfcatch>
79</cftry>
80
81<!--- Stored Procedures Logging --->
82<cftry>
83    <cfloop query="cfdebug_storedproc">
84
85        <cfset CFLog.setATTUID(client.ATTUID) />
86        <cfset CFLog.setRequestID(RequestID) />
87        <cfset CFLog.setBody(cfdebug_storedproc.Body) />
88        <cfset CFLog.setDatasource(cfdebug_storedproc.Datasource) />
89        <cfset CFLog.setIPAddress(CGI.REMOTE_ADDR) />
90        <cfset CFLog.setEndTime(cfdebug_storedproc.EndTime) />
91        <cfset CFLog.setExecutionTime(cfdebug_storedproc.ExecutionTime) />
92        <cfset CFLog.setLine(cfdebug_storedproc.Line) />
93        <cfset CFLog.setName(cfdebug_storedproc.result[cfdebug_storedproc.currentRow][1].name) />
94        <cfset CFLog.setCFRowCount(cfdebug_storedproc.RowCount) />
95        <cfset CFLog.setStartTime(cfdebug_storedproc.StartTime) />
96        <cfset CFLog.setCFScript(CGI.SCRIPT_NAME) />
97        <cfset CFLog.setURLString(CGI.QUERY_STRING) />
98        <cfset CFLog.setTemplate(cfdebug_storedproc.Template) />
99        <cfset CFLog.setCFTimeStamp(cfdebug_storedproc.TimeStamp) />
100        <cfset CFLog.setSQLTimeStamp(Now()) />
101        <cfset CFLog.setType(cfdebug_storedproc.Type) />
102
103        <cfset CFLogger.save(CFLog) />
104    </cfloop>
105    <cfcatch type="Any">
106        <!--- Error reporting query event --->
107    </cfcatch>
108</cftry>

More Entries

BlogCFC was created by Raymond Camden. This blog is running version 5.9.7. Contact Blog Owner