Thursday, August 15, 2019

ColdFusion: Pretty Printing JSON with Google's gson

Here's a simple example on how to pretty print JSON with ColdFusion.

Install or load Google's gson.jar file by building it from the git repo https://github.com/google/gson or download the latest jar 



<cfscript>
variables.myJSON = {"ugly":"put in some ugly/compressed json here"};
variables.objJSON = CreateObject("java","com.google.gson.GsonBuilder").setPrettyPrinting().create();
variables.objParser= CreateObject("java","com.google.gson.JsonParser");
variables.jsonElement =  variables.objParser.parse(variables.myJSON);
variables.result = variables.objJSON.toJson(variables.jsonElement);
WriteOutput("<textarea cols='80' rows='20'>#variables.result#</textarea>");
</cfscript>

Monday, August 5, 2019

Oracle: Obtaining a date for a given business day in a month.

Oracle SQL to get the date for a given business day in a passed in month, sysdate used for example.

With a as(
SELECT
  trunc(sysdate,'MM')- 1 + level AS day
 ,to_number(to_char(trunc(sysdate,'MM')- 1 + level,'D'))  as weekday
 ,rownum as business_day
FROM
  dual
  where to_char(trunc(sysdate,'MM')- 1 + level,'D') not in (1,7)
  -- add in a check for a holday calendar here
CONNECT BY LEVEL between 1 and EXTRACT(day from last_day(sysdate)) 
)
select a.day  from a where business_day = 5

Wednesday, July 31, 2019

PERL: Utility to sort and remove duplicates from a csv file.

Here is a little PERL Script that will take a file and sort the data lines and remove duplicates.


#!/usr/bin/perl

$ifile=$ARGV[0];
$ofile=$ARGV[1];

$header=`sed -n '1p' $ifile` ;
$data=`sed '1d' $ifile | sort -u` ;
open(my $fh, '>', $ofile) or die "Could not open file '$ofile' $!";
print $fh $header;
print $fh $data;

close $fh;
exit 0

Tuesday, May 21, 2019

RegEx: Searching for CFQUERY tags that are not Query of Queries

I recently had to search several thousand files for ColdFusion <CFQUERY> tags that were hitting a database and not the dbtype="query". The following regex pattern in Sublime will do the trick

^(?!.*dbtype.*)(<cfquery .*>)

Monday, April 22, 2019

Oracle: Looking at the cursor metadata

Based on information provided at https://boobalganesan.blogspot.com/2017/08/dbmssqldescribecolumns-tips.html here are a few steps for dumping out cursor meta data returned from a stored proc.

first, create a global temp table


 CREATE GLOBAL TEMPORARY TABLE desc_col_test
    (
    col_id NUMBER(10),
    col_desc VARCHAR2(255)
);

then run this block

declare
    
   O_RES SYS_REFCURSOR;

    v_cursor_number number;
    v_columns number;
    v_desc_tab dbms_sql.desc_tab;

begin
  
delete from desc_col_test;

 --Adust to your variables accordingly 
