none
Connect out from ML Studio Classic (Web service) RRS feed

  • Question

  • How do I connect out from ML Studio Classic (Web service)?

    Below is sample code which we used to connect out from ML Studio Classic (Web service) and failed to retrieve data. Below is error code FYR

    #############################
    # ///////////////       Error       //////////////////
    #############################

    FailedToEvaluateRScript: The following error occurred during evaluation of R script: R_tryEval: return error: Error in ..fun(inputDF) : could not find function "odbcDriverConnect" , Error code: LibraryExecutionError, Http status code: 400

    #######################################
    # ///////////////       DB DATA RETRIEVAL       //////////////////
    #######################################

    library("RODBC")
    library("AzureML")

    DB_RETRIEVAL_FUNCTION = function(nodalId, orgId, dayahead_intraday, pssId){
      
      conn  = odbcDriverConnect(paste("driver={ODBC Driver 17 for SQL Server}", 
                                      "Server=",
                                      "database=", 
                                      "uid=", "pwd=",
                                      sep = ";"))
      
      getexogeneousvariablesByNodalId_Meteodata = function(conn, nodalId, 
                                                           dayahead_intraday,  
                                                           orgId, pssId) {
        
        qry = sprintf("EXEC [SP_RE_R_INPUT_WFX_METEOTEST_CLOUD_MOVE] %d", 
                      nodalId);
        
        res = sqlQuery(conn, qry)
        
        return(res)
      }
      
      inputData = getexogeneousvariablesByNodalId_Meteodata(conn, nodalId, 
                                                            dayahead_intraday,
                                                            orgId, pssId)
      
      return(inputData)
      
    }

    DB_DATA = DB_RETRIEVAL_FUNCTION(nodalId = xxx, 
                                    orgId = xx,
                                    dayahead_intraday = x, 
                                    pssId = x)

    # DB_DATA

    wsID = ""
    wsAuth = ""

    wsobj = workspace(wsID, wsAuth)

    outputSchemaDF <- data.frame(matrix(ncol = 5, nrow = 0))
    x <- c("BLOCK_ID", "DATA_DATETIME", "RADIATION", 
           "TEMPERATURE", "CLOUD_COVER")
    colnames(outputSchemaDF) <- x

    DBRetrievalWebService <- publishWebService(
      wsobj,
      fun = DB_RETRIEVAL_FUNCTION,
      name = "DBRetrievalWebService",
      inputSchema = list(
        nodalId = "numeric",
        orgId = "numeric",
        dayahead_intraday = "numeric",
        pssId = "numeric"
      ),
      data.frame=TRUE
    )
    Monday, January 13, 2020 8:46 AM

All replies

  • Hi,

    What database you are trying to connect to? Thanks.

    Regards,

    GiftA-MSFT.

    If a post helps to resolve your issue, please click “Mark as Answer” and/or “Vote as helpful”. By marking a post as Answered and/or Helpful, you help others find the answer faster.  Thanks.

    Monday, January 13, 2020 11:54 PM
    Moderator
  • Hello,

    Currently we are using "Microsoft SQL Server 2017 Standard Edition" and we are trying to retrieve data from this server through Web services.

    Thanks & Regards,

    Amogh Kaliwal

    Tuesday, January 14, 2020 7:36 AM
  • Hi,

    Seems like you are having issues connecting to the database based on the error. Please check to ensure that RODBC has been installed and that you can successfully connect/query the database prior to publishing the function to the web service. Please let me know if you can connect and query the database, but still unable to retrieve data after publishing the function to the web service. Thanks.

    Regards,

    GiftA-MSFT.

    If a post helps to resolve your issue, please click “Mark as Answer” and/or “Vote as helpful”. By marking a post as Answered and/or Helpful, you help others find the answer faster.  Thanks.

    Wednesday, January 15, 2020 1:53 AM
    Moderator
  • Hello Amogh,

    You can also try to use the import data module to get data from any of the supported data sources below and connect any transformation modules to format the data and use it in the Execute R script.

    Data source Use with
    Web URL via HTTP Get data that is hosted on a web URL that uses HTTP and that has been provided in the CSV, TSV, ARFF, or SvmLight formats
    Hive Query Get data from distributed storage in Hadoop. You specify the data you want by using the HiveQL language
    Azure SQL Database Get data from Azure SQL Database or from Azure SQL Data Warehouse
    Azure Table Get data that is stored in the Azure table service
    Import from Azure Blob Storage Get data that is stored in the Azure blob service
    Data Feed Providers Get data exposed as a feed in OData format
    Import from On-Premises SQL Server Database Get data from an on-premises SQL Server database using the Microsoft Data Management Gateway
    Azure Cosmos DB Get data stored in JSON format in Azure Cosmos DB.

    Thursday, January 16, 2020 4:39 AM
    Moderator