Archive for category SQL
Accedere a MySql server dall’esterno
Avete appena installato MySql da una macchina remota e non riuscite ad effettuare l’accesso con il vostro client?
Non vi preoccupate, è normale. La configurazione di default di MySQL non permette l’accesso dall’esterno. Chiaramente non per complicarvi la vita, ma per motivi di sicurezza, visto che un servizio aperto sull’esterno, non configurato come di deve, è sempre un ottimo inizio per fare breccia… Quindi attezione…
Morale a parte vediamo come procedere per abilitare l’accesso dall’esterno:
Per prima cosa dobbiamo modificare il file di configurazione /etc/my.cnf in modo che il server MySql sia in ascolto sull’IP esterno, quindi, assicuriamo che la linea skip-networking sia commentata o non presente. Dopo di che modifichiamo (o inseriamo se non esiste) la linea bind-address come segue :
bind-address=[IP-SERVER]
Salviamo e riavviamo MySql
/etc/init.d/mysqld restart
Una volta che MySql è in ascolto sull’interfaccia esterna dobbiamo impostare le grant sugli utenti che possono accedere dall’esterno. Quindi accediamo al server in locale e lanciamo il seguente comando.
mysql> GRANT ALL ON foo.* TO bar@'202.54.10.20' IDENTIFIED BY 'PASSWORD';
Vediamo ci capire cosa si puo fare con il seguente comando; diamo grant complete sul database foo, su tutte le tabelle [*], all’utente bar che si connette dall’IP 202.54.10.20 che ha come password PASSWORD.
In questo modo possiamo collegarci solo se il nostro client ha come IP 202.54.10.20. Nel caso l’ip non sia sempre lo stesso, possiamo utilizzare il carattere jolly % come con l’operatore like, visto che l’ip del client viene messo in like con quello specificato nella grant.
Esempio TO bar@’%’ vuol dire “all’utente bar che si collega da ogni IP”.
Anche questa volta tutto molto semplice no? Se avete problemi, o notate imperfezioni, commentate pure!
Generare una stringa randomica in TSQL
Proprio oggi avevo la necessità di generare una chiave alfanumerica casuale (o stringa randomica che di si voglia) all’interno di una procedura SQL Server. Senza investigare troppo sul motivo di questa necessità 
ecco qua la soluzione.
Una procedura che dato un set di caratteri e una lunghezza N, ritorna in output una strina di N caratteri presi dal set specificato, in modo casuale.
CREATE PROC genera_string_random (@p_len INT, @p_random_string VARCHAR(100) OUTPUT)
AS
BEGIN
DECLARE @v_string VARCHAR(100);
DECLARE @v_type TINYINT;
DECLARE @v_source VARCHAR(100)
DECLARE @v_lenght_source AS TINYINT
SET @v_source = ‘0123456789ABCDEFGHILMNOPQRSTUVZabcdefghilmnopqrstuvz’ – set caratteri
SET @v_lenghtSource = LEN(@v_source) - 1
— scelta del nuovo carattere da aggiungere alla stringa finale
WHILE ( @p_len > 0 )
BEGIN
SET @v_password = @v_password + SUBSTRING(@v_source, CONVERT(INT, ROUND(1 + (RAND() * (@v_lenghtSource)), 0)), 1);
SET @p_len = @len - 1;
END
SET @p_random_string = @v_password;
END
GO
Per utilizzare la procedura:
DECLARE @v_random_string AS VARCHAR(100)
EXEC genera_string_random 15, @v_random_string OUTPUT
SELECT @v_random_string
Niente di piu facile vero? Chiaramente potete cambiare il set di caratteri in base alle vostre esigenze, ma ricordate che la stringa generata non è univoca, quindi non utilizzatela come chiave primaria di una tabella…
Risolvere collation conflict in SQL Server
State avendo a che fare con questo fastidiosissimo errore?
Cannot resolve collation conflict for equal to operation.
Il collation è un set di caratteri utilizzato per gestire alfabeti di diversi linguaggi, edè composto da una serie di regole per gestire in confronti tra caratteri dello stesso alfabeto. Si ottiene l’errore di collation conflict quando si tenta di confrontare due campi con collation diversi.
Per ovviare al problema, senza cambiare il collate definito sulla tabella, dobbiamo effettuare un cast su uno dei due campi, come mostrato nell’esempio:
tab1.c1 è un char(2) ed ha collate SQL_Latin1_General_CP1_CI_AS
tab2.c2 è un char(2) edha collate SQL_Latin1_General_CI_AS
La prima query non effettua il cast e va in errore, la seconda viene eseguita con successo
SELECT * FROM tab1 JOIN tab2 ON tab1.c1 = tab2.c2
Cannot resolve collation conflict for equal to operation.
SELECT * FROM tab1 JOIN tab2 ON tab1.c1 COLLATE Latin1_General_CI_AS = tab2.c2
Attenzione al fatto che eventuali indici sulla colonna soggetta al cast non verranno considerati!
Insert in tabella con campi IDENTITY
Non so se vi è mai capitato di dover inserire un record su una tabella specificando un campo definito identity.
Vista la natura di un identity (o auto increment), che gestisce la generazione del valore del campo in modo sequenziale, non è possibile di default possibile specificare tale valore.
Esempio
–Creo la tabella
CREATE TABLE [TAB1]
(
[ID_TAB1] INT IDENTITY (1,1) NOT NULL, –Campo Idenity che parte da 1 e si incrementa di 1 ad ogni insert
[NAME] VARCHAR(50) NOT NULL
);
–La seguente insert andrà in errore
INSERT INTO [TAB1] ([ID_TAB1], [NAME]) VALUES (123,’Pippo’);
Messaggio 544, livello 16, stato 1, riga 1
Cannot insert explicit value for identity column in table ‘TAB1′ when IDENTITY_INSERT is set to OFF.
Per effettuare l’inserimento, SQL Server 2005 mette disposizione il seguente una variabile di sistema, IDENTITY_INSERT, da utilizzare come segue.
SET IDENTITY_INSERT [TAB1] ON – Abilita la possibilità di settare un campo identity
INSERT INTO [TAB1] ([ID_TAB1], [NAME]) VALUES (123,’Pippo’);
SET IDENTITY_INSERT [TAB1] OFF — Disabilita la possibilità di settare un campo identity
Stessa cosa per un UPDATE, con l’unica differenza che l’errore in tal caso sara il seguente:
UPDATE [TAB1] SET [ID_TAB1] = 123 WHERE [ID_TAB1] = 1
Messaggio 8102, livello 16, stato 1, riga 1
Cannot update identity column ‘ID_TAB1′.