Hot questions for Using Azure in database

Question:

I need to add sqljdbc42.jar library to classpath so I can connect to Azure SQL Database. How (if possible) can I do that in Azure Web Service - Web App (Java with Tomcat server). Is there any other way to add JDBC library?

Server error:

    java.sql.SQLException: No suitable driver found for jdbc:sqlserver://XXXXXXX.database.windows.net:1433;database=XXXXXX;user=XXXXX@XXXXX;password=XXXXXXXX;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
    at java.sql.DriverManager.getConnection(DriverManager.java:689)
    at java.sql.DriverManager.getConnection(DriverManager.java:270)
    at common.DBConnection.<init>(DBConnection.java:23)
    at servlets.Login.doPost(Login.java:77)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:521)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1096)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:674)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1500)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1456)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)
Dec 30, 2015 3:04:48 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [servlets.Login] in context with path [/deviceweb] threw exception
java.lang.NullPointerException
    at common.DBConnection.addParameter(DBConnection.java:33)
    at servlets.Login.doPost(Login.java:78)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:521)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1096)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:674)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1500)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1456)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)

Web page error:

    type: Exception report

    message:

    description: The server encountered an internal error that prevented it from fulfilling this request.

    exception:

    java.lang.NullPointerException
        common.DBConnection.addParameter(DBConnection.java:33)
        servlets.Login.doPost(Login.java:78)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
        org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

note: The full stack trace of the root cause is available in the Apache Tomcat/8.0.30 logs.

Connection string:

String connectionString = "jdbc:sqlserver://testnabaza.database.windows.net:1433;" + "database=testnabaza;"
            + "user=butterbeast@testnabaza;" + "password=1!qQaaaaaa;" + "encrypt=true;"
            + "trustServerCertificate=false;" + "hostNameInCertificate=*.database.windows.net;" + "loginTimeout=30;";

Copied from Portal: jdbc:sqlserver://testnabaza.database.windows.net:1433;database=testnabaza;user=butterbeast@testnabaza;password={your_password_here};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;


Answer:

Easiest way around this was to simply add the sqljdbc42.jar file (probably located in C:\Program Files\Microsoft SQL Server JDBC Driver\sqljdbc_3.X\enu) to the \lib\ext directory of my local Java installation before creating the .zip file.

for best help use the given link https://dzone.com/articles/java-access-sql-azure-jdbc

Question:

I am trying to get posts of my friends like social networking. Since, I cannot use the things like "INNER JOIN" in client side with Easy tables, I have to use custom api. Here is my approach:

Android client:

List<Pair<String, String>> params = new ArrayList<Pair<String, String>>();
    params.add(new Pair("id", user.getId()));
    params.add(new Pair("otherParam", otherParam));

    client.getClient().invokeApi("getFeed", "POST", params, new ApiJsonOperationCallback() {
        @Override
        public void onCompleted(JsonElement jsonElement, Exception exception, ServiceFilterResponse response) {
            System.out.println("exception: " + exception.toString());
            GsonBuilder gsonb = new GsonBuilder();
            Gson gson = gsonb.create();

            JsonArray array = jsonElement.getAsJsonArray();
            for (int i = 0; i < array.size(); i++) {
                postList.add(gson.fromJson(array.get(i).getAsJsonObject().toString(), Post.class));
            }
        }
    });

feed.js:

var api = {
post: (request, response, next) => {
    var query = {
        sql: 'SELECT * FROM Posts INNER JOIN Friends ON Posts.userId = Friends.followingId WHERE Friends.followingId = @id ORDER BY Posts.createdAt DESC',
        parameters: [
            { id: 'id', otherParam: 'otherParam' }
        ]
    };

    request.azureMobile.data.execute(query)
        .then(function (results) {
            response.json(results);
        });
    }
};

module.exports = api;

I am not even sure whether this is a correct way to do it or we can access tables in easy tables like this, but there is not any detail in exception print. Exception is "Error while processing request". Thanks in advance.


Answer:

Your code is very close - it is just the specification of parameters to the SQL statement that is not quite right. You need to specify parameters in the following format:

var query = {
    sql: 'SELECT * FROM Posts INNER JOIN Friends ON Posts.userId = Friends.followingId WHERE Friends.followingId = @id ORDER BY Posts.createdAt DESC',
    parameters: [
        { name: 'id', value: 'id' }, 
        { name: 'otherParam', value: 'otherParam' }
    ]
};

API reference for this is at http://azure.github.io/azure-mobile-apps-node/module-azure-mobile-apps_src_data_execute.html.

To get a bit more debugging information out of the server, you can bump up the logging level. If you are running locally, run the app with an additional command line parameter:

node --debug app.js ---logging.level silly

If you are running hosted on Azure, you can set the log level for Application Logging (filesystem) to verbose and open the log stream. This will show you the exact SQL statements being executed and the parameters.

Question:

I try to connect to Azure SQL with MS JDBC driver:

import java.sql.*;

public class ExampleSQLJDBC {

    public static void main(String[] args) {

        // Setting.
        String connectionUrl = "jdbc:sqlserver://SERVER.database.windows.net:1433;database=DATABASE;encrypt=true;trustServerCertificate=true;";
        String user = "USER@SERVER";
        String pass = "PASSWORD";

        // Declare the JDBC object.
        Connection conn = null;

        try {
            // Establish the connection.
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            conn = DriverManager.getConnection(connectionUrl, user, pass);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}

But I am getting:

com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Broken pipe ClientConnectionId:742ba7b5-43bb-441b-8e8d-bbb9dc5fa8ce".
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2226)
    at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1756)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1803)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1454)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1285)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:700)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1131)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at run.ExampleSQLJDBC.main(ExampleSQLJDBC.java:21)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)

Interestingly, I can connect to the database from the same computer with Navicat. Hence, the problem should not be in the setting of firewalls.

Metadata:

  • Server: Azure V12
  • Driver: 6.0 (sqljdbc42.jar)
  • JRE: 1.8.0_72-b15 (from Oracle)
  • _JAVA_OPTIONS: -Djsse.enableCBCProtection=false
  • security.provider.1: sun.security.provider.Sun
  • OS: OS X 10.11.5

How can I connect to Azure SQL from Java?

Edit:

If I compile the code from Peter Pan with javac 1.8.0_92 on OS X and execute the bytecode with java 1.8.0_92 on OS X, the error does not disappear.

Edit 2: When I debuged the SSL connection details with -Djavax.net.debug=ssl:handshake:verbose:

trigger seeding of SecureRandom
done seeding SecureRandom
Ignoring unavailable cipher suite: TLS_DHE_DSS_WITH_AES_256_GCM_SHA384
Ignoring unavailable cipher suite: TLS_RSA_WITH_AES_256_CBC_SHA
Ignoring unavailable cipher suite: TLS_DHE_RSA_WITH_AES_256_GCM_SHA384
Ignoring unavailable cipher suite: TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA
Ignoring unavailable cipher suite: TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384
Ignoring unavailable cipher suite: TLS_RSA_WITH_AES_256_CBC_SHA256
Ignoring unavailable cipher suite: TLS_DHE_DSS_WITH_AES_256_CBC_SHA
Ignoring unavailable cipher suite: TLS_ECDH_ECDSA_WITH_AES_256_GCM_SHA384
Ignoring unavailable cipher suite: TLS_ECDH_RSA_WITH_AES_256_CBC_SHA384
Ignoring unavailable cipher suite: TLS_RSA_WITH_AES_256_GCM_SHA384
Ignoring unavailable cipher suite: TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA384
Ignoring unavailable cipher suite: TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384
Ignoring unavailable cipher suite: TLS_ECDH_RSA_WITH_AES_256_CBC_SHA
Ignoring unavailable cipher suite: TLS_ECDH_RSA_WITH_AES_256_GCM_SHA384
Ignoring unavailable cipher suite: TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384
Ignoring unavailable cipher suite: TLS_DHE_RSA_WITH_AES_256_CBC_SHA256
Ignoring unavailable cipher suite: TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA
Ignoring unavailable cipher suite: TLS_DHE_DSS_WITH_AES_256_CBC_SHA256
Ignoring unavailable cipher suite: TLS_DHE_RSA_WITH_AES_256_CBC_SHA
Ignoring unavailable cipher suite: TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA
Ignoring unavailable cipher suite: TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
Allow unsafe renegotiation: false
Allow legacy hello messages: true
Is initial handshake: true
Is secure renegotiation: false
Ignoring unsupported cipher suite: TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256 for TLSv1
Ignoring unsupported cipher suite: TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256 for TLSv1
Ignoring unsupported cipher suite: TLS_RSA_WITH_AES_128_CBC_SHA256 for TLSv1
Ignoring unsupported cipher suite: TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256 for TLSv1
Ignoring unsupported cipher suite: TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256 for TLSv1
Ignoring unsupported cipher suite: TLS_DHE_RSA_WITH_AES_128_CBC_SHA256 for TLSv1
Ignoring unsupported cipher suite: TLS_DHE_DSS_WITH_AES_128_CBC_SHA256 for TLSv1
Ignoring unsupported cipher suite: TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256 for TLSv1.1
Ignoring unsupported cipher suite: TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256 for TLSv1.1
Ignoring unsupported cipher suite: TLS_RSA_WITH_AES_128_CBC_SHA256 for TLSv1.1
Ignoring unsupported cipher suite: TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256 for TLSv1.1
Ignoring unsupported cipher suite: TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256 for TLSv1.1
Ignoring unsupported cipher suite: TLS_DHE_RSA_WITH_AES_128_CBC_SHA256 for TLSv1.1
Ignoring unsupported cipher suite: TLS_DHE_DSS_WITH_AES_128_CBC_SHA256 for TLSv1.1
%% No cached client session
*** ClientHello, TLSv1.2
RandomCookie:  GMT: 1466081162 bytes = { 238, 135, 254, 162, 132, 164, 75, 240, 96, 107, 17, 103, 60, 109, 229, 149, 31, 110, 218, 87, 49, 29, 121, 114, 115, 6, 44, 32 }
Session ID:  {}
Cipher Suites: [TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256, TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256, TLS_RSA_WITH_AES_128_CBC_SHA256, TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256, TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256, TLS_DHE_RSA_WITH_AES_128_CBC_SHA256, TLS_DHE_DSS_WITH_AES_128_CBC_SHA256, TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA, TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA, TLS_RSA_WITH_AES_128_CBC_SHA, TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA, TLS_ECDH_RSA_WITH_AES_128_CBC_SHA, TLS_DHE_RSA_WITH_AES_128_CBC_SHA, TLS_DHE_DSS_WITH_AES_128_CBC_SHA, TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256, TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256, TLS_RSA_WITH_AES_128_GCM_SHA256, TLS_ECDH_ECDSA_WITH_AES_128_GCM_SHA256, TLS_ECDH_RSA_WITH_AES_128_GCM_SHA256, TLS_DHE_RSA_WITH_AES_128_GCM_SHA256, TLS_DHE_DSS_WITH_AES_128_GCM_SHA256, TLS_ECDHE_ECDSA_WITH_3DES_EDE_CBC_SHA, TLS_ECDHE_RSA_WITH_3DES_EDE_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA, TLS_ECDH_ECDSA_WITH_3DES_EDE_CBC_SHA, TLS_ECDH_RSA_WITH_3DES_EDE_CBC_SHA, SSL_DHE_RSA_WITH_3DES_EDE_CBC_SHA, SSL_DHE_DSS_WITH_3DES_EDE_CBC_SHA, TLS_EMPTY_RENEGOTIATION_INFO_SCSV]
Compression Methods:  { 0 }
Extension elliptic_curves, curve names: {secp256r1, sect163k1, sect163r2, secp192r1, secp224r1, sect233k1, sect233r1, sect283k1, sect283r1, secp384r1, sect409k1, sect409r1, secp521r1, sect571k1, sect571r1, secp160k1, secp160r1, secp160r2, sect163r1, secp192k1, sect193r1, sect193r2, secp224k1, sect239k1, secp256k1}
Extension ec_point_formats, formats: [uncompressed]
Extension signature_algorithms, signature_algorithms: SHA512withECDSA, SHA512withRSA, SHA384withECDSA, SHA384withRSA, SHA256withECDSA, SHA256withRSA, SHA224withECDSA, SHA224withRSA, SHA1withECDSA, SHA1withRSA, SHA1withDSA
Extension server_name, server_name: [type=host_name (0), value=hlt3qa7607.database.windows.net]
***
main, WRITE: TLSv1.2 Handshake, length = 233
main, called close()
main, called closeInternal(true)
main, SEND TLSv1.2 ALERT:  warning, description = close_notify
main, WRITE: TLSv1.2 Alert, length = 2
main, called closeSocket(true)
main, waiting for close_notify or alert: state 5
main, received EOFException: ignored
main, called closeInternal(false)
main, close invoked again; state = 5
main, handling exception: java.io.IOException: SQL Server did not return a response. The connection has been closed. ClientConnectionId:1eb03958-1566-4844-b842-e020f0c36121
main, called closeSocket()
com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "SQL Server did not return a response. The connection has been closed. ClientConnectionId:1eb03958-1566-4844-b842-e020f0c36121".... 

Edit 3: After setting the driver to log all:

