Hot questions for Using Azure in sql

Question:

I'm new to azure, I don't know how to connect to a Table I created on an Azure Database. I want to get the table data (SELECT *) and populate them in a GridView in android. I know the 'populating' part using an adapter, all I want to know is how to connect and receive the data from the Table :)

I've tried THIS tutorial by Microsoft however, I'm facing some difficulties when applying the same tutorial to my scenario.

Here's what I tried:

@Override

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



setContentView(R.layout.activity_home_screen);

    try {
        mClient = new MobileServiceClient(
                "LINK",
                "KEY",
                this);

} catch (MalformedURLException e) {

}

    refreshItemsFromTable();
 }



private void refreshItemsFromTable()
{
          //ActivityData is my Entity class 

        new AsyncTask<Void, Void, Void>() {
            //
            @Override
            protected Void doInBackground(Void... params) {
                try {
                    //This is the problematic area. My actual table's name is "Activity" on the Azure SQL
                    final MobileServiceList<ActivityData> result = mToDoTable.eq(false).execute().get();
                    runOnUiThread(new Runnable() {

                        @Override
                        public void run() {
                            mAdapter.clear();

                            for (ActivityData item : result) {
                                mAdapter.add(item);
                            }
                        }
                    });
                } catch (Exception exception) {
                    Toast.makeText(HomeScreen.this, exception.toString(),Toast.LENGTH_LONG);
                }
                return null;
            }
        }.execute();
    }

How do I specify the SQL SELECT query in this scenario? Looks like this code isn't the way to do it? I would like to populate a GridView with the table data :)


Answer:

Most sure way that you will get data from Azure Table Storage to your android is via REST API. It is relatively straight forward and you have full control.

For example, to fetch (query) data you can check the following sample request:

Request Syntax:
GET /myaccount/Customers()?$filter=(Rating%20ge%203)%20and%20(Rating%20le%206)&$select=PartitionKey,RowKey,Address,CustomerSince  HTTP/1.1

Request Headers:
x-ms-version: 2013-08-15
x-ms-date: Mon, 25 Nov 2013 15:25:14 GMT
Authorization: SharedKeyLite myaccount:<some key>
Accept: application/atom+xml,application/xml
Accept-Charset: UTF-8
DataServiceVersion: 2.0;NetFx
MaxDataServiceVersion: 2.0;NetFx

More on that:

Please note that Azure tables does not support SQL language. Azure Tables are totally different type of storage (so called no-sql).

Question:

I have a rest service application running the Java Spring framework. The application depends on a connection to an external MySQL DB, which is connected via JDBC.

My issue is maintaining a solid connection between the rest service and the MySQL db. I have what I consider a rudimentary connection failsafe in place that looks something like:

public Connection getConnection() throws SQLException {
    if(connection == null){
         this.buildConnection();
    }
    else if(!connection.isValid(10)){ //Rebuild connection if it is no longer valid
        connection.close();
        this.buildConnection();
    }
    return connection;
}

Using this method should ensure that the connection is valid before any query is executed. My problem is that I periodically get an exception thrown when calling this method:

Could not create connection to database server. Attempted reconnect 3 times. Giving up. SQLState: 08001. ErrorCode: 0.

The things that have me super perplexed about this are:

  1. This error only happens periodically. Many times the connection works just find.
  2. I test this same application on my developer machine and this error never occurs.

I custom configured the MySQL DB on my own server, so I control all its config options. From this I know that this issue isn't related to the maximum number of connections allowed, or a connection timeout.

Edit - Update 1:

  • This service is hosted as Cloud Service on Microsoft Azure platform.
  • I accidentally set it up as an instance in Northern Europe, while the DB is located in North America - probably not related, but trying to paint the whole picture.
  • Tried the advice at this link with no success. Not using thread pools, and all ResultSets and Statements/PreparedStatements are closed after.

Edit - Update 2

After some refactoring, I was able to successfully implement a HikariCP Connection Pool as outlined by @M.Deinum below. Unfortunately, the same problem persists. Everything works great on my local machine, and all Unit Tests pass, but as soon as I push it to Azure and wait more than a few minutes between requests, I get this error, when trying to grab a connection from the pool:

springHikariCP - Connection is not available, request timed out after 38268ms. SQLState: 08S01. ErrorCode: 0.

My HikariCP configuration is as follows:

//Set up connection pool
HikariConfig config = new HikariConfig();
config.setDriverClassName("com.mysql.jdbc.Driver"); 
config.setJdbcUrl("jdbc:mysql://dblocation");

