Category Archives: Database

How to display multiple count(*) values in one row with Group By based on different conditions

sql-injection-database

It has been a while for me to write SQL query due to the heavy involvement with budgetary meetings and other managerial errands. So when the developer asked me the help on the query today, it took me a longer period to finally figure out.

Challenge:
Let us assume this scenario : we have a table with all applications submitted, along with the approval status (“Approved”, “Denied” and “Pending”) and process date/time.  Now, we need to display a report with the requested company information and how many applications submitted, approved, and denied they had in a particular time period.

It simply starts with the Count(*) and Group By Requested Company ID in the SQL. Continue reading

How to setup a schedule task to export Oracle table data to CSV file

OracleThe flight information was not updated as expected, so I need to re-write the script to dump the flight data from one of our Oracle database table into a CSV file, which can then be used to replace another Text file to update the flight information display devices.

A very important requirement for this data file: The column name should be on the first line.

Thought about a couple of other ways, like as complicated as creating a VB.net page to do so; or as simple as using Oracle database job scheduling. Finally I settled down to use SQL*Plus query and DOS batch file combined with Windows Task Scheduler. Here are the details I implemented. Continue reading

How to fix Save error prompt when changing data type of a table in MS SQL 2008

Today just tried to do a quick database table change in MS SQL 2008 R2, but got the Save (Not Permitted) error prompt as the below.

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

Continue reading

How to escape special character while writing SQL query in Oracle

I ran a group of SQL queries in TOAD Editor against Oracle database, but got interruption with the special character “&” within the query (the TOAD will prompt a Variables window to ask for input value). To proceed these queries without any interruption, I need to escape such special characters.

Here is the method I used:

SET DEFINE ~;

OR

SET DEFINE = OFF;

In this way, when you are using TOAD or SQL*Plus, the DEFINE setting can be turned off to allow &’s (ampersands) to be used in text.

For escaping other special characters (like Single or Double Quotes), please refer to this FAQ How does one escape special characters when writing SQL queries?

How to export Chinese characters encoded UTF-8 from MySql to CSV correctly

I am using buddypress with extend profiles in one of my WordPress site to deal with the membership requirement. Now I need to export all members to a CSV file for an event. It is known that buddypress is not easily to be viewed in the list format due to the design of customizable xprofile feature. Fortunately I can easily export the buddypress members into CSV file with a nice BP Export Users plugin.

I downloaded and installed the plug-in, modified the php file with my own extended fields. And ran it, then a good members list with correct profile fields information. But one small problem though, the Chinese characters became these messy characters which could not be recognized after exporting. Even I know the MySql database was set up right with UTF-8 encode. Continue reading

How to correctly import no-English (UTF-8) characters from MySql4 into MySql 5.0 with mysqldump

I tried to upgrade my WordPress to 2.9.2 but I had to upgrade my database MySql first since WordPress 2.9.2 requires higher MySQL version. Just like I usually did with the database backup routine, I used “mysqldump” command to export data from MySQL 4.0 and then used “mysql” to import data into MySql 5.0. Everything seemed OK at the first, but I later on found out all my Chinese characters turned into non-readable mess characters.

The problem was my host provider, 1 and 1 located in Europe, uses “latin1-german1_ci” as default database collation. And the dumped SQL script from the MySql 4.0 does not have the database collation information even it is in UTF-8, so all new tables created in the new MySql 5.0 have the latin1_german1_ci as the default collation and latin as the default character set.

Continue reading

How to update one table from values in another table

OracleThis is a simple one SQL I used yesterday for the Oracle application development. I have two tables which have two common fields (let us say, table_id and col1). The col1 in table1 is empty since it is a newly created table, and the col1 in table2 has the data but I will not use table2 since it is a old table. What I need to do is to move all col1 data from table2 to table1. The challenge is Table1 and Table2 do not have the exact same data on table_id.

First I used the following SQL statement to update the col1 in the table1 from column value in table2:

update table1
set col1=(select col1 from table2 where table2.table_id=table1.table_id)

Continue reading

How to update Oracle column with sequence numbers

OracleI have a Oracle database table with some fields (columns) in numeric format. Now I need to update one column, namely store_id, to make it a primary column with unique number id. Since there were a lot of duplicate data in this column, the best way to do is to update this column with the sequence numbers.

