Finding table by column name in a database

From time to time I need to find a table in a database which I know the column name but I have to search for the table.

Here's how to do it in Oracle:
SELECT TABLE_NAME, COLUMN_NAME
FROM USER_TAB_COLUMNS -- COLS,
WHERE COLUMN_NAME = 'ColumnNameHere'
GROUP BY TABLE_NAME, COLUMN_NAME;

Here's how to do it in SQL Server:
SELECT obj.Name, col.Name
FROM sysobjects obj, syscolumns col
WHERE obj.id = col.id
AND col.Name = 'ColumnNameHere'

Protected free Android applications

One issue I'm facing from time to time is people copying my apps & change the ad network code.

I found the following methods good fighting them:

1. Use ProGuard to obfuscate the code. If the code is obfuscated it's harder to remove other protective measures listed below. Some notes on ProGuard:
   (a) Move important functionality from non-obfuscated functions (such as onCreate)
2. Check application signature, use: getPackageManager().getPackageInfo(getPackageName(), PackageManager.GET_SIGNATURES).signatures[0]
3. CRC/Checksum validation of resources
4. CRC/Checksum validation of the APK.
5. Check if the application is running in debugger.
6. Check installed package name.

When you discover an issue DO NOT stop the application. Start a sequence that will cause the application crash (for example, clear a list that should not be empty).

AndEngine BitmapTextureAtlas extension

Last few days I played with AndEngine, an open source, free, games framework for Android. I'm not sure yet if AndEngine will save time building new games.

Anyhow, one weird part in AndEngine is the way you load all the resources into a single canvas. You have to define the location of each resource on the canvas, and if you change the resource size you have to recalculate everything.

Here is a simple extension to BitmapTextureAtlas that calculate everything automatically and saves you the trouble of positioning the resources on the canvas.

public class BitmapTextureAtlasEx extends BitmapTextureAtlas {
 int mUsedWidth;
 
 public BitmapTextureAtlasEx(int width, int height, TextureOptions options) {
  super(width, height, options);
  
  mUsedWidth = 0;
 }
 
 public TextureRegion appendTextureAsset(Context c, String assetPath) {
  TextureRegion region = BitmapTextureAtlasTextureRegionFactory.createFromAsset(
         this, c, assetPath, mUsedWidth, 0);
  mUsedWidth += region.getWidth();
  
  return region;
 }
 
 public TiledTextureRegion appendTiledAsset(Context c, String assetPath, int tiledColumns, int tiledRows) {
  TiledTextureRegion region = BitmapTextureAtlasTextureRegionFactory.createTiledFromAsset(
         this, c, assetPath, mUsedWidth, 0, tiledColumns, tiledRows);
  mUsedWidth += region.getWidth();
  
  return region;
 } 
}

And this is how to use it (in the onLoadResources funcation):

mTexture = new BitmapTextureAtlasEx(1024, 128, TextureOptions.BILINEAR_PREMULTIPLYALPHA);
      
mTextureRegion = this.mTexture.appendTextureAsset(this, "texture.png");
mTiledRegion = this.mTexture.appendTiledAsset(this, "tile.png", 1, 1);

Free resources for applications

This post is a work in progress.

Here are few places I've found for useful for free resources. You should verify if the specific resource license fit your needs.

Free fonts:
*. http://www.fontsquirrel.com/ (verify the copy rights per font, most are not really free)
*. http://scripts.sil.org/cms/scripts/page.php?site_id=nrsi&id=OFL (read the OFL license first)

Free clip arts:
*. http://www.openclipart.org/.
*. Huge list of sites that contain free images.

Free music/sound affects:
*. http://www.freesound.org - Creative common license.
*. There are many sites that looks OK, just search for 'free sound effects'.

A bit off-topic - royalty free resources (not free):
*. Music loops
*. Tons of resources: The Marketing Guide for Game Developer

Monetizing Android applications

In a recent talk I've heard by Itay Rokni of StartApp.com he listed the various monetizing options Android developer has (to date):

• In app ads - Ads inside the application: banners, full page, leads generators etc. See for example AdMob.