Jun 17, 2016 9:26:37 AM com.microsoft.sqlserver.jdbc.SQLServerDriver:1 connect
FINER: ENTRY Arguments not traced.
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:serverName Value:hlt3qa7607.database.windows.net
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:portNumber Value:1433
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:databaseName Value:DATABASE
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:encrypt Value:true
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:trustServerCertificate Value:true
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection <init>
FINE: ConnectionID:1 created by (SQLServerDriver:1)
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection login
FINER: ConnectionID:1 Start time: 1466148398058 Time out time: 1466148413058 Timeout Unit Interval: 1200
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection login
FINE: ConnectionID:1 This attempt server name: hlt3qa7607.database.windows.net port: 1433 InstanceName: null useParallel: false
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection login
FINE: ConnectionID:1 This attempt endtime: 1466148399258
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection login
FINE: ConnectionID:1 This attempt No: 0
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection connectHelper
FINE: ConnectionID:1 Connecting with server: hlt3qa7607.database.windows.net port: 1433 Timeout slice: 1194 Timeout Full: 15
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.TDSChannel open
FINER: TDSChannel (ConnectionID:1): Opening TCP socket...
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
FINER: ConnectionID:1 ClientConnectionId: 1a3032c8-493d-4f99-8641-d0f02211d82c Requesting encryption level:ON
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
FINER: ConnectionID:1 ClientConnectionId: 1a3032c8-493d-4f99-8641-d0f02211d82c ActivityId 866c0053-25be-4eb2-90c7-a40b5b0535a4-1
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.TDSChannel logPacket
FINEST: /192.168.1.186:63328 SPID:0 ConnectionID:1 ClientConnectionId: 1a3032c8-493d-4f99-8641-d0f02211d82c Prelogin request                                         ...
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.TDSChannel logPacket
FINEST: /192.168.1.186:63328 SPID:0 ConnectionID:1 ClientConnectionId: 1a3032c8-493d-4f99-8641-d0f02211d82c Prelogin response
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
FINE: ConnectionID:1 ClientConnectionId: 1a3032c8-493d-4f99-8641-d0f02211d82c Server returned major version:12
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
FINER: ConnectionID:1 ClientConnectionId: 1a3032c8-493d-4f99-8641-d0f02211d82c Negotiated encryption level:ON
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
FINER: ConnectionID:1 ClientConnectionId: 1a3032c8-493d-4f99-8641-d0f02211d82c Ignoring prelogin response option:5
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL
FINER: TDSChannel (ConnectionID:1) Enabling SSL...
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL
FINER: TDSChannel (ConnectionID:1) SSL handshake will trust any certificate
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL
FINEST: TDSChannel (ConnectionID:1) Getting TLS or better SSL context
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL
FINEST: TDSChannel (ConnectionID:1) Initializing SSL context
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL
FINEST: TDSChannel (ConnectionID:1) Creating SSL socket
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel$ProxySocket getInputStream
FINEST: TDSChannel (ConnectionID:1) (ProxySocket): Getting input stream
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel$ProxySocket getOutputStream
FINEST: TDSChannel (ConnectionID:1) (ProxySocket): Getting output stream
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL
FINER: TDSChannel (ConnectionID:1) Starting SSL handshake
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel$ProxyOutputStream writeInternal
FINEST: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyOutputStream@13c78c0b Writing 238 bytes
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeOutputStream writeInternal
FINEST: TDSChannel (ConnectionID:1) (SSLHandshakeOutputStream): Starting new TDS packet...
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSWriter writeBytes
FINEST: TDSWriter@41629346 (ConnectionID:1) Writing 8 bytes
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeOutputStream writeInternal
FINEST: TDSChannel (ConnectionID:1) (SSLHandshakeOutputStream): Writing 238 bytes...
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSWriter writeBytes
FINEST: TDSWriter@41629346 (ConnectionID:1) Writing 238 bytes
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel$ProxyOutputStream flush
FINEST: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyOutputStream@13c78c0b Flushing
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeOutputStream flush
FINEST: TDSChannel (ConnectionID:1) (SSLHandshakeOutputStream): Ignored a request to flush the stream
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream readInternal
FINEST: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@12843fce Reading 5 bytes
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream readInternal
FINEST: TDSChannel (ConnectionID:1) (SSLHandshakeInputStream): Reading 5 bytes...
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream ensureSSLPayload
FINEST: TDSChannel (ConnectionID:1) (SSLHandshakeInputStream): No handshake response bytes available. Flushing SSL handshake output stream.
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeOutputStream endMessage
FINEST: TDSChannel (ConnectionID:1) (SSLHandshakeOutputStream): Finishing TDS message
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSWriter endMessage
FINEST: TDSWriter@41629346 (ConnectionID:1) Finishing TDS message
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel logPacket
FINEST: /0:0:0:0:0:0:c0a8:1ba:63328 SPID:0 TDSWriter@41629346 (ConnectionID:1) sending packet (246 bytes)
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel write
FINER: TDSChannel (ConnectionID:1) write failed:Broken pipe
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.SQLServerException logException
FINE: *** SQLException:ConnectionID:1 ClientConnectionId: 1a3032c8-493d-4f99-8641-d0f02211d82c com.microsoft.sqlserver.jdbc.SQLServerException: Broken pipe ClientConnectionId:1a3032c8-493d-4f99-8641-d0f02211d82c Broken pipe ClientConnectionId:1a3032c8-493d-4f99-8641-d0f02211d82c
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.SQLServerException logException
FINE: com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2226)com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2210)com.microsoft.sqlserver.jdbc.TDSChannel.write(IOBuffer.java:1894)com.microsoft.sqlserver.jdbc.TDSWriter.flush(IOBuffer.java:4285)com.microsoft.sqlserver.jdbc.TDSWriter.writePacket(IOBuffer.java:4186)com.microsoft.sqlserver.jdbc.TDSWriter.endMessage(IOBuffer.java:3192)com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeOutputStream.endMessage(IOBuffer.java:824)com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream.ensureSSLPayload(IOBuffer.java:687)com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream.readInternal(IOBuffer.java:762)com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream.read(IOBuffer.java:754)com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream.readInternal(IOBuffer.java:949)com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream.read(IOBuffer.java:937)sun.security.ssl.InputRecord.readFully(InputRecord.java:465)sun.security.ssl.InputRecord.read(InputRecord.java:503)sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:973)sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1375)sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1403)sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1387)com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1682)com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1803)com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1454)com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1285)com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:700)com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1131)java.sql.DriverManager.getConnection(DriverManager.java:664)java.sql.DriverManager.getConnection(DriverManager.java:247)ExampleSQLJDBC.main(ExampleSQLJDBC.java:44)
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.SQLServerConnection:1 close
FINER: ENTRY
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel disableSSL
FINER: TDSChannel (ConnectionID:1) Disabling SSL...
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel disableSSL
FINEST: TDSChannel (ConnectionID:1) Rewiring proxy streams for SSL socket close
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel disableSSL
FINER: TDSChannel (ConnectionID:1) Closing SSL socket
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel$ProxyOutputStream writeInternal
FINEST: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyOutputStream@13c78c0b Writing 7 bytes
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel$ProxyOutputStream flush
FINEST: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyOutputStream@13c78c0b Flushing
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream readInternal
FINEST: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@12843fce Reading 5 bytes
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream readInternal
FINEST: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@12843fce Read -1 bytes
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel disableSSL
FINER: TDSChannel (ConnectionID:1) SSL disabled
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel close
FINEST: TDSChannel (ConnectionID:1): Closing inputStream...
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel close
FINEST: TDSChannel (ConnectionID:1): Closing outputStream...
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel close
FINER: TDSChannel (ConnectionID:1): Closing TCP socket...
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.SQLServerConnection:1 close
FINER: RETURN
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream ensureSSLPayload
FINER: TDSChannel (ConnectionID:1) (SSLHandshakeInputStream): Ending TDS message threw exception:Broken pipe ClientConnectionId:1a3032c8-493d-4f99-8641-d0f02211d82c
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream readInternal
FINER: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@12843fce Broken pipe ClientConnectionId:1a3032c8-493d-4f99-8641-d0f02211d82c
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream readInternal
FINER: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@12843fce Reading bytes threw exception:Broken pipe ClientConnectionId:1a3032c8-493d-4f99-8641-d0f02211d82c
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel$ProxySocket close
FINER: TDSChannel (ConnectionID:1) (ProxySocket): Ignoring close
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL
FINER: Broken pipe ClientConnectionId:1a3032c8-493d-4f99-8641-d0f02211d82c
java.io.IOException: Broken pipe ClientConnectionId:1a3032c8-493d-4f99-8641-d0f02211d82c
    at com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream.ensureSSLPayload(IOBuffer.java:692)
    at com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream.readInternal(IOBuffer.java:762)
    at com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream.read(IOBuffer.java:754)
    at com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream.readInternal(IOBuffer.java:949)
    at com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream.read(IOBuffer.java:937)
    at sun.security.ssl.InputRecord.readFully(InputRecord.java:465)
    at sun.security.ssl.InputRecord.read(InputRecord.java:503)
    at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:973)
    at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1375)
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1403)
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1387)
    at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1682)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1803)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1454)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1285)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:700)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1131)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at ExampleSQLJDBC.main(ExampleSQLJDBC.java:44)

Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL
FINER: java.security path: /Library/Java/JavaVirtualMachines/jdk1.8.0_92.jdk/Contents/Home/jre/lib/security
Security providers: [SUN version 1.8, SunRsaSign version 1.8, SunEC version 1.8, SunJSSE version 1.8, SunJCE version 1.8, SunJGSS version 1.8, SunSASL version 1.8, XMLDSig version 1.8, SunPCSC version 1.8, Apple version 1.8]
SSLContext provider info: Sun JSSE provider(PKCS12, SunX509/PKIX key/trust factories, SSLv3/TLSv1/TLSv1.1/TLSv1.2)
SSLContext provider services:
[SunJSSE: KeyFactory.RSA -> sun.security.rsa.RSAKeyFactory
  aliases: [1.2.840.113549.1.1, OID.1.2.840.113549.1.1]
, SunJSSE: KeyPairGenerator.RSA -> sun.security.rsa.RSAKeyPairGenerator
  aliases: [1.2.840.113549.1.1, OID.1.2.840.113549.1.1]
, SunJSSE: Signature.MD2withRSA -> sun.security.rsa.RSASignature$MD2withRSA
  aliases: [1.2.840.113549.1.1.2, OID.1.2.840.113549.1.1.2]
, SunJSSE: Signature.MD5withRSA -> sun.security.rsa.RSASignature$MD5withRSA
  aliases: [1.2.840.113549.1.1.4, OID.1.2.840.113549.1.1.4]
, SunJSSE: Signature.SHA1withRSA -> sun.security.rsa.RSASignature$SHA1withRSA
  aliases: [1.2.840.113549.1.1.5, OID.1.2.840.113549.1.1.5, 1.3.14.3.2.29, OID.1.3.14.3.2.29]
, SunJSSE: Signature.MD5andSHA1withRSA -> sun.security.ssl.RSASignature
, SunJSSE: KeyManagerFactory.SunX509 -> sun.security.ssl.KeyManagerFactoryImpl$SunX509
, SunJSSE: KeyManagerFactory.NewSunX509 -> sun.security.ssl.KeyManagerFactoryImpl$X509
  aliases: [PKIX]
, SunJSSE: TrustManagerFactory.SunX509 -> sun.security.ssl.TrustManagerFactoryImpl$SimpleFactory
, SunJSSE: TrustManagerFactory.PKIX -> sun.security.ssl.TrustManagerFactoryImpl$PKIXFactory
  aliases: [SunPKIX, X509, X.509]
, SunJSSE: SSLContext.TLSv1 -> sun.security.ssl.SSLContextImpl$TLS10Context
  aliases: [SSLv3]
, SunJSSE: SSLContext.TLSv1.1 -> sun.security.ssl.SSLContextImpl$TLS11Context
, SunJSSE: SSLContext.TLSv1.2 -> sun.security.ssl.SSLContextImpl$TLS12Context
, SunJSSE: SSLContext.TLS -> sun.security.ssl.SSLContextImpl$TLSContext
  aliases: [SSL]
, SunJSSE: SSLContext.Default -> sun.security.ssl.SSLContextImpl$DefaultSSLContext
, SunJSSE: KeyStore.PKCS12 -> sun.security.pkcs12.PKCS12KeyStore
]
java.ext.dirs: /Users/jan/Library/Java/Extensions:/Library/Java/JavaVirtualMachines/jdk1.8.0_92.jdk/Contents/Home/jre/lib/ext:/Library/Java/Extensions:/Network/Library/Java/Extensions:/System/Library/Java/Extensions:/usr/lib/java
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.SQLServerException logException
FINE: *** SQLException:ConnectionID:1 ClientConnectionId: 1a3032c8-493d-4f99-8641-d0f02211d82c com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Broken pipe ClientConnectionId:1a3032c8-493d-4f99-8641-d0f02211d82c". The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Broken pipe ClientConnectionId:1a3032c8-493d-4f99-8641-d0f02211d82c".
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.SQLServerException logException
FINE: com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2226)com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1756)com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1803)com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1454)com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1285)com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:700)com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1131)java.sql.DriverManager.getConnection(DriverManager.java:664)java.sql.DriverManager.getConnection(DriverManager.java:247)ExampleSQLJDBC.main(ExampleSQLJDBC.java:44)
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.SQLServerConnection:1 close
FINER: ENTRY
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel close
FINEST: TDSChannel (ConnectionID:1): Closing inputStream...
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel close
FINEST: TDSChannel (ConnectionID:1): Closing outputStream...
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel close
FINER: TDSChannel (ConnectionID:1): Closing TCP socket...
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.SQLServerConnection:1 close
FINER: RETURN
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.SQLServerConnection:1 close
FINER: ENTRY
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel close
FINEST: TDSChannel (ConnectionID:1): Closing inputStream...
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel close
FINEST: TDSChannel (ConnectionID:1): Closing outputStream...
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.TDSChannel close
FINER: TDSChannel (ConnectionID:1): Closing TCP socket...
Jun 17, 2016 9:26:43 AM com.microsoft.sqlserver.jdbc.SQLServerConnection:1 close
FINER: RETURN

Answer:

The problem was solved with these steps:

  1. Open terminal (/Applications/Utilities/Terminal)
  2. Run scutil --get HostName (case sensitive!)
  3. If hostname is not set or contains .local, run sudo scutil --set HostName "newname"
  4. Try JDBC Azure SQL Connection again - hostname change is effective immediately

