locked
Uploading image with location and text to mobile service database RRS feed

  • Question

  • Hi can someone please explain how I can combine these two scripts? One I got from the Geolocation Sample, and the other from instructions on uploading an image to Azure Storage.

    But I want to combine the query into one so I upload the text, description and location AND the imageURL etc at once. But I don't know how to modify this script to add the mssql query (this script looks like it has no query or indication of tables, columns)

    var azure = require('azure');
    var qs = require('querystring');
    var appSettings = require('mobileservice-config').appSettings;
    
    function insert(item, user, request) {
        // Get storage account settings from app settings. 
        var accountName = appSettings.STORAGE_ACCOUNT_NAME;
        var accountKey = appSettings.STORAGE_ACCOUNT_ACCESS_KEY;
        var host = accountName + '.blob.core.windows.net';
    
        if ((typeof item.containerName !== "undefined") && (
        item.containerName !== null)) {
            // Set the BLOB store container name on the item, which must be lowercase.
            item.containerName = item.containerName.toLowerCase();
    
            // If it does not already exist, create the container 
            // with public read access for blobs.        
            var blobService = azure.createBlobService(accountName, accountKey, host);
            blobService.createContainerIfNotExists(item.containerName, {
                publicAccessLevel: 'blob'
            }, function(error) {
                if (!error) {
    
                    // Provide write access to the container for the next 5 mins.        
                    var sharedAccessPolicy = {
                        AccessPolicy: {
                            Permissions: azure.Constants.BlobConstants.SharedAccessPermissions.WRITE,
                            Expiry: new Date(new Date().getTime() + 5 * 60 * 1000)
                        }
                    };
    
                    // Generate the upload URL with SAS for the new image.
                    var sasQueryUrl = 
                    blobService.generateSharedAccessSignature(item.containerName, 
                    item.resourceName, sharedAccessPolicy);
    
                    // Set the query string.
                    item.sasQueryString = qs.stringify(sasQueryUrl.queryString);
    
                    // Set the full path on the new new item, 
                    // which is used for data binding on the client. 
                    item.imageUri = sasQueryUrl.baseUrl + sasQueryUrl.path;
    
                } else {
                    console.error(error);
                }
                request.execute();
            });
        } else {
            request.execute();
        }
    }


    This is the other query I want to combine with

    function insert(item, user, request) { 
        var queryString = "INSERT INTO Place (title, description, location) VALUES (?, ?, geography::STPointFromText('POINT(' + ? + ' ' + ? + ')', 4326))";         
        mssql.query(queryString, [item.title, item.description, item.longitude.toString(), item.latitude.toString()], { 
                    success: function() { 
                         request.respond(statusCodes.OK, {}); 
                    } 
              }); 
    } 

    EDIT: I split the code up according to another sample and came up with this, but not sure if it is correct way to do it.

    var azure = require('azure');
    var qs = require('querystring');
    var appSettings = require('mobileservice-config').appSettings;
    
    function insert(item, user, request) {
        // Get storage account settings from app settings. 
        var accountName = appSettings.STORAGE_ACCOUNT_NAME;
        var accountKey = appSettings.STORAGE_ACCOUNT_ACCESS_KEY;
        var host = accountName + '.blob.core.windows.net';
    
    
        var containerName = 'mypictures-' + item.albumid.toLowerCase();
        var pictureRelativePath = '/' + containerName + '/' + item.fileName;
        var pictureThumbnailRelativePath = '/' + containerName + '/' + item.thumbnailFileName;
    
        // Create the container if it does not exist
        // Use public read access for the blobs, and the SAS to upload        
        var blobService = azure.createBlobService(accountName, accountKey, host);
        blobService.createContainerIfNotExists(containerName, { publicAccessLevel: 'blob' }, function (error) {
            if (!error) {
                // Container exists now define a policy for write access
                // that starts immediately and expires in 5 mins
                var sharedAccessPolicy = createAccessPolicy();
    
                // Create the blobs urls with the SAS
                item.imageurl = createResourceURLWithSAS(accountName, accountKey, pictureRelativePath, sharedAccessPolicy, host);
                item.thumbnailurl = createResourceURLWithSAS(accountName, accountKey, pictureThumbnailRelativePath, sharedAccessPolicy, host);
            }
            else {
                console.error(error);
            }
    
    		var queryString = "INSERT INTO myTable (title, description, location, containerName, resourceName, sasQueryString, imageURL) VALUES (?, ?, geography::STPointFromText('POINT(' + ? + ' ' + ? + ')', 4326), ?,?,?,?)";
    		mssql.query(queryString, [item.title, item.description, item.longitude.toString(), item.latitude.toString(),
    		item.containerName, item.resourceName, item.sasQueryString, item.imageURL], {
    			success: function () {
    				request.respond(statusCodes.OK, {});
    			}
    		});
            //request.execute();
        });
    }
    
    function createResourceURLWithSAS(accountName, accountKey, blobRelativePath, sharedAccessPolicy, host) {
        // Generate the SAS for your BLOB
        var sasQueryString = getSAS(accountName,
                            accountKey,
                            blobRelativePath,
                            azure.Constants.BlobConstants.ResourceTypes.BLOB,
                            sharedAccessPolicy);
    
        // Full path for resource with SAS
        return 'https://' + host + blobRelativePath + '?' + sasQueryString;
    }
    
    function createAccessPolicy() {
        return {
            AccessPolicy: {
                Permissions: azure.Constants.BlobConstants.SharedAccessPermissions.WRITE,
                // Start: use for start time in future, beware of server time skew 
                Expiry: formatDate(new Date(new Date().getTime() + 5 * 60 * 1000)) // 5 minutes from now
            }
        };
    }
    
    function getSAS(accountName, accountKey, path, resourceType, sharedAccessPolicy) {
        return qs.encode(new azure.SharedAccessSignature(accountName, accountKey)
                                        .generateSignedQueryString(path, {}, resourceType, sharedAccessPolicy));
    }
    
    function formatDate(date) {
        var raw = date.toJSON();
        // Blob service does not like milliseconds on the end of the time so strip
        return raw.substr(0, raw.lastIndexOf('.')) + 'Z';
    }



    • Edited by Sal_S Friday, November 28, 2014 4:14 AM
    Friday, November 28, 2014 3:23 AM

