Netbeans on Windows XP

Posted in Java, MySql on November 30th, 2010 by phoenixdigital

Here are some tips for getting Netbeans working on Windows XP.
Read more »

MSSQL Rollback

Posted in MSSQL on September 12th, 2010 by phoenixdigital

Restoring a column in a table from a backup. IF you have accidentally deleted a column from a table or corrupted the data for that column and you cant restore from a backup due to new records being entered this is the best way to get the data back for at least 99% of the records.
Read more »

Import/Export MySQL databases and other commands

Posted in MySql on June 16th, 2010 by phoenixdigital

Just a quick reminder of how to backup and restore MySQL databases.

nice mysqldump --opt -u [user_name] -p [database_name] | gzip > [backup_file].dump.gz
or
nice mysqldump --opt -u [user_name] -p [database_name] | 7za a -si database7z 20140306-database.dump.7z

To restore

nice gzip -d [backup_file].dump.gz
nice mysql [database_name] < [backup_file].dump

To create a database

mysql -u root -p
create database [the_database_name]

To change the database
use [the_database_name];

To show all databases
show databases;

To show indexes on a table
SHOW INDEXES FROM products;

To show all tables
show tables;
or
show tables like "test1";

Adding a user if following doesn’t work

CREATE USER blah
or
insert into user (Host,User ,Password,Select_priv ,Insert_priv ,Update_priv ,Delete_priv ,Create_priv ,Drop_priv ,Reload_priv ,Shutdown_priv ,Process_priv ,File_priv ,Grant_priv ,References_priv ,Index_priv ,Alter_priv ,Show_db_priv ,Super_priv ,Create_tmp_table_priv ,Lock_tables_priv ,Execute_priv ,Repl_slave_priv ,Repl_client_priv ,Create_view_priv ,Show_view_priv ,Create_routine_priv ,Alter_routine_priv ,Create_user_priv ,Event_priv ,Trigger_priv ,ssl_type ,ssl_cipher ,x509_issuer ,x509_subject ,max_questions ,max_updates ,max_connections ,max_user_connections) VALUES ('%', 'theUser', password('thePassword'), "Y","Y","Y","Y","Y","N","Y","N","Y","Y","N","Y","Y","N","Y","N","Y","Y","Y","N","N","Y","Y","Y","Y","N","Y","Y","","","","",0,0,0,0);

Reload all user tables and priveleges
FLUSH PRIVILEGES;

Tags: , , , , ,

Retrieve rows where count of linked items greater than zero

Posted in MSSQL on May 30th, 2010 by phoenixdigital

Say you have two tables where one is linked to the other by it’s ID or CODE and you only want to retrieve a list of Table1 items which have linked Table2 items. You also want to get the count of Table2 items for each.

Table1
id(int)
title(varchar)
another_id(int)

Table2
id(int)
table1_id(int)
title(varchar)
date_added(date)


SELECT t1.id, t1.title, count(t2.id) 
FROM Table1 t1
JOIN Table2 t2 ON t2.table1_id = t1.id
WHERE t1.another_id = 32
GROUP BY t1.code, t1.title
HAVING count(t2.id) > 0

Now say you want a list of Table1 with the most recent Table2 item ONLY


SELECT       t1.*, t2.*
FROM         Table1 t1
INNER JOIN   Table2 t2 
ON           t2.table1_id = t1.id
LEFT JOIN    Table2 t2later
ON           t2.table1_id = t2later.table1_id
AND          t2later.date_added > t2.date_added
WHERE        t2later.table1_id is null

Ref

Tags: , ,

Free Australian Postcode Data

Posted in Programming, Sql on May 14th, 2010 by phoenixdigital

Handy data file of all Australian postcodes with lat and long

http://www.corra.com.au/downloads/Australian_Post_Codes_Lat_Lon.zip

Recursive SQL query for parent child data

Posted in MSSQL on February 3rd, 2010 by phoenixdigital

Powerful scripts to retrieve parent child data in a few simple calls
http://www.vbforums.com/showthread.php?t=366078

Tags: , , , ,

MSSQL Scripts and Programming

Posted in MSSQL on November 18th, 2009 by phoenixdigital

These hints will allow you to create MSSQL scripts to performs updates to remote databases. The following examples utilise the loops and temporary tables with a more complex example at the end bringing them all together.
Read more »

Tags: , , , , ,

Installing Apache, PHP and MySQL on OSX Leopard

Posted in MySql, OS X Tips, PHP Programming on September 10th, 2009 by phoenixdigital

These instructions may not be 100% complete as I am writing this after the fact. However someone with a basic set of Unix skills can probably fill in the gaps. This post is primarily here to remind me of the basics involved. I will revisit this next time I install everything again from scratch.

Leopard comes with a built in apache server and PHP. All that is required is for PHP to be activated. MySql however is required to be installed.

Read more »

Tags: , , , , , , , ,

Kill SQL Processes in MSSQL

Posted in MSSQL on September 8th, 2009 by phoenixdigital

When trying to restore a database from a backup or perform any operation that requires full access you may be warned there are still users connected. To remove these processes you will need to go to the activity monitor of SQL Server Management Studio.
Read more »

Tags: , , ,

MSSQL Turn off Identity and Constraint Check

Posted in MSSQL on July 27th, 2009 by phoenixdigital

When inserting raw data into a Microsoft SQL database you may need to turn off the primary key check and any other constraints just so you can get your data into the system. The following commands will achieve this.

SET IDENTITY_INSERT [TableName] ON;
ALTER TABLE [TableName] NOCHECK CONSTRAINT all;

To restore all checks back to the way they were

SET IDENTITY_INSERT [TableName] OFF;
ALTER TABLE [TableName] CHECK CONSTRAINT all;

Tags: , , ,