Category Archives: Tips

26/Oct/2020

Listing relationship constraints in SQL Server

By Gabriel
DECLARE @tableName VARCHAR(64);
SET @tableName = 'MY_TABLE_NAME';
SELECT
SO_P.name AS parent_table
, SC_P.name AS parent_column
, 'is a foreign key of' AS direction
, SO_R.name AS referenced_table
, SC_R.name AS referenced_column
, *
FROM
sys.foreign_key_columns FKC
INNER JOIN sys.objects SO_P ON SO_P.object_id = FKC.parent_object_id
INNER JOIN sys.columns SC_P ON (SC_P.object_id = FKC.parent_object_id) AND (SC_P.column_id = FKC.parent_column_id)
INNER JOIN sys.objects SO_R ON SO_R.object_id = FKC.referenced_object_id
INNER JOIN sys.columns SC_R ON (SC_R.object_id = FKC.referenced_object_id) AND (SC_R.column_id = FKC.referenced_column_id)
WHERE
((SO_P.name = @tableName) AND (SO_P.type = 'U'))
OR
((UPPER(SO_R.name) = UPPER(@tableName)) AND (SO_R.type = 'U'))
;
view raw list_relationships.sql hosted with ❤ by GitHub

Leave a comment | posted in Tips

19/Jul/2020

PostgreSQL script to look up for a text

By Gabriel
DO $$ <<lookup_contents>>
DECLARE
_search VARCHAR := 'something'; —what are you looking for?
_limit INTEGER := 1000000;
_total_columns INTEGER := 0;
_total_read INTEGER := 0;
_mark INTEGER;
_text_columns REFCURSOR;
_text_column RECORD;
_address_columns REFCURSOR;
_address_column RECORD;
BEGIN
RAISE NOTICE 'Look up contents for %', _search;
—For each column in the database
OPEN _text_columns FOR
SELECT
t.table_schema
, t.table_name
, c.column_name
, CONCAT('SELECT ', c.column_name, ' AS data_found FROM ', t.table_schema, '.', t.table_name, ' WHERE (UPPER(', c.column_name,') LIKE UPPER(''%', _search, '%'')) ORDER BY ', c.column_name,';') AS "selection_query"
FROM
information_schema.tables t
INNER JOIN
information_schema.columns c
ON c.table_name = t.table_name
AND c.table_schema = t.table_schema
WHERE
(t.table_schema = 'public') —schema
AND (t.table_type = 'BASE TABLE') —tables
AND ((c.character_maximum_length IS NOT NULL) AND (c.character_maximum_length > 1)) —more than 1 character
AND (c.udt_name IN ('bpchar', 'char', 'varchar', 'text')) —only texts
ORDER BY
t.table_schema, t.table_name
;
LOOP
FETCH _text_columns INTO _text_column;
EXIT WHEN NOT FOUND;
IF _total_read >= _limit THEN
RAISE NOTICE 'Reached the limit of % reads', _limit;
EXIT;
END IF;
_total_columns := _total_columns + 1;
SELECT INTO _mark MOD(_total_columns, 10);
IF _mark = 0 THEN
RAISE NOTICE 'Processed % columns so far…', _total_columns;
END IF;
—RAISE NOTICE 'Executing: %', _text_column.selection_query;
OPEN _address_columns FOR EXECUTE _text_column.selection_query;
LOOP
FETCH _address_columns INTO _address_column;
EXIT WHEN NOT FOUND;
_total_read := _total_read + 1;
RAISE NOTICE 'Found: %.%.% = %', _text_column.table_schema, _text_column.table_name, _text_column.column_name, SUBSTRING(_address_column.data_found, 1, 100);
IF _total_read >= _limit THEN
RAISE NOTICE 'Reached the limit of % reads', _limit;
EXIT;
END IF;
END LOOP;
CLOSE _address_columns;
END LOOP;
CLOSE _text_columns;
RAISE NOTICE 'Concluding % columns reads between % records', _total_columns, _total_read;
END
lookup_contents $$;
view raw PostgreSQL-Search_contents.sql hosted with ❤ by GitHub

