How to use Cloud RDS library

Applicable for

wec2013 | wec7 | wince5 | wince6

Amazon Relational Database Service (Amazon RDS)

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and scalable storage capacity while managing time-consuming database administration tasks, allowing you up to focus on your applications and business.

Toradex RDS Cloud library provides access to MySQL instance running on Amazon RDS cloud service which allows user application running on a Toradex computer module to access the database and use it from anywhere in the world. Amazon RDS automatically patches the database software and taking backup of database, storing it for user-defined retention period and enabling point-in-time recovery. User will benefit from the flexibility of being able to scale the compute resources or storage capacity associated with your Database Instance (DB Instance) using Amazon Management Console.

Notes:

Toradex RDS Cloud library only supports MySQL instance on Amazon RDS at the moment. Microsoft SQL Server, Oracle and PostgreSQL are not yet supported.
Toradex RDS Cloud library APIs support accessing MySQL instance. Management of the MySQL instance has to be done through the AWS Management console, using http://aws.amazon.com/console/
For MySQL query statements syntax, please refer http://dev.mysql.com/doc/refman/5.6/en/sql-syntax.html
Once the Amazon RDS MySQL instance has been set up properly, database table creation and set up can also be handled using MySQL Workbench http://www.mysql.com/products/workbench/
Internet connection is required to use this Library.
We have tested queries resulting in data downloads upto 40MB approx.
Huge data uploads or data access like retrieving all 1000+ rows of a table at once is not recommended.
In case you need more support, please get in touch with our engineers at support@toradex.com

For more information on Amazon RDS, please refer http://aws.amazon.com/rds/

Setting up of MySQL instance ( Amazon RDS )

Please refer:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Welcome.html
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_GettingStarted.html

Open MySQL Handle

Before calling any other function, call MySqlOpen() with all input parameters: Sql Account Handle, Amazon RDS Endpoint, Sql User Name, Sql User Password

These required parameters will be available after setting up the MySQL instance through the AWS Management Console.

It will return TRUE if successful or FALSE if the call fails. Use GetLastError() to get extended details.

MYSQL sqlAccount = NULL;		// MySQL Account Handle
CHAR sqlInput[MYSQL_QUERY_STRING_LENGTH] = {0};
 
if (!MySqlOpen(&sqlAccount, AMAZON_RDS_MYSQL_INSTANCE_ENDPOINT, SQL_USER, SQL_USER_PASSWORD))
{
	printf("\nOpen failed %d\n", GetLastError());					
}
else
{
	printf("\nOpen successful\n");
}

sqlAccount needs to be passed to all function calls after this point.

Connect to Amazon RDS MySQL instance

MySqlConnect() or MySqlConnectWithSsl() is used to connect to the Amazon RDS MySQL instance. Using MySqlConnectWithSsl() results in use of SSL encryption for all later MySQL requests. This is the second function to be called after MySqlOpen(). It is mandatory to call MySqlConnect() or MySqlConnectWithSsl() (depending on whether user requires encryption or not) after MySqlOpen(), otherwise all further calls will automatically fail.

Amazon RDS creates an SSL certificate and installs the certificate on the DB instance when Amazon RDS provisions the instance. These certificates are signed by a certificate authority. The public key is stored at https://rds.amazonaws.com/doc/mysql-ssl-ca-cert.pem . This certificate should be downloaded to the Toradex Module and added to the device via Control Panel, if MySqlConnectWithSsl() needs to be used and SSL is required. Change the extension of the file to .cer before adding. If certificate is not added, MySqlConnectWithSsl() will fail.

First parameter is the sqlAccount handle returned by MySqlOpen() function call
Second parameter specifies the port number to be used for the connection. Specifying 0 will use port number 3306 which is the default port for MySQL.
It will return TRUE if successful or FALSE if the call fails. Use GetLastError() or WSAGetLastError() to get extended error details.

if (!MySqlConnect(sqlAccount, 0))
{
	printf("\nConnect failed. Error %d. Network Error %d\n", GetLastError(), WSAGetLastError());
	printf("MySQL error number: %d\n", sqlAccount->sqlErrorNumber);
	printf("MySQL error message: %s\n", sqlAccount->sqlErrorString);	
}
else
{
	printf("\nConnection successful\n");
}

