dagrek

I have a general section which dynamically displays reports, which are preconfigured in a certain way.  There are some totals and data rows which are not part of the metrics so net result is I had some CF code looking at the column lists and displaying appropriately.  Turns out there are very different results between Lucee and Adobe Coldfusion, for this example, it's important to create a sample table like so:  CREATE TABLE testtable( [Col1] [varchar](50) NULL, [ACol2] [varchar](50) NULL ) so that ACol2 is actually alphabetically before Col1.  Then run this <cfquery datasource="aTestDSN" name="qry"> select * from testtable </cfquery> <cfset columns = ""> <cfloop array="#getmetadata(qry)#" item="aryItem"> <cfset columns = listappend(columns, aryItem.Name)> </cfloop> <table> <tr><td>getmetadata</td><td><cfoutput>#columns#</cfoutput></td></tr...

If there's one thing I don't like it is people confusing a date with a timestamp, and how a lazy bit of development can ruin a load of data. Here is a simple query inserting data to a timestamp I found in a system where all the created dates were truncated because of the incorrect syntax. Bad one which was in use: insert into testdate values (<cfqueryparam cfsqltype="cf_sql_date" value="#createodbcdatetime(now())#">); Result :  2015-09-29 00:00:00.000 Best One (easiest to read) insert into testdate values(getdate() ); Result : 2015-09-29 10:10:09.880 Not great (no binding) insert into testdate values(#createodbcdatetime(now())#); Result :  2015-09-29 10:14:44.000 What the bad one should have been insert into testdate values(<cfqueryparam cfsqltype=" cf_sql_timestamp " value="#createodbcdatetime(now())#">)

As part of a PCI Compliance audit, I recently ran a scan on a database using software call Card Recon. A little odd thing occurred. At a point in the past, a row of data in a column, which had been dropped from the database schema, contained a single test credit card number. However, the Card Recon software showed that the data was still there in the database file (this was a SQL Database) in the form of a SQL Ghost Record. A Ghost Record can appear when running a delete or insert command and when running delete and insert in different queries but related by the same indexed data, you can read all about it over at Ghost "Rows" Buster in  SQL Server on Technet. It's basically a record somewhere in the database file, but not directly in a database table and is living in a bit of spare fragmented space somewhere and this needs to be cleaned up. Following this procedure managed to remove the Ghost Record: Convert the database to Simple (only so the transactio...

The ColdFusion calls to external systems will just hang around FOREVER waiting for a reply, until the server just gives up! This is because like any good application software, it can't really be sure that it "should" be able to terminate any connection it is waiting for in case it is waiting for something really important (maybe a booking receipt, or credit card payment receipt!!). Clearly some will be outside your control, but when a dodgy SQL query has gone ape, you can always kill that on the database and release the ColdFusion thread and everything will return to normal. Find the session in SQL like so:  select sq.text,r.session_id,r.status,r.command,r.cpu_time,r.total_elapsed_time from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(sql_handle) AS sq Then issue the KILL command. KILL 59 -- where 59 is the value from session_id.

While testing an application against Adobe ColdFusion vs Railo 4.2.1 ; everything went quite well except for a simple piece of inline SQL for an a legacy appication with a bit HashBytes encryption. Nothing too fancy there, just comparing Hashed String with an inputted string, like so:  .... where hashedkey =HashBytes('SHA1', <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.unhashedkey#">) Except... The input of HashBytes is a binary. Adobe CF, created the hashedkey (elsewhere) with a cfqueryparam type of  cf_sql_varchar but not cast/ converted as a binary. There was no cf_sql_nvarchar which was added in CF10,   Railo came back with a different results here running this code on each environment: <cfquery name="qryInteresting" datasource="datasource"> select hashbytes('SHA1', 'poodle') nocfqueryparam , hashbytes('SHA1', cast('poodle' as varchar(50) ) ) nocfqueryparamC...

While http://www.red-gate.com/ provides some good search functionality (for FREE) for looking through the objects for reference code, I used to use the " FindMyData_String " procedure below (I would give a reference and amend the post if I could find where it came from). This was a blunt tool, which I never really changed or modified and only pointed me in the right direction to where the data might be. Now I use http://www.apexsql.com/sql_tools_search.aspx ; this is FREE and awesome; you can select the database, tables, type of columns, dateformate etc etc, and it is very fast. just download it and check it out on your dev machine. It will tell you the column and the value so you can see exactly what you are looking for. Blunt Tool for searching all tables ALTER PROCEDURE [dbo].[FindMyData_String] @DataToFind NVARCHAR(4000), @ExactMatch BIT = 0 AS SET NOCOUNT ON DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysNa...

Oracle's package syntax is a nice way of packaging sets of functions/ procedures etc, however when a package is updated, that brings everything down. If you have a cached JDBC connection, that causes even more problems as you have to recycle the connections. On SQL, there is no package, and thus you could end up with hundreds if not thousands of little procedures and functions. A good trick is to use schemas to organize them: this example will return 2 queries; could also use input output parameters create schema [myutils]; create procedure [ myutils ].[aFewQueries] @someid int output ,@someid2 int output as begin --  set nocount on; added to prevent extra result sets from interfering with SELECT statements. set nocount on; -- Insert statements for procedure here select * from table1 where id = @someid; select * from  table2_other where  id = @someid ;  select  @someid2  = 5; end go In ColdFusion you could neatly call this ...