• Paid apps - The user pay to get the application. Not free options.


• Freemium - Basic application is free, the user can buy the full application or in-app buy more features.


• Notification bar ads - Ads that appear not inside the application, but in the notification bar. For example see airpush.com.


• Search icon - Create a search icon after app installation. The provider pay per installation or revenue share. For example see StartApp.com

Few notes if you're using In app ads:

• Trust no one - AdMob is the industry benchmark and every says 'we're better than AdMob'. I found this statement to be almost always wrong.


• Use mediation layer - Use mediation layer such as AdWhirl to control which ad networks you're using. Ad networks go up and down, it's better to control the follow of ads dynamically and not depend on updating the APK in the market.

Bulk image resizing/changing format

Sometimes there's a need to resize images or just changes their formats. Mostly if the original is on high resolution or improper format for mobile applications. While there are many tools to do this, the best I've found is a plug-in for Paint.NET called Paint.NET Bulk Image Processor. It's free, open source and super easy to use.

innerActive & AdWhirl, take 2

Few days ago I've published a post how to make an AdWhirl adapter for innerActive. Just after publishing the post I've noticed innerActive released a major version for their Android SDK (3.0.3).

Here is the new adapter:

import android.app.Activity;

import android.util.Log;

import android.view.View;



import com.adwhirl.AdWhirlLayout;

import com.adwhirl.AdWhirlLayout.AdWhirlInterface;

import com.inneractive.api.ads.InneractiveAdEventsListener;

import com.inneractive.api.ads.InneractiveAdView;

import com.inneractive.api.ads.InneractiveAdComponent;



public class InnerActiveCustomEvents implements AdWhirlInterface {

	private static final String INNERACTIVE_PUBLISHER_ID = "TBD";

	

	private AdWhirlLayout mAdWhirlLayout;

	private Activity mActivity;



	public AdWhirlMultipleCustomEvents(AdWhirlLayout mAdWhirlLayout, Activity mActivity) {

		super();

		this.mAdWhirlLayout = mAdWhirlLayout;

		this.mActivity = mActivity;

	}



	public void adWhirlGeneric() {

	}

	

	public void inneractiveBanner() {

	    // BUGBUG - This fixes the Android OS bug with 'getCacheTotalSize' which causes

		// NullPointerException prior to Android 2.3, see also:

		// http://code.google.com/p/android/issues/detail?id=10789

		android.webkit.WebViewDatabase webViewDB = android.webkit.WebViewDatabase.getInstance(mActivity);

		if (webViewDB == null) {

			mAdWhirlLayout.rollover();

			return;

		}

		

		InneractiveAdView banner = InneractiveAdComponent.getAdView(mAdWhirlLayout.getContext(), INNERACTIVE_PUBLISHER_ID, InneractiveAdComponent.BANNER_AD_TYPE, 0);



		banner.setListener(new InneractiveAdEventsListener() {			

			@Override

			public void onReceiveAd(InneractiveAdView adView) {

				adView.setVisibility(View.VISIBLE);

				Log.v("InnerActive Banner Listener", "Received Ad.");



				mAdWhirlLayout.handler.post(new AdWhirlLayout.ViewAdRunnable(mAdWhirlLayout, adView));

				mAdWhirlLayout.adWhirlManager.resetRollover();

				mAdWhirlLayout.rotateThreadedDelayed();   				

			}

			

			@Override

			public void onFailedToReceiveAd(InneractiveAdView adView) {

				Log.v("InnerActive Banner Listener", "Failed To Receive Ad.");

				mAdWhirlLayout.rollover();

			}

			

			@Override

			public void onClickAd(InneractiveAdView adView) {

			}

		});

	}

}

Using innerActive ads on Android (with AdWhirl)

UPDATE- This post is obsolete, please check this update post.


I've decided to try using multiple ad agencies, and one of them was innerActive. I had 2 problems with innerActive: (1) They don't supply an AdWhirl adapter, (2) There's seem to be a bug in their SDK as of version 2.0625

