Showing posts from 2020

Switch database on PGSQL

To switch to another database when connect to PostgreSQL Server use following command on psql.
postgres=>\connectdatabasenamepostgres=> \c databasenameFor eg: Lets say you are connected to default postgres database and want to connect to database name mydatabase, try below syntax
postgres=> \c mydatabase or postgres=> \connect mydatabasemydatabase=>

Commonly use oracle queries and commands

To View all databases 
Shutdown database SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. Start the instance SQL> startup;ORACLE instance started. Total System Global Area 4966053832 bytes Fixed Size 8906696 bytes Variable Size 922746880 bytes Database Buffers 4026531840 bytes Redo Buffers 7868416 bytes Database mountedOpen the databaseSQL> alter database open;SQL> select instance_name, status from v$instance;INSTANCE_NAME STATUS
---------------- ------------
SQL> alter database open;
Database altered.
SQL> select instance_name, status from v$instance;
---------------- ------------
orcl            OPEN

MariaDB delete duplicate using ROW_NUMBER()

Please note : Window Functions are available in MariaDB 10.2 onwards
In our previous article, we talked about deleting duplicate data in MySQL using join. In this article we are going to demo on how to delete duplicate using row_number(). Since we already have our demo table created with dummy data, going ahead directly with the query
Step 1: Find duplicate using row_number()
SELECT id FROM (SELECT id, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) AS row_num FROM demo) d WHERE row_num > 1; +------+ | id | +------+ | 7 | | 3 | +------+ 2 rows in set (0.000 sec)
Step 2: Select * from demo  select * from demo; +------+--------+ | id   | name   | +------+--------+ |    1 | abc    | |    2 | abd    | |    3 | vijay  | |    4 | vijay  | |    5 | ajay   | |    6 | sanjay | |    7 | vijay  | +------+--------+ 7 rows in set (0.000 sec)
Step 3: Delete duplicateDELETE FROM demo WHERE id IN (SELECT id FROM (SELECT id,ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) AS row_num FROM demo) d  WHE…

Powershell to find Text in PDF and Word

In continuation to my previous article on PowerShell to find hyperlink texts in ppt, here I am going to show how to find hyperlinks in Word or PDF files. Sample code below, tweak it for your purpose and drop me a comment if you need help.

$FilePath= "C:\Users\xxx\"  $OurDocuments = Get-ChildItem -Path "$FilePath" -Filter "*.pdf" -Recurse #change to .doc* for word
$Word = New-Object -ComObject word.application $Word.Visible = $false $i = 0
$OurDocuments | ForEach-Object { try {     $Document = $Word.Documents.Open($_.FullName,$false,$true)      #"Processing file: {0}" -f $Document.FullName     try{     $Document.Hyperlinks | ForEach-Object {         if ($_.Address -like "*" -or $_.Text -like "*")          {                 "Found issues {0} `r`n" -f $Document.Fullname                  "Found issues {0} `r`n" -f $_.Address                 "Found issues {0} `r`n" -f $_.Hyperlinks …

PowerShell to find HyperLink Text in PowerPoint

Recently I faced a scenario where I needed to find if a particular url is used in hyperlink in 100s of ppt. Opening ppt and looking for the ppt is cumbersome,  so wrote a small PowerShell to find the url. Here is the PowerShell Script code
Click here to view article on powershell to find hyerlink text in word or pdf

$FilePath= "C:\Users\xxx\Path" Add-Type -AssemblyName Office $ppt = New-Object -ComObject powerpoint.Application $DocumentsLib = Get-ChildItem -Path "$FilePath" -Filter "*.ppt*" -Recurse $Doc = New-Object -ComObject powerpoint.Application
$i=0 $DocumentsLib | ForEach-Object {
Write-Host $_.FullName $Doc = $ppt.Presentations.Open($_.FullName,$Null,$Null,[Microsoft.Office.Core.MsoTriState]::msoFalse) $Slides = $Doc.Slides Foreach ($Slide in $Slides) {     $Slide.Hyperlinks | ForEach-Object {         if ($_.Address -like "*texttofind*" -or $_.Text -like "texttofind*")          {            write-host $_.Address            write-host "Foun…

Drop Database in Oracle

Steps to drop a container database in oracle
Step 1 : Connect to the database as sysdba bash#$ sqlplus / as sysdba
Step 2 : Shutdown the database SQL> shutdown immediate;
Step 3 : Start mount the database with exclusive restrict to avoid the database being opened SQL> startup mount exclusive restrict;
Step 4 : Drop database SQL> drop database;

Getting Started with MariaDB

In our previous post, we demonstrated steps to install MariaDB Server on Ubuntu, in this article, we are going to show how to create database in MySQL/MariaDB. We are going to talk about DDL(Data Definition Language), how to create tables and authorize users to access them.

Start your mariadb-server and follow the steps

Creating Databases, Tables, and Authorized UsersStep 1: Connect to your mariadb-server and run below sql. This can be done via mysql client or any other tools like Heidisql, phpmyadmin etc. This is going to create a new database in your server.  CREATE DATABASE myFirstDB; Check if the database already exists and create
CREATE DATABASE IF NOT EXISTS myFirstDB; To list all the databases in a server.
SHOW DATABASES; To connect to your database
USE DATABASE_NAME; Once you have selected your database, use following to create user
CREATE USER 'username'@'localhost' IDENTIFIED BY 'user_password'; To create a user that can connect from any host, use the …