Base64 Document Database Integration

Overview

Provides the following two smart services that are database agnostic and supports dynamic queries.  

Key Features & Functionality

  • Get Base64 Document from Database - Retrieves a document as a base64 string from a SQL query and converts it into an Appian document, returning the document id.
  • Insert Document as Base64 into Database - Takes an Appian document id, converts to Base64 and updates the database as specified by a SQL query.
Anonymous
Parents
  • If anybody has used this can you please post some examples of the sql and node inputs for the two services. It just needs a little bit of guidance to be easy to consume.

    Much Appreciated.

  • Hi Dave

    If this helps, this is how I'm using the Insert Document as Base 64 smart service

    The insert SQL command is structured so that each entry mirrors the layout of the MySQL database 

    ="INSERT INTO MONITORING_dp_config VALUES (:publisher,:name,:documentId,:description,:configid,:isuieditallowed)"

    each value in the insert is then populated via the relevant input node entry - the input names are exact matches for the insert statement. When you pass the documentId of the document you want to store as Base64 the Smart Service will look for that document and convert it accordingly. 

    Hope this helps you out.

    Cheers

    Paul 

Comment
  • Hi Dave

    If this helps, this is how I'm using the Insert Document as Base 64 smart service

    The insert SQL command is structured so that each entry mirrors the layout of the MySQL database 

    ="INSERT INTO MONITORING_dp_config VALUES (:publisher,:name,:documentId,:description,:configid,:isuieditallowed)"

    each value in the insert is then populated via the relevant input node entry - the input names are exact matches for the insert statement. When you pass the documentId of the document you want to store as Base64 the Smart Service will look for that document and convert it accordingly. 

    Hope this helps you out.

    Cheers

    Paul 

Children
  • JNDI is the datasource name (Example: jdbc/AppianBusinessDS) - You can find the datasource name under admin console>>Datasources or in the datastore object , you can find the datasource name

  • In the end I got this working too. Thought I'd post and perhaps save someone else a little frustration. This really is a good plugin, would benefit from a few examples.

    The node inputs on the Get Base64 Document from Database

    • Document Name - example "MyFile"
    • Format - or file extension, example "txt"
    • Jndi Name - as above
    • Sql Statement – Example "SELECT fileContent FROM MR_AD_FileInfo WHERE fileId = " & pv!fileId.
      • Note: fileContent is the field hold the base64 string (Varchar(MAX))
    • Target Folder - standard appian folder type.
  • Afraid I'm not using the counterpart smart service to create a document from the base64 entry. Sorry - my use case is for one way only. Have you checked the STDOUT log file to see if anything is being trapped there ? 

  • Paul,

    Finally... this worked perfectly. I was struggling with how the sql married with the inputs and how to pass it the document.

    If you have a similar example for retrieving the base64 and saving as a file that will complete the round trip. Right now I'm fetching and though it all looks good and I'm getting no errors... no file is being written.

    Many Thanks, Dave