if (!MySqlConnectWithSsl(sqlAccount, 0))
{
	printf("\nConnect failed. Error %d. Network Error %d\n", GetLastError(), WSAGetLastError());
	printf("MySQL error number: %d\n", sqlAccount->sqlErrorNumber);
	printf("MySQL error message: %s\n", sqlAccount->sqlErrorString);	
}
else
{
	printf("\nConnection successful\n");
}

Querying rows of database

MySqlRowQuery() function is use to query the database for row data, which is stored in a local file after the function call.

First parameter is the sqlAccount handle returned by MySqlOpen() function call
Second parameter is the SELECT query statement. All rules applicable for constructing MySql SELECT queries are applicable. For example: "SELECT * FROM mysqldb.TestDbTable" where mysqldb is the schema/database name and TestDbTable is the MySQL table name
Third parameter is the path of the local file in which the row query data will be stored
Fourth parameter will contain the number of columns present in the table
It will return TRUE if successful or FALSE if the call fails. Use GetLastError() or WSAGetLastError() to get extended error details.

if (!MySqlRowQuery(sqlAccount, sqlInput, filePath, &numberOfColumns))
{
	if (NULL != sqlAccount)
	{
		printf("Query failed. Error %d. Network Error %d\n", GetLastError(), WSAGetLastError());
		printf("MySQL error number: %d\n", sqlAccount->sqlErrorNumber);
		printf("MySQL error message: %s\n", sqlAccount->sqlErrorString);	
	}
}	
else
{
	printf("\nQuery successful\n");
	printf("\nNumber of columns: %d\n", numberOfColumns);
}

List fields of table in database

MySqlListFields() is use to get the column field details, which is stored in a local file after the function call.

First parameter is the sqlAccount handle returned by MySqlOpen() function call
Second parameter is the table name for which the column fields have to be retrieved. For Example: "mysqldb.TestDbTable" where "mysqldb" is the schema/database name and "TestDbTable" is the table name
Third parameter is the path of the local file in which the column field results will be stored
Fourth parameter will contain the number of columns present in the table
It will return TRUE if successful or FALSE if the call fails. Use GetLastError() or WSAGetLastError() to get extended error details.

if (!MySqlListFields(sqlAccount, sqlInput, filePath, &numberOfColumns))
{
	if (NULL != sqlAccount)
	{
		printf("List fields failed. Error %d. Network Error %d\n", GetLastError(), WSAGetLastError());
		printf("MySQL error number: %d\n", sqlAccount->sqlErrorNumber);
		printf("MySQL error message: %s\n", sqlAccount->sqlErrorString);	
		}
}	
else
{
	printf("\nList fields successful\n");
	printf("\nNumber of columns: %d\n", numberOfColumns);
}

Create a new schema/database

MySqlCreateDb() is used to create a new database table

First parameter is the sqlAccount handle returned by MySqlOpen() function call
Second parameter is the CREATE query statement. All rules applicable for constructing MySql CREATE queries are applicable. For Example: "CREATE TABLE mysqldb.TestDbTable(Column1 int primary key auto_increment, Column2 int)" where "mysqldb" is the schema/database name and "TestDbTable" is the table name
It will return TRUE if successful or FALSE if the call fails. Use GetLastError() or WSAGetLastError() to get extended error details.

if (!MySqlCreateDb(sqlAccount, sqlInput))
{
	if (NULL != sqlAccount)
	{
		printf("Create database failed. Error %d. Network Error %d\n", GetLastError(), WSAGetLastError());
		printf("MySQL error number: %d\n", sqlAccount->sqlErrorNumber);
		printf("MySQL error message: %s\n", sqlAccount->sqlErrorString);			
	}
}
else
{
	printf("\nDatabase created\n");
}

Drops an existing schema/database

MySqlDropDb() is used to delete and existing schema/database

First parameter is the sqlAccount handle returned by MySqlOpen() function call
Second parameter is the name of the schema/database to drop. For Example: "mysqldb.TestDbTable" where "mysqldb" is the schema/database name and "TestDbTable" is the table name
It will return TRUE if successful or FALSE if the call fails. Use GetLastError() or WSAGetLastError() to get extended error details.

if (!MySqlDropDb(sqlAccount, sqlInput))
{
	if (NULL != sqlAccount)
	{
		printf("Drop database failed. Error %d. Network Error %d\n", GetLastError(), WSAGetLastError());
		printf("MySQL error number: %d\n", sqlAccount->sqlErrorNumber);
		printf("MySQL error message: %s\n", sqlAccount->sqlErrorString);
	}
}
else
{
	printf("\nDatabase dropped\n");
}