YOUR_SCHEMA.YOUR_PKG_YOUR_PROD(p_dt => TO_DATE ('31-12-2018 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), o_res => O_RES);

v_cursor_number := dbms_sql.to_cursor_number(rc => O_RES);
    --Get information on the columns
    dbms_sql.describe_columns(v_cursor_number, v_columns, v_desc_tab);
--Loop through all the columns, find COLUMN1 position and type
    for i in 1 .. v_desc_tab.count loop
      
INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_name:'||decode(v_desc_tab(i).col_name, Null, 'Null', v_desc_tab(i).col_name));

INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_type: ' ||
 case
                 when v_desc_tab(i).col_type = 1 then 'VARCHAR2'
                 when v_desc_tab(i).col_type = 2 then 'NUMBER'
                 when v_desc_tab(i).col_type = 8 then 'LONG'
                 when v_desc_tab(i).col_type =  9 then 'VARCHAR'
                 when v_desc_tab(i).col_type =  12 then 'DATE'
                 when v_desc_tab(i).col_type =  23 then 'RAW'
                 when v_desc_tab(i).col_type =  69 then 'ROWID'
                 when v_desc_tab(i).col_type =  96 then 'CHAR'
                 when v_desc_tab(i).col_type =  100 then 'BINARY_FLOAT'
                 when v_desc_tab(i).col_type =  101 then 'BINARY_DOUBLE'
                 when v_desc_tab(i).col_type =  105 then 'MLSLABEL'
                 when v_desc_tab(i).col_type =  106 then 'MLSLABEL'
                 when v_desc_tab(i).col_type =  112 then  'CLOB'
                 when v_desc_tab(i).col_type =  113 then 'BLOB'
                 when v_desc_tab(i).col_type =  114 then 'BFILE'
                 when v_desc_tab(i).col_type =  115 then 'CFILE'
                 when v_desc_tab(i).col_type =  178 then 'TIME'
                 when v_desc_tab(i).col_type =  179 then 'TIME WITH TIME ZONE'
                 when v_desc_tab(i).col_type =  180 then 'TIMESTAMP'
                 when v_desc_tab(i).col_type =  181 then 'TIMESTAMP WITH TIME ZONE'
                 when v_desc_tab(i).col_type =  231 then 'TIMESTAMP WITH LOCAL TIME ZONE'
                 when v_desc_tab(i).col_type =  182 then 'INTERVAL YEAR TO MONTH'
                 when v_desc_tab(i).col_type =  183 then 'INTERVAL DAY TO SECOND'
                 else 'OTHER' end);


 INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_precision:'||decode(v_desc_tab(i).col_precision, Null, 'Null', v_desc_tab(i).col_precision));

 INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_max_len:'||decode(v_desc_tab(i).col_max_len, Null, 'Null', v_desc_tab(i).col_max_len));

 INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_name_len:'||decode(v_desc_tab(i).col_name_len, Null, 'Null', v_desc_tab(i).col_name_len));

 INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_schema_name:'||decode(v_desc_tab(i).col_schema_name, Null, 'Null', v_desc_tab(i).col_schema_name));

 INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_schema_name_len:'||decode(v_desc_tab(i).col_schema_name_len, Null, 'Null', v_desc_tab(i).col_schema_name_len));

 INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_scale:'||decode(v_desc_tab(i).col_scale, Null, 'Null', v_desc_tab(i).col_scale));

 INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_charsetid:'||decode(v_desc_tab(i).col_charsetid, Null, 'Null', v_desc_tab(i).col_charsetid));

 INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_charsetform:'||decode(v_desc_tab(i).col_charsetform, Null, 'Null', v_desc_tab(i).col_charsetform));

    end loop;

 
end;
/

SELECT *
FROM
  (SELECT col_id,
    regexp_substr(col_desc,'[^:]+',1,1) col_desc_name,
    regexp_substr(col_desc,'[^:]+',1,2) col_desc_value
  FROM desc_col_test
  ) pivot (MAX(col_desc_value) FOR col_desc_name IN ('col_name' AS COL_NAME, 'col_type' AS COL_TYPE, 'col_precision' AS COL_PRECISION,
  'col_max_len' AS COL_MAX_LEN, 'col_name_len' AS COL_NAME_LEN,
  'col_schema_name' AS COL_SCHEMA_NAME, 'col_schema_name_len' AS COL_SCHEMA_NAME_LEN,
  'col_scale' AS COL_SCALE, 'col_charsetid' AS COL_CHARSETID,
  'col_charsetform' AS COL_CHARSETFORM, 'Nullable' AS NULLABLE))
ORDER BY col_id;

Friday, April 5, 2019

How to Make an Ugly Excel Document: Otherwise known as Unit Testing POIUtility 4.0 (Apache POI 4.0.1) compatible


I've been working on a revamp of Ben Nadel's excellent but aging POIUtility for the generation of Excel files.   I've been needing XLSX support so I updated the code to use Apache POI 4.0.1 and embedded Mark Mandel's JavaLoader and the needed packages for it to work standalone without java lib dependencies on the server.

Here's a summary of what I've done so far;
  • Uses Apache POI 4.0.1 with JavaLoader
  • Supports creation of both XLS and XLSX
  • RowSpan support.  Either by itself or with colSpan to create a range (see ugly image below)
  • poi:row update attribute when working with existing templates.
  • refactored the CSSRule.cfc  into cfscript, 
TODO: Work on updating the POIUtility.cfc now that the groundwork is complete.

I did yank the coldfusion_mx_7 folder, figured it's about time for people to move ahead on that one.

Tested on CommandBox with ColdFusion 2018

Go to my repo or the code https://github.com/wiggick/POIUtility.cfc

Side by side results of XLS and XLST Generation
Color, fill pattern, and region test.  (note text colors are just random selection )


Friday, March 29, 2019

Crystal Report Tricks: Using special fields in formulas

When you want to use a special field in Crystal Reports in a formula, you can specify the special field  by using the special field name without spaces.  I admit I haven't tried them all, but in this case, the comments are multi-line and I wanted to grab just the first line.

split(ReportComments,chr(13))[1]