Java Question Connect directly to remote MS SQL DB

[GuerillaProgrammer]

I want to use B4A to connect directly to a remote MS SQL DB hosted on Amazon AWS.

1). I do not want to use a web service or other third app to pass requests.
2). I do not care about security consequences for now.
3). I do not want some long convoluted SQL tutorial on how to use the connection after it's connected.

I want the instructions that tell me what to do to include the jdbc driver in my B4A project, and how to manage the #includes and initializations so that I can get a connection by passing my DBs url, my login credentials, and the password.

When I search for this, I find all kinds of info for MySql, SQLite, Oracle, and libraries and methods for using a webservice or mini-webserver.

NONE OF THAT HELPS ME!!!

Sorry for shouting. My frustration level with what should be a very straightforward thing being a hopeless maze of complexity aggravated by unnecessary steps and complication has almost pushed me over the edge. I came to B4A for the simplicity. If I wanted to bang my head against a wall til bloody, I'd have stuck with IntelliJ


Going on 2 years now. DOES ANYBODY HAVE AN ANSWER???????

 

[keirS]

There is a JDBC MS SQL libary.

 

[GuerillaProgrammer]

Two different ways I've attempted this

//##################  Model 1  ##########################################################
/**
*This set of code and errors is using the methodology for creating a library
*(Erel's excellent youtube instructional video)
*It works perfectly up until the line in the tester class which reads as:
*"strNamesRA = sql_Tool.testQuery()"
*/


//This is the code for the library which accesses the remote SQL installation,
//and returns the data in an array of strings.
//It also prints the names to the standard out.
//----------------------------------------------------------------------------------
package com.MyDomainName.Examples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.lang.Class.*;

import anywheresoftware.b4a.BA.ShortName;

@ShortName("SQL_Access1")
public class SQL_Access1
   { 
   static final String DRIVER_CLASS = "net.sourceforge.jtds.jdbc.Driver";
 
   static final String DB_URL = "Remote SQL URL:1433/TableName";
   static final String DB_USER = "The Login";
   static final String DB_PASS = "The Password";
 
   /**
    * testQuery accesses the SQL db and returns a string array containing results to caller. test3
    * @throws ClassNotFoundException
    * @throws SQLException
    */
   public static String[] testQuery() throws ClassNotFoundException, SQLException
     {
     int numRows = 0;
   
     String[] strNamesRA;
   
    Class.forName(DRIVER_CLASS);
    Connection jdbcConnection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);

     Statement stmt = jdbcConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
   
     ResultSet rs = stmt.executeQuery("select * from TableName");
   
     //get rowcount to create array of correct size
     if (rs.last())
       {
      numRows = rs.getRow();
     
      strNamesRA = new String[numRows];
     
      // Return to beginning
      rs.beforeFirst();
       } 
     
    strNamesRA = new String[numRows];
   
    numRows = 0;//Re-use var 
    while (rs.next())
       {
//    System.out.println(rs.getString("FN"));
   
    strNamesRA[numRows] = rs.getString("FN");
    numRows++;
       } 
    return strNamesRA;
     } 
   }
//Library code for SQLAccess1.lib ends here
//----------------------------------------------------------------------------------

//Code for testing the library
//==================================================================================
Sub Process_Globals
   Dim sql_Tool As SQL_Access1
End Sub

Sub Activity_Create(FirstTime As Boolean)
   Dim strNamesRA() As String
 
   strNamesRA = sql_Tool.testQuery()
End Sub
//Testing code ends here.
//==================================================================================