Problem A: The bug
The function onWindowFocusChanged which runs on the UI thread calls the function setRefreshInterval which has the keyword synchronized which means it waits for a lock. The lock is taken by the ad request thread. If the onWindowsFocusChanged will be called during an ad request there will be an ANR exception due to a delay on the UI thread.

Solution:
I wrote a wrapper that seems to fix this issue.

import java.util.concurrent.locks.ReentrantLock;



import android.content.Context;

import android.util.AttributeSet;



import com.innerActive.ads.InnerActiveAdView;



public class InnerActiveAdViewFixed extends InnerActiveAdView {

private ReentrantLock mLock = new ReentrantLock();



public InnerActiveAdViewFixed(Context context) {

 super(context);

}



public InnerActiveAdViewFixed(Context context, AttributeSet attrs) {

 super(context, attrs);

}



public InnerActiveAdViewFixed(Context context, AttributeSet attrs, int defStyle) {

 super(context, attrs, defStyle);

}



// Patch

@Override

public void onWindowFocusChanged(boolean hasWindowFocus) {

 if (mLock.tryLock() == true) {

  try {

   super.onWindowFocusChanged(hasWindowFocus);

  } finally {

   mLock.unlock();

  }

 }

}



@Override

public byte start() {

 mLock.lock();

 try {

  return super.start();

 } finally {

  mLock.unlock();

 }

}

}

Problem B: No AdWhirl adapter
Solution:
I wrote a custom AdWhirl adapter for innerActive.

import android.util.Log;

import android.view.View;



import com.adwhirl.AdWhirlLayout;

import com.adwhirl.AdWhirlLayout.AdWhirlInterface;

import com.innerActive.ads.InnerActiveAdView;



public class InnerActiveCustomEvents implements AdWhirlInterface{

private AdWhirlLayout mAdWhirlLayout;



public InnerActiveCustomEvents(AdWhirlLayout adWhirlLayout) {

 super();

 this.mAdWhirlLayout = adWhirlLayout;

}



@Override

public void adWhirlGeneric() {

}



public void inneractiveBanner() {

 final InnerActiveAdViewFixed banner = new InnerActiveAdViewFixed(mAdWhirlLayout.getContext());

 banner.setVisibility(View.VISIBLE);

 banner.setRefreshInterval(0);

 banner.setScrollContainer(true);

 banner.setBgColor(0xFF000000);

 banner.setTxtColor(0xFFFFFFFF);



 banner.setListener(new InnerActiveAdView.AdEventsListener() {

 

  @Override

  public void onReceiveAd(InnerActiveAdView arg0) {

   banner.setVisibility(View.VISIBLE);

   Log.v("InnerActive Banner Listener", "Received Ad.");



   mAdWhirlLayout.handler.post(new AdWhirlLayout.ViewAdRunnable(mAdWhirlLayout, banner)); 



   mAdWhirlLayout.adWhirlManager.resetRollover();

   mAdWhirlLayout.rotateThreadedDelayed();   

  }

 

  @Override

  public void onFailedToReceiveAd(InnerActiveAdView arg0) {

   Log.v("InnerActive Banner Listener", "Failed To Receive Ad.");   

   mAdWhirlLayout.rollover();   

  }

 });   

}

}

I'm not going to write a step-by-step guide, but here is the key notes:

1. Integrate innerActive ads & make sure everything works.
2. Remove the ad view from the layout & the appropriate code the the ad view.
3. Integrate AdWhirl:
   3.1. Create an application in AdWhirl web site & get SDK Key
   3.2. Add AdWhirl JAR
   3.3. Add the AdWhirl SDK Key to the AndroidManifest.xml
   3.4. Add the com.adwhirl.AdWhirlLayout to the application layout
   
4. Add the following code (in the onCreate functions)
   AdWhirlLayout adWhirlLayout = (AdWhirlLayout)findViewById(R.id.adwhirl_layout);
   adWhirlLayout.setAdWhirlInterface(new InnerActiveCustomEvents(adWhirlLayout));
   
5. Add innerActive to AdWhirl as custom event - name: innerActive, function name: inneractiveBanner
   

Notes -