Leave a comment | posted in Tips

17/Oct/2019

SQL Server script to scan the whole database looking for a specific value in a specific column

By Gabriel
DECLARE @search_column VARCHAR(255);
DECLARE @search_value VARCHAR(255);
DECLARE @search_type INT; /* 1: specific column 2: string columns 3: numeric columns */
SET @search_column = 'SEARCHING_COLUMN_NAME'; —wrap with %% for wider search
SET @search_value = 'Any value'; —wrap with %% for wider search
SET @search_type = 1;
DECLARE @containing_tables TABLE
(
table_name VARCHAR(255) NOT NULL,
column_name VARCHAR(255) NOT NULL,
column_value VARCHAR(255) NOT NULL
);
DECLARE all_tables_cursor CURSOR FAST_FORWARD for
SELECT
t.name AS 'TableName', c.name AS 'ColumnName'
FROM
sys.columns c JOIN sys.tables t ON c.object_id = t.object_id
WHERE
((@search_type = 1) AND (UPPER(c.name) LIKE UPPER(@search_column))) /* search into a specific column */
OR
((@search_type = 2) AND (c.user_type_id IN (35, 99, 167, 175, 231, 239))) /* search into all string columns */
OR
((@search_type = 3) AND (c.user_type_id IN (48,52,56,108,127))) /* search into all numeric columns */
ORDER BY TableName, ColumnName;
DECLARE @table_name VARCHAR(255), @column_name VARCHAR(255)
OPEN all_tables_cursor
FETCH NEXT FROM all_tables_cursor INTO @table_name, @column_name
WHILE (@@FETCH_STATUS <> –1)
BEGIN
IF (@@FETCH_STATUS <> –2)
BEGIN
BEGIN TRY
DECLARE @select_statement VARCHAR(1000);
SET @select_statement = 'DECLARE all_values_cursor CURSOR FAST_FORWARD FOR SELECT [' + @column_name + '] FROM [' + @table_name + '] WHERE (CONVERT(VARCHAR(255), [' + @column_name + ']) LIKE ' + CHAR(39) + @search_value + CHAR(39) + ');';
EXEC(@select_statement)
DECLARE @column_value VARCHAR(255)
OPEN all_values_cursor
FETCH NEXT FROM all_values_cursor INTO @column_value
WHILE (@@FETCH_STATUS <> –1)
BEGIN
IF (@@FETCH_STATUS <> –2)
BEGIN
INSERT INTO @containing_tables (table_name, column_name, column_value) VALUES (@table_name, @column_name, @column_value);
END
FETCH NEXT FROM all_values_cursor INTO @column_value
END
CLOSE all_values_cursor;
DEALLOCATE all_values_cursor;
END TRY
BEGIN CATCH
PRINT 'Error reading ' + @table_name + '.' + @column_name + ': ' + ERROR_MESSAGE();
END CATCH;
END
FETCH NEXT FROM all_tables_cursor INTO @table_name, @column_name
END
CLOSE all_tables_cursor;
DEALLOCATE all_tables_cursor;
—Bonus instruction, you can customize the print below to accomodate specific needs
DECLARE all_selects_cursor CURSOR FAST_FORWARD for
SELECT
table_name, column_name, column_value
FROM
@containing_tables
GROUP BY table_name, column_name, column_value;
OPEN all_selects_cursor
FETCH NEXT FROM all_selects_cursor INTO @table_name, @column_name, @column_value
WHILE (@@FETCH_STATUS <> –1)
BEGIN
IF (@@FETCH_STATUS <> –2)
BEGIN
BEGIN TRY
PRINT 'SELECT * FROM ' + @table_name + ' WHERE (' + @column_name + ' = ' + CHAR(39) + @search_value + CHAR(39) + ');';
END TRY
BEGIN CATCH
PRINT 'Error reading ' + @table_name + '.' + @column_name + ': ' + ERROR_MESSAGE();
END CATCH;
END
FETCH NEXT FROM all_selects_cursor INTO @table_name, @column_name, @column_value
END
CLOSE all_selects_cursor;
DEALLOCATE all_selects_cursor;
SELECT table_name, column_name, column_value, count(column_value) AS repeating FROM @containing_tables GROUP BY table_name, column_name, column_value;

