danielleevandenbosch

OS: RaspberryPI Lite (But I think something like Ubuntu server would work as well) users dvandenbosch (sudoer) gui (non sudoer)

install dotfiles (This is for Dan Van Den Bosch own sanity) (Not required) https://github.com/danielleevandenbosch/dotfiles

Setup Autologin

create this file as root /etc/systemd/system/getty@tty1.service.d/autologin.conf

danielleevandenbosch

sudo apt update
sudo apt install software-properties-common
sudo apt-add-repository 'deb http://ftp.de.debian.org/debian sid main' #https://packages.debian.org/sid/amd64/openjdk-8-jdk/download -- you can change to another mirror here
sudo apt update --allow-unauthenticated
sudo apt install openjdk-8-jdk 
java -version
echo "export JAVA_HOME=$(dirname $(dirname $(readlink -f $(which javac))))" | sudo tee -a /etc/profile
source /etc/profile
echo $JAVA_HOME

danielleevandenbosch

-- Query for X$Attrib table
SELECT
    my_Attrib.Xa$Id    AS my_Id
,   my_Attrib.Xa$Type  AS my_Type
,   my_Attrib.Xa$ASize AS my_ASize
,   my_Attrib.Xa$Attrs AS my_Attrs
FROM X$Attrib AS my_Attrib;

-- Query for X$Field table
SELECT

danielleevandenbosch

CREATE OR REPLACE FUNCTION $funcname$ () RETURNS VOID LANGUAGE plpgsql VOLATILE AS 
$$$
/*
==================================================
Author:     $user$
Created At: $date$
Where Used: $var$
==================================================
*/
DECLARE

danielleevandenbosch

DO
$$
DECLARE
  value int := 0;
  sql text := 'The constructed select statement';
  rec1 record;
  rec2 record;
BEGIN
  FOR rec1 IN
        SELECT

danielleevandenbosch

    --table search
SELECT id '#'
      ,owner     AS schema_name
      ,c.tbl     AS table_name
      ,col       AS column_name
      ,nullflag  AS nullflag
FROM sysprogress.syscolumns_full AS c
WHERE 1=1
AND c.tbl LIKE '%%'
AND c.col LIKE '%order%'

danielleevandenbosch

CREATE or replace FUNCTION trunc_on_nth_occurrence_of_string(
                                                   _original_string text
                                                 , _search_string   TEXT
                                                 , _nth_occurrence  int
                                                  ) RETURNS TEXT LANGUAGE sql AS $$
    /*
    ==================================================
    Author:     dvandenbosch
    Created At: 7/9/2020
    Where Used: https://stackoverflow.com/questions/62801797/find-nth-position-of-a-specific-character-in-a-string-in-postgres/62802753#62802753

danielleevandenbosch

create or replace function it.function_get_create(_schema_dot_table text, _single_pkey_column_name text DEFAULT 'id'::text, _single_pkey_column_type text DEFAULT 'INTEGER'::text) returns void
	language plpgsql
as $$
    /*
  ***************************************************************************************************
  Create Date:          2019-03-25
  Author:               Dan Van Den Bosch
  Used By:              SQL consoles.
  Why:                  This is to be ran by SQL developers on tables to create functions that get 1 row
  Usage: SELECT it.function_get_create('myschema.mytable')

danielleevandenbosch

'Written by Ryan Ross, Mark Mason, and Dan Van Den Bosch for PC-DMIS basic scripting language 
'to run this demo you will need to run the following in your prefered sql editor
'`create schema it; create table it.animals (animal TEXT);`
'change the connection string to suite your db, server, username, and password
Sub Main()
        Dim conn As Object 
        Set conn = Nothing
        SET conn = CreateObject("ADODB.Connection") 'late binding
      conn.Open "Driver={PostgreSQL UNICODE};database=MY_DB;server=my_server;port=5432;uid=postgres;sslmode=disable;readonly=0;protocol=7.4;User ID=my_account;password=my_password;"
        conn.Execute "INSERT INTO it.animals (animal) VALUES ('piggy')" '(CommandText)

danielleevandenbosch

using System;
using ADODB; //you wil need to go to the references and add ado
namespace ST_a3f1080b1dda4f098e33c979fc1015cf
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        #region vdbc#
        public const string postgresConnStringTest = "DRIVER={PostgreSQL ANSI};DATABASE=ERP_TEST;SERVER=10.1.202.88;UID=pgservice;PWD=my_pa$$w0RD";  // test
        public const string postgresConnString     = "PostgreSQL30";                                                                                     // live