//Error log for the run.
//__________________________________________________________________________________
LogCat connected to: 320060ca13f5a000
--------- beginning of /dev/log/system
--------- beginning of /dev/log/main
** Service (starter) Create **~i:** Service (starter) Start **
** Activity (main) Create, isFirst = true **
Error occurred on line: 32 (Main)
java.lang.ClassNotFoundException: net.sourceforge.jtds.jdbc.Driver
at java.lang.Class.classForName(Native Method)
at java.lang.Class.forName(Class.java:251)
at java.lang.Class.forName(Class.java:216)
at com.CodeSmith.Examples.SQL_Access1.testQuery(SQL_Access1.java:46)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:702)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:336)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:246)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:134)
at b4a.example.main.afterFirstLayout(main.java:102)
at b4a.example.main.access$000(main.java:17)
at b4a.example.main$WaitForLayout.run(main.java:80)
at android.os.Handler.handleCallback(Handler.java:733)
at android.os.Handler.dispatchMessage(Handler.java:95)
at android.os.Looper.loop(Looper.java:146)
at android.app.ActivityThread.main(ActivityThread.java:5653)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1291)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1107)
at dalvik.system.NativeStart.main(Native Method)
Caused by: java.lang.NoClassDefFoundError: net/sourceforge/jtds/jdbc/Driver
... 24 more
Caused by: java.lang.ClassNotFoundException: Didn't find class "net.sourceforge.jtds.jdbc.Driver" on path: DexPathList[[zip file "/data/app/b4a.example-24.apk"],nativeLibraryDirectories=[/data/app-lib/b4a.example-24, /vendor/lib, /system/lib, /system/lib/arm]]
at dalvik.system.BaseDexClassLoader.findClass(BaseDexClassLoader.java:56)
at java.lang.ClassLoader.loadClass(ClassLoader.java:497)
at java.lang.ClassLoader.loadClass(ClassLoader.java:457)
... 24 more

//End of error log
//__________________________________________________________________________________
//###############  End Model 1  ########################################################

 

[GuerillaProgrammer]

//############### Model 2 #############################################################
/**
*This set of code is using the MS SQL library kindly suggested by keirS on this topic page:
*https://www.b4x.com/android/forum/threads/connect-directly-to-remote-ms-sql-db.67927/
*
*It works perfectly, with no crashes or warnings whatsoever (not even an exception),
*except that there is no data returned in the List variable "L" as one can see from the watch window
*pictured below.
*/

//------------------------------------------------------------
//Manifest Editor Entry

AddManifestText(
<uses-sdk android:minSdkVersion="4" android:targetSdkVersion="14"/>
<supports-screens android:largeScreens="true"
android:normalScreens="true"
android:smallScreens="true"
android:anyDensity="true"/>)
AddPermission("android.permission.INTERNET")
AddPermission("android.permission.ACCESS_NETWORK_STATE")
SetApplicationAttribute(android:icon, "@drawable/icon")
SetApplicationAttribute(android:label, "$LABEL$")
//------------------------------------------------------------

#Region  Project Attributes
   #ApplicationLabel: B4A Example
   #VersionCode: 1
   #VersionName:
   'SupportedOrientations possible values: unspecified, landscape or portrait.
   #SupportedOrientations: unspecified
   #CanInstallToExternalStorage: False
#End Region

#Region  Activity Attributes
   #FullScreen: False
   #IncludeTitle: True
#End Region

Sub Process_Globals
End Sub

Sub Globals
   Dim db As MSSQL
End Sub

Sub Activity_Create(FirstTime As Boolean)
  Dim L As  List
'  db = setDatabase("Server IPNUMBER (not name)","databasename","username","password")
  db.setDatabase("Remote SQL Server URL:1433/","TableName","Login","Password")//Redacted for security
'
  Try
  'select query
   
       //I've tried with "TableName", "[dbo].TableName", and "[DB_Name].[dbo].[TableName]"
       //No difference
  L = db.Query("select * from TableName")  '--> return rows as a LIST
       
       
     //=========================================================
  L = db.TableList '--> return table list in database
     //=========================================================
       
       

  ' or update query  (True: If need transactional query)
     ' db.ExecuteNonQuery("update tablename set fieldname = 'forexample' where key=1",True)
   
  Catch
  Msgbox(LastException.Message,"Error Occured")
  End Try
End Sub

//############### End Model 2 #########################################################

I have this working PERFECTLY in IntelliJ using the AsynchTask methodology, but I'm sick to DEATH of their "Cat's ball of yarn" IDE.


Erel, you wrote to me once at (https://www.b4x.com/android/forum/t...ct-to-any-remote-db.31540/page-14#post-297277)

(Caps and bolding are mine for emphasis)
"ANYTHING THAT YOU CAN DO IN JAVA CAN BE DONE WITH B4A.If the jdbc driver is supported by Android then you can use it in B4A (with a wrapper).

In most cases it is not recommended to connect directly to the database from the mobile app for various reasons including security.

Use the forum search to find relevant libraries: https://www.b4x.com/android/forum/pages/results/?query=mssql&page=1&prefix=1"


Now I know all of the security caveats, but
a). I don't control the Amazon AWS server, and can't put apps or servelets on it, and
b) I don't give a rat's ass about the credentials being in plain text.