view raw
SQLServer.sql
hosted with ❤ by GitHub

Leave a comment | posted in Tips

15/Sep/2019

Importing a public SSL certificate into a JVM

By Gabriel

You are trying to connect your pretty application to an external HTTPS endpoint and getting an odd out of blue exception like below:

PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

That’s happening because, when connecting via HTTPS, the public SSL certificate from the destiny must be available to JVM truststore. Sometimes it is not updated. So, let’s do it with two simple command lines.

1. Have the public certificate in your machine:

Linux

openssl s_client -connect <HOST>:443 -servername <HOST>:443 < /dev/null | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > public.crt

Windows

openssl s_client -connect <HOST>:443 -servername <HOST>:443 < NUL | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > public.crt

2. Import the public certificate:

<JDK_HOME>\bin\keytool -import -alias <HOST> -keystore <JRE_HOME>\lib\security\cacerts -file public.crt

All good!

Leave a comment | posted in Tips

14/Apr/2019

SQL Server script to replicate all data from a remote database to a local database

By Gabriel
/*
Replicates all data from the remote database to the local database. The local contents are removed, all current local data is erased and not recoverable.
You have to set the value for @LINKED_SERVER_NAME as your local Linked Server name for the remote SQL Server instance.
*/
print 'Starting replication…';
—Control for structure alterations over the process
declare @CONTROL_TABLES table
(
TABLE_NAME varchar(255) NOT NULL,
CONSTRAINT_NAME varchar(255) NULL,
STATUS_ENABLED int NULL,
HAS_IDENTITY int NULL
);
—Local linked server alias
declare @LINKED_SERVER_NAME varchar(255);
—Database name (both "from" and "to" must be same name)
declare @DATABASE_COPY varchar(255);
/**
Define controllers
**/
print 'Setting controllers…';
set @LINKED_SERVER_NAME = '127.0.0.1,11433';
—Using local database name
select @DATABASE_COPY = db_name();
—Identify all user tables with constraints
insert into @CONTROL_TABLES (TABLE_NAME, CONSTRAINT_NAME, STATUS_ENABLED, HAS_IDENTITY)
select
O2.NAME,
O.NAME,
case when ((C.STATUS & 0x4000)) = 0 then 1 else 0 end,
null
from SYS.SYSCONSTRAINTS C
inner join SYS.SYSOBJECTS O on O.ID = C.CONSTID
inner join SYS.SYSOBJECTS O2 on O2.ID = O.PARENT_OBJ
where
(O2.NAME in (select TABLE_NAME from INFORMATION_SCHEMA.TABLES where (TABLE_TYPE = 'BASE TABLE')))
and (O.XTYPE in ('C', 'F'))
;
—Identify all user tables with identity
update A
set
HAS_IDENTITY = 1
from
@CONTROL_TABLES as A
inner join (select
T.NAME as NAME
from
SYS.SCHEMAS as S
inner join SYS.TABLES as T on S.SCHEMA_ID = T.SCHEMA_ID
where exists
(
select 1 from SYS.IDENTITY_COLUMNS
where OBJECT_ID = T.OBJECT_ID
)) as B on A.TABLE_NAME = B.NAME
;
insert into @CONTROL_TABLES (TABLE_NAME, HAS_IDENTITY)
select
T.NAME,
1
from
SYS.SCHEMAS as S
inner join SYS.TABLES as T on S.SCHEMA_ID = T.SCHEMA_ID
where
exists (select 1 from SYS.IDENTITY_COLUMNS where OBJECT_ID = T.OBJECT_ID)
and (T.NAME not in (select TABLE_NAME from @CONTROL_TABLES))
;
/**
For each table, disable its constraints
**/
print 'Disabling constraints…';
declare NOCHECK_STATEMENTS_CURSOR cursor FAST_FORWARD for
select
distinct 'alter table ' + TABLE_NAME + ' nocheck constraint ' + CONSTRAINT_NAME + ';'
from
@CONTROL_TABLES
where
(STATUS_ENABLED = 1);
declare @NOCHECK_DISABLE_STATEMENT varchar(255)
open NOCHECK_STATEMENTS_CURSOR
fetch next from NOCHECK_STATEMENTS_CURSOR into @NOCHECK_DISABLE_STATEMENT
while (@@fetch_status <> –1)
begin
if (@@fetch_status <> –2)
begin
print @NOCHECK_DISABLE_STATEMENT
exec(@NOCHECK_DISABLE_STATEMENT)
end
fetch next from NOCHECK_STATEMENTS_CURSOR into @NOCHECK_DISABLE_STATEMENT
end
deallocate NOCHECK_STATEMENTS_CURSOR;
/**
For each table, disable its triggers
**/
print 'Disabling triggers…';
declare DISABLE_STATEMENTS_CURSOR cursor fast_forward for
select
'disable trigger ' + SYSOBJECTS.NAME + ' on ' + object_name(PARENT_OBJ) + ';'
from
SYSOBJECTS
inner join SYS.TABLES T on SYSOBJECTS.PARENT_OBJ = T.OBJECT_ID
inner join SYS.SCHEMAS S on T.SCHEMA_ID = S.SCHEMA_ID
where
(SYSOBJECTS.TYPE = 'TR');
declare @DISABLE_STATEMENT varchar(255)
open DISABLE_STATEMENTS_CURSOR
fetch next from DISABLE_STATEMENTS_CURSOR into @DISABLE_STATEMENT
while (@@fetch_status <> –1)
begin
if (@@fetch_status <> –2)
begin
print @DISABLE_STATEMENT
exec(@DISABLE_STATEMENT)
end
fetch next from DISABLE_STATEMENTS_CURSOR into @DISABLE_STATEMENT
end
deallocate DISABLE_STATEMENTS_CURSOR;
/**
Remove all local contents
**/
print 'Removing local contents…';
declare DELETE_STATEMENTS_CURSOR cursor fast_forward for
select
'delete from ' + TABLE_NAME + ';'
from
INFORMATION_SCHEMA.TABLES
where
(TABLE_TYPE = 'BASE TABLE')
order by TABLE_NAME
;
declare @DELETE_STATEMENT varchar(255)
open DELETE_STATEMENTS_CURSOR
fetch next from DELETE_STATEMENTS_CURSOR into @DELETE_STATEMENT
while (@@fetch_status <> –1)
begin
if (@@fetch_status <> –2)
begin
print @DELETE_STATEMENT
exec(@DELETE_STATEMENT)
end
fetch next from DELETE_STATEMENTS_CURSOR into @DELETE_STATEMENT
end
deallocate DELETE_STATEMENTS_CURSOR;
/**
Copy data from remote server to local environment
**/
print 'Copying data from remote server…';
—All non identity tables
declare COPY_STATEMENTS_CURSOR cursor fast_forward for
select
'insert into ' + TABLE_NAME + ' select * from "' + @LINKED_SERVER_NAME + '".' + @DATABASE_COPY + '.dbo.' + TABLE_NAME + ';'
from
INFORMATION_SCHEMA.TABLES
where
(TABLE_TYPE = 'BASE TABLE')
and (TABLE_NAME not in (select TABLE_NAME from @CONTROL_TABLES where (HAS_IDENTITY = 1)))
order by TABLE_NAME
;
declare @COPY_STATEMENT varchar(max);
open COPY_STATEMENTS_CURSOR
fetch next from COPY_STATEMENTS_CURSOR into @COPY_STATEMENT
while (@@fetch_status <> –1)
begin
if (@@fetch_status <> –2)
begin
print @COPY_STATEMENT
exec(@COPY_STATEMENT)
end
fetch next from COPY_STATEMENTS_CURSOR into @COPY_STATEMENT
end
deallocate COPY_STATEMENTS_CURSOR;
—All identity tables
declare @TABLE_NAME varchar(255);
declare @COLUMNS_NAME varchar(max);
declare COPY_IDENTITY_STATEMENTS_CURSOR cursor fast_forward for
select
distinct(TABLE_NAME)
from
@CONTROL_TABLES
where
(HAS_IDENTITY = 1)
order by TABLE_NAME
;
declare @COPY_IDENTITY_STATEMENT varchar(max);
open COPY_IDENTITY_STATEMENTS_CURSOR
fetch next from COPY_IDENTITY_STATEMENTS_CURSOR into @TABLE_NAME
while (@@fetch_status <> –1)
begin
if (@@fetch_status <> –2)
begin
set @COLUMNS_NAME = null;
select @COLUMNS_NAME = coalesce(@COLUMNS_NAME + ',', '') + COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where (TABLE_NAME = @TABLE_NAME);
set @COPY_IDENTITY_STATEMENT = 'set identity_insert ' + @TABLE_NAME + ' on;';
set @COPY_IDENTITY_STATEMENT = @COPY_IDENTITY_STATEMENT + 'insert into ' + @TABLE_NAME + ' (' + @COLUMNS_NAME + ') select ' + @COLUMNS_NAME + ' from "' + @LINKED_SERVER_NAME + '".' + @DATABASE_COPY + '.dbo.' + @TABLE_NAME + ';'
set @COPY_IDENTITY_STATEMENT = @COPY_IDENTITY_STATEMENT + 'set identity_insert ' + @TABLE_NAME + ' off;';
print @COPY_IDENTITY_STATEMENT;
exec(@COPY_IDENTITY_STATEMENT);
end
fetch next from COPY_IDENTITY_STATEMENTS_CURSOR into @TABLE_NAME
end
deallocate COPY_IDENTITY_STATEMENTS_CURSOR;
/**
For each table, enable its triggers
**/
print 'Enabling triggers…';
declare ENABLE_STATEMENTS_CURSOR cursor fast_forward for
select
'enable trigger ' + SYSOBJECTS.NAME + ' on ' + object_name(PARENT_OBJ) + ';'
from
SYSOBJECTS
inner join SYS.TABLES T on SYSOBJECTS.PARENT_OBJ = T.OBJECT_ID
inner join SYS.SCHEMAS S on T.SCHEMA_ID = S.SCHEMA_ID
where
(SYSOBJECTS.TYPE = 'TR');
declare @ENABLE_STATEMENT varchar(255)
open ENABLE_STATEMENTS_CURSOR
fetch next from ENABLE_STATEMENTS_CURSOR into @ENABLE_STATEMENT
while (@@fetch_status <> –1)
begin
if (@@fetch_status <> –2)
begin
print @ENABLE_STATEMENT
exec(@ENABLE_STATEMENT)
end
fetch next from ENABLE_STATEMENTS_CURSOR into @ENABLE_STATEMENT
end
deallocate ENABLE_STATEMENTS_CURSOR;
/**
For each table, enable its constraints
**/
print 'Enabling constraints…';
declare CHECK_STATEMENTS_CURSOR cursor fast_forward for
select
'alter table ' + TABLE_NAME + ' with check check constraint ' + CONSTRAINT_NAME + ';'
from
@CONTROL_TABLES
where
(STATUS_ENABLED = 1)
;
declare @CHECK_STATEMENT varchar(max);
open CHECK_STATEMENTS_CURSOR
fetch next from CHECK_STATEMENTS_CURSOR into @CHECK_STATEMENT
while (@@fetch_status <> –1)
begin
if (@@fetch_status <> –2)
begin
print @CHECK_STATEMENT;
exec(@CHECK_STATEMENT);
end
fetch next from CHECK_STATEMENTS_CURSOR into @CHECK_STATEMENT
end
deallocate CHECK_STATEMENTS_CURSOR;
print '…all good!';