Source: https://youtrack.jetbrains.com/issue/JRE-221#comment=27-1826316

Question:

Can somebody tell me how I can use includeInlineCount(); in android studio to count the number of rows in an azure database table?

I have tried this method:

final MobileServiceList<Crime> result = mToDoTable.where().includeInlineCount().execute().get();

And this method:

final MobileServiceList<Crime> result = mToDoTable.includeInlineCount().execute().get();

I did not get an error from running this code. Instead the textView that was suppose to display the number of rows just displayed the vaule of the first column for each row. How can I count these values and display that number?


Answer:

@Cain,it seem to be a duplicate thread with Count number of strings returned from Azure query, please see that answer.

Best Regards.

Question:

I am trying to count the number of rows in an Azure database table and display that number on my android app. I have read Azure's documentation on retrieving data from tables here:

https://azure.microsoft.com/en-us/documentation/articles/mobile-services-android-how-to-use-client-library/#querying

But it doesn't mention a built-in COUNT function. Does the Azure library have a COUNT function?


Answer:

As @AlexChen-Wx said, you can use the method includeTotalCount of Object MobileServiceTable on the JavaScript backend of Azure Mobile Service to count the number of records.

If you want to directly count on the Android App, you can try to use the method includeInlineCount of Class MobileServiceTable in Java, please see the source code at https://github.com/Azure/azure-mobile-services/blob/master/sdk/android/src/sdk/src/main/java/com/microsoft/windowsazure/mobileservices/table/MobileServiceTable.java#L349.

Question:

What is the best and correct way to list Azure Database for PostgreSQL servers present in my Resource Group using Azure Java SDK? Currently, we have deployments that happen using ARM templates and once the resources have been deployed we want to be able to get the information about those resources from Azure itself. I have tried doing in the following way:

PagedList<SqlServer> azureSqlServers = azure1.sqlServers().listByResourceGroup("resourceGrpName");
//PagedList<SqlServer> azureSqlServers = azure1.sqlServers().list();
for(SqlServer azureSqlServer : azureSqlServers) {
    System.out.println(azureSqlServer.fullyQualifiedDomainName());
}
System.out.println(azureSqlServers.size());

But the list size returned is 0.

However, for virtual machines, I am able to get the information in the following way:

PagedList<VirtualMachine> vms = azure1.virtualMachines().listByResourceGroup("resourceGrpName");
for (VirtualMachine vm : vms) {
    System.out.println(vm.name());
    System.out.println(vm.powerState());
    System.out.println(vm.size());
    System.out.println(vm.tags());
}

So, what is the right way of getting the information about the Azure Database for PostgreSQL using Azure Java SDK?

P.S. Once I get the information regarding Azure Database for PostgreSQL, I would need similar information about the Azure Database for MySQL Servers.

Edit: I have seen this question which was asked 2 years back and would like to know if Azure added Support for Azure Database for PostgreSQL/MySQL servers or not. Azure Java SDK for MySQL/PostgreSQL databases?


Answer:

According to my test, we can use java sdk azure-mgmt-resources to implement your need. For example

  1. Create a service principal
az login
# it will create a service pricipal and assign a contributor rolen to the sp
az ad sp create-for-rbac -n "MyApp"  --scope "/subscriptions/<subscription id>" --sdk-auth

  1. code
        String tenantId = "<the tenantId you copy >";
        String clientId = "<the clientId you copy>";
        String clientSecret= "<the clientSecre you copy>";
        String subscriptionId = "<the subscription id you copy>";
       ApplicationTokenCredentials creds = new 
          ApplicationTokenCredentials(clientId,domain,secret,AzureEnvironment.AZURE);
            RestClient restClient =new RestClient.Builder()
                                   .withBaseUrl(AzureEnvironment.AZURE, AzureEnvironment.Endpoint.RESOURCE_MANAGER)
                                   .withSerializerAdapter(new AzureJacksonAdapter())
                                   .withReadTimeout(150, TimeUnit.SECONDS)
                                   .withLogLevel(LogLevel.BODY)
                                   .withResponseBuilderFactory(new AzureResponseBuilder.Factory())
                                   .withCredentials(creds)
                                   .build();


            ResourceManager resourceClient= ResourceManager.authenticate(restClient).withSubscription(subscriptionId);
            ResourceManagementClientImpl  client=  resourceClient.inner();

            String filter="resourceType eq 'Microsoft.DBforPostgreSQL/servers'"; //The filter to apply on the operation
            String expand=null;//The $expand query parameter. You can expand createdTime and changedTime.For example, to expand both properties, use $expand=changedTime,createdTime
            Integer top =null;// The number of results to return. If null is passed, returns all resource groups.
            PagedList<GenericResourceInner> results= client.resources().list(filter, null,top);
            while (true) {
                for (GenericResourceInner resource : results.currentPage().items()) {

                    System.out.println(resource.id());
                    System.out.println(resource.name());
                    System.out.println(resource.type());
                    System.out.println(resource.location());
                    System.out.println(resource.sku().name());
                    System.out.println("------------------------------");
                }
                if (results.hasNextPage()) {
                    results.loadNextPage();
                } else {
                    break;
                }
            }

Besides, you also can use Azure REST API to implement your need. For more details, please refer to https://docs.microsoft.com/en-us/rest/api/resources/resources

Question:

I am trying to fetch data from MySQL database which is hosted in Microsoft Azure and my spring boot maven app is also deployed in Azure.

When I am hitting my data hosting link, it displays data in JSON format on a web browser. For Example - https://xyz.azurewebsites.net/checklist displays data in JSON format.

On the other end When I am hitting my front-end link, it displays my design. For Example - https://xyz.azurewebsites.net/search.html displays HTML view.

Note - This complete app working fine on local server //localhost/3306. But it is not running fine when I am deploying it on Microsoft Azure. Kind of mismatch issue.

Where am I doing a mistake?

Hierarchy

 |-Project
   |-src/main/java
     |-Controller
     |-DAO
     |-Entity
     |-Main
     |-Repository
     |-Service
     |-src/main/resources
     |-Static
        |- Search.html
        |- Index.html
     |-application.properties

Pom.xml

project xmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.example</groupId>
<artifactId>CheckListMavenWebThree</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>war</packaging>

<!-- <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> 
    <version>1.4.7.RELEASE</version> </parent> -->
<dependencies>

    <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-
boot-starter-web -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
        <version>1.4.7.RELEASE</version>
    </dependency>


    <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
        <version>1.4.7.RELEASE</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>6.0.6</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-tomcat</artifactId>
        <version>1.5.6.RELEASE</version>
        <scope>provided</scope>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
        <version>1.5.6.RELEASE</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework.data/spring-data-envers -->
    <dependency>
        <groupId>org.springframework.data</groupId>
        <artifactId>spring-data-envers</artifactId>
        <version>1.1.6.RELEASE</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-envers -->
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-envers</artifactId>
        <version>5.2.10.Final</version>
    </dependency>


</dependencies>

<properties>
    <java.version>1.8</java.version>
</properties>
<build>
    <testSourceDirectory>src/main/test</testSourceDirectory>
    <resources>
        <resource>
            <directory>src/main/resources</directory>
            <excludes>
                <exclude>**/*.java</exclude>
            </excludes>
        </resource>
        <!-- <resource> <directory>src/main/webapp</directory> <excludes> <exclude>**/*.java</exclude> 
            </excludes> </resource> -->
    </resources>
    <plugins>
        <plugin>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>3.6.1</version>

            <configuration>
                <source>1.8</source>
                <target>1.8</target>
            </configuration>
        </plugin>
        <plugin>
            <groupId>org.apache.tomcat.maven</groupId>
            <artifactId>tomcat7-maven-plugin</artifactId>
            <version>7</version>
        </plugin>
        <plugin>
            <artifactId>maven-war-plugin</artifactId>
            <version>3.0.0</version>
        </plugin>
    </plugins>
</build>

Controller.java (able to fetch data on host(Azure))

package com.example.Controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.http.MediaType;

import com.example.Entity.Checklist;
import com.example.Service.CheckListService;

@Controller
public class CheckListController {
@Autowired
private CheckListService checkListService;

@RequestMapping(value = "/checklist" , method = RequestMethod.GET)
@ResponseBody
public Object index() {
    return checkListService.findAll();
}

App.js

var app = angular.module('myApp', [ 'ngResource' ]);
app.controller('CheckListController', [
    '$scope',
    '$resource',
    function($scope, $resource) {
        function fetchAllCheckList() {
            $scope.CheckList = 
$resource('http://localhost:8080/CheckListMavenWebThree/checklist')
                    .query(function(data) {
                        console.log(data);
                        $scope.Message = data;
                        return data;
                    });
        };

        fetchAllCheckList();
        $scope.refresh = function() {
            fetchAllCheckList();
            console.log(fetchAllCheckList());
        };

Search.html

<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>CheckList</title>
<link rel="stylesheet" type="text/css" href="./css/right-panel.css">
</head>

<body ng-app="myApp" onload="javascript:closeNav()">
<div ng-controller="CheckListController" id="main">

<div >

        <span style="font-size: 23px; cursor: pointer"     onclick="openNav()">&#9776;
            Search</span>
    </div>


    <form name="checkListForm" id="main">

        <table border="2" >


            <tr>
                <th>Site_Name</th>
                <th>WSC_Serial_Number</th>
                <th>CheckList_01</th>
                <th>CheckList_02</th>
                <th>CheckList_03</th>
                <th>CheckList_04</th>
                <th>CheckList_05</th>
                <th>CheckList_06</th>
                <th>CheckList_07</th>
                <th>CheckList_08</th>
                <th>CheckList_09</th>
                <th>CheckList_10</th>
                <th>CheckList_11</th>
                <th>CheckList_12</th>
                <th>CheckList_13</th>
                <th>CheckList_14</th>
                <th>Notes</th>
                <th>Completed_By</th>
                <th>Setup_Tech</th>
                <th>Date</th>
            </tr>
            <div><label style="font-size:18px; font-family: "Lato", sans-serif;">Search: <input ng-model="search.$"></label><br><br>
            <tr ng-repeat="row in CheckList  | filter:search:strict">
                <td><span ng-bind="row.site_Name"></span></td>
                <td><span ng-bind="row.wsc_Serial_Number"></span></td>
                <td><span ng-bind="row.checkList_01"></span></td>
                <td><span ng-bind="row.checkList_02"></span></td>
                <td><span ng-bind="row.checkList_03"></span></td>
                <td><span ng-bind="row.checkList_04"></span></td>
                <td><span ng-bind="row.checkList_05"></span></td>
                <td><span ng-bind="row.checkList_06"></span></td>
                <td><span ng-bind="row.checkList_07"></span></td>
                <td><span ng-bind="row.checkList_08"></span></td>
                <td><span ng-bind="row.checkList_09"></span></td>
                <td><span ng-bind="row.checkList_10"></span></td>
                <td><span ng-bind="row.checkList_11"></span></td>
                <td><span ng-bind="row.checkList_12"></span></td>
                <td><span ng-bind="row.checkList_13"></span></td>
                <td><span ng-bind="row.checkList_14"></span></td>
                <td><span ng-bind="row.notes"></span></td>
                <td><span ng-bind="row.completed_By"></span></td>
                <td><span ng-bind="row.setup_Tech"></span></td>
                <td><span ng-bind="row.date"></span></td>
                <td>
            </tr>
        </div>
        </table>

        <div id="mySidenav" class="sidenav" onmouseover="openNav()"
            onmouseleave="closeNav()">
            <!--  <a href="javascript:void(0)" class="closebtn" onclick="closeNav()">&times;</a> -->

            <a href="search.html">&nbsp;&#9906;&nbsp;&nbsp;Search</a> <a
                href="createnew.html">&#169;&nbsp;&nbsp;Create</a> <a href="update.html">&#9851;&nbsp;&nbsp;Update</a>
            <a href="delete.html">&#9762;&nbsp;&nbsp;Delete</a>
            <a href="earth.html">&#9762;&nbsp;&nbsp;Earth</a>
        </div>

        <span><button type="button" data-ng-click="refresh()" id="refresh">Refresh</button></span>
</div>
</form>
<script type='text/javascript'  src="./js/rightpanel.js"></script>
<script type='text/javascript'  src="./js/angular.min.js"></script>
<script type='text/javascript'  src="./js/angular-resource.min.js"></script>
<script type='text/javascript' src="js/app.js"></script>
<link rel="stylesheet" href="./css/style.css">
<link rel="stylesheet" href="./css/theme-default.css">
<link rel="stylesheet" href="./css/theme-blue.css">
<link rel="stylesheet" href="./css/bootstrap.min.css">

Web.xml

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<system.webServer>
<handlers>
  <add name="httpPlatformHandler" path="*" verb="*" 
modules="httpPlatformHandler" resourceType="Unspecified" />
</handlers>
<httpPlatform processPath="%JAVA_HOME%\bin\java.exe"
    arguments="-Djava.net.preferIPv4Stack=true -
Dserver.port=%HTTP_PLATFORM_PORT% -jar &quot;%HOME%\site\wwwroot\gs-spring-
boot-0.1.0.jar&quot;">
</httpPlatform>
</system.webServer>
</configuration>

Application.properties (I am naive so I just learned and tried to implement. Is application.properties is correct? It allow me to connect mysql with azure from local host also)

spring.mvc.view.prefix=/static/js/
spring.mvc.view.suffix=.jsp
spring.datasource.url=jdbc:mysql://*****.mysql.database.azure.com:3306/*****?    verifyServerCertificate=true&useSSL=true&requireSSL=false&serverTimezone=UTC
spring.datasource.username:*******@*******server
spring.datasource.password:*******
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver


# Keep the connection alive if idle for a long time (needed in production)
spring.datasource.testWhileIdle = true
spring.datasource.validationQuery = SELECT 1

# Show or not log for each sql query
spring.jpa.show-sql = true

# Hibernate ddl auto (create, create-drop, update)
spring.jpa.hibernate.ddl-auto = update

# Naming strategy
spring.jpa.hibernate.naming-strategy =     org.hibernate.cfg.ImprovedNamingStrategy

# Use spring.jpa.properties.* for Hibernate native properties (the prefix is
# stripped before adding them to the entity manager)

# The SQL dialect makes Hibernate generate better SQL for the chosen     database
spring.jpa.properties.hibernate.dialect =     org.hibernate.dialect.MySQL5Dialect
logging.level.org.hibernate.SQL:debug

#naming convention according to me
spring.jpa.hibernate.naming.physical-    strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

What stuff Am I doing wrong? Is that hosting link issue?


Answer:

According to your description, your project works fine locally, so consider the issue of deployment.

You could followed the official tutorials to deploy spring-boot project to azure.

Combining the steps in the official tutorials and your actual situation, I provide the following check points:

Point 1: Please use mvn package to bulid the JAR package in the directory under which the pom.xml file is located.

]

Point 2: Please make sure that the jar package name configured in web.config is the same as the uploaded jar package name.

Point 3: Please use FTP to publish jar files and web.config to D:\home\site\wwwroot\ directory on KUDU.

Point 4: Please make sure ApplicationSettings matches your project such as jdk version,tomcat version.

If you want to deploy a war file, you need to configure the ApplicationSettings of your app service on Azure portal, then upload the war file into the path D:\home\site\wwwroot\webapps.

As references, please refer to the documents and threads below.

1.Configure web apps in Azure App Service

2.Create a Java web app in Azure App Service

3.Deploying Springboot to Azure App Service.

Hope it helps you.

Question:

tried in Azure to connect Web App - Java with SQL Database. I used this example and this page: https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-java-simple-windows/

I copy paste my connection string and add my IP to SQL servers/mydatabase/settings/firewall settings

When I run it from Tomcat8(Eclipse) - locally, I get the bottom error.

I can connect with SQL Management Studio with User and Pass that I used in conn. string so access data should be right. I also add sqljdbc4.jar lib downloaded from link in article.

java.sql.SQLException: No suitable driver found for jdbc:sqlserver://mydatabase.database.windows.net:1433;database=mydatabase;user=[Server admin]@mydatabase;password=[MyAdminPassword];encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30; at java.sql.DriverManager.getConnection(DriverManager.java:689) at java.sql.DriverManager.getConnection(DriverManager.java:270) at customerwebservice.SQLDatabaseTest.getDatabase(SQLDatabaseTest.java:30) at customerwebservice.GetCustomers.doGet(GetCustomers.java:34) at javax.servlet.http.HttpServlet.service(HttpServlet.java:622) at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:521) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1096) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:674) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1500) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1456) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:745)

Code:

import java.sql.*;
import com.microsoft.sqlserver.jdbc.*;

public class SQLDatabaseTest {

public String getDatabase(){

    String connectionString = "";

    connectionString = "jdbc:sqlserver://mydatabase.database.windows.net:1433;database=mydatabase;user=[Server admin]@mydatabase;password=[My Password];encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";

    // Declare the JDBC objects.
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    PreparedStatement prepsInsertPerson = null;
    PreparedStatement prepsUpdateAge = null;

    try {           
        connection = DriverManager.getConnection(connectionString);

        return "Connected";      
    }
    catch (Exception e) {
        e.printStackTrace();
    }
    finally {
        // Close the connections after the data has been handled.
        if (prepsInsertPerson != null) try { prepsInsertPerson.close(); } catch(Exception e) {}
        if (prepsUpdateAge != null) try { prepsUpdateAge.close(); } catch(Exception e) {}
        if (resultSet != null) try { resultSet.close(); } catch(Exception e) {}
        if (statement != null) try { statement.close(); } catch(Exception e) {}
        if (connection != null) try { connection.close(); } catch(Exception e) {}
    }

   return "Not Connected"; 


   }
}

EDITED: I figured out what was wrong. Because I am using Mac I did not add sqljdbc42.jar file to the Classpath. In windows you just run .exe but on Mac you need to manually add it.

This answer helps me add it to Classpath: https://stackoverflow.com/a/1676261/1436690

But now I have another problem, because I also need to add sqljdbc42.jar into Azure Web App classpath.


Answer:

I figured out what was wrong. Because I am using Mac I did not add sqljdbc42.jar file to the Classpath. In windows you just run .exe but on Mac you need to manually add it.

This answer helps me add it to Classpath: https://stackoverflow.com/a/1676261/1436690

Question:

I am trying to deploy a java spring-mvc web app into azure, but having trouble on connecting the MySQL in-app.

After deployed, I have updated the host, port, username and password in my xml according to the connection string in

/data/mysql/MYSQLCONNSTR_localdb.txt

Any helps on this? A side question, anyone how to get the connection string in java? so that if the port number changed, will not need to update the xml.

Encounter this error:

Type Exception Report

Message Request processing failed; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Description The server encountered an unexpected condition that prevented it from fulfilling the request.

Exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:982)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
Root Cause

org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
    org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:394)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:474)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:484)
    jbr.springmvc.dao.UserDaoImpl.validateUser(UserDaoImpl.java:44)
    jbr.springmvc.service.UserServiceImpl.validateUser(UserServiceImpl.java:26)
    jbr.springmvc.controller.LoginController.loginProcess(LoginController.java:28)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:498)
    org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:114)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