To accomplish this, I need to create a sequence object in Oracle, and use Oracle cursor to loop and update the table column.

Here is the PL/SQL I used and worked.

DECLARE

Cursor store_id
IS
SELECT store_id FROM store_locations FOR UPDATE;

BEGIN

FOR c_store_id IN store_id LOOP
UPDATE cms_store_locations
SET store_id = store_seq.nextval
WHERE CURRENT OF store_id;

END LOOP;
commit;
END;
/

How to move Microsoft SQL Server data to different disk drive

Microsoft SharePoint Server 2007Recently just re-installed the Microsoft Office SharePoint Server (MOSS) 2007 applications and database on new servers. But the SharePoint data files were in the C: system drive, which does not have enough space.

To move database files to the D: data drive, what I needed to do is

  1. Remove Content Database from the SharePoint Administrative Central.
  2. Deattch the database from Microsoft SQl server
  3. Move data files to the D: drive
  4. Re-attach the database
  5. Add Content Database to Web Application.

How to export and import MySql database

image I have tried to re-configure the old web site log analyst tools – LiveStats XSP 6.2 for a long, long time. Finally I decided to wipe all of them and re-install again. To be safe, I have to back-up all MySql data (installed by LiveStats) first. Since the server is a Windows 2000, and I could not use web interface like Phpmyadmin. Well, I can copy all *.MYD files. But I found a better way by using the the following commands came with MySQL installation.
Continue reading

Is the Read-Only attribute on OS Oracle data file a bug for Oracle 10g?

In today’s Oracle 10g training, we were asked to do a exercise on tablespace. I needed to make a tablespace Read Only and Read Write. The syntax was simple, just “ALTER TABLESPACE tbs READ ONLY (READ WRITE”. But while I was playing around, I thought what would happen on the data file after I turned on the Read Only with the tablespace. So I checked the data file (like c:\oracle\oradata\something.dbf) and found out it did not have Read-only attribute even after I altered the tablespace as Read Only. Interesting, it seems the Oracle did not look up the OS level when it deals with the tablespace read-only attributes.

To make things more interesting, I turned on the Read-Only attribute for this tablespace’s data file (Right Click, and check the Read-Only in the proprieties window). Then I went ahead to make more alterations on the tablespace, like increasing the extents size etc. The funny thing was I could see these system changes in Oracle through some dba_ views, but actually they did not take effective. Since I could not insert any new record to a full tablespace even I “successfully” increase the size in Oracle.

Not sure Oracle already aware this situation or not.

How to display 0 in conditional [group by] report in Count() SQL Query

sql-injection-database Challenge:
Let me assume this: we have two tables, one is Regions table with all region information (East, West, etc. ) and we have another table with Sales information. Now, we need to display a report with region information and how many sales conducted in each region with greater than $1000 value in each sale. It sounds simple at the beginning since a Count(*) with Group By can do this trick.

But what if I need to still show the region which does not have any sales greater than $1000 on the report? Using Count(*) itself in the query, the region without any sales greater than $1000 will not show in the query result, period.

Continue reading

How to remote connect to Microsoft SQL Server Express 2005

Challenge: By default, the SQL Server Express 2005 was not installed for remote access. Which means networking protocols are disabled by default in SQL Server Express. Thus, if you simply installed Express and chose all the defaults like I did, SQL Server Express will only be able to have connections originating on the local machine where SQL Server is installed.

That is why I got the following error message when I tried to connect to one SQL Server Express instance installed on another server from my desktop’s SQL Server Management Studio Express.

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

And this is the screen shot I have.

sql_remote_error

Continue reading

How easily to move Oracle data from one machine to another

Oracle LogoOk, finished the installation of Oracle 9i on the new machine. It is time for me to move all existing data from my old machine to this new server. I do not want to create all stuffs from the scratch, and do not want to use exp/imp utility either. I thought there should be a easy way to do so. Since I am a web designer, I will give such question to the DBA in my team.

It turns out there is an easy way, yeah!

Here are some steps I did to move all data from the old machine to new machine with the help of my DBA co-worker. Continue reading