• If you're using multiple custom events, they should all be in the same function, so copy only the inneractiveBanner function.


• If you're using ProGaurd make sure you read my previous post & also add the custom events adapter & innerActive ad views to the ProGuard exceptions list.

ProGuard & AdWhirl

Compilation of an application with AdWhirl & ProGuard throws many warning, for all those AdWhirl adapter libraries not compiled with the application. I found multiple solutions online, some saying just ignore all the warning ProGuard throws:
-ignorewarnings

Don't use it - there's a better solution, ignoring only the problematic parts:
-dontwarn com.adwhirl.adapters.*

Here's the best solution for Proguard & AdWhirl as far as I know:

-dontwarn com.adwhirl.adapters.*

-keep class com.adwhirl.** { *;}
-keep public class com.adwhirl.adapters.AdMobAdapter {*;}
-keep public class com.adwhirl.adapters.ZestAdzAdapter {*;}
-keep public class com.adwhirl.adapters.MillennialAdapter {*;}
-keep public class com.admob.android.ads.** {*;}
-keep public class com.millennialmedia.android.** {*;}
-keep public class com.zestadz.android.** {*;}

-keep public class * extends Android.view.View {
public (android.content.Context);
public (android.content.Context, android.util.AttributeSet);
public (android.content.Context, android.util.AttributeSet, int);
public void set*(...);
}

-keepclassmembers class *{
public void *(android.view.View);
}

Error conversion to Dalvik format failed with error 1

UPDATE: This issue was fixed in SDK Tools 17. If you have SDK Tools 17+ the solution here will not help you.

There is a bug in the latest Android SDK Tools (revisions 12, 13, 14, 15 & 16) which cause the following error:
Error conversion to Dalvik format failed with error 1
when you try to export your Android application & got Proguard enabled.

Google will probably fix it within few days, until then DO NOT UPDATE to Android SDK Tools revision 12. Someone already opened a bug on this issue.

Solution 1 - Restore SDK Tools v11 (ugly but it works):
If you did upgrade, the instructions how to install Android SDK Tools v11 on top can be found here. I did something similar which I think is safer & easy to undo:
1. Download Android SDK Tools v11 installer from here: http://dl.google.com/android/installer_r11-windows.exe
2. Rename the existing Android SDK\Tools folder to Tools-v12
3. Open the installer using 7zip, go to folder $_OUTDIR, and copy the folder 'Tools' the the position under Android SDK folder.

Solution 2 - Upgrade to Proguard 4.7 (needs to be reapplied after every SDK Tools upgrade):
Manually update Proguard to version 4.7. You'll have to do it again after each update of the SDK Tools.
1. Download Proguard 4.7 (zip): http://sourceforge.net/projects/proguard/files/proguard/4.7/
2. Extract the folders "bin" & "lib" from the zip on top the existing Proguard installation in the SDK Tools Proguard folders "bin" & "lib", should be around here (Win x64):
C:\Program Files (x86)\Google\Android SDK\tools\proguard

Android SDK Error: Unable to resolve target 'Google Inc.:Google APIs:X'

I the past I wrote a post on a similar issue, but here goes -
There are few variants of this error message:
Unable to resolve target 'Google Inc.:Google APIs:1'
Unable to resolve target 'Google Inc.:Google APIs:2'
Unable to resolve target 'Google Inc.:Google APIs:3'
Unable to resolve target 'Google Inc.:Google APIs:4'
Unable to resolve target 'Google Inc.:Google APIs:5'
Unable to resolve target 'Google Inc.:Google APIs:6'
Unable to resolve target 'Google Inc.:Google APIs:7'
Unable to resolve target 'Google Inc.:Google APIs:8'
Unable to resolve target 'Google Inc.:Google APIs:9'
Unable to resolve target 'Google Inc.:Google APIs:10'
Unable to resolve target 'Google Inc.:Google APIs:11'
Unable to resolve target 'Google Inc.:Google APIs:12'
Unable to resolve target 'Google Inc.:Google APIs:13'
Unable to resolve target 'Google Inc.:Google APIs:14'
Unable to resolve target 'Google Inc.:Google APIs:15'
Unable to resolve target 'Google Inc.:Google APIs:16'
Unable to resolve target 'Google Inc.:Google APIs:17'
Unable to resolve target 'Google Inc.:Google APIs:18'
Unable to resolve target 'Google Inc.:Google APIs:19'
Unable to resolve target 'Google Inc.:Google APIs:20'
Unable to resolve target 'Google Inc.:Google APIs:21'