view raw
data_replication.sql
hosted with ❤ by GitHub

Leave a comment | posted in Tips

13/Jun/2018

Redirect Tomcat console messages to file

By Gabriel

Despite the fact that offical Tomcat’s Wiki says:

System.out and System.err are both redirected to CATALINA_BASE/logs/catalina.out when using Tomcat’s startup scripts (bin/startup.sh/.bat or bin/catalina.sh/.bat). Any code that writes to System.out or System.err will end up writing to that file. (link)

For some reason that scape from my understanding, this is not 100% true. Many dirty System.out don’t go to catalina file, been readable only from console.

So, the solution to guarantee that all console messages became available in a file (usually for troubleshooting purposes) is:

  1. Locate the file startup.sh or startup.bat
  2. Look into for call “%EXECUTABLE%” start %CMD_LINE_ARGS%
  3. Replace the command above for call “%EXECUTABLE%” run >..\logs\console.log 2>&1 start %CMD_LINE_ARGS% run >..\logs\console.log 2>&1

 

Leave a comment   |  tags: , , | posted in Tips

13/Dec/2017

Spring Framework marks transaction to rollback when everything is fine

By Gabriel

Eventually, you can’t conclude the whole process flux because, at the end, the Spring Framework marks the transaction to rollback, even when all exceptions were caught.

