danielleevandenbosch

'code author: Daniel Van Den Bosch
'copyright 2017
'CC-BY
'paste this code into a new module and replace all MsgBox code with Lmsgbox
'this will log all the message boxes to a text file on hte users c drive under 
'C:\db_objects\error_logs\ with the application name.
'this will allow you to support the application should the end user run into issues.
'used with good error handling, it will allow you to see the error messages the end user gets.
'tested and works in ms-access

danielleevandenbosch

CREATE OR REPLACE FUNCTION  dateadd(_timelabel VARCHAR(25),_timevalue NUMERIC,_timegiven TIMESTAMP)
RETURNS TIMESTAMP AS
$BODY$
DECLARE returnVal TIMESTAMP;
/*
-- =============================================
-- Author     : Daniel Van Den Bosch
-- Create date: 8/20/2018
-- Description: Use dateadd (a ms sql server function) in postgres
-- How To use : Your are going to have to add quotes to the _timelabel AND it is a good idea to cast :: TO TIMESTAMP _timegiven::TIMESTAMP

danielleevandenbosch

-- change :year to desired year. :variable is a php PDO binding thing
SELECT to_char(date_trunc('week', dd)::date - '1 day'::INTERVAL,'mm/dd/yy') AS sunday
     , to_char(date_trunc('week', dd)::date + '0 day'::INTERVAL,'mm/dd/yy') AS monday
     , to_char(date_trunc('week', dd)::date + '1 day'::INTERVAL,'mm/dd/yy') AS tuesday
     , to_char(date_trunc('week', dd)::date + '2 day'::INTERVAL,'mm/dd/yy') AS wednesday
     , to_char(date_trunc('week', dd)::date + '3 day'::INTERVAL,'mm/dd/yy') AS thursday
     , to_char(date_trunc('week', dd)::date + '4 day'::INTERVAL,'mm/dd/yy') AS friday
     , to_char(date_trunc('week', dd)::date + '5 day'::INTERVAL,'mm/dd/yy') AS saturday
FROM generate_series
				  (( '1/1/' ||  :year)::timestamp

danielleevandenbosch

  /*
  Legal:
  This GIST is provided without any sort of warranty and is to be used by the user at their own risk.
  CC-BY 2019
  Daniel Lee Van Den Bosch - SQL Developer Stone Plastics Zeeland, Michigan 49464 United States
  Description:
  This SQL is used to create standard audit fields in an existing table.
  to use it, run the SQL below and then run the following function
  SELECT it.audit_fields('my_schema.my_table');
  NOTE: if you already have a column created_at, created_by, updated_at, updated_by you may need to run this in pieces.

danielleevandenbosch

CREATE SCHEMA it;
CREATE OR REPLACE  FUNCTION it.random_between(low INT ,high INT)
   RETURNS INT AS
$$
BEGIN
   RETURN floor(random()* (high-low + 1) + low);
END;

$$ language 'plpgsql' STRICT;
CREATE FUNCTION it.dice_roles (number_of_roles INT default 1) RETURNS TABLE (a int, b int, roll int) LANGUAGE sql AS

danielleevandenbosch

CREATE OR REPLACE FUNCTION ltree_swap (_ltree ltree, _old_val LTREE, _new_val LTREE,  _position INT) RETURNS LTREE LANGUAGE plpgsql AS
$$
/*
==================================================
Author:     dvandenbosch
Created At: 11/14/2019
Use: SELECT '44.22.23'::TEXT::LTREE AS orginal, ltree_swap('44.22.23'::TEXT::LTREE,'23'::TEXT::LTREE,'fish'::TEXT::LTREE,2) AS swap
Note: _position parameter is zero based
==================================================
*/

danielleevandenbosch

Create screen session

screen -S dans_screen

--do your stuff 

Detatch (not kill session) 

ctrl + a (letgo of ctrl + a ) + d

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

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

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')