Reason: Could not find the proper Google APIs Android SDK version. If the android SDK is installed correctly the problem is that the platform SDK requested by the "AndroidManifest.xml" android:minSdkVersion is not installed. For example:
'Google Inc.:Google APIs:1' - change the Android SDK in the project properties
'Google Inc.:Google APIs:2' - change the Android SDK in the project properties
'Google Inc.:Google APIs:3' - install Google APIs SDK Android 1.5
'Google Inc.:Google APIs:4' - install Google APIs SDK Android 1.6
'Google Inc.:Google APIs:5' - install Google APIs SDK Android 2.0
'Google Inc.:Google APIs:6' - install Google APIs SDK Android 2.0.1
'Google Inc.:Google APIs:7' - install Google APIs SDK Android 2.1
'Google Inc.:Google APIs:8' - install Google APIs SDK Android 2.2
'Google Inc.:Google APIs:9' - install Google APIs SDK Android 2.3
'Google Inc.:Google APIs:10' - install Google APIs SDK Android 2.3.3
'Google Inc.:Google APIs:11' - install Google APIs SDK Android 3.0
'Google Inc.:Google APIs:12' - install Google APIs SDK Android 3.1
'Google Inc.:Google APIs:13' - install Google APIs SDK Android 3.2
'Google Inc.:Google APIs:14' - install Google APIs SDK Android 4.0
'Google Inc.:Google APIs:15' - install Google APIs SDK Android 4.0.3
'Google Inc.:Google APIs:16' - install Google APIs SDK Android 4.1
'Google Inc.:Google APIs:17' - install Google APIs SDK Android 4.2
'Google Inc.:Google APIs:18' - install Google APIs SDK Android 4.3
'Google Inc.:Google APIs:19' - install Google APIs SDK Android 4.4
'Google Inc.:Google APIs:20' - change the Android SDK in the project properties
'Google Inc.:Google APIs:21' - install Google APIs SDK Android 5.0.1

You can do this using the Android SDK Setup utility:
Option 1: Eclipse -
"Windows" -> "Android SDK and AVD Manager" -> "Available packages" -> "Third Party" -> "Google"

Option 2: Command line - start the "SDK Setup" here (x86):
C:\Program Files\Google\Android SDK\SDK Setup.exe
or here (x64)
C:\Program Files (x86)\Google\Android SDK\SDK Setup.exe

getCacheTotalSize causes NullPointerException

After starting to use a new AdMob SDK I noticed the following exception in the Android Market Developer Console:http://www.blogger.com/img/blank.gif
java.lang.NullPointerException
at android.webkit.WebViewDatabase.getCacheTotalSize(WebViewDatabase.java:735)
at android.webkit.CacheManager.trimCacheIfNeeded(CacheManager.java:557)
at android.webkit.WebViewWorker.handleMessage(WebViewWorker.java:195)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:123)
at android.os.HandlerThread.run(HandlerThread.java:60)

I solved it using a suggestion I found online. You can use other ways to disable the ads, depending which SDK you use:

WebViewDatabase webViewDB = WebViewDatabase.getInstance(this);
if (webViewDB == null) {
  View adView = findViewById(R.id.adView);
  if (adView != null)
    adView.setVisibility(View.GONE);
}

Anyhow, as far as I remember this issue was fixed in the Android OS 2.3+, so until this OS version will catch you have to disable the ads to people with this problem.

Improving Remote Desktop speed on Vista/Win7

A fellow kept complaining that his Remote Desktop (mstsc) connection to the office is super slow. I told him that I had the same problem since I moved from Windows XP to Windows 7 & he found the following solution:

Run the following command line:
netsh interface tcp set global autotuninglevel=disabled
to undo it:
netsh interface tcp set global autotuninglevel=normal

For full details check this post. It's not optimal, but it works & I didn't notice any problem since.

Memory debugging and Android application

Using DDMS

*. Compile & run the application on an enumlator in debug (e.g. Manifest.xml, android:debuggable="true").
*. open DDMS

Checking the heap status:
*. Select your application, click on 'Show heap updates' on the toolbar.
*. Play around with your application and check the results.

Checking the allocations:
*. Select the allocation tracker tab, click on 'Start Tracking'
*. Press 'Get Allocations' when you want to check the allocations.

Note: do not use both heap status & allocations - the heap monitoring will cause a lot of allocations.

Display ListPreference value in the preferences screen

One missing feature in the preferences activity in Android applications is to display the selected value of a ListPreference. By default, to view the selected value of a ListPreference you must click the preference and see what's selected in the combo box.

Here's an example how to create a preferences activity which displays the selected value of any number of ListPreferences in the activity.

public class MyPreferenceActivity extends PreferenceActivity 
  implements SharedPreferences.OnSharedPreferenceChangeListener {
    
    private ArrayList<ListPreference> mListPreferences;
    private String[] mListPreferencesKeys = new String[] {
     "prefKey1", // The 'android:key' value of the ListPreference
     "prefKey2"  // The 'android:key' value of the ListPreference
     ....
     ....
     ....
    };

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);

        addPreferencesFromResource(R.xml.preferences);
        
        mListPreferences = new ArrayList<ListPreference>();
        SharedPreferences sharedPrefs = getPreferenceManager().getSharedPreferences();
        sharedPrefs.registerOnSharedPreferenceChangeListener(this);
        for (String prefKey : mListPreferencesKeys) {
         ListPreference pref = (ListPreference)getPreferenceManager().findPreference(prefKey);
         mListPreferences.add(pref);
         onSharedPreferenceChanged(sharedPrefs, prefKey);
        }
    }

    @Override
 public void onSharedPreferenceChanged(SharedPreferences pref, String prefKey) {
     for (ListPreference listPref : mListPreferences) {
      if (listPref.getKey().equals(prefKey))
       listPref.setSummary(listPref.getEntry());
     }
 }
}

Notes:

• This code will update the values if the user selects a new value


• If you're defining preferences keys in the strings.xml file, make the mListPreferencesKeys list an integer list, and get the string at run time using 'getString'

Problem uninstalling android application