Answers

  • Hi there, 

    I'm guessing you have already created a table in the mobile service to store all data, and the geolocation and imageUrl information is already there, is it correct?

    or from your client side, re-define your class (eg Place) by adding string property ImageUri, you can retrieve the imageUri, and assign it to ImageUri..

    then on the server side code should be like this way(make sure you have added the imageUri column).

    mssql.query(queryString, [item.title, item.description,item.imageUri , item.longitude.toString(), item.latitude.toString()]

    Regards,

    Jenny 




    • Edited by JennyLi-MSFT Monday, December 8, 2014 8:36 AM
    • Proposed as answer by Manu Rekhar Wednesday, December 10, 2014 9:39 AM
    • Marked as answer by Manu Rekhar Monday, December 15, 2014 9:29 AM
    Monday, December 8, 2014 8:27 AM

All replies

  • Hello Sal_S,

    Thank you for reaching out to us. We am currently researching to gather more information with regards to your request.  We shall revert back to you with an update at the earliest.  Sincerely appreciate your patience.

    Thanks,
    Syed Irfan Hussain

    Friday, November 28, 2014 12:30 PM
  • Hi there, 

    I'm guessing you have already created a table in the mobile service to store all data, and the geolocation and imageUrl information is already there, is it correct?

    or from your client side, re-define your class (eg Place) by adding string property ImageUri, you can retrieve the imageUri, and assign it to ImageUri..

    then on the server side code should be like this way(make sure you have added the imageUri column).

    mssql.query(queryString, [item.title, item.description,item.imageUri , item.longitude.toString(), item.latitude.toString()]

    Regards,

    Jenny 




    • Edited by JennyLi-MSFT Monday, December 8, 2014 8:36 AM
    • Proposed as answer by Manu Rekhar Wednesday, December 10, 2014 9:39 AM
    • Marked as answer by Manu Rekhar Monday, December 15, 2014 9:29 AM
    Monday, December 8, 2014 8:27 AM