Check if connection is alive

MySqlPing() is used to check if connection to the MySQL instance is alive

First parameter is the sqlAccount handle returned by MySqlOpen() function call
It will return TRUE if successful or FALSE if the call fails. Use GetLastError() or WSAGetLastError() to get extended error details.

if (!MySqlPing(sqlAccount))
{
	printf("Connect failed. Error %d. Network Error %d\n", GetLastError(), WSAGetLastError());								
}
else
{
	printf("\nServer connection OK\n");
}

Update data in the table

MySqlUpdateQuery() is use to update data in the database table

First parameter is the sqlAccount handle returned by MySqlOpen() function call
Second parameter is the UPDATE query statement. All rules applicable for constructing MySql UPDATE queries are applicable. For example: "UPDATE mysqldb.TestDbTable SET Column2 = 1001 WHERE Column1 = 900" where mysqldb is the schema/database name and TestDbTable is the table name
It will return TRUE if successful or FALSE if the call fails. Use GetLastError() or WSAGetLastError() to get extended error details.

if (!MySqlUpdateQuery(sqlAccount, sqlInput))
{
	if (NULL != sqlAccount)
	{
		printf("Update failed. Error %d. Network Error %d\n", GetLastError(), WSAGetLastError());
		printf("MySQL error number: %d\n", sqlAccount->sqlErrorNumber);
		printf("MySQL error message: %s\n", sqlAccount->sqlErrorString);	
	}
}
else
{
	printf("\nNumber of affected rows is %d\n", MySqlAffectedRows(sqlAccount));
}

Insert data in table

MySqlInsertQuery() is use to insert data in the table

First parameter is the sqlAccount handle returned by MySqlOpen() function call
Second parameter is the INSERT query statement. All rules applicable for constructing MySql INSERT queries are applicable. For example: "INSERT INTO mysqldb.TestDbTable (Column1, Column2) VALUES(900, 1000)" where mysqldb is the schema/database name and TestDbTable is the table name
It will return TRUE if successful or FALSE if the call fails. Use GetLastError() or WSAGetLastError() to get extended error details.

if (!MySqlInsertQuery(sqlAccount, sqlInput))
{
	if (NULL != sqlAccount)
	{
		printf("Insert failed. Error %d. Network Error %d\n", GetLastError(), WSAGetLastError());		
		printf("MySQL error number: %d\n", sqlAccount->sqlErrorNumber);
		printf("MySQL error message: %s\n", sqlAccount->sqlErrorString);		
	}
}
else
{
	printf("\nNumber of affected rows is %d\n", MySqlAffectedRows(sqlAccount));
 
}

Delete data in the table

MySqlDeleteQuery() is used to delete data in the table

First parameter is the sqlAccount handle returned by MySqlOpen() function call
Second parameter is the DELETE query. All rules applicable for constructing MySql DELETE queries are applicable. For example: "DELETE FROM mysqldb.TestDbTable WHERE Column1 = 900" where mysqldb is the schema/database name and TestDbTable is the table name
It will return TRUE if successful or FALSE if the call fails. Use GetLastError() or WSAGetLastError() to get extended error details.

if (!MySqlDeleteQuery(sqlAccount, sqlInput))
{
	if (NULL != sqlAccount)
	{
		printf("Delete failed. Error %d. Network Error %d\n", GetLastError(), WSAGetLastError());
		printf("MySQL error number: %d\n", sqlAccount->sqlErrorNumber);
		printf("MySQL error message: %s\n", sqlAccount->sqlErrorString);			
	}
}
else
{
	printf("\nNumber of affected rows is %d\n", MySqlAffectedRows(sqlAccount));
}

Add, Delete of Modify columns in a table

MySqlAlterquery() is used to add, delete or modify columns in the table

First parameter is the sqlAccount handle returned by MySqlOpen() function call
Second parameter is the ALTER query. All rules applicable for constructing MySql ALTER queries are applicable. For example: "ALTER TABLE mysqldb.TestDbTable ADD Column3 int" where mysqldb is the schema/database name and TestDbTable is the table name
It will return TRUE if successful or FALSE if the call fails. Use GetLastError() or WSAGetLastError() to get extended error details.

