sql

SQL: One of the most valuable skills – Craig Kerstiens

February 13, 2020 / 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

February 21, 2019 / English Content, 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 19 forks. 112 stars. 3 open issues. Recent commits: Update README.md, GitHub …

Oracle SQL Developer Keep Alive plugin Read More »

ORA-01461: can bind a LONG value only for insert into a LONG column

August 5, 2015 / English Content, Knowledgebase / , , ,
Okey, you know Oracle. A very good database, a very old database, a very solid rock. Not famous for its error messages. I have already stumbled upon a misleading error on the old rock. This error anyway is incredible. If you try to push a very long text in a varchar2, you can end up with …

ORA-01461: can bind a LONG value only for insert into a LONG column 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 »

dapper-dot-net – Simple SQL object mapper for SQL Server – Google Project Hosting

February 21, 2019 / Italian Content, Structured Query Language (SQL) / , , , ,
   Dapper is a single file you can drop in to your project that will extend your IDbConnection interface. […] A key feature of Dapper is performance. The following metrics show how long it takes to execute 500 SELECT statements against a DB and map the data returned to objects. The performance tests are broken …

dapper-dot-net – Simple SQL object mapper for SQL Server – Google Project Hosting Read More »

NoSQL Rocks? We try to understand

February 21, 2019 / English Content, IT Featured, Knowledgebase, Structured Query Language (SQL) / , , ,
At Gioorgi.com we was never a SQL fan. In 2000 we thinked SQL was boring, mostly because sql algebra could be a bit boring. Then we found this book written by one of the father of SQL. Years ago Google and then Facebook popped out with new incredible ideas, for improved and super fast scalability, which eventually …

NoSQL Rocks? We try to understand Read More »

SQL Server e Python

February 21, 2019 / Italian Content, Python, Structured Query Language (SQL) / , ,
L’integrazione tra .NET e SQL Server è data per scontata: in questo articolo esploreremo invece  come accedere a SQL Server da Python. Uno dei vantaggi di python è che è un linguaggio molto facile da imparare, con un notevole numero di librerie e un’ottima integrazione multi piattaforma. Oltre a ciò python offre un sistema di …

SQL Server e Python Read More »

SQL Server: meta programmazione e meta descrittori

February 21, 2019 / Italian Content, Structured Query Language (SQL) / , , ,
In questo articolo della serie SQL Server, vedremo come creare  in modo dinamico delle istruzione per impostare delle policy di sicurezza. Creeremo da zero una stored procedure chiamata sp_FixOverallGrant che in modo dinamico imposterà i profili di sicurezza per due utenze, una in lettura e scrittura (EBRB0_APP) ed una in sola lettura (EBRB0_USR). In particolare …

SQL Server: meta programmazione e meta descrittori Read More »

SQLite alter table

February 21, 2019 / Italian Content, Structured Query Language (SQL) / , , , , , ,
SQLite is a small, powerful embedded database. A friend of mine started using it about six years ago. Some years ago it comes also on top of  Python 2.5. It is used inside FileMaker Bento: its ultra customized model is based on a big sqllite db DropBox client, to store its internal state iPhone: stores your SMS and …

SQLite alter table Read More »