If someone else steals or finds the tablets or phones I deploy this to, changing the credentials on the server is a quick and sinplefix,
and there will be bigger problems than a competitor having a useless login.

This will be used only for private use, and will never be in the app store.


Isn't there a solution that works in B4A without fighting with the Android mess to get there? Isn't that the B4A raison d`etre?
Isn't that what you meant by "Anything that you can do in Java can be done with B4A."

You have always been helpful, responsive, and probably the smartest person I've ever interacted with. (I was a reactor operator in the US Navy, A programmer for Lucent Technologies for 4 years, and worked for 4 years as an architect\developer for a garage startup that is now a multi-state, multi-million dollar concern with huge customers in a huge customer base 10 years later. I say that all not to toot my horn, but so you understand I'm qualified to say it, and not saying "probably the smartest person... " lightly.)

B4A is great. I have created several professional apps for Android using it and SQLite, but this JDBC connectivity through Android is the biggest PITA I've encountered in, literally, years.

I get the feeling that you're holding back, but I don't understand why.

Please help me figure this out so I don't have to use the Android mess.

 

[Erel]

You need to add a reference to the JDBC library. You can either add it in the library with @DependsOn or from B4A with #AdditionalJar.

 

[GuerillaProgrammer]

Hi Erel. I will try this as soon as I can and get back to you.

Is there somewhere that I can view the B4A build hierarchy.

If you don't mind, I'd like to know why the jdbc...jar being included in my library's classpath works when tested with a java test class in eclipse, but not on the B4A side.

I suspected something like this, but since both the B4A library compiler and the "MS SQL" library written by hasanaydin52 are black boxes to me, I didn't know where else to look.

Thank you.

 

[Erel]

If you don't mind, I'd like to know why the jdbc...jar being included in my library's classpath works when tested with a java test class in eclipse, but not on the B4A side.

There is no such thing as library's classpath. The references are stored in the Eclipse project.

You need to use @DependsOn to tell the IDE to include other jars. Those jars should be located in the additional libraries folder.

https://www.b4x.com/android/forum/t...-libraries-without-eclipse.29918/#post-173756

 

[DonManfred]

but not on the B4A side.

because you did not copy the jar (which you used in eclipse) to your additionallibs folder.
b4a should raise an exception when you used a dependson in your wrappercode and it does not find the jar...

Edit: After reading your unreadable code (USE CODE TAGS WHEN POSTING CODE!):
Note that you need to add an dependson directive to your wrappercode to get it to work

[..]
@ShortName("SQL_Access1")
@DependsOn(values={"jarfilenamewithoutextension"})

 

[GuerillaProgrammer]

because you did not copy the jar (which you used in eclipse) to your additionallibs folder.
b4a should raise an exception when you used a dependson in your wrappercode and it does not find the jar...

Edit: After reading your unreadable code (USE CODE TAGS WHEN POSTING CODE!):
Note that you need to add an dependson directive to your wrappercode to get it to work

I did put the lib in my additional libraries folder, if you mean the folder where the library compiler puts its output. It is there as we type.

As for my unreadable code, I didn't find the tag for marking code blocks in the second level menu under the icon above, and it took some time to create the document, so I was in no mood to fight one more piece of complication at the end of a long day. My apologies.

If you cut and paste it into notepad++ or any modern IDE that understands Java, it will do the syntax highlighting for you, and if you've set your environment properly, it will probably indent it too.

Thanks for the suggestion. I will give it a try.

 

[DonManfred]

and your code looks like

dim a As String = "Hello world!"

 

[GuerillaProgrammer]

and your code looks like

dim a As String = "Hello world!"

As for my unreadable code, I didn't find the tag for marking code blocks in the second level menu under the icon above, and it took some time to create the document, so I was in no mood to fight one more piece of complication at the end of a long day. My apologies.

I found it as I was replying to you, mostly because you're telling me about it made me take more than a cursory look. Tomorrow I will edit my post. The majority of the "unreadability" is because there are more comments than code. Even poorly formatted the code alone is pretty "Hello world".

Thanks again.