The evidence of this scenario is this following message Transaction was marked for rollback only; cannot commit. The exception thrown is org.springframework.orm.jpa.JpaSystemException.

It happens because in some point of all code processed, your flow had an exception unchecked. The simplest solution is to use checked exceptions and avoid use NoResultException unless it is really necessary.

Leave a comment | posted in Tips

30/Oct/2017

Generating client for SOAP using CXF

By Gabriel

What is needed

  • WSDL file describing the services
  • Apache CXF (3.1+)
  • JDK (1.7+)

Straight to point

In order to create the set of Client’s classes, verify for the following environment variables:

  • JAVA_HOME: Path to JDK location, eg. C:\Progra~1\Java\jdk1.7.0_80
  • CXF_HOME: Path to CXF location, eg. C:\Java\apache-cxf-3.1.13

Now, open a command prompt and execute the command: wsdl2java.bat -p net.itfromhell.itsatrap.serviceclient -d C:\Temp\TestClient -all -ant -exsh false -dns true -dex true -encoding UTF-8 -verbose Test.wsdl

Where…

  • wsdl2java.bat: Tool to generate encapsulated service’s client from WSDL document
  • net.itfromhell.itsatrap.serviceclient: Package of generated classes
  • C:\Temp\TestClient: Output directory to generated classes
  • Test.wsdl: WSDL file

Leave a comment   |  tags: , , , | posted in Tips

20/Jun/2014

PWC6345: There is an error in invoking javac.

By Gabriel

Accessing your application in GlassFish you get this error message:

org.apache.jasper.JasperException: PWC6345: There is an error in invoking javac. A full JDK (not just JRE) is required

Try to verify the parameter AS_JAVA inside of file glassfish\config\asenv.bat(.conf). This parameter should be the JDK path.

Leave a comment   |  tags: | posted in Tips

20/Jun/2014

Get access to DAS for other Domain than default using shell

By Gabriel

Need to access your GlassFish as administrator but in other Domain than default. Use the tool asadmin, to do that you need to specify the port of DAS to this specific Domain:

asadmin --host localhost --port 4848 enable-secure-admin

Leave a comment   |  tags: | posted in Tips