if (!MySqlAlterQuery(sqlAccount, sqlInput))
{
	if (NULL != sqlAccount)
	{
		printf("Alter failed. Error %d. Network Error %d\n", GetLastError(), WSAGetLastError());
		printf("MySQL error number: %d\n", sqlAccount->sqlErrorNumber);
		printf("MySQL error message: %s\n", sqlAccount->sqlErrorString);
	}
}
else
{
	printf("\nNumber of affected rows is %d\n", MySqlAffectedRows(sqlAccount));
}

Get the number of records in the table

MySqlGetNumberOfRows() is used to get the number of records in the database table

First parameter is the sqlAccount handle returned by MySqlOpen() function call
Second parameter is the table name for which number of records has to be retrieved
Third parameter will return the number of records in the table
It will return TRUE if successful or FALSE if the call fails. Use GetLastError() or WSAGetLastError() to get extended error details.

if (!MySqlGetNumberOfRows(sqlAccount, sqlInput, &numberOfRows))
{
	if (NULL != sqlAccount)
	{
		printf("Get records failed. Error %d. Network Error %d\n", GetLastError(), WSAGetLastError());
		printf("MySQL error number: %d\n", sqlAccount->sqlErrorNumber);
		printf("MySQL error message: %s\n", sqlAccount->sqlErrorString);	
	}
}
else
{
	printf("\nNumber of records in table is %d\n", numberOfRows);
}

Get the number of affected rows

MySqlAffectedRows() returns the number of rows, changed, deleted or inserted

First parameter is the sqlAccount handle returned by MySqlOpen() function call
It will return TRUE if successful or FALSE if the call fails. Use GetLastError() or WSAGetLastError() to get extended error details.

printf("\nNumber of affected rows is %d\n", MySqlAffectedRows(sqlAccount));

List tables in the database instance

MySqlListTables() provides the list of tables in the database

First parameter is the sqlAccount handle returned by MySqlOpen() function call
Second parameter is the name of the MySQL database instance
Third parameter is the path of the local file in which the table field results will be stored
It will return TRUE if successful or FALSE if the call fails. Use GetLastError() or WSAGetLastError() to get extended error details.

if (!MySqlListTables(sqlAccount, sqlInput, filePath))
{
	if (NULL != sqlAccount)
	{
		printf("List tables failed. Error %d. Network Error %d\n", GetLastError(), WSAGetLastError());
		printf("MySQL error number: %d\n", sqlAccount->sqlErrorNumber);
		printf("MySQL error message: %s\n", sqlAccount->sqlErrorString);	
	}
}
else
{
	printf("\nList of tables read successfully\n");
}

Set the default schema of the connection

MySqlInitDb() sets the default schema for the currently opened connection

First parameter is the sqlAccount handle returned by MySqlOpen() function call
Second parameter is the name of the schema to use for all subsequent requests
It will return TRUE if successful or FALSE if the call fails. Use GetLastError() or WSAGetLastError() to get extended error details.

if (!MySqlInitDb(sqlAccount, sqlInput))
{
	if (NULL != sqlAccount)
	{
		printf("Error %d. Network Error %d\n", GetLastError(), WSAGetLastError());
		printf("MySQL error number: %d\n", sqlAccount->sqlErrorNumber);
		printf("MySQL error message: %s\n", sqlAccount->sqlErrorString);	
	}
}
else
{
	printf("\nDefault schema changed to %s\n", sqlInput);
}

Close connection to the Amazon RDS MySQL instance

MySqlQuit() closes the connection to the MySQL instance

First parameter is the sqlAccount handle returned by MySqlOpen() function call
It will return TRUE if successful or FALSE if the call fails. Use GetLastError() or WSAGetLastError() to get extended error details.

MySqlQuit(sqlAccount);

Close the handle for MySQL connection

MySqlClose() closes the handle opened by call to MySqlOpen(). This function must be called before closing any application in which this library is being used.

First parameter is the address of the sqlAccount handle returned by MySqlOpen() function call
It will return TRUE if successful or FALSE if the call fails. Use WSAGetLastError() to get extended error details.

if (!MySqlClose(&sqlAccount))
{
	printf("Close failed. Network Error %d\n", WSAGetLastError());						
}

Clear MySQL error number and error string

MySqlClearError() clears the MySQL error number and error string which would have occurred from a previous error.

First parameter is the address of the sqlAccount handle returned by MySqlOpen() function call

MySqlClearError(sqlAccount)

Need more help?

Ask the experts in our Community!