//Connection pool properties
Properties prop = new Properties();
prop.setProperty("user", "Username");
prop.setProperty("password", "Password");
prop.setProperty("verifyServerCertificate", "false");
prop.setProperty("useSSL","true");
prop.setProperty("requireSSL","true");
config.setDataSourceProperties(properties);

config.setMaximumPoolSize(20);
config.setConnectionTestQuery("SELECT 1");
config.setPoolName("springHikariCP");
config.setLeakDetectionThreshold(5000); 

config.addDataSourceProperty("dataSource.cachePrepStmts", "true");
config.addDataSourceProperty("dataSource.prepStmtCacheSize", "250");
config.addDataSourceProperty("dataSource.prepStmtCacheSqlLimit", "2048");
config.addDataSourceProperty("dataSource.useServerPrepStmts", "true");

dataSource = new HikariDataSource(config);

Any help would be greatly appreciated.


Answer:

I suggest using a proper JDBC Connection Pool like HikariCP that together with a validation query which will execute on correct intervals should give you fresh and proper connections each time.

Assuming you are using Spring and xml to configure the datasource.

<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource">
    <property name="poolName" value="springHikariCP" />
    <property name="dataSourceClassName"       value="com.mysql.jdbc.jdbc2.optional.MysqlDataSource" />
    <property name="dataSourceProperties">
        <props>
            <prop key="url">${jdbc.url}</prop>
            <prop key="user">${jdbc.username}</prop>
            <prop key="password">${jdbc.password}</prop>
        </props>
    </property>
</bean>

It by default validates connections on checkout. I suggest a try out.

As you are using java bases config I suggest the following

@Bean
public DataSource dataSource() {
    HikariDataSource ds = new HikariDataSource();
    ds.setPoolName("springHikariCP");
    ds.setMaxPoolSize(20);
    ds.setLeakDetectionThreshold(5000);
    ds.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
    ds.addDataSourceProperty("url", url);
    ds.addDataSourceProperty("user", username);
    ds.addDataSourceProperty("password", password);
    ds.addDataSourceProperty("cachePrepStmts", true);
    ds.addDataSourceProperty("prepStmtCacheSize", 250);
    ds.addDataSourceProperty("prepStmtCacheSqlLimit", 2048);
    ds.addDataSourceProperty("useServerPrepStmts", true);
    ds.addDataSourceProperty("verifyServerCertificate", false);
    ds.addDataSourceProperty("useSSL", true);
    ds.addDataSourceProperty("requireSSL", true);

    return ds;
}

Question:

It would be nice if I know the various ways to load data into Azure SQL Data Warehouse.

I could connect to Azure SQL DW using JDBC from my Ubuntu machine.

  1. Is there any direct way to load from Azure Blob storage into Azure SQL DW without using Azure Data Factory ?

  2. Ways to load data from Ubuntu to Azure SQL DW.

  3. Java API to talk to Azure.

  4. Directly loading data from HDInsights to SQL DW.

Thanks


Answer:

The best and fastest way to load data to Azure SQL DW is to use Polybase to load delimited flat files sitting in Azure Blob Storage. See Polybase described here.

