Oracle : Convert a column into row in Oracle


In Oracle 11g database new functionality UNPIVOT to convert the column into rows.

My answer on stackoverflow.com on the same with example.

Python : Sqlite Insert Libraries of varying size into table


My answer accepted in stackoverflow.

Below is the code that I have written to create an insert statement for python which accepts the table name and dictionary. This will allow the varying size dictionaries to directly map into an Insert statement. Similar logical can be used for UPDATE statement as well.


import sqlite3
def create_insert_string(table_name,input_dictionary,conn_db):
#validate connection
c = conn_db.cursor()
#Check if the table is existing in the database if not throw error
table_exist=c.execute('SELECT COUNT(*) FROM sqlite_master WHERE type=\'table\' AND name=\''+table_name+'\'');
if table_exist.fetchone()[0]==0:
return 'Error'
insert_str = 'INSERT INTO '+table_name+' ( '
count=0;
for keys in input_dictionary.keys():
if count == 0:
insert_str=insert_str+ ' '+keys
else:
insert_str=insert_str+ ', '+keys
count=count+1
count=0;
insert_str=insert_str+' ) VALUES ( '
for keys in input_dictionary.keys():
if count == 0:
insert_str=insert_str+ ' :'+keys
else:
insert_str=insert_str+ ', :'+keys
count=count+1
insert_str=insert_str+' )'
return insert_str
# Main Program
conn = sqlite3.connect('C:\\PYTHON_SCRIPTS\\test.db')
dict={'column1':'value1','column2':'value2'}
insert_string=create_insert_string('test',dict,conn)
print(insert_string)
c = conn.cursor()
c.execute(insert_string,dict)
conn.commit()
c.close()

Python Scripting : Get max Last Modified Date using os.walk


Below is the code that i have written to get the max Lsat Modified Date of the files in all folders that i can delete all the folders that are not not being used by me for a long time.

#-*-coding:utf8;-*-
#qpy:2
#qpy:console
import os
import datetime
print “This is console module”
def comparefiletime(in_file,in_date) :
lastdate =datetime.datetime.fromtimestamp(os.path.getmtime(in_file))
if lastdate > in_date:
return lastdate
else:
return in_date

def getmaxdatefolders(in_root_fldr):
for foldern, sfoldern, filen in os.walk(in_root_fldr):
maxdate = datetime.datetime(1900, 1, 1,00,00)
for fileo in filen:
maxdate = comparefiletime(os.path.join(foldern,fileo),maxdate);
printmax date for the folder %s is %s’ %(foldern, maxdate)

getmaxdatefolders(‘storage/emulated/0/’);

WINSCP Command Line for FTP Automation in windows


WinSCP is a very good tool for FTP and SFTP file transfers in windows. But what you may not know is it comes along a command line tool called WinSCP.COM which performs the FTP/SFTP operations using command line.

This can be very useful in FTP Automation operations in windows using a BAT file.

Good Post on Clean Code


Using SQL Statements as a String in Class file while working on Android or Java application can be such a pain.

I was checking in the net to find out whether there are any ways in which we can store the SQL Statments separately so that the code can be more readable.

I found some interesting stuff. Please find below the links for the same.

1. Clean Code

2. Java Practices : Keep SQL out of code.

3. Android : Store SQL Queries in an XML File (Stackoverflow answer)

I am working on a way to store all my SQL Statements in an XML File while working with Android.

Found this post on placing XML File with Data on Android in StackOverflow.

res

  • No subdirectorys are allowed under the specific resource-folders.
  • The R-class indexes all resources and provides simple access.
  • There are some simple methods which help reading files stored in the res-directory

assets

  • Subdirectorys are allowed (as much as you like).
  • No indexing by the R-class
  • Reading resources stored in assets is done using the AssetManager.

Also found a cool post on Working with resources in Android : Link.

 

SQL Statements in the case of Android can be stored in the strings.xml under the resources–> values  folder.

In case you using a Database class which is a pure java class the this link will be able to help you on how to do the same.

In my case, I created a separate DAO Class called the DBClass which deals with the connecting and disconnecting with the database. I set the context of the DAO class so that the strings can be used here.

Java Swing- Create dialog in front its JFrame parent


If you want to create a child frame which you would want the parent frame to freeze(not accept input) untill it receives from the child frame.

Refer this question in stackoverflow. Should help.

Working with date in SQLite for Android


Working with dates in Oracle for reporting is very easy ( Since Oracle recognizes the date as datatype). But SQLite does not recognize Date as a datatype, this causes an issue when when you have to query for records that fall in between a range in SQLite.

 

Oracle it is very easy, just use he to_date command –

Oracle query :

SELECT amount,purchase_date FROM table_z
WHERE purchase_date >= TO_DATE('21-MAY-2011’,’DD-MON-YYYY’)
  AND puchase_date  <  TO_DATE('21-JAN-2012','DD-MON-YYYY');

In SQLite date needs to be converted into appropriate format before storing it in the database.

While storing in the database the date can be converted into YYYYMMDD format and stored in the database as an integer. Storing in this format in the database will ensure that older dates will have lesser value than the later ones.

Eg: 21-MAY-2011 will have 20110521

      21-JAN-2012  will have 20120121

The integer 20110521 < 20120121

So the SQlite equivalent of the query will be

SQLite Query :

SELECT amount,purchase_date FROM table_z
WHERE purchase_date >= 20110521
  AND puchase_date  <  20120121

Source: StackOverflow