NielsLiisberg’s gists

NielsLiisberg / ifs_write_blob.sql

Created January 7, 2025 14:59

SQL IFS_WRITE_BLOB function

	-- Write a BLOB to IFS function
	-- Note1: This also works for save files
	-- Note2: This also compiles on vanilla systems without QSYSINCL installed
	-- Note3: I am using library QUSRSYS. I suggest you put it into your own tool library
	-- Simply paste this gist into ACS SQL and step through the example.
	--
	--
	-- It is a cool example how far you can go with SQL: Have fun -
	-- (C) Niels Liisberg 2025
	--

NielsLiisberg / ifs_path.sql

Last active November 22, 2024 11:59

SQL return path or file name or extension or file with extension for a full path

	-- Returns the path, filename, extension or file with extension
	-- section of a IFS filename
	--
	-- Simply paste this gist into ACS SQL and step through the example.
	--
	-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
	--
	-- It is a cool example how far you can go with SQL: Have fun -
	-- (C) Niels Liisberg 2024
	--

NielsLiisberg / setenvvar.sql

Last active January 24, 2024 12:55

SQL: set environment variable

	-- Set environment variable from a string value
	-- you need library QSYSINC installed:
	-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/apiref/conQSYSINC.htm
	--
	-- Simply paste this gist into ACS SQL and step through the example.
	--
	-- It is a cool example how far you can go with SQL: Have fun -
	-- (C) Niels Liisberg 2023
	--
	-- This gist is distributed on an "as is" basis, without warranties

NielsLiisberg / copy_source_to_ifs.sql

Last active December 11, 2023 11:03

SQL - Export source files members to IFS and preserving member text

	-- Copy members from a source physical file to IFS directory and preserving the member text as part of the filename.
	--
	-- Notes:
	-- 1) I am using library QUSRSYS. I suggest you put it into your own tool library
	-- 2) The output direcory has to exists.
	-- 3) Files contents are being replaces if they already exists.
	-- 4) IFS files CCSID is set to 1208 that is UTF-8
	--
	-- Simply paste this gist into ACS SQL and step through the example.
	--

NielsLiisberg / uuid.sql

Created November 20, 2023 11:13

SQL generate GUID / UUID

	-- Use MI to generarte a RFC 4122 compiant UUID / GUID
	--
	-- This also showcase how to integrate the MI api using
	-- C code directly into your UDF
	--
	-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
	--
	-- Simply paste this gist into ACS SQL and step through the example.
	--
	-- It is a cool example how far you can go with SQL: Have fun -

NielsLiisberg / ifs_access.sql

Last active October 24, 2023 10:58

SQL - Check access to an IFS file or folder

	-- SQL Scalar function to check access to an IFS file or folder

	-- It returns:
	-- 0=If the file or folder Exists and you have access
	-- -1=No access or file does not exists
	--
	-- This is a wrapper for the access() unix API:
	-- https://pubs.opengroup.org/onlinepubs/009695299/functions/access.html

	-- Note second parameter is the acces mode, that defaults to F_OK

NielsLiisberg / ifs_delete_file.sql

Created October 11, 2023 12:05

SQL - Delete (unlink) a IFS file

	-- SQL Scalar function to delete ( unlink) a IFS files
	-- this is a wrapper for unlink (delete IFS files) system / unix API

	-- Simply paste this gist into ACS SQL and run it to create the UDTF.

	-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library

	-- It is a cool example how far you can go with SQL: Have fun -
	-- (C) Niels Liisberg 2023

NielsLiisberg / capitalize.sql

Last active September 1, 2023 09:18

UDTF to return a capitalize first letter in each word of a string


	-- UDTF to return a capitalize first letter in each word of a string

	-- Simply paste this gist into ACS SQL and run it to create the UDTF.

	-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library

	-- It is a cool example how far you can go with SQL: Have fun -
	-- (C) Niels Liisberg 2023

NielsLiisberg / dspdbr

Last active August 16, 2023 14:53

List database relations with keys for files/tables

	-- Returns database relations with keys
	-- for SQL tables, the name is convert to the physical filename first.

	-- Simply paste this gist into ACS SQL and run it to create the UDTF.

	-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library

	-- It is a cool example how far you can go with SQL: Have fun -
	-- (C) Niels Liisberg 2023

NielsLiisberg / to_num.sql

Last active January 1, 2024 10:07

TO_NUM is the TO_NUMBER in a lax and european version

	-- Convert text to decimal number for europeans where decimal point is a comma
	--
	-- This also showcase how to integrate the C code directly into your UDTF
	--
	-- Simply paste this gist into ACS SQL and step through the example.
	--
	-- Note: I am using the library QUSRSYS. I suggest you put it into your own tool library
	--
	-- It is a cool example how far you can go with SQL: Have fun 😀
	-- (C) Niels Liisberg 2022

Niels Liisberg NielsLiisberg