This video shows the complete end to end steps to create a simple stored procedure in MS SQL server and call the same from the Android App.
In the first part of the video it shows how to create a simple stored procedure using MS SQL Server Management Studio (SSMS). It uses a test table to create procedure. It then tests the Stored procedure by framing the respective EXECUTE query to call the stored procedure.
In the second part, it shows how to create a simple Android App to call the stored procedure on the MS SQL Server through TCP/IP connection. It uses the concepts shown in the part-1 of this video – How to connect to MS SQL Server database from your Android App? Link below:
https://programmerworld.co/android/how-to-connect-to-ms-sql-server-database-from-your-android-app-complete-steps/
In the Android App, it uses a very simple layout to display the output (that is the number of row count received). It also shows the relevant permissions needed to be granted in Manifest file and the gradle file update with the required implementation.
I hope you liked this video, for any questions, suggestions or appreciation please contact us at: https://programmerworld.co/contact/ or email at: programmerworld1990@gmail.com
package com.programmerworld.mssqlstoredprocedureapp;
import androidx.appcompat.app.AppCompatActivity;
import androidx.core.app.ActivityCompat;
import android.Manifest;
import android.content.pm.PackageManager;
import android.os.Bundle;
import android.os.StrictMode;
import android.view.View;
import android.widget.TextView;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
public class MainActivity extends AppCompatActivity {
private TextView textView;
private static String ip = "192.168.1.137";
private static String port = "1433";
private static String Classes = "net.sourceforge.jtds.jdbc.Driver";
private static String database = "testDatabase";
private static String username = "test";
private static String password = "test";
private static String url = "jdbc:jtds:sqlserver://"+ip+":"+port+"/"+database;
private Connection connection = null;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
ActivityCompat.requestPermissions(this, new String[]{Manifest.permission.INTERNET}, PackageManager.PERMISSION_GRANTED);
textView = findViewById(R.id.textView);
StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
StrictMode.setThreadPolicy(policy);
try {
Class.forName(Classes);
connection = DriverManager.getConnection(url, username,password);
textView.setText("SUCCESS");
} catch (ClassNotFoundException e) {
e.printStackTrace();
textView.setText("ERROR");
} catch (SQLException e) {
e.printStackTrace();
textView.setText("FAILURE");
}
}
public void buttonMS_SQL_StoredProcedure(View view){
if (connection == null){
textView.setText("SQL Query Failed");
return;
}
try {
CallableStatement callableStatement = connection.prepareCall("{call TestSP(?,?)}");
callableStatement.setInt(1, 2);
callableStatement.registerOutParameter(2, Types.INTEGER);
callableStatement.execute();
callableStatement.getMoreResults();
int intOutput = callableStatement.getInt(2);
textView.setText(Integer.toString(intOutput));
callableStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.programmerworld.mssqlstoredprocedureapp">
<uses-permission android:name="android.permission.INTERNET"/>
<application
android:allowBackup="true"
android:icon="@mipmap/ic_launcher"
android:label="@string/app_name"
android:roundIcon="@mipmap/ic_launcher_round"
android:supportsRtl="true"
android:theme="@style/AppTheme">
<activity android:name=".MainActivity">
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>
</manifest>
<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".MainActivity">
<TextView
android:id="@+id/textView"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Hello World!"
app:layout_constraintBottom_toBottomOf="parent"
app:layout_constraintLeft_toLeftOf="parent"
app:layout_constraintRight_toRightOf="parent"
app:layout_constraintTop_toTopOf="parent" />
<Button
android:id="@+id/button"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginStart="96dp"
android:layout_marginTop="100dp"
android:onClick="buttonMS_SQL_StoredProcedure"
android:text="@string/stored_procedure_ms_sql"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent" />
</androidx.constraintlayout.widget.ConstraintLayout>apply plugin: 'com.android.application'
android {
compileSdkVersion 29
buildToolsVersion "29.0.3"
defaultConfig {
applicationId "com.programmerworld.mssqlstoredprocedureapp"
minSdkVersion 26
targetSdkVersion 29
versionCode 1
versionName "1.0"
testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
}
buildTypes {
release {
minifyEnabled false
proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
}
}
}
dependencies {
implementation fileTree(dir: "libs", include: ["*.jar"])
implementation 'androidx.appcompat:appcompat:1.1.0'
implementation 'androidx.constraintlayout:constraintlayout:1.1.3'
testImplementation 'junit:junit:4.12'
androidTestImplementation 'androidx.test.ext:junit:1.1.1'
androidTestImplementation 'androidx.test.espresso:espresso-core:3.2.0'
implementation 'net.sourceforge.jtds:jtds:1.3.1'
}
<resources>
<string name="app_name">MS SQL Stored Procedure App</string>
<string name="stored_procedure_ms_sql">Stored Procedure MS SQL</string>
</resources>
// Top-level build file where you can add configuration options common to all sub-projects/modules.
buildscript {
repositories {
google()
jcenter()
}
dependencies {
classpath "com.android.tools.build:gradle:4.0.1"
// NOTE: Do not place your application dependencies here; they belong
// in the individual module build.gradle files
}
}
allprojects {
repositories {
google()
jcenter()
}
}
task clean(type: Delete) {
delete rootProject.buildDir
}
Below is the SQL Query used in the Microsoft SQL Server Management Studio (SSMS):
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE TestSP
-- Add the parameters for the stored procedure here
@C2_Value int = 0,
@return_count INT OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select * from TEST_TABLE where C2 = @C2_Value;
SELECT @return_count=@@ROWCOUNT;
END
GO
DECLARE @Count INT;
EXECUTE TestSP 2, @Count OUTPUT;
Select @Count as 'Number of counts';select * from TEST_TABLE;Some Images for reference
