Thursday, March 5, 2020

Why Lucee is Better Than ColdFusion #1

Lucee's Query of Queries is better than Adobe.  Try this little snippet on Lucee 5 and then CF2018 or earlier

       myQuery=QueryNew("Country,Capital City,Amount##","varchar,varchar,numeric");
       /*Add array values in column*/

Sunday, February 9, 2020

XSLT with carriage returns and line breaks

I needed to translate carriage returns and line feeds to <BR/> elements in an XSLT process.  Sometimes you get hung up how you normally use functions and then hit a language wall that you think should work,

In this case, the solution was to iterate over the selection and output the elements with an appended tag.

<tr><td><xsl:for-each select="tokenize(customerinfo/@comments, '&#xD;&#xA;')">
                <xsl:value-of select="." /><br/>

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 or download the latest jar 

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

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(
  trunc(sysdate,'MM')- 1 + level AS day
 ,to_number(to_char(trunc(sysdate,'MM')- 1 + level,'D'))  as weekday
 ,rownum as business_day
  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  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.



$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 here are a few steps for dumping out cursor meta data returned from a stored proc.

first, create a global temp table

    col_id NUMBER(10),
    col_desc VARCHAR2(255)

then run this block


    v_cursor_number number;
    v_columns number;
    v_desc_tab dbms_sql.desc_tab;

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: ' ||
                 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;


  (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;