Structured Query Language (SQL)

Structured Query Language (SQL) is a standard computer language for relational database management and data manipulation. SQL is used to query, insert, update and modify data

Simple method to add historic feature to SQLite

April 6, 2021 / English Content, IT Featured, Knowledgebase, Structured Query Language (SQL) / , / Leave a Comment
Abstract: Make a database which can be store historic modification is often considered a “secondary” activity, but bad design leads to databases that are difficult to optimize and often not very understandable. In this article we illustrate a simple method that respects the dictates of relational theory & is easy to understand. As a plus …

Simple method to add historic feature to SQLite Read More »

Semplice metodo per storicizzare i dati su database

April 7, 2021 / Italian Content, Structured Query Language (SQL) / , / Leave a Comment
Abstract: Storicizzare i dati spesso è un attività considerata “secondaria”, ma un cattivo design porta a base dati difficili da ottimizzare e spesso poco comprensibili. In questo articolo illustriamo un semplice metodo che rispetta i dettami della teoria relazionale, è facile da capire ed è attivamente usato in produzione. Autori: Ernesto Ghetti (idea), Giovanni Giorgi …

Semplice metodo per storicizzare i dati su database Read More »

Avoid Kafka if unsure (think twice series)

December 2, 2019 / English Content, IT Featured, Knowledgebase, Structured Query Language (SQL) / , ,
Some co-workers started using Apache Kafka con a bunch of our Customers. Apache Kafka is a community distributed event streaming platform capable of handling trillions of events a day. Initially conceived as a messaging queue, Kafka is based on an abstraction of a distributed commit log[*]. To get this goal, Apache Kafka needs a complex …

Avoid Kafka if unsure (think twice series) Read More »

SQL: One of the most valuable skills – Craig Kerstiens

March 12, 2021 / English Content, Structured Query Language (SQL) / , , ,
I admit it. I suffered from an “algebra narcoleptic syndrome” during my relational database lessons at University (1996 circa). Ok it is a fake. But it seems so real on these days. I was unable to avoid sleeping. I learned to love SQL after some years and now I think SQL is the most powerful, …

SQL: One of the most valuable skills – Craig Kerstiens Read More »

Removing duplicate rows in PostgreSQL, Oracle and SQLite

November 28, 2019 / English Content, Knowledgebase, Structured Query Language (SQL) / , , , , , , ,
Sometimes you need to remove nasty duplicate on a table, based on a subset of the column. On every big database there is something called “rowid” which can be used to indentify a column in a unique way. On PostgreSQL is called ctid, as we shall see: Oracle way: DELETE FROM your_table WHERE rowid not …

Removing duplicate rows in PostgreSQL, Oracle and SQLite Read More »

Oracle SQL Developer Keep Alive plugin

March 15, 2021 / English Content, Gio's Software Projects, Knowledgebase, Software, Structured Query Language (SQL) / , , ,
On some environment, on some customers, Oracle connections are drop after some idle time (i.e. 2 minutes). I have found a small plugin for Sql Developer to solve this issue: it is very handy and logs its usage. Code is also very clean https://github.com/scristalli/sql-developer-keepalive 20 forks. 120 stars. 3 open issues. Recent commits: Update README.md, GitHub …

Oracle SQL Developer Keep Alive plugin Read More »

Oracle Auto increment trigger HOWTO

February 21, 2019 / English Content, IT Featured, Knowledgebase, Structured Query Language (SQL) / , , , ,
Oracle SQL Developer is full of nice feature, damned by a overwhelming options pane, like the one I will describe to you right now. Even if  Oracle databases (<12) does not support auto increment, you can easily ask to your sql data modeler to generate for you a sequence and a trigger in a automatic …

Oracle Auto increment trigger HOWTO Read More »

Oracle Database Sync

February 21, 2019 / English Content, IT Featured, Knowledgebase, Structured Query Language (SQL) / , ,
Oracle SQL Developer is full of nice feature, damned by a overwhelming options pane, like the one I will describe to you right now. I will show here a very fast way of comparing different database and auto-generate migration script. Reverse engineer the source database using Oracle Data Modeler Now select the right arrow  shown …

Oracle Database Sync Read More »

Oracle Invalid number ORA-01722

February 21, 2019 / English Content, Knowledgebase, Structured Query Language (SQL) / , , , ,
I stumbled upon a very brain-f**k error on Oracle 10g on these days. Context: the following query [sql]SELECT * FROM ( SELECT TO_NUMBER(CUSTOMER_ID) AS SNDG FROM BAD_CODES_TABLE WHERE AND I_LIKE=UPPER(‘STATIC_CONDITION’) AND CUSTOMER_ID NOT LIKE ‘%P%’ ) S WHERE TO_NUMBER(S.SNDG) >2000[/sql] could trigger a Invalid number if CUSTOMER_ID column contains invalid numbers. Why? Well…if you ask …

Oracle Invalid number ORA-01722 Read More »

Come evitare iniezioni SQL lato DB in SQLServer, Oracle, MySQL

October 22, 2020 / Italian Content, Structured Query Language (SQL) / , , , , , , , ,
 Come evitare iniezione SQL: lato SQL Server (SP_EXECUTESQL) In generale va evitato nel modo più assoluto la scrittura di query sql diamiche. Va evitato cioè  l’uso lato SQL Server di sp_executesql e EXEC Di seguito mostriamo come trasformare una query “dinamica” in una “statica”   CREATE PROCEDURE search_orders @custid nchar(5) = NULL, @shipname nvarchar(40) = …

Come evitare iniezioni SQL lato DB in SQLServer, Oracle, MySQL Read More »

SQL Server Backup & Restore

February 21, 2019 / Italian Content, Knowledgebase, Software, Structured Query Language (SQL) / , , ,
E’ facile fare un backup con sql server: Basta selezionare tasto destro Tasks>>Backup su un db. Ma come fare il restore?… Ecco un semplice script che chiarisce la cosa (non sempre lampante dallo wizard di restore….): [sql] — Usare il seguente comando per recuperare i parametri sorgente da usare nella MOVE — Nel nostro caso …

SQL Server Backup & Restore Read More »

Help Daitan find a Sql solution for codezauker

September 6, 2019 / English Content, IT Featured, Software, Structured Query Language (SQL) / , , ,
In my code ramblings during the developement of Code Zauker, I ended up studing a bit NoSql  database. Code zauker started using Redis, because Redis is a very bold memory-based no-sql db. Redis also support complex data type like sorted set, lists and so on, which was very userful. Anyway I needed a very fast …

Help Daitan find a Sql solution for codezauker Read More »