From time to time I'm facing an application that refuses to uninstall using the market. Here is the procedure I'm using (if any step works - there's no need to continue):

1. Uninstall using the Android Market application
2. Uninstall using the settings menu from the home screen: 'Menu' -> 'Settings' -> 'Applications' -> 'Manage Applications' -> select the application & uninstall.
3. Uninstall using a 3rd party tool, for example: ASTRO file manager (in the application click 'Menu' -> 'Tools' -> 'Application Manager/Backup' -> long click the application -> 'Uninstall')
4. Reboot the phone and try steps 2 & 3 again.
   

Performing research on the Android Market

There are various out there that can help perform a research on Android applications.

1. Android Market application
2. Android Market web site - Has difference number of downloads scale. While the highest value in the Market application is >250K, in the web site there are different values: 50K-100K, 100K-500K, 500K-1M, 1M-5M, 5M-10M, 10M-50M
3. AndroLib - Has application rating (stars) history
4. AndroidZoom - Has application history: release dates & events (when the application passed a download group (e.g. 10K-50K, 50K-250K).
5. AppAnnie - Market analytics (alternatives: Android App Tracker - Has application market ranking history, AndroidRanking.com - Another Android market tracking site).

If you have more interesting sites, please share.

Update Jan-2013: VisionMobile just lunched a great site for developers called Developer Economics that contain a tools atlas with all the tools that can help the developer.

onActivityResult not called in time

I wrote a small program that needed to use a preference screen. After the preferences were updated the application needs to update. To do that I used startActivityForResult to start the preferences screen, and onActivityResult to update the application.

Problem: onActivityResult wasn't called in time. Using Toast I've noticed it was called BEFORE the preferences screen opens.

Solution: In my case - the application was defined as launchMode=singleInstance, removing this put things back to place.

Note: This is true for Android 2.3.3. I didn't check the documentation to see if it's a normal behavior or an issue.

Problem uninstalling Android SDK samples

This is the second time it happens to me so I've decided to write a post about it.

Problem: in the "Android SDK and AVD Manager"/"SDK Setup" selecting "Samples for SDK ..." package and clicking 'delete' deletes the content of the samples folder, but does not remove the entry from the "Android SDK and AVD Manager".

Solution:
1. Close the "Android SDK and AVD Manager"/"SDK Setup" (close the eclipse if it's running).
2. Delete the SDK specific samples folder. If the folder is locked kill any 'java.exe' process running (or reboot).
3. Restart the "Android SDK and AVD Manager"/"SDK Setup"

Distance on earth calculation in SQL

Here's how to create an SQL function to calculate the distance (in KM) between to points on earth. This is an estimation since the distance is calculated as if the earth was round, but it's close enough for most purposes:

CREATE FUNCTION dist (@latFrom float, @longFrom float,@latTo float, @longTo float)
        RETURNS float
AS
BEGIN
        DECLARE @Distance float
        SET @Distance =
                (SIN(RADIANS(@latFrom)) *
                SIN(RADIANS(@latTo)) +
                COS(RADIANS(@latFrom)) *
                COS(RADIANS(@latTo)) *
               
                COS(RADIANS(@longFrom - @longTo)))
 
        SET @Distance = (DEGREES(ACOS(@Distance))) * 69.09 * 1.609344
       
        RETURN(@Distance)
END

TimePicker & DatePicker with keyboard edit problem

Problem: When using the TimePicker & DatePicker objects in an AlertDialog (and probably anywhere else), the time & date seem to not to update when the user is editing the value using a keyboard (or soft keyboard).

Solution: Before calling the functions to get the date & time from the objects call TimePicker or DatePicker clearFocus() method.

I found few threads talking about this problem without a solution. I found the solution when I looked at the TimePickerDialog source code which did not suffer from this problem.

Android SQLite Alter Table

When upgrading SQLite database sometimes you need to change the tables scheme. The problem is that SQLite Alter Table SQL format does not support all the options you might need (specifically - dropping columns, changing column types, renaming columns).

One way around this issue is to create a new table & copy the data to the new table. There are few options here, but the best way (I think) is as follows:
1. Create new table in the new format.
2. Copy the data.
3. Drop the original table
4. Rename the new table

For example:

BEGIN TRANSACTION;
CREATE TABLE t_new(a,b);
INSERT INTO t_new SELECT a,b FROM t;
DROP TABLE t;
ALTER TABLE t_new RENAME TO t;
COMMIT;

IMPORTANT: As far as I know 'execSQL' of 'SQLiteDatabase' will NOT run multiple commands. You should run each SQL command in a separate 'execSQL' call (the transaction can be handled from the SQLiteDatabase, not need for explicit SQL commands).

Build.VERSION.SDK_INT on Android 1.5

It won't be long before all Android 1.5 devices will disappear, until then it's a good idea to support them. The problem is the 'Build.VERSION.SDK_INT' was introduced only on Android 1.6, and before that there were only string values.

Here's a work-around this issue - it will return the correct value for Android 1.5+:

public static int getSdkInt() {
   if (Build.VERSION.RELEASE.startsWith("1.5"))
       return 3;
 
   try {
       return HelperInternal.getSdkIntInternal();
   } catch (VerifyError e) {
       return 3;
   }
}

private static class HelperInternal {
   private static int getSdkIntInternal() {
       return Build.VERSION.SDK_INT;              
   }
}

Note: the internal class is required since the Java virtual machine checks if a class is valid before loading it. In our case, a java VM running Android 1.5 will not be able to load Build.VERSION.SDK_INT and will crash.

UPDATE: Added VerifyError catch to prevent exceptions on some weird Android 1.5 devices.