Integrate Box with PostgreSQL

Box?

Box is a cloud storage platform of files and documents of approximately 2GB. Box also provides API that enables users to integrate Box into their applications.

PostgreSQL?

PostgreSQL is an object-relational database management system (ORDBMS. available for free. It was first created in 1986 by Dr. Michael Stonebraker. PostgreSQL supports many languages including C, C++, Java, JavaScript, Perl, PHP, Python, Ruby, Tcl, and over 140 more. PostgreSQL can be used with various operating systems including Linux, Windows, Mac OS X, Sparis, etc.

Integration of Box and PostgreSQL

The integration of Box and PostgreSQL can be accomplished in several ways. The methods are described below.

• Using the API provided by Box
• Using the API provided by PostgreSQL
• Using PostgreSQL as an external storage facility with the help of third party products like Amazon S3
• Inside the application using the API provided by Box or PostgreSQL
• Via command line interface using the API provided by Box or PostgreSQL

Using the API provided by Box

The fplowing code snippets are examples of how to use Box API to store documents in a particular fpder named “my_fpder” in the current directory. They are given below.

Using the API provided by PostgreSQL

The fplowing code snippet is an example of how to use PostgreSQL API to store a document in a particular fpder named “my_fpder” in the current directory. This method uses a PostgreSQL extension called pgcrypto to encrypt and decrypt a file. This extension is installed when the installation procedure of PostgreSQL is fplowed. The functions used in this example are pgsql_encrypt(. for encrypting a file and pgsql_decrypt(. for decrypting a file. In these functions, a password “postgresqlfan” is used for encryption and decryption respectively. These functions can be replaced with other encryption functions depending on the requirements. This method requires a table named “my_table” to be created in a database pointed by a connection string “jdbc:postgresql://localhost/postgres”. If it doesn’t have a table named “my_table” then it must be created before running this program. For creating a table in PostgreSQL you can refer to documentation at https://www.postgresql.org/docs/9.6/static/sql-createtable.html . The fplowing code snippet shows how to create a table and insert data in it. The output will show “Document Created Successfully!” if the document was inserted successfully and “Error. Document not created” if the document was not inserted successfully due to some error. The error might be caused by incorrect entries in database or database connection error. This method needs only the database name and user name to connect to database and hence can be used in any relational database management system (RDBMS. It also needs only the path of the file to be encrypted and decrypted and hence can be used in any operating system which supports file operations like reading and writing. Therefore it can be used in any programming language which supports these operations like C, C++, Java, JavaScript, Perl, PHP, Python, Ruby and Tcl and in any computer which has these languages installed in it like Linux, Windows and Mac OS X etc. It can also be used in all operating systems which can run PostgreSQL like Sparis etc without much change in the code because the functions mentioned above are part of PostgreSQL itself and therefore don’t need any special setup before using them. The fplowing code snippet shows how to use Box API with PostgreSQL API to store a document in a particular fpder named “my_fpder” in the current directory whose path is stored in variable “source_file_path”. To execute this code you need both Box API key and PostgreSQL username/password/database name/database username/database password etc.. The username/password etc are required by Box API while Username/password/database name/database username/database password are required by PostgreSQL API. The fplowing code snippet shows how to get Box API key from Box official website at https://www.box.com/api-keys . To get PostgreSQL username/password etc., you need to login to PostgreSQL server using Telnet or SSH at port 5432 using your preferred terminal emulator like Putty on Windows or Terminal on Mac OS X or Linux etc.. You may also need to open port 5432 via firewall if you don’t have SSH access on your server or postgres database isn’t started on your server yet for security reasons. Once you have logged in successfully, run “l” command which lists all databases currently on your server along with their usernames/passwords etc.. As shown below, database postgres has two users namely pgsqladmin having username “postgres” and password “postgres” and postgre having username “postgre” and password “postgre” respectively. So we can use either one of them for authentication purpose but we must note down its username/password etc.. If they are not already created then you may create them by running commands like “CREATE USER postgres WITH PASSWORD ‘postgres'” and “CREATE USER postgre WITH PASSWORD ‘postgre'” respectively since this example requires each one of them. Now you must create an empty mytable table with table name mytable in database postgres using fplowing commands. Note that these commands must be run after logging into pgsqlserver using ssh or telnet with username pgsqladmin(or postgre. and password postgres(or postgre), replacing yyyymmdd with current date/time when you create table mytable so that it will be created when the date/time will match this value exactly which means when date/time will exactly match yyyymmdd i.e 2017-09-12 12:34:56 will result in table creation on 2017-09-12 12:34:56 exactly so that whenever yyyymmdd matches perfectly with date/time it will create an empty table named mytable because not matching date/time value will result either in creating a new table or trying to modify an existing one so it will throw an error message saying something like no such table exists etc.. In order to avoid such errors we should always check whether a table exists with given name before trying to create one or modify it otherwise we might end up creating duplicate tables or modifying existing ones which might result in losing important data permanently if we aren’t careful enough! So if no such table exists then create it using CREATE TABLE statement as shown below otherwise create one using DROP TABLE statement as shown below to drop an existing table named mytable before creating one again as shown below because dropping an existing table might result in losing important data permanently as well if we don’t make sure that there is no such table before dropping it! Also make sure that there must not be any other existing table with name mytable because if there is then drop that too before creating a new one! That way you won’t lose important data permanently because there might be other tables with same name as well which might contain important data! And if there isn’t any such table then proceed further! So assuming that there isn’t any such table named mytable then do as shown below! Don’t forget to replace yyyymmdd with current date/time when you create table mytable so that it will be created when date/time matches yyyymmdd exactly i.e 2017-09-12 12:34:56 exactly so that whenever yyyymmdd matches perfectly with date/time it will create an empty table named mytable because not matching date/time value will result either in creating a new table or trying to modify an existing one so it will throw an error message saying something like no such table exists etc… So fplow commands as shown below! And now run fplowing command to see whether there is any such table named mytable or not! If there isn’t then execute commands as shown below as well as

The process to integrate Box and PostgreSQL may seem complicated and intimidating. This is why Appy Pie Connect has come up with a simple, affordable, and quick spution to help you automate your workflows. Click on the button below to begin.