For loading from HDInsight I suppose you could use SQOOP (though I haven't tried that to load Azure SQL DW) but the best approach is to have HDInsight write to a flat file (for example by inserting to an external table in Hadoop) and then using Polybase in Azure SQL DW to read the files.

Sorry I can't help with your other questions about Java and Ubuntu.

Question:

I am facing an issue to connect to azure MySQL database with Java. Though whenever I am connecting with same database with MySQL workbench the connection working as expected.

To connect the same I tried below codes where used both of the url string mentioned.

Class.forName("com.mysql.jdbc.Driver");
// String url = "jdbc:mysql://***-mysqldbserver.mysql.database.azure.com:3306/medilegaldb?autoReconnect=true";
String url ="jdbc:mysql://***-mysqldbserver.mysql.database.azure.com:3306/medilegaldb?useSSL=true&requireSSL=false";
Connection con = DriverManager.getConnection(url, "user", "pass");

Need help to identify the issue.


Answer:

Which version of MySQL-connector-java did you use? I used 5.1.6 and encountered the same error. But if I use 5.1.31, it works. Please let me know if this fix your issue.

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.31</version>
    </dependency>

Question:

I am working on azure offline sync using sqlite. and have couple of questions. I am new to android and azure

  1. Is it possible to define primary_key in local sqlite store (When i am working on azure apis)? Because i see in android column types supported are very few. What if i need a primary key which would help to work on offline data (may be for performance or any reason)

    ColumnDataType: Boolean, Integer, Real, String, Date, DateTimeOffset, Other localStore.defineTable("FR_Tbl_Items",tableDefinition);

  2. Another problem i am facing is, As per ToDoItem example of microsoft, First i should have one class for table (ToDoItem) define all the columns then use it to create MobileServiceTable object to fetch the data from azure. But if we need offline sync again we have to use defineTable function. This doesn't take ToDoItem class as argument. We have to define again in hash map and pass it. Why is it like this? Or is there any other way to do it?

private MobileServiceTable mToDoTable; and public void defineTable(String tableName, Map columns)

EDIT: I know sqlite supports primary key, but looks like azure doesn't support. I don't see any provision to create table with primary key

private void define_Tbl_ChangedTable_Table(DBManager localStore) throws MobileServiceLocalStoreException {
    Map<String, ColumnDataType> tableDefinition = new HashMap<String, ColumnDataType>();
    tableDefinition.put("id", ColumnDataType.String);
    tableDefinition.put("lastupdatetime", ColumnDataType.Date);
    localStore.defineTable("Tbl_ChangedTable", tableDefinition);
}
  1. Is there any place i can see data type mapping between Azure/Android/Sqlite ? Azure shows Number, Data as column type, What i should use in Table class for Number (can i use Number,Date classes of Java) then finally what data type i should chose from ColumnDataType

Answer:

For Question 1, you can define primary key in local store. There were the similar threads had been answered, please refer to Is it possible to apply primary key on the text fields in android database and how to add primary key to text datatype in android sqlite?. And from SQLite offical document http://www.sqlite.org/datatype3.html, it only support 5 kind of data type.

Continue for Question 3, SQLite Datatypes NULL, INTEGER, REAL, TEXT and BLOB are respectively mapping Java datatypes null, int & long(according to the magnitude of the integer value), double, String and using the JDBC function ResultSet.getBinaryStream(). If you want to save or read other datatypes, you just need to serialize or deserialize between type String and the other type with SQLite TEXT or BLOB type.

For Question 2, The MobileService APIs designed by MS Azure for Android Client & offline data sync are based on the best practise with the languge feature in the long term support. So MS supply the guideline document to help developers using them easily and avoid implementing the same features byself, please refer to https://azure.microsoft.com/en-us/documentation/articles/mobile-services-android-how-to-use-client-library/ and https://azure.microsoft.com/en-us/documentation/articles/mobile-services-android-get-started-offline-data/ to try to do it.

Question:

I'm trying to write Function, which get triggered by Service Bus and write code to Azure. I'm here stuck not able to access DB at all. I don't get any error here, but neither I get Print results from select statement(Test2).

What is wrong in code?

Before running this code, I have downloaded jdbc driver and followed following example. http://biercoff.com/add-microsoft-sql-jdbc-driver-to-maven/

package com.function;
import com.microsoft.azure.functions.annotation.*;
import com.microsoft.azure.functions.*;

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

/**
 * Azure Functions with Azure Storage Queue trigger.
 */
public class TopicTriggerSQLOutput {
    /**
     * This function will be invoked when a new message is received at the specified path. The 
message contents are provided as input to this function.
     */
   @FunctionName("TopicTriggerSQLOutput")
    public void run(
         @ServiceBusTopicTrigger(
            name = "message",
            topicName = "newtopic",
            subscriptionName = "newsubscription",
            connection = "topicconnstring"
        ) String message,
        final ExecutionContext context
    ) {
        /*Creating SQL Connection. I need help here:
        */


            String connectionUrl = 
"jdbc:sqlserver://sql...;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";

            ResultSet resultSet = null;

            try (Connection connection = DriverManager.getConnection(connectionUrl);
                Statement statement = connection.createStatement();) {

                    context.getLogger().info("Test 1"); // This is printed out.

                // Create and execute a SELECT SQL statement.
                String selectSql = "SELECT TOP 10 artist FROM [dbo].[RadioEventsTarget]";
                resultSet = statement.executeQuery(selectSql);

                // Print results from select statement
                    while (resultSet.next()) {
                    System.out.println(resultSet.getString(2) + " " + resultSet.getString(3));
                    context.getLogger().info("Test 2"); // This is never printed.

                }
        }
            // Handle any errors that may have occurred.
            catch (SQLException e) {
                e.printStackTrace();
            }

        context.getLogger().info("Message: " + message); // this is printed.
    }
}

Answer:

I have tested the sql part by using your code, the code works fine.

As you didn't got any error, the only reason that I can think of is that there is no data in the table(RadioEventsTarget).

Question:

I have created Azure SQL Data warehouse and done pause and resume actions using Java SDK

Now I want to disable Geo backup policy when creation of Azure SQL Data warehouse.

How can I do it using Java SDK?

Refer below image for Geo backup policy in Azure SQL Data warehouse.


Answer:

You could use Azure Rest API to do this.

PUT https://management.azure.com/subscriptions/***********/resourceGroups/shui156/providers/Microsoft.Sql/servers/shui156/databases/shui156/geoBackupPolicies/Default?api-version=2014-04-01

Body:

{
  "id": "/subscriptions/***********/resourceGroups/shui156/providers/Microsoft.Sql/servers/shui156/databases/shui156/geoBackupPolicies/Default",
  "name": "Default",
  "type": "Microsoft.Sql/servers/databases/geoBackupPolicies",
  "location": "East US",
  "kind": null,
  "properties": {
    "state": "Disabled",
    "storageType": null
  }
}

Question:

I have a java spring boot application that use hibernate as ORM. The database is an Azure SQL Server.

I've enabled the setShowSql on vendor adapter configuration.

When I want to find objects, I used the TypedQuery's methods setFirstResult and setMaxResults and than invoke the getResultList method.

The query printed in the console doesn't contains the OFFSET and ROW FETCH clauses and it seems that Hibernate first retrieve all result and than apply the pagination on the resulted list.

This obviously causes performance issues.

Where am I doing wrong?

Below I report the sample code I used.

query.setFirstResult(pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
...
query.getResultList()

Answer:

The only time Hibernate will explicitly include the OFFSET and FETCH clauses under a SQL Server dialect will be when the following conditions are met:

  1. You must use org.hibernate.dialect.SQLServer2012Dialect or any future 2012+ version.
  2. Your query must include an ORDER BY clause.
  3. Your query is not executing a TOP clause query.

The SQLServer2012Dialect uses a customized LimitHandler implementation called SQLServer2012LimitHandler that you can see here that explicitly handles this use case or otherwise falls back to the old behavior.

If both of the requirements above are being met but the logic is still fallig back to the old behavior for some reason, then it's a bug. In that case, you probably should update HHH-12152 with a test case so we can fix it.

Question:

I want to use an inner join query with Java and Azure mobile apps, but it looks like api does not let it do operations among the tables. Is there any way to do it?

List<ToDoItem> result = table.where()
                            .field("complete").eq(false)
                            .execute().get();

As you see, all the operations are over a table.


Answer:

@shanks, There is a similar SO thread Implementing table joins in the Mobile Apps Node.js backend using SQL which has been answered and you can refer to.

Assumption that using Node.js as the backend of Azure Mobile App, the steps as below:

  1. Define a custom API to execute custom SQL statements via the function azureMobile.data.execute with the query of inner join SQL or the query for a view created with inner join sql. You can refer to the subsection How to: Execute custom SQL statements of the section Custom APIs.
  2. Call the custom API via android client, please see the section How to: Call a custom API.

Question:

I have a SQL Database in Microsoft Azure.I want to access this SQL database from a java application in eclipse. The Azure portal has a provision for adding client IP address to firewall settings of Azure SQL Database.Only these IP addresses are allowed to access the database.And now I am stuck in coding to allow access to a given ip address programmatically . Can anyone help?

This is the Exception i am getting :

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open server 'pluginserver' requested by the login. Client with IP address '105.241.8.90' is not allowed to access the server. ClientConnectionId:7c605cae-5bb6-452e-a606-243b1fab304f

While researching i got code to do the same in c#.But i want the code in java.

public void AddFirewallRule(FirewallRule rule)
    {
        using (SqlConnection conn = new SqlConnection(this.ConnectionString))
        using (SqlCommand cmd = conn.CreateCommand())
        {
            conn.Open();
            cmd.CommandText = "sp_set_firewall_rule";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = rule.Name;
            cmd.Parameters.Add("@start_ip_address", SqlDbType.VarChar).Value = rule.startIPAddress.ToString();
            cmd.Parameters.Add("@end_ip_address", SqlDbType.VarChar).Value = rule.endIPAdress.ToString();
            cmd.ExecuteNonQuery();
        }
    }

Answer:

Per my exprience, the exception means that it need to firstly open server socket via TCP/IP to login before doing some actions, but the firewall didn't allow your IP address to access. That's contradict with each other.

The issue was caused by using JDBC driver in Java to create a firewall rule for Azure SQL Database. The Java/JDBC specification not includes this feature.

On Azure, you can only create or update a firewall rule in Java via two ways: REST API or Azure SDK for Java (not via JDBC for MS-SQLServer).

You can refer to the offical doc https://azure.microsoft.com/en-us/documentation/articles/sql-database-configure-firewall-settings-rest/ to know how to configure the firewall for SQL Database.

For creating a firewall rule, you can try to using the Class HttpClient of Apache HttpComponent or the Class HTTPConnection of JDK to request the REST API Create or Update Firewall Rule.

Or you can try to using the Interface FirewallRuleOperations got by the Interface SqlManagementClient, please see the package com.microsoft.azure.managemant.sql in Javadocs of Azure SDK for Java.

For using Azure SDK for Java, you need to add the maven repositories below into your Eclipse porject.

<dependency>
    <groupId>com.microsoft.azure</groupId>
    <artifactId>azure-svc-mgmt</artifactId>
    <version>0.9.2</version>
</dependency>
<dependency>
    <groupId>com.microsoft.azure</groupId>
    <artifactId>azure-svc-mgmt-sql</artifactId>
    <version>0.9.2</version>
</dependency>

Question:

I have a java web application which is deployed on microsoft azure cloud instance.

Environment details :

Jdk 8,Tomcat 8,MySQL Server 5.5 (NOTE: All the installations are done manually NOT selected from azure console environment)

Unfortunately I lost some data from my database 1 week before and i don't have backup of that data.

Question is : Can microsoft azure recover mysql dump of 1 week before data for us?

Please help.


Answer:

As Microsoft Azure support was too bad and could not help us in that.

We had recovered some data based on logs of my application but still, most of the data got lost.

One thing which irritated us in this matter was microsoft azure support even after we subscribed them by paying they had a reply time of 8 hours delay.

So now we moved our all the applications to AWS cloud so that this thing would never happen again.

Question:

I am trying to get byte array from temp file. I know my connection works because I am getting the correct values of the map's strings. But I keep getting a null byte array. Please help! Any help is greatly appreciated!

package packagename
import android.content.Context;
import android.os.AsyncTask;
import android.util.Log;
import com.microsoft.azure.storage.CloudStorageAccount;
import com.microsoft.azure.storage.blob.CloudBlob;
import com.microsoft.azure.storage.blob.CloudBlobClient;
import com.microsoft.azure.storage.blob.CloudBlobContainer;
import com.microsoft.azure.storage.blob.ListBlobItem;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Hashtable;
import java.util.Map;

public class Getfilereference extends AsyncTask<Map<String,byte[]>,Void,Map<String,byte[]>> {
    public Context mContext;

    public Getfilereference(Context context) {
        mContext = context;
    }

    @Override
    protected Map<String, byte[]> doInBackground(Map<String, byte[]>... params) {
        Map<String, byte[]> dictionary = new Hashtable<>();
        try {
            final String storageConnectionString =
                    "myconnectionstring";
            final String azureblobstoragecontainername = "mycontainer";
            CloudStorageAccount storageAccount = CloudStorageAccount.parse(storageConnectionString);
            CloudBlobClient blobClient = storageAccount.createCloudBlobClient();
            CloudBlobContainer container = blobClient.getContainerReference(azureblobstoragecontainername);
            for (ListBlobItem blobItem : container.listBlobs()) {
                if (blobItem instanceof CloudBlob) {
                    File file;
                    file = File.createTempFile("familyimages", null, mContext.getCacheDir());
                    CloudBlob blob = (CloudBlob) blobItem;
                    blob.download(new FileOutputStream(file + "\\" + blob.getName()));
                    FileInputStream fis = new FileInputStream(file + "\\" + blob.getName());
                    byte[] t = new byte[(file + "\\" + blob.getName()).length()];
                    fis.read(t);
                    fis.close();
                    dictionary.put(blob.getName(), t);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dictionary;
    }

    @Override
    protected void onPostExecute(Map<String, byte[]> dictionary2) {
        DirectoryOpenHelper dbhelper = new DirectoryOpenHelper(mContext);
        for (Map.Entry<String, byte[]> entry : dictionary2.entrySet()) {
            String key = entry.getKey();
            byte[] value = entry.getValue();
            dbhelper.openDB();
            dbhelper.insertfamilyimageinrow(value, Integer.valueOf(key));
            Log.i("Info",key);
        }
    }
}

Answer:

I replaced

byte[] t = new byte[(file + "\\" + blob.getName()).length()]; 
fis.read(t); fis.close(); dictionary.put(blob.getName(), t); 

with

RandomAccessFile f = new RandomAccessFile(file+"\\"+blob.getName(), "r");
byte[] b = new byte[(int)f.length()]; f.read(b);
dictionary.put(blob.getName(), b);