Programming

You are currently browsing the archive for the Programming category.

How to fetch records between specific times but relative dates?

March 19, 2011 in Programming | 2 comments

Recently I had to write a report for a client that goes out everyday. The query was required to contain results between 09 30 AM yesterday and 09 30 AM today. I’m no great shakes in SQL and so I set about to use Google to find my answer.

Believe it or not, I had some hard time finding pages that gave me this information. A lot of potential sites came up and I poured through a lot of information and finally came across what I wanted. I’m posting it here again so the next time you need something like this, you can stumble upon this more easily.

SELECT * FROM table_in_question
WHERE date_in_question >= TIMESTAMP(DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY), ’093000′)
AND date_in_question < TIMESTAMP(CURDATE(),'093000');

The query is pretty simple and self explanatory so I will give you a very short explanation. The TIMESTAMP function of MySQL with two arguments will return an object after concatenating the date and time part from the arguments. This object’s data type is datetime which is what most people use to store times in the database. From here on it is the usual comparison in the where clause. To break it down further:

TIMESTAMP(DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY), ’093000′) today will return 2011-03-18 09:30:00.000

and

TIMESTAMP(DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY), ’093000′) tomorrow will return 2011-03-19 09:30:00.000

When you write a query like this, you let the database do some heavy lifting very easily as compared to writing that code yourself in the business and just passing it as arguments to the query.

Happy St. Patrick’s day & cheers! :-)

Tags: , , ,

MySQL Workbench – Error Code: 1175

September 16, 2010 in Programming | 5 comments

Have you ever encountered the following error when using MySQL Workbench to perform an UPDATE or DELETE without the PRIMARY KEY or INDEX like most of us do?

Error Code: 1175
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

If you do, the fix is pretty simple:

  1. Select Edit->Preferences
  2. Navigate to SQL Editor Tab
  3. Uncheck this option: “Forbid UPDATE and DELETE statements without a WHERE clause (safe updates)”

While the text states that this will only prevent statements from running without a WHERE clause (which is a good thing), it will also prevent statements which do not use the PRIMARY KEY or INDEX in the WHERE clause.

PS: Now that you have unchecked it, make sure you are SUPER careful when running queries! :-D

Tags: , ,

Pretty Printing (Formatting) XML using Java

September 14, 2010 in Programming | No comments

As a part of the project I was working on, I had to deal with XML request and response documents. The DOM documents were big and very cluttered. It was a not a sight that will please the eyes.

I did some quick googling and combined various posts from various places to create this method that will do some neat printing of XML when you are in a bind and neatly formatted XML. Feel free to tweak and use as you see fit.

/**
 * Sends the XML document to any stream. To print on console pass System.Out as the argument for OutputStream
 * @param xmlDocument
 * @param stream
 * @throws Exception
 */
public void printXMLToStream (Document xmlDocument, OutputStream stream) throws Exception {
	try {
		Transformer transformer = TransformerFactory.newInstance().newTransformer();
		transformer.setOutputProperty("omit-xml-declaration","yes");
		transformer.setOutputProperty(OutputKeys.METHOD, "xml");
		transformer.setOutputProperty(OutputKeys.INDENT, "yes");
		transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "3");
		transformer.transform(new DOMSource(xmlDocument), new StreamResult(stream));
	}
	catch (Exception e) {
		throw new Exception("Error occured in printXMLToStream()\n" + e);
	}
}

Tags: ,

TortoiseSVN equivalent on Ubuntu? Get RabbitVCS!

September 14, 2010 in Operating Systems, Programming | No comments

Have you guys been wondering about moving to Ubuntu and have been held back because of a good GUI based shell extension like TortoiseSVN.

Well you don’t have to anymore. I was just doing some googling to figure out an Ubuntu equivalent of TortoiseSVN and I stumbled upon RabbitVCS.

I’m going to be using it over time and will let you folks know how this works out for me. Please feel free to give it a shot and let me know if this was a useful tool.

Here are the installation instructions for Karmic and Lucid. For other versions/distros please visit their site.

sudo add-apt-repository ppa:rabbitvcs/ppa
sudo apt-get update
sudo apt-get install rabbitvcs-cli rabbitvcs-gedit rabbitvcs-core rabbitvcs-nautilus rabbitvcs-thunar

Here are a few screenshots:

Nautilus Integration
Nautilus Integration

Commit Dialog
Commit Dialog

Show Log
Show Log

Tags: ,

Symbolic Link /var/www and your workspace folder in Ubuntu

October 24, 2009 in Operating Systems, Programming | 4 comments

I’m not sure if my post’s heading puts across exactly what I want to convey. Anyway, I stumbled upon a neat trick which I wanted to share with you folks.

When you usually do PHP development on Ubuntu, you will have to put your files into /var/www or whichever is your document root configured for Apache for the application to be able to run. The downside here is that, you will not always have root permission and every time you either want to add, change or remove a file, you need to do so using ‘sudo’. So what you can do is map any folder (in this case, a folder in your workspace when you use an IDE like Eclipse) to the /var/www (or your document root folder). That way your files will be visible under the /var/www folder and you can execute your files or even debug them without much hassle.

Here is what I did:

1. Go into /var folder and remove the existing www folder

cd /var
sudo rm -rf www/

2. Create a folder which you would use as your workspace

cd /home/username/
mkdir workspace/www/

I have set it up this way, so that my Java project go into workspace and all my PHP development goes into workspace/www/ folder
3. Now go back to /var folder and create the mapping

cd /var
sudo ln -s /home/username/workspace/www/

That’s it! Now you are all set. Any file you drop into /home/username/workspace/www will be automatically visible in your Apache and you can click on it to run the web application.

Please do let me know if I have done something wrong or if there is anyway to do this better.

PS: Just make sure you don’t have an index.html or something like that inside /www/. If that is there, instead of getting a folder listing you will directly be taken to that page. I think there is a configuration change in Apache that will prevent this from happening. Well, what you would like to do is up to you.

Tags: , ,

ADOdb + PHP = Efficient Coding

October 14, 2009 in Programming | 2 comments

I recently stumbled upon this Data Abstraction Layer for PHP.  Here is a bit of background info: ADOdb consists of a set of classes that encapsulate frequently used methods to read and write from a database. And what is brilliant about ADOdb is that it supports all the popular DB’s in the industry that include MySQL, PostgreSQL, MS SQL, Oracle and a few others.

Please visit their site for more information on how to find something that suits your needs. Let me tempt you!

Look at what little it took for me to get data from a MySQL DB. Click here to download the required package and unzip the package into your /var/www folder or whichever folder is your document root for the Apache server

Connect($server, $user, $pwd, $db);
$rs = $DB->Execute("select * from table");

while ($array = $rs->FetchRow()) {
print_r($array);
}

The site has a lot more cooler ways of accessing the DB's. Have fun trying out different stuff!

Tags: , ,