Root Cause

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
    com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2209)
    com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:776)
    com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
    sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:352)
    com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:284)
    java.sql.DriverManager.getConnection(DriverManager.java:664)
    java.sql.DriverManager.getConnection(DriverManager.java:208)
    org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:153)
    org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:144)
    org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:196)
    org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:159)
    org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
    org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
    org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:394)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:474)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:484)
    jbr.springmvc.dao.UserDaoImpl.validateUser(UserDaoImpl.java:44)
    jbr.springmvc.service.UserServiceImpl.validateUser(UserServiceImpl.java:26)
    jbr.springmvc.controller.LoginController.loginProcess(LoginController.java:28)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:498)
    org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:114)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
Root Cause

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
    com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:343)
    com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2132)
    com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:776)
    com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
    sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:352)
    com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:284)
    java.sql.DriverManager.getConnection(DriverManager.java:664)
    java.sql.DriverManager.getConnection(DriverManager.java:208)
    org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:153)
    org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:144)
    org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:196)
    org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:159)
    org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
    org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
    org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:394)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:474)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:484)
    jbr.springmvc.dao.UserDaoImpl.validateUser(UserDaoImpl.java:44)
    jbr.springmvc.service.UserServiceImpl.validateUser(UserServiceImpl.java:26)
    jbr.springmvc.controller.LoginController.loginProcess(LoginController.java:28)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:498)
    org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:114)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
Root Cause

java.net.SocketException: Permission denied: connect
    java.net.TwoStacksPlainSocketImpl.socketConnect(Native Method)
    java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
    java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
    java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
    java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)
    java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
    java.net.Socket.connect(Socket.java:589)
    java.net.Socket.connect(Socket.java:538)
    java.net.Socket.<init>(Socket.java:434)
    java.net.Socket.<init>(Socket.java:244)
    com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:253)
    com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:292)
    com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2132)
    com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:776)
    com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
    sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:352)
    com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:284)
    java.sql.DriverManager.getConnection(DriverManager.java:664)
    java.sql.DriverManager.getConnection(DriverManager.java:208)
    org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:153)
    org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:144)
    org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:196)
    org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:159)
    org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
    org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
    org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:394)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:474)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:484)
    jbr.springmvc.dao.UserDaoImpl.validateUser(UserDaoImpl.java:44)
    jbr.springmvc.service.UserServiceImpl.validateUser(UserServiceImpl.java:26)
    jbr.springmvc.controller.LoginController.loginProcess(LoginController.java:28)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:498)
    org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:114)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
Note The full stack trace of the root cause is available in the server logs.

The web app is working fine in my local, able to connect and insert data into database.

Appreciate the helps.


Answer:

I faced similar issue. Finally found out that, I must use latest version of MySQL-connector-java jar. I was using 5.1.6 version before.. the error was gone after upgrading to 5.1.30 version or later

Question:

I'm executing lot's of batches, containing prepared insert statements

public static void main(String... args) throws Exception {
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    BufferedReader csv = new BufferedReader(new InputStreamReader(Main.class.getClassLoader().getResourceAsStream("records.csv")));
    String line;
    createConnectionAndPreparedStatement();
    while ((line = csv.readLine()) != null) {
        tupleNum++;
        count++;
        List<String> row = new ArrayList<String>(Arrays.asList(line.split(";")));

        tupleCache.add(row);
        addBatch(row, ps);
        if (count > BATCH_SIZE) {
            count = 0;
            executeBatch(ps);
            tupleCache.clear();
        }
    }
}

protected static void createConnectionAndPreparedStatement() throws SQLException {
    System.out.println("Opening new connection!");
    con = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);
    con.setAutoCommit(true);
    con.setAutoCommit(false);
    ps = con.prepareStatement(insertQuery);

    count = 0;
}


private static void executeBatch(PreparedStatement ps) throws SQLException, IOException, InterruptedException {
    try {
        ps.executeBatch();
    } catch (BatchUpdateException bue) {
        if (bue.getMessage() != null && bue.getMessage().contains("Exceeded the memory limit")) {
            // silently close the old connection to free resources
            try {
                con.close();
            } catch (Exception ex) {}
            createConnectionAndPreparedStatement();
            for (List<String> t : tupleCache) {
                addBatch(t, ps);
            }
            // let's retry once
            ps.executeBatch();
        }
    }
    System.out.println("Batch succeeded! -->" + tupleNum );
    con.commit();
    ps.clearWarnings();
    ps.clearBatch();
    ps.clearParameters();
}

private static void addBatch(List<String> tuple, PreparedStatement ps) throws SQLException {
    int sqlPos = 1;
    int size = tuple.size();
    for (int i = 0; i < size; i++) {
        String field = tuple.get(i);
        //log.error(String.format("Setting value at pos [%s] to value [%s]", i, field));
        if (field != null) {
            ps.setString(sqlPos, field);
            sqlPos++;
        } else {
            ps.setNull(sqlPos, java.sql.Types.VARCHAR);
            sqlPos++;
        }
    }
    ps.addBatch();
}

So in standalone application everything is fine and no exceptions occur after 700k batch insertions. But when I execute actually same code in custom pig StoreFunc after about 6-7k batch insertions I get the following exception:

java.sql.BatchUpdateException: 112007;Exceeded the memory limit of 20 MB per session for prepared statements. Reduce the number or size of the prepared statements.
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1824)

And only restarting connection helps. Can someone help me with ideas why it's happening and how to fix it?


Answer:

According to your description & the error information, per my experience, I think the issue was caused by the configuration about memory at the server side of SQL Azure, such as memory limits for connections within the server resource pool.

I tried to follow the clue to search for the specific explaination about connection memory limits, but failed, besides the content below from here.

Connection Memory

SQL Server sets aside three packet buffers for every connection made from a client. Each buffer is sized according to the default network packet size specified by the sp_configure stored procedure. If the default network packet size is less than 8KB, the memory for these packets comes from SQL Server's buffer pool. If it's 8KB or larger, the memory is allocated from SQL Server's MemToLeave region.

And I continued to search for packet size & MemToLeave and view them.

Based on the above information, I guess that "Exceeded the memory limit of 20 MB per session for prepared statements" means all memory used of parallel connections over the max memory buffer pool of SQL Azure instance.

So there are two solutions I suggested which you can try.

  1. Recommended to reduce the value of BATCH_SIZE variable to make the server memory cost less than the max size of memory buffer pool.
  2. Try to scale up your SQL Azure instance.

Hope it helps.


Here are two new suggestions.

  1. I'm really not sure that the MS jdbc driver whether supports the current scenario using Apache Pig to do this like a paralleled ETL job. Please try to use jtds jdbc driver instead of the MS one.
  2. A better way I think is using more professional tools to do this, such as sqoop or kettle.

Question:

I'm trying to implement code that uses the Azure Java SDK to list the SQL Server instances running on my Azure subscription.

I followed the examples posted and wrote the following code:

Azure azure  = Azure.authenticate(credentials)).withDefaultSubscription();
SqlServers sqlServers = azure.sqlServers();
PagedList<SqlServer> list = sqlServers.list();

But the last line throws a java.lang.NoSuchMethodError with the following stack trace:

Exception in thread "main" java.lang.NoSuchMethodError: com.fasterxml.jackson.databind.type.TypeBindings.create(Ljava/lang/Class;[Lcom/fasterxml/jackson/databind/JavaType;)Lcom/fasterxml/jackson/databind/type/TypeBindings;
at com.microsoft.rest.serializer.JacksonAdapter.constructJavaType(JacksonAdapter.java:119)
at com.microsoft.rest.serializer.JacksonAdapter.deserialize(JacksonAdapter.java:131)
at com.microsoft.rest.ServiceResponseBuilder.buildBody(ServiceResponseBuilder.java:216)
at com.microsoft.rest.ServiceResponseBuilder.build(ServiceResponseBuilder.java:110)
at com.microsoft.azure.AzureResponseBuilder.build(AzureResponseBuilder.java:56)
at com.microsoft.azure.management.sql.implementation.ServersInner.listDelegate(ServersInner.java:553)
at com.microsoft.azure.management.sql.implementation.ServersInner.access$400(ServersInner.java:42)
at com.microsoft.azure.management.sql.implementation.ServersInner$17.call(ServersInner.java:539)
at com.microsoft.azure.management.sql.implementation.ServersInner$17.call(ServersInner.java:535)
at rx.internal.operators.OnSubscribeMap$MapSubscriber.onNext(OnSubscribeMap.java:69)
at retrofit2.adapter.rxjava.RxJavaCallAdapterFactory$RequestArbiter.request(RxJavaCallAdapterFactory.java:173)
at rx.Subscriber.setProducer(Subscriber.java:211)
at rx.internal.operators.OnSubscribeMap$MapSubscriber.setProducer(OnSubscribeMap.java:102)
at retrofit2.adapter.rxjava.RxJavaCallAdapterFactory$CallOnSubscribe.call(RxJavaCallAdapterFactory.java:152)
at retrofit2.adapter.rxjava.RxJavaCallAdapterFactory$CallOnSubscribe.call(RxJavaCallAdapterFactory.java:138)
at rx.Observable.unsafeSubscribe(Observable.java:10142)
at rx.internal.operators.OnSubscribeMap.call(OnSubscribeMap.java:48)
at rx.internal.operators.OnSubscribeMap.call(OnSubscribeMap.java:33)
at rx.internal.operators.OnSubscribeLift.call(OnSubscribeLift.java:48)
at rx.internal.operators.OnSubscribeLift.call(OnSubscribeLift.java:30)
at rx.internal.operators.OnSubscribeLift.call(OnSubscribeLift.java:48)
at rx.internal.operators.OnSubscribeLift.call(OnSubscribeLift.java:30)
at rx.Observable.subscribe(Observable.java:10238)
at rx.Observable.subscribe(Observable.java:10205)
at rx.observables.BlockingObservable.blockForSingle(BlockingObservable.java:444)
at rx.observables.BlockingObservable.single(BlockingObservable.java:341)
at com.microsoft.azure.management.sql.implementation.ServersInner.list(ServersInner.java:488)
at com.microsoft.azure.management.resources.fluentcore.arm.collection.implementation.TopLevelModifiableResourcesImpl.list(TopLevelModifiableResourcesImpl.java:116)
at AzureDiscoveryClient.main(AzureDiscoveryClient.java:19)

This obviously looks like a defect as the SDK allows to do that and should return a reasonable error code if something goes wrong.

Still, is there any other way to list the SQL Server instances running in my Azure subscription? Or perhaps I'm doing something in my code?


Answer:

Please refer to my working code as below:

import com.microsoft.azure.AzureEnvironment;
import com.microsoft.azure.PagedList;
import com.microsoft.azure.credentials.ApplicationTokenCredentials;
import com.microsoft.azure.management.Azure;
import com.microsoft.azure.management.sql.SqlServer;
import com.microsoft.azure.management.sql.SqlServers;

import java.io.IOException;

public class ListSqlInstance {

    public static void main(String[] args) throws IOException {


        ApplicationTokenCredentials credentials = new ApplicationTokenCredentials(
                "{client Id}",
                "{talent Id}",
                "{secret}",
                AzureEnvironment.AZURE);
        Azure.Authenticated azureAuth = Azure.authenticate(credentials);
       Azure azure = azureAuth.withDefaultSubscription();

        SqlServers sqlServers = azure.sqlServers();
        PagedList<SqlServer> list = sqlServers.list();
        System.out.println(list.size());
    }
}

My sdk version:

<dependency>
    <groupId>com.microsoft.azure</groupId>
    <artifactId>azure</artifactId>
    version>1.12.0</version>
</dependency>

Don't forget grant access sql server permissons to your client.

Hope it helps you.

Question:

Can anyone please help me figure out an Azure function in a Maven Java project to read a MongoDB database?

I am struggling to find any useful examples on github or in Microsoft's Azure documentation.

Here's what I have so far - although it fails to compile because it can't access a class I've defined in a separate library I mean to include.

My pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <name>CRUD Azure Functions</name>
    <version>1.0.0</version>
    <artifactId>crud-azure-functions</artifactId>
    <description>Experimenting with Azure Functions</description>
    <packaging>jar</packaging>

    <parent>
        <artifactId>my-parent-module</artifactId>
        <groupId>com.snoop.dougg</groupId>
        <version>1.0.0</version>
    </parent>

    <properties>
        <start-class>com.snoop.dougg.azure</start-class>
    </properties>

    <dependencies>
        <dependency>
            <groupId>com.microsoft.azure</groupId>
            <artifactId>azure-functions-java-core</artifactId>
            <version>1.0.0-beta-2</version>
        </dependency>
        <dependency>
            <groupId>com.snoop.dougg</groupId>
            <artifactId>my-common-lib</artifactId>
        </dependency>
    </dependencies>

    <build>
        <pluginManagement>
            <plugins>
                <plugin>
                    <artifactId>maven-resources-plugin</artifactId>
                    <version>3.0.2</version>
                </plugin>
                <plugin>
                    <groupId>com.microsoft.azure</groupId>
                    <artifactId>azure-functions-maven-plugin</artifactId>
                    <version>0.1.7</version>
                </plugin>
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-shade-plugin</artifactId>
                    <version>3.1.0</version>
                </plugin>
            </plugins>
        </pluginManagement>
        <plugins>
            <plugin>
                <groupId>com.microsoft.azure</groupId>
                <artifactId>azure-functions-maven-plugin</artifactId>
                <configuration>
                    <resourceGroup>java-functions-group</resourceGroup>
                    <appName>crud-azure-functions</appName>
                    <region>Central US</region>
                    <appSettings>
                        <property>
                            <name>FUNCTIONS_EXTENSION_VERSION</name>
                            <value>beta</value>
                        </property>
                    </appSettings>
                </configuration>
                <executions>
                    <execution>
                        <id>package-functions</id>
                        <goals>
                            <goal>package</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
            <plugin>
                <artifactId>maven-resources-plugin</artifactId>
                <executions>
                    <execution>
                        <id>copy-resources</id>
                        <phase>package</phase>
                        <goals>
                            <goal>copy-resources</goal>
                        </goals>
                        <configuration>
                            <overwrite>true</overwrite>
                            <outputDirectory>${project.basedir}</outputDirectory>
                            <resources>
                                <resource>
                                    <directory>${project.basedir}</directory>
                                    <includes>
                                        <include>**/*jar</include>
                                    </includes>
                                </resource>
                            </resources>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-shade-plugin</artifactId>
                <configuration>
                    <outputFile>${project.basedir}/${project.artifactId}-${project.version}.jar</outputFile>
                    <shadedArtifactAttached>false</shadedArtifactAttached>
                </configuration>
                <executions>
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>shade</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</project>

And then here's my Java code:

package com.snoop.dougg.azure;

import com.snoop.dougg.common.model.mongodb.Item;
import com.microsoft.azure.serverless.functions.ExecutionContext;
import com.microsoft.azure.serverless.functions.HttpRequestMessage;
import com.microsoft.azure.serverless.functions.HttpResponseMessage;
import com.microsoft.azure.serverless.functions.annotation.AuthorizationLevel;
import com.microsoft.azure.serverless.functions.annotation.FunctionName;
import com.microsoft.azure.serverless.functions.annotation.HttpTrigger;
import com.microsoft.azure.serverless.functions.annotation.TableInput;

import javax.servlet.http.HttpServletResponse;
import java.util.Optional;

public class ItemCrudFunctions {

    public static void main() {}

    @FunctionName("DouggHelloWorld")
    public HttpResponseMessage<String> helloWorld(
            @HttpTrigger(name = "req", methods = {"get"}, authLevel = AuthorizationLevel.ANONYMOUS)
                 HttpRequestMessage<Optional<String>> request,
            final ExecutionContext context) {
        return request.createResponse(HttpServletResponse.SC_OK, "Good news everyone!");
    }

    @FunctionName("ItemRetrieval")
    public HttpResponseMessage<Item> retrieveItem(
            @HttpTrigger(name = "retrieveItem",
                    route = "/item/{itemId}",
                    methods = {"get"},
                    authLevel = AuthorizationLevel.FUNCTION)
                HttpRequestMessage<Optional<String>> requestMessage,
            @TableInput(name = "itemTableInput",
                    tableName = "item",
                    connection = "AzureWebJobsStorage",
                    rowKey = "{itemId}") Item item) {
        return requestMessage.createResponse(HttpServletResponse.SC_OK, item);
    }
}

But I'm getting an error: cannot find symbol: class Item, and on top of that, I have no idea if this would even work if it did compile. I've been completely unable to find any good examples of Maven Java Azure Functions so far...


Answer:

I tried to access my azure mongo database in my java azure function. On my side, I used mongo-java-driver lib instead of the com.snoop.dougg lib you used.Please see my sample code as below:

Function Class:

package com.fabrikam.functions;

import com.microsoft.azure.serverless.functions.annotation.*;
import com.microsoft.azure.serverless.functions.ExecutionContext;

import com.mongodb.MongoClient;
import com.mongodb.MongoClientURI;
import com.mongodb.client.MongoDatabase;

public class Function {
    @FunctionName("mongo")
    public String mongo(@HttpTrigger(name = "req", methods = {"get", "post"}, authLevel = AuthorizationLevel.ANONYMOUS) String req,
                    ExecutionContext context) {

    try {
        MongoClientURI uri = new MongoClientURI("***");
        MongoClient mongoClient = new MongoClient(uri);
        MongoDatabase database = mongoClient.getDatabase("db");
        return String.format("Hello, I get database name : %s!", database.getName());

    } catch (Exception e) {
        e.printStackTrace();
        return "Access Error!";
    }
}

pom.xml:

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.fabrikam.functions</groupId>
    <artifactId>fabrikam-functions</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>Azure Java Functions</name>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
        <functionAppName>fabrikam-functions-20171017112209094</functionAppName>
    </properties>

    <dependencies>
        <dependency>
            <groupId>com.microsoft.azure</groupId>
            <artifactId>azure-functions-java-core</artifactId>
            <version>1.0.0-beta-1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.mongodb/mongo-java-driver -->
        <dependency>
            <groupId>org.mongodb</groupId>
            <artifactId>mongo-java-driver</artifactId>
            <version>3.6.3</version>
        </dependency>


        <!-- Test -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <pluginManagement>
            <plugins>
                <plugin>
                    <artifactId>maven-resources-plugin</artifactId>
                    <version>3.0.2</version>
                </plugin>
                <plugin>
                    <groupId>com.microsoft.azure</groupId>
                    <artifactId>azure-functions-maven-plugin</artifactId>
                    <version>0.1.5</version>
                </plugin>
            </plugins>
        </pluginManagement>

        <plugins>
            <plugin>
                <groupId>com.microsoft.azure</groupId>
                <artifactId>azure-functions-maven-plugin</artifactId>
                <configuration>
                    <resourceGroup>java-functions-group</resourceGroup>
                    <appName>${functionAppName}</appName>
                    <region>westus2</region>
                    <appSettings>
                        <property>
                            <name>FUNCTIONS_EXTENSION_VERSION</name>
                            <value>beta</value>
                        </property>
                    </appSettings>
                </configuration>
                <executions>
                    <execution>
                        <id>package-functions</id>
                        <goals>
                            <goal>package</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
            <plugin>
                <artifactId>maven-resources-plugin</artifactId>
                <executions>
                    <execution>
                        <id>copy-resources</id>
                        <phase>package</phase>
                        <goals>
                            <goal>copy-resources</goal>
                        </goals>
                        <configuration>
                            <overwrite>true</overwrite>
                            <outputDirectory>${project.build.directory}/azure-functions/${functionAppName}
                            </outputDirectory>
                            <resources>
                                <resource>
                                    <directory>${project.basedir}</directory>
                                    <includes>
                                        <include>host.json</include>
                                        <include>local.settings.json</include>
                                    </includes>
                                </resource>
                            </resources>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
            <plugin>
                <artifactId>maven-assembly-plugin</artifactId>
                <configuration>
                    <descriptorRefs>
                        <descriptorRef>jar-with-dependencies</descriptorRef>
                    </descriptorRefs>
                    <archive>
                        <manifest>
                            <mainClass>com.fabrikam.functions.Function</mainClass>
                        </manifest>
                    </archive>
                </configuration>
                <executions>
                    <execution>
                        <id>make-assembly</id>
                        <phase>package</phase>
                        <goals>
                            <goal>single</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

Then please use command mvn-clean-package and you will see two jar files generated.

One is that it does not contain dependent jar packages, and the second one contains dependent jar packages.

Move the fabrikam-functions-1.0-SNAPSHOT-jar-with-dependencies jar into the path:${project.basedir}/target/azure-functions/${function-app-name}/

For me ,it looks like E:\TestAzureFunction\fabrikam-functions\target\azure-functions\fabrikam-functions-20171017112209094.

Don't forget rename the jar to fabrikam-functions-1.0-SNAPSHOT.

Finally, I run the azure function successfully and get the output result.

In addition, you could refer to this github doc for more configuration details about azure function maven plugin.

Hope it helps you.

Question:

How can I select a row from an Azure database table using user input for the where clause. For example:

final MobileServiceList<NewsfeedItems> result = mToDoTable.where().field("location").eq(county).execute().get();

In the above line of code, county is a variable for a string. But this line searches for the string county instead of searching for the county variable value. Any help would be greatly appreciated.


Answer:

Where did you initialize the String county ? In the same function/method ? If you did not initialize it in the same function it is possible that the variable can not be resolved (reference) and so county is not treated as a variable but as a string.

In the .eq-function it is possible to insert a String variable and a string itself.

If a variable is inserted and can not be resolved because it is not local (or a class variable) the input in the .eq-function is processed as a string.

As a test create a variable county in the same function and initialize it with a particular value that appears in the database like

    private void searchContact(View view) {
    String name = newContactName.getText().toString();
    // Search for the contact based on the name field
    mobileContactTable.where().field("name").eq(name).execute(...
    }

and then run the code again

Question:

I'm working on the project which requires Azure Cosmos DB. However, I cannot connect to Azure cloud in the office because of the security issue. So I thought the emulator is fit for my case. Is the emulator works on the environment where connecting to Azure is blocked? Even without Internet?

Thank you.


Answer:

The Cosmos DB emulator is running locally on your machine or in Docker.

This means that you should be able to either install the emulator as a service or pull the docker image and run it in your machine. The emulator won't call anything external while it's running so as long as you have the internet to download the installer or the image you will be able to connect to it.

Read more about the Azure Cosmos DB emulator here.

Question:

I have a database "library_db" hosted on azure, which has a table "admin_db", with the fields "username" and "password". How can I check in java if a certain user is existent in my table? (If a user has been registered and its username and password is present in my table)

I have tried the following:

public void LoginAction(String user, String password) {
    try {
        Connection connection = DriverManager.getConnection(host, username, passwordServer);
        SQLServerPreparedStatement preparedStatement = (SQLServerPreparedStatement) connection.prepareStatement("SELECT `username`, `password` FROM `admin_db` WHERE `username`=? AND `password`=?");
        preparedStatement.setString(1, user);
        preparedStatement.setString(2, password);
        SQLServerResultSet resultSet =preparedStatement.executeQuery();
        if (resultSet.next()) {
            System.out.println("login successful");
        } 
    } catch (Exception e) {
        System.out.println("Login fail");
    }

The code runs without errors, but it executes the catch->Output:Login fail


Answer:

This is the solution:

 SQLServerPreparedStatement preparedStatement = (SQLServerPreparedStatement)connection.prepareStatement("SELECT username, password FROM admin_db WHERE username=? AND password=?");

Question:

I have a rest api where I have the databases in azure and the application with docker + kubernetes, so alright.

I have more than 10 clients and each client owns a database, I did not want to have a docker image for each client, just have a base image where for each client would have a connect string, the proposed solution was to make a setenv.sh to make the connections

setenv.sh

#!/bin/bash

dbuser="xxx@iafox"
dbpassword="mypassword"
dbconnectstring="jdbc:sqlserver://xxx.database.windows.net:1433;database=ts-demo1;user=xxx@iafox;password=mypassword;encrypt=true;trustServerCertificate=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"
dburl="jdbc:sqlserver://xxx.database.windows.net:1433;database=ts-demo1;"

export CATALINA_OPTS=" ${SYSTEM_PROPS} -Ddbuser='${dbuser}' -Ddbpassword=${dbpassword} -Ddbconnectstring='${dbconnectstring}' -Ddburl='${dburl}'"

server.xml

<Realm className="org.apache.catalina.realm.JDBCRealm" connectionURL="${dbconnectstring}" driverName="com.microsoft.sqlserver.jdbc.SQLServerDriver" roleNameCol="role" userCredCol="password" userNameCol="login" userRoleTable="userRole" userTable="v_login"/>

Until then everything is working. But now I do not know the next step, if for every setenv that I create I will have a configmap in kubernetes, or if I will have all the connect strings in the same setenv .... I wanted a help in this


Answer:

You can set multiple environment variables from one configMap as documented here.

But I would recommend storing passwords and connection strings with sensitive information in a secret.

You can reference Kubernetes secrets in a similar way to set the container's environment vars.

Question:

I have a database in Azure and want to connect to it using my java. I've tried running the sample code: package com.function;

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

public class Function {

    public static void main(String[] args) {

        // Connect to database - all strings were taken
        String hostName = "HOSTNAME";
        String dbName = "DBNAME";
        String user = "MY_USER";
        String password = "MY_PASSWORD";
        String url = String.format("jdbc:sqlserver://%s:1433;database=%s;user=%s;password=%s;encrypt=true;"
            + "hostNameInCertificate=*.database.windows.net;loginTimeout=30;", hostName, dbName, user, password);
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url);
            String selectSql = "SELECT * FROM BUSINESSES FOR JSON PATH";

            try (Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(selectSql)) {
                while (resultSet.next())
                {
                    System.out.println(resultSet.getString(1) + " "
                        + resultSet.getString(2));
                }
                connection.close();
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}

However, every time I try to run it I get the error

I'm not sure how to solve it, and if anyone knows what the problem is it would be of great help.

Notice - in the pom.xml there are references to these:

 <maven.compiler.source>1.8</maven.compiler.source>
 <maven.compiler.target>1.8</maven.compiler.target>
 <groupId>com.microsoft.sqlserver</groupId>
 <artifactId>mssql-jdbc</artifactId>
 <version>7.0.0.jre8</version>

Answer:

You seem to start the application in a console (the screenshot looks like that), so I assume you do something like

java Function

from the directory where the class file is residing.

When starting it this way, you have to provide the jdbc-driver's jar as classpath:

java -cp .;some/dir/where/the/jar/resides/mssql.jar Function

The reference in the pom.xml only lead to the download of the jar when using maven but doesn't add it automatically to the JVM's "global" classpath.

If it's 100% sure that the JDBC-driver is part of the classpath, you can try and register the driver to the DriverManager yourself (the driver is supposed to do that itself, but some of them don't):

DriverManager.registerDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");

Question:

I have a java class called getdata which contains some SQL code which shows on another java class as a listview, my problem is every time i try to view the data it freezes up for a little while and then displays the data because i am getting my data from a cloud database. Is there anyway to initialize this SQL code to run when i start my application where the user is busy logging in and keeps the array on ram so that the app does not lag when i try to view my products

Here is my getdata.java:

public class GetData {
Connection connect;
String ConnectionResult = "";
Boolean isSuccess = false;




public List<Map<String,String>> doInBackground() {

    List<Map<String, String>> data = null;
    data = new ArrayList<Map<String, String>>();
    try
    {
        ConnectionHelper conStr=new ConnectionHelper();
        connect =conStr.connectionclass();        // Connect to database
        if (connect == null)
        {
            ConnectionResult = "Check Your Internet Access!";
        }
        else
        {
            // Change below query according to your own database.
            String query = "select * from cc_rail";
            Statement stmt = connect.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()){
                Map<String,String> datanum=new HashMap<String,String>();
                datanum.put("NAME",rs.getString("RAIL_NAME"));

                datanum.put("PRICE",rs.getString("RAIL_UNIT_PRICE"));

                datanum.put("RANGE",rs.getString("RAIL_RANGE"));

                datanum.put("SUPPLIER",rs.getString("RAIL_SUPPLIER"));

                datanum.put("SIZE",rs.getString("RAIL_SIZE"));
                data.add(datanum);
            }


            ConnectionResult = " successful";
            isSuccess=true;
            connect.close();
        }
    }
    catch (Exception ex)
    {
        isSuccess = false;
        ConnectionResult = ex.getMessage();
    }

    return data;
}

and here is my .java file where i view the data and the lag happens:

  public View onCreateView(LayoutInflater inflater, ViewGroup container,
                         Bundle savedInstanceState) {
    View rootView = inflater.inflate(R.layout.fragment_valance, container, false);

    list = (ListView)rootView.findViewById(R.id.raill);
    add = (Button)rootView.findViewById(R.id.btn_add);
    progressBar = (ProgressBar)rootView.findViewById(R.id.PB_Getting);
    progressBar.setVisibility(View.GONE);

    add.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            Intent i = new Intent(getActivity(),Add_valance.class);
            startActivity(i);
        }
    });


          List<Map<String,String>> MyData = null;
            GetValence mydata =new GetValence();
            MyData= mydata.doInBackground();
            String[] fromwhere = { "NAME","PRICE","SIZE" ,"RANGE","SUPPLIER"};

            int[] viewswhere = {R.id.Name_txtView , R.id.price_txtView,R.id.size_txtView};

            ADAhere = new SimpleAdapter(getActivity(), MyData,R.layout.list_valence, fromwhere, viewswhere);

            list.setAdapter(ADAhere);

Answer:

This is what I was talking about in the comments.

GetData.java:

public class GetData extends AsyncTask<String, Integer, Map<String, String>> {
    @Override
    protected Map<String, String> doInBackground(String... params) {
        //do your thing here
        return resultMap;
    }
    @Override
    protected void onPostExecute(Map<String, String> result) {
        StorageClass.setArrayData(result);
    }
}

StorageClass.java:

public class StorageClass {
    private static Map<String,String> arrayData = new HashMap<>();

    public static void setArrayData(Map<String,String> results){
        arrayData = results;
    }

    public static Map<String, String> getArrayData(){
        return arrayData;
    }
}

Question:

So Azure spit the following code for me to insert into an activity (Android Studio is what I'm using)

Add the following line to the top of the .java file containing your launcher activity:

import com.microsoft.windowsazure.mobileservices.*;

Inside your activity, add a private variable

private MobileServiceClient mClient;

Add the following code the onCreate method of the activity:

mClient = new MobileServiceClient("https://pbbingo.azurewebsites.net", this);

Add a sample item class to your project::

public class ToDoItem{ public String id; public String Text;}

In the same activity where you defined mClient, add the following code:

ToDoItem item = new ToDoItem();
item.Text = "Don't text and drive";
mClient.getTable(ToDoItem.class).insert(item, new TableOperationCallback<item>(){
public void onCompleted(ToDoItem entity, Exception exception, ServiceFilter response)
{
if(exception == null){
//Insert Succeeded
} else {
//Insert Failed 
}
}});

My goal is to create a login page. I understand that the above was probably offered up more with a ToList in mind. I just want to get the syntax correct today. The problem I think, is my basic class structure. I have created an OnClick Listener within my on create that gets the ID from a button in my layout. I don't need it checking for anything in the database until the button has been actually clicked to either login or register.

public class LoginClass extends AppCompatActivity{  
       public void onCreate(Bundle savedInstanceState) {
          setContentView(R.layout.MyLoginLayout);
          MobileServiceClient mClient = null; 

          try {
              mClient = new MobileServiceClient ("myAzureWebsite", "AzureKey", this);
} catch (MalformedURLException e) {
e.printStackTrace();
}

Button Attempt = (Button) findViewById (R.id.mySubmitButton);
final MobileServiceClient finalMClient = mClient; // finalized so I can use it later. 

Attempt.setOnClickListener(new View.OnClickListener() {
     @Override
     public void onClick (View v) {
         final View thisView = v;
         final MyToDoItemClass item = new MyToDoItemClass();

In MyToDoItemClass I have two variables (Both String) Just left over from
the example of a ToDoList (they are String ID and String Text)

         item.Text = "Filler"; 
         item.ID = "Fill";
finalMClient.getTable(MyToDoItemClass.class).insert(new Table OperationCallback<item>() { //<--- I'm getting an error that the variable, item
is from an unknown class...

public void onCompleted (Item entity, Exception exception, ServiceFilterResponse response){
if(exception == null) {
Intent i = new Intent (LoginClass.this, MainActivity.class);
startActivity(i);
}else{
Toast.makeText(thisView.getContext(), "Failed", Toast.LENGTH_LONG).show();
}}
});
}
});
}}

The problem is with that the TableOperationCallback is saying that the item from MyToDoItemClass class is from an unknown class.


Answer:

There are many issues in your code, as below.

  1. According to the javadoc for class MobileServiceClient, there is not a method insert(TableOperationCallback<E> callback), so the code finalMClient.getTable(MyToDoItemClass.class).insert(new Table OperationCallback<item>() {...} is invalid.

  2. The generics E in Table OperationCallback<E> means that you need to write a POJO class name instead of E, not an object variable name like item, so the correct code should be new Table OperationCallback<MyToDoItemClass>, please see the Oracle tutorial for Generics to know more details.

  3. The figure below shows all methods insert of class MobileServiceClient. The bold word Deprecated under the method name means that you should not use it for developing on new project, it‘s only compatible for old project on the new version of Java SDK.

Please follow the offical tutorial to develop your app. Any concern, please feel free to let me know.

Question:

I have a Java application running as a WebJob on Azure. This job tries to connect to my Azure SQL Server. If the encryption is on, I get a connection error:

The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "java.security.cert.CertificateException: Failed to validate the server name in a certificate during Secure Sockets Layer (SSL) initialization.".

If encrypt = false it connects fine.

Shouldn't the communication between resources in Azure always be encrypted?

Also, if I run the application locally, the database connection successfully connects in encrypted mode.


Answer:

First- I should explain that Azure SQL DB is always going to default to an encrypted connection. The error you're seeing is speaking of verifying the identity of the server that you're communicating with as part of the handshake for SSL. [There's some unpacking of this idea over here.] (https://security.stackexchange.com/questions/1779/how-does-an-ssl-server-prove-its-identity)

I unpacked the encryption of communication with Azure SQL Database in answers on this MSDN thread- but I'll bring the relevant portion here:

When using Azure SQL DB, connections are encrypted even when the connection
string does not include those portions we recommend. 
...
Encryption occurs only if there is a verifiable server certificate,
otherwise the connection attempt fails.

Your error, as outlined on this MSDN article, arises from the following situation:

If the encrypt property is set to true and the trustServerCertificate 
property is set to false and if the server name in the connection string 
does not match the server name in the SQL Server SSL certificate, the 
following error will be issued: The driver could not establish a secure 
connection to SQL Server by using Secure Sockets Layer (SSL) encryption. 
Error: "java.security.cert.CertificateException: Failed to validate the
server name in a certificate during Secure Sockets Layer (SSL) initialization."

Apparently, the JDBC driver for SQL Server 4.0 seems to resolve this error: Azure VM Fails to Connect to Azure SQL Service

Certificate Exception connecting to Azure SQL with JDBC with default connection string (the one the management console says to use)

Question:

My connection java file contains :

try {
    Log.i("Login", "Establishing Connection...");
    // SET CONNECTIONSTRING
    Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance();
    Log.i("JDBC","found");
    Connection DbConn = DriverManager.getConnection("<connection_string>");

    Log.i("Login","Connected");
    Statement stmt = DbConn.createStatement();
    ResultSet insert = stmt.executeQuery("insert into UserLogin(username, password) values (admin, admin);");
    ResultSet reset = stmt.executeQuery(" select * from UserLogin ");

    Toast.makeText(this, reset.getString(1), Toast.LENGTH_SHORT).show();

    DbConn.close();

    // go to newsfeed
} catch (Exception e) {
    Log.e("Error connection","" + e.getMessage());
}

When my connection string contains: jdbc:jtds:sqlserver://, I gets error in connection :null,and when it's jdbc:sqlserver://,         I gets error in connection:no suitable driver found.

I'm trying to connect Android with Azure SQL DB.

I've seen Question1, Question2, Question3, Question4.   No Answer.


Answer:

@NiravMadariya, The JDBC connection string with jTDS shoule be jdbc:jtds:sqlserver://<server>:<port>/<database>, please see the jTDS FAQ which includes URL format and the reason for No suitable driver exception.

Note, if you are using the JTDS JDBC driver for Auzre SQL DB, then you will need to add ssl=require to the URL of the connection string as like this jdbc:jtds:sqlserver://<server>:<port>/<database>?ssl=require.

Meanwhile, using Microsoft JDBC Driver for SQL Server is a recommend way for Azure SQL DB, please see https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-java-simple/.

Question:

I'm new to ElcipseLink and JPA. I've successfully set up my persistence.xml and EclipseLink library. However, when trying to retrieve data from the server, I get invalid object name: dbo.Material.

My values are stored in the leermiddelen database. The scheme is dbo and the table is Material. When I try to specify the cataloge name (leermiddelen), then I get an error that specifying the database name is not supported in Azure.

A little help? (btw, when manually connection with a connection string and the driver from Microsoft, it does work)

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="2.0">
<persistence-unit name="test" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <exclude-unlisted-classes>true</exclude-unlisted-classes>
    <class>models.Material</class>
    <properties>
        <property name="javax.persistence.jdbc.driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <property name="javax.persistence.jdbc.url"
                  value="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"/>
        <property name="javax.persistence.jdbc.user" value="xxxxxxxxxxxx"/>
        <property name="javax.persistence.jdbc.password" value="xxxxxxxxxx"/>
    </properties>
</persistence-unit>

and the java code:

EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("test");
    EntityManager entityManager = entityManagerFactory.createEntityManager();
    EntityTransaction userTransaction = entityManager.getTransaction();
    try {
        userTransaction.begin();
        @SuppressWarnings("unchecked")
        List<Material> materials = entityManager.createQuery("SELECT e from Material e").getResultList();
        System.out.println("Getting materials list...");
        for (Iterator<Material> iterator = materials.iterator(); iterator.hasNext(); ) {
            Material m = (Material) iterator.next();
            System.out.println(m.toString());
        }
        entityManager.getTransaction().commit();
    } catch (Exception e) {
        entityManager.getTransaction().rollback();
    }
    entityManager.close();
    entityManagerFactory.close();
}

Finally the error:

[EL Warning]: 2016-04-05 11:16:13.426--UnitOfWork(1690796457)--Exception        [EclipseLink-4002] (Eclipse Persistence Services - 2.6.2.v20151217-774c696):  org.eclipse.persistence.exceptions.DatabaseException
 Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'dbo.Material'.
Error Code: 208
Call: SELECT Number, Amount, CanReserve, Description, Firm, IsAvailable, Name, PhotoUrl, Price FROM dbo.Material

Answer:

Conclusion for the question. It seems that the issue was caused by the incorrect jdbc url. The jdbc url for Azure SQL Database is as below.

jdbc:sqlserver://<hostname>.database.windows.net:1433;database=<database-name>;user=<username>@<hostname>;password={your_password_here};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

For getting the connection string on Azure portal, please see https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-java-simple-windows/#step-4-get-connection-string.

Question:

I'm currently developing an android app that requires data to be inserted into an azure Mobile Service DB. An id string and a first login integer, to be exact. However the following error is being thrown up.

"IllegalArgumentException: The class representing the MobileServiceTable must have a single id property defined"

The id value that I need to insert into the database is being passed back from a fragment interface using passId(). Inside the override of this is where I am attempting to insert the values into azure as shown below.

@Override
public void passId(String id) {

    userInstance user = new userInstance();
    user.user_id = id;
    user.first_login = 0;
    mClient.getTable(userInstance.class).insert(user, new TableOperationCallback<userInstance>() {
        public void onCompleted(userInstance entity, Exception exception, ServiceFilterResponse response) {
            if (exception == null) {
                // Insert succeeded
            } else {
                // Insert failed
            }
        }
    });

The mClient var represents the MobileServicesClient as shown below

try {

      mClient = new MobileServiceClient(
              "https://xxxx.azure-mobile.net/",
              "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
              this);

    } catch (MalformedURLException e) {
        e.printStackTrace();
    } catch (Exception e){
        e.printStackTrace();
    }

The table name that I am trying to insert the data into is "user_table" if that helps at all.

I hope you're able to help, and thanks in advance for any help you guys give me.


Answer:

SOLUTION:

Because the Azure Table that I was attempting to add data to auto created an "id" column, the user object that I was using to construct user info to insert into the database had to define an "id" String. As shown below:

public class userInstance {

    @com.google.gson.annotations.SerializedName("id")
    public String mId;

    @com.google.gson.annotations.SerializedName("user_id")
    public String mUserId;

    @com.google.gson.annotations.SerializedName("first_login")
    public int mLogin;

}

Question:

I am trying the sample application for changeFeedProcessor SDK for Azure-Cosmos.

GIT link of repo: https://github.com/Azure/azure-documentdb-changefeedprocessor-java

have configured all the required properties as below

COSMOSDB_ENDPOINT={hostandport}

COSMOSDB_SECRET={secretKey}

COSMOSDB_DATABASE={DBName}

COSMOSDB_COLLECTION={CollectionName}

COSMOSDB_AUX_COLLECTION={AuxCollectionName}

COSMOSDB_LEASE_COLLECTION={LeaseCollectionName}

and provided the hostname in sample.java

In the method DocumentLeaseServiceManager.initialize(), documentServices.createDocument() operation is success, while documentServices.deleteDocument() fails with the below error

java.lang.UnsupportedOperationException: PartitionKey value must be supplied for this operation. at com.microsoft.azure.documentdb.DocumentClient.addPartitionKeyInformation(DocumentClient.java:3346) at com.microsoft.azure.documentdb.DocumentClient.deleteDocument(DocumentClient.java:1047) at com.microsoft.azure.documentdb.changefeedprocessor.services.DocumentServices.deleteDocument(DocumentServices.java:248) at com.microsoft.azure.documentdb.changefeedprocessor.internal.documentleasestore.DocumentServiceLeaseManager.initialize(DocumentServiceLeaseManager.java:116) at com.microsoft.azure.documentdb.changefeedprocessor.ChangeFeedEventHost.initializeIntegrations(ChangeFeedEventHost.java:165) at com.microsoft.azure.documentdb.changefeedprocessor.ChangeFeedEventHost.start(ChangeFeedEventHost.java:143) at com.microsoft.azure.documentdb.changefeedprocessor.ChangeFeedEventHost.lambda$registerObserverFactory$0(ChangeFeedEventHost.java:133) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)

Would really appreciate a solution.


Answer:

That version of the Change Feed Processor seems rather old. It could be possible that is not compatible with partitioned Lease containers. If your Lease collection is partitioned (Partition Key should be /id), try the updated Change Feed Processor that is already part of the Java SDK.

Reference: https://github.com/Azure/azure-cosmosdb-java/tree/v3

Example: https://github.com/Azure/azure-cosmosdb-java/tree/v3/examples/src/main/java/com/azure/data/cosmos/examples/ChangeFeed

Question:

I am trying to connect my Azure Database using JDBC. But it fails to connect. I have used server name,username,database name and password correctly.

using Mysql Connector v8.0.13

import java.sql.*;
import java.util.Properties;


public class MyConnection {

    public static Connection getConnection() {


        System.out.println("MySQL JDBC driver detected in library path.");

        Connection connection = null;

        try
        {


            String url ="jdbc:mysql://<servername>:3306/<databaseName>?useSSL=true&requireSSL=false"; 
            connection = DriverManager.getConnection(url, <username>, <password>);
        }
        catch (SQLException e)
        {
            //throw new SQLException("Failed to create connection to database.", e);
        }
        if (connection != null) 
        { 
            System.out.println("Successfully created connection to database.");

        }
        else {
            System.out.println("Failed to create connection to database.");
        }
        System.out.println("Execution finished.");


        return connection;
    }
}

I was expecting to show "Successfully created connection to database." but its showing "Failed to create connection to database."


Answer:

Just as summary for the comments and my additional content.

As @LomatHaider said, the issue caused from Java code throwed an error about Time Zone. According to the MySQL offical document MySQL Server Time Zone Support, as below.

Time Zone Variables

MySQL Server maintains several time zone settings:

  • The system time zone. When the server starts, it attempts to determine the time zone of the host machine automatically and uses it to set the system_time_zone system variable. The value does not change thereafter.

    To explicitly specify the system time zone for MySQL Server at startup, set the TZ environment variable before you start mysqld. If you start the server using mysqld_safe, its --timezone option provides another way to set the system time zone. The permissible values for TZ and --timezone are system dependent. Consult your operating system documentation to see what values are acceptable.

  • The server current time zone. The global time_zone system variable indicates the time zone the server currently is operating in. The initial time_zone value is 'SYSTEM', which indicates that the server time zone is the same as the system time zone.

So if never set Time Zone variable for MySQL, the default time zone setting in MySQL is followed to the OS time zone. As I known, the default time zone on Azure is UTC, then the time zone of client connection on-premise or on local may will be different with MySQL on Azure, the confliction caused this issue information like as below.

java.sql.SQLException: The server time zone value 'Malay Peninsula Standard Time' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

So the solution to fix it is as the two options below:

  1. Follow the MySQL offical document to SET GLOBAL time_zone = timezone; by the SUPER privilege.
  2. Add the additional parameters ?useTimezone=true&serverTimezone=UTC in connection string to force use the same time zone value for JDBC connection session.

There is a blog MySQL JDBC Driver Time Zone Issue :: \[SOLVED\] introduced the same issue with this one, and to fix it by the second option above.

Question:

I'm trying to connect with external MySQL database with web application posted on Azure. Code is working perfectly fine on Tomcat hosted on Localhost, but on Azure all functions requiring connection return with error:

Exception: java.net.SocketException: Permission denied: connect Message: ; nested exception is: java.net.SocketException: Permission denied: connect    

Connection function code is:

private static String hostName = "sql11.freesqldatabase.com";
private static String dbName = "xxxx";
private static String user = "xxxx";
private static String password = "xxxx";
private static String portNumber = "xxxx";
private Connection connect() {
    String url ="jdbc:mysql://"+hostName+":"+portNumber+"/"+dbName+"?user="+user+"&password="+password;
    Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection(url);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    return conn;
}

Answer:

I tired to reproduce your issue but failed.

Here , I tried to create a java spring-boot project to test connection with Azure MySQL Database.

Snippet of my code:

    private static String hostName = "<your host name>";
    private static String dbName = "sys";
    private static String user = "<user name>";
    private static String password = "password";
    private static String portNumber = "3306";

    @RequestMapping("/hello")
    public String index() throws SQLException {
        Connection conn = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        }
        try {
            String url = "jdbc:mysql://"+hostName+":"+portNumber+"/"+dbName+"?verifyServerCertificate=true&useSSL=true&requireSSL=false&serverTimezone=UTC";
            conn = DriverManager.getConnection(url, user, password);

        } catch (SQLException e) {
            System.out.println("error!!!!");
            System.out.println(e.getMessage());
            e.printStackTrace();
            return e.getMessage();
        }
        return conn.getCatalog();
    }

My web.config file:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
  <system.webServer>
    <handlers>
      <add name="httpPlatformHandler" path="*" verb="*" modules="httpPlatformHandler" resourceType="Unspecified" />
    </handlers>
    <httpPlatform processPath="%JAVA_HOME%\bin\java.exe"
        arguments="-Djava.net.preferIPv4Stack=true -Dserver.port=%HTTP_PLATFORM_PORT% -jar &quot;%HOME%\site\wwwroot\demo-0.0.1-SNAPSHOT.jar&quot;">
    </httpPlatform>
  </system.webServer>
</configuration>

You could check points as below if you can not connect to your database:

1.Don't miss set SSL parameters.

2.Please set white IP address of your azure web app.

3.Don't miss -Djava.net.preferIPv4Stack=true setting in your web.config.

You could find more details from this thread: JavaMail API to iMail -- java.net.SocketException: Permission denied: connect

Hope it helps you.

Question:

I have an Azure resource group, and I want to be able to fetch a specific relating to my PostGreSQL server in my to my Resource Group.

In the Azure Java SDK (1.1.0), I want to fetch

URL l_resourceUrl = Thread.currentThread().getContextClassLoader()
            .getResource("azureauth.properties");

final File credFile = new File(l_resourceUrl.getFile());

Azure azure = Azure.configure().withLogLevel(LogLevel.NONE)
            .authenticate(credFile).withDefaultSubscription();

azure.getAzurePortalHandle().sqlServers();

for (SqlServer lt_gr : x.list()) {
    System.out.println(lt_gr.name());
 }

Unfortunately the postgresql server does not show up among this list (obviously), but I do not see any other option that would provide me with a PostgreSQL server object that can allow me to fetch server info provided by Azure, such as ip-addresses, compute units stc...

Any suggestions are most welcome.

Thanks

Baubak

PS: Afterword : As suggested I have already used Generic Resource, but it seems to be quite restrictive in regards to the server information suchs as : Server State, Status, Performance Configuration etc...


Answer:

Using Azure SDK for Java, the postgresql server is belong to GenericResources, not SqlServers (just Azure SQL Database).

Here is my sample code to list all PostgreSQL resource in a resource group.

Azure azure = Azure.configure().withLogLevel(LogLevel.NONE)
        .authenticate(credFile).withDefaultSubscription();
String resourceGroupName = "<your-resource-group-name>";
String providerNamespace = "Microsoft.DBforPostgreSQL";
Iterator<GenericResource> iter = azure.genericResources().listByResourceGroup(resourceGroupName).iterator();
/*
 * List all resource without resource group
 * Iterator<GenericResource> iter = azure.genericResources().list().iterator();
 */
while (iter.hasNext()) {
    GenericResource gr = iter.next();
    if (providerNamespace.equals(gr.resourceProviderNamespace()))
        System.out.println(gr.name() + "\t" + gr.resourceProviderNamespace() + "/" + gr.resourceType());
}

If you want to fetch a specified postgresql resource, as the code below.

String resourceGroupName = "<your-resource-group-name>";
String providerNamespace = "Microsoft.DBforPostgreSQL";
String resourceType = "servers";
String resourceName = "<your-resource-name>";
GenericResource gr = azure.genericResources().get(resourceGroupName, providerNamespace, resourceType,
            resourceName);
System.out.println(gr.name() + "\t" + gr.resourceProviderNamespace() + "/" + gr.resourceType());

Meanwhile, the all above codes works when you have the permission of some roles like Contributor, please see the document Use Role-Based Access Control to manage access to your Azure subscription resources to know how to configure.


Update: Here is my sample code for listing server parameters of postgresql.

String resourceGroupName = "<your-resource-group-name>";
String providerNamespace = "Microsoft.DBforPostgreSQL";
String resourceType = "servers";
String resourceName = "<your-resource-name>";
String apiVersion = "2017-04-30-preview";
String postgresConfigurationURL = "https://management.azure.com/subscriptions/%s/resourceGroups/%s/providers/Microsoft.DBforPostgreSQL/servers/%s/configurations?api-version=%s";
String url = String.format(postgresConfigurationURL, subscription, resourceGroupName, resourceName, apiVersion);
System.out.println(url);
HttpURLConnection conn = (HttpURLConnection) new URL(url).openConnection();
conn.addRequestProperty("Authorization", "Bearer " + cred.getToken("https://management.azure.com/"));
conn.addRequestProperty("Content-Type", "application/json; charset=utf-8");
BufferedReader br = new BufferedReader(new InputStreamReader(conn.getInputStream()));
String line = null;
while((line=br.readLine())!=null){
    System.out.println(line);
}
br.close();

The output as below.

{
    "value": [
        {
            "properties": {
                "value": "on",
                "description": "Enable input of NULL elements in arrays.",
                "defaultValue": "on",
                "dataType": "Boolean",
                "allowedValues": "on,off",
                "source": "system-default"
            },
            "id": "/subscriptions/ed0caab7-c6d4-45e9-9289-c7e5997c9241/resourceGroups/so-v-jiapa/providers/Microsoft.DBforPostgreSQL/servers/peterpostgres/configurations/array_nulls",
            "name": "array_nulls",
            "type": "Microsoft.DBforPostgreSQL/servers/configurations"
        }, ...]

The internal url comes from a command az postgres server configuration list --resource-group <your-resource-group-name> --server-name <your-postgres-name> of the Azure CLI tool.

Hope it helps.

Question:

I'm using Android Studio and talk to a cloud-based Azure database. I know about the query limit of 50 and want to circumvent this. I use this query:

private List<ItemInfo> retrieveItemNumList() throws ExecutionException, InterruptedException {
    return mItemInfoTable.select("Item_Number", "Item_Description").execute().get();
}

I've already found solutions such as:

[Queryable(MaxTop = 1000)]
public IQueryable<Place> GetAll() 

However, this is a .NET solution while I am using Node.js. Also, I'm a complete noob so I don't know how to access the back ends functions of Azure. Could someone walk me through how to enable a query of 1000?

Thanks.


Answer:

This question was answered on the MSDN forums today - https://social.msdn.microsoft.com/Forums/en-US/5efee2d6-417c-4d48-99d5-b8836d733a3e/override-50-row-limit-for-android-app?forum=azuremobile

Question:

Error occurred while connecting with azure database Jul 07, 2016 8:42:32 PM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL INFO: java.security path: C:\Program Files\Java\jdk1.8.0_60\jre\lib\security Security providers: [SUN version 1.8, SunRsaSign version 1.8, SunEC version 1.8, SunJSSE version 1.8, SunJCE version 1.8, SunJGSS version 1.8, SunSASL version 1.8, XMLDSig version 1.8, SunPCSC version 1.8, SunMSCAPI version 1.8] SSLContext provider info: Sun JSSE provider(PKCS12, SunX509/PKIX key/trust factories, SSLv3/TLSv1/TLSv1.1/TLSv1.2) SSLContext provider services: [SunJSSE: KeyFactory.RSA -> sun.security.rsa.RSAKeyFactory aliases: [1.2.840.113549.1.1, OID.1.2.840.113549.1.1] , SunJSSE: KeyPairGenerator.RSA -> sun.security.rsa.RSAKeyPairGenerator aliases: [1.2.840.113549.1.1, OID.1.2.840.113549.1.1] , SunJSSE: Signature.MD2withRSA -> sun.security.rsa.RSASignature$MD2withRSA aliases: [1.2.840.113549.1.1.2, OID.1.2.840.113549.1.1.2] , SunJSSE: Signature.MD5withRSA -> sun.security.rsa.RSASignature$MD5withRSA aliases: [1.2.840.113549.1.1.4, OID.1.2.840.113549.1.1.4] , SunJSSE: Signature.SHA1withRSA -> sun.security.rsa.RSASignature$SHA1withRSA aliases: [1.2.840.113549.1.1.5, OID.1.2.840.113549.1.1.5, 1.3.14.3.2.29, OID.1.3.14.3.2.29] , SunJSSE: Signature.MD5andSHA1withRSA -> sun.security.ssl.RSASignature , SunJSSE: KeyManagerFactory.SunX509 -> sun.security.ssl.KeyManagerFactoryImpl$SunX509 , SunJSSE: KeyManagerFactory.NewSunX509 -> sun.security.ssl.KeyManagerFactoryImpl$X509 aliases: [PKIX] , SunJSSE: TrustManagerFactory.SunX509 -> sun.security.ssl.TrustManagerFactoryImpl$SimpleFactory , SunJSSE: TrustManagerFactory.PKIX -> sun.security.ssl.TrustManagerFactoryImpl$PKIXFactory aliases: [SunPKIX, X509, X.509] , SunJSSE: SSLContext.TLSv1 -> sun.security.ssl.SSLContextImpl$TLS10Context aliases: [SSLv3] , SunJSSE: SSLContext.TLSv1.1 -> sun.security.ssl.SSLContextImpl$TLS11Context , SunJSSE: SSLContext.TLSv1.2 -> sun.security.ssl.SSLContextImpl$TLS12Context , SunJSSE: SSLContext.TLS -> sun.security.ssl.SSLContextImpl$TLSContext aliases: [SSL] , SunJSSE: SSLContext.Default -> sun.security.ssl.SSLContextImpl$DefaultSSLContext , SunJSSE: KeyStore.PKCS12 -> sun.security.pkcs12.PKCS12KeyStore ] java.ext.dirs: C:\Program Files\Java\jdk1.8.0_60\jre\lib\ext;C:\WINDOWS\Sun\Java\lib\ext

Sample Code used

package UtilityCreateSensorData;
// Use the JDBC driver  
import java.sql.*;  
import com.microsoft.sqlserver.jdbc.*;  

public class SQLDatabaseConnection {

     // Connect to your database.  
    // Replace server name, username, and password with your credentials  
    public  void InsertData(String Value1,String Value2,String Value3,int Value4) {  

        String connectionString ="jdbc:sqlserver://test.database.windows.net:1433;database=test;user=TestUsername;password=TestPassword";  


        // Declare the JDBC objects.  
        Connection connection = null; 
        Statement statement = null;   
        ResultSet resultSet = null;  
        PreparedStatement prepsInsertProduct = null; 


        try {  
            connection = DriverManager.getConnection(connectionString); 
            // Create and execute an INSERT SQL prepared statement.  
           String insertSql = "INSERT INTO [dbo].[tblAutomationTest] (DESC,MessageStart,MessageEnd,SenseCount) VALUES " + "(Value1, Value2, Value3, Value4);";  

           prepsInsertProduct = connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);  
           prepsInsertProduct.execute();  

            // Retrieve the generated key from the insert.  
            resultSet = prepsInsertProduct.getGeneratedKeys();  

            // Print the ID of the inserted row.  
            while (resultSet.next()) { System.out.println("Generated: " + resultSet.getString(1));  
            }  

        }  
        catch (Exception e) {  
            e.printStackTrace();  
        }  
        finally {  
            if (connection != null) try { connection.close(); } catch(Exception e) {}
         // Close the connections after the data has been handled.  
            if (prepsInsertProduct != null) try { prepsInsertProduct.close(); } catch(Exception e) {}  
            if (resultSet != null) try { resultSet.close(); } catch(Exception e) {}  
            if (statement != null) try { statement.close(); } catch(Exception e) {}  
        }  
        }

}  

Answer:

according to your code, the issue was caused by the incorrect connection string for Azure SQL Database.

The correct format of connection string for SQL Azure is like below.

jdbc:sqlserver://<hostname>.database.windows.net:1433;database=<database-name>;user=<username>@<hostname>;password={your_password_here};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

That you can find it at the link Show connection strings of the right bar of the tab DASHBOARD on Azure old portal, or see it at the link Show database connection strings on Azure new portal.

You can refer to my answer for the SO thread How to connect to Azure SQL with JDBC to see the figures and codes, or see the offical article https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-java-simple/.

Question:

I have a MySQL database on Azure. On Netbeans, I have a web project with Hibernate. I've generated the hibernate.cfg and was OK. Then, I tried to generate reveng.xml, but after a minute it shows a error message: 'Unable to establish a connection to the database with the selected Hibernate configuration file . Check the details of the connection in hibernate.cfg.xml'. When I go to Azure web, it shows that I've used all my available connections (my azure account allows only 4 mysql connections) but I think that I only use 1 connection when I try to generate reveng file.


Answer:

There are two kind of configuration files for Hibernate include XML configuration file hibernate.cfg.xml and Properties configuration file hibernate.properties.

If you want to configure the Hibernate session factory using XML file hibernate.cfg.xml, the prefix hibernate. is not needed, but need for the hibernate.properties file.

More details for XML configuration file, please see the section XML configuration file of the offical document.

Question:

I am currently developing a program for my final year project in college which requires database connectivity online and offline. I have a Oracle 11g xe database setup on a Microsoft Azure Windows Server 2012 VM and a similar database on my localhost. For security reasons the database stored on the localhost must be encrypted or made secure in some other way.

Is there anyway through Oracle to keep the databases synchronized or will I have to achieve this through my program (java program with JDBC) ? Also is there anyway to secure or encrypt data stored in an Oracle 11g xe database ?

Any help would be greatly appreciated. Thanks.


Answer:

Based on my understanding, I think you want to synchronize Oracle database hosted between localhost and Azure VM.

Per my experience, the simple way is using some data synchronization tools to do.

I recommend that you can try to use these tools below running on localhost for your needs.

  1. WARTS (for Java): http://warts.sourceforge.net/
  2. DBeaver (for Java): http://dbeaver.jkiss.org/docs/features/#Data_transfer_(export/import)
  3. SqlSync (running on Linux): http://silvercoders.com/en/products/sqlsync/

Hope it helps.

Question:

I trying to get data from ms access database stored in Azure File Storage. I set right path to storage, but when I try to get data from base I got this error.My application work fine when ms access database is on my machine.

This is my code:

Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
connection = DriverManager.getConnection("jdbc:ucanaccess:http://example/example/example/Database.accdb");

When I change the url I get error

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.2 given file does not exist:.


Answer:

I downloaded the zip file of UCanAccess source code and viewed it, it seems not to be the ability to connect the access database file via network protocols like HTTP, besides via the url of local filesystem like //c:/... on Windows or ///path/... on Unix-like OS.

So for implementing your needs, there are two solutions as below.

  1. First download the access database file you need to the running environment, then to use it and to back it up to Azure File Storage.
  2. Recommended. Follow the Azure offical tutorial for Windows or Linux to create a file share, and mount the file share using SMB protocol as a local filesystem on your OS, then to use your access file like a local file.

Hope it helps.

Question:

With Basic4Android I can get access to a SQL server database using Remote Database Connector (RDC) (see http://www.b4x.com/android/forum/threads/remote-database-connector-rdc-connect-to-any-remote-db.31540/ ). I can put the SQL server database on Azure. But how can I put the Java web server on Azure ?


Answer:

Per my experience, I suggest you to create a Windows VM on Azure and install Java Environment to deploy the RDC on it. Please refer to https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-tutorial-classic-portal/ and https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-log-on-windows-server/.

Why is Azure VM? Why is Windows?

The causes are as following:

  1. The RDC is a lightweight Java web server based on Jetty, and the Jetty version is 7.4.2. However, the version of Jetty on Azure Webapps from gallery is 9.1.2. They have the incompatible component of different implementation, such as Websocket.
  2. The Jetty Server of RDC is running as a standalone application, not a Java servlet container. So RDC can not be deployed on Azure Webapps.
  3. The RDC http listen port is 17178. It can be configured in config.properties. But if RDC as a CloudService run on Azure, you need to configure some properties for Cloud Service Role such as public ip & port.
  4. If you want to deploy it on Azure Linux VM, you need to run command chmod u+x for the decompressed files in RDC.zip and create a RunRLC.sh for boot Main class 'RemoteServer'.

Note: I recommend Microsoft SQL JDBC version 4.0+ Driver to access Azure SQLDatabase and SQL Server on Azure. The RDC's default JDBC for MSSQL is jTDS that it is incompatible with MSSQL on Azure.

Best Regards.