Schogini

NSString *message = [[NSString alloc] initWithUTF8String:(char *)sqlite3_column_text(statement, colIndex)];

This is the code used to get the string result from a query in sqlite3. Here the obtained result is a char value and we have to convert it to NSString. Here, the first argument is statement, which is the sqlite3_stmt object, declared as follows
sqlite3_stmt *statement;
The second argument is the integer value, which represent the column index from the query.

sqlite3_column_int(statement, 0);

This is the code used to get the integer result from a query in sqlite3. Here the first argument is statement, which is the sqlite3_stmt object, declared as follows

sqlite3_stmt *statement;

The second argument is the integer value, which represent the column index from the query.

sqlite3_last_insert_rowid(database);

This is the code used to get the last inserted rowid in sqlite3. Here database is the sqlite3 object, declared as follows

sqlite3 *database;

Backing up your database is very important in a database driven application. Ideally database should be backed up often. There are a lot of ways to accomplish this. I’ll show how to back up database using command-line commands.

The command to run the backup is:

mysqldump -u mysqluser -p mysqldatabase

“mysqldump” program is a tool for creating database backups.

The parameters used are:

“-u” switch means you’re going to specify a username to connect with, which must follow, like “-u mysqluser” above

“-p” switch means you’re either going to immediately specify the password to use (with no space), or it’ll prompt you for one

The final parameter used in the example above is the name of the database to backup

If you run the command above, you would see the contents of your database go scrolling on the screen.

To place the contents of the output into a file, execute the following command

mysqldump -u mysqluser -p mysqldatabase > db_backup.sql

Now you should be able to see a file named “db_backup.sql”, and if you open it you can see a SQL script with the structure and content of your database ready for restoration or migration.

Now compress this SQL script using GZip compression. Instead of gzip, you can use bzip, tar etc..

To add compression into this command, just execute the following command.

mysqldump -u mysqluser -p mysqldatabase | gzip > db_backup.sql.gz

Now your database backup is ready to import/export for future use.

NSString *storePath = [[self applicationDocumentsDirectory]stringByAppendingPathComponent:@"DataBase.sqlite"];
// Check to see if the store already exists
NSFileManager *fileManager = [NSFileManager defaultManager];
// If the expected store doesn't exist, copy the default store.
if (![fileManager fileExistsAtPath:storePath])
{
NSString *defaultStorePath = [[NSBundle mainBundle] pathForResource:@"DataBase" ofType:@"sqlite"];
if (defaultStorePath)
{
[fileManager copyItemAtPath:defaultStorePath toPath:storePath error:NULL];
}
}

To connect with a database, you need the following things.

1. A Database System.
2. A Database Connector
3. Apache Tomcat
4. jdk

The database system we use is MySQL. You can download it from the following location.

http://www.filehippo.com/download_mysql/

At the time of installation, you can configure the username and password.

After installation, you can go to Start->Programs->MySQL->MySQL Command Line Client. Give password to login.

You can create a database and table using the following commands in MySQL.

SQL> create database Company;
SQL> use Company;
SQL> create table Employee(Empid varchar(20),EmpName varchar(20),Designation varchar(20),primary key(Empid));
SQL> insert into Employee values('SCH001','Krishna','Software Engineer');
SQL> insert into Employee values('SCH002','Steve','Interface Designer');

Also we need an appropriate database connector which makes connection between jdk and MySQL.
You can download the connector from the following link.

http://mysqlmirror.netandhost.in/Downloads/Connector-J/mysql-connector-java-5.1.11.zip

Extract the contents of the zipped file to any location in your computer.

After doing this, you need to set class path in Envirornment variables. You can do that by following the steps below:

My Computer->Properties->Advanced->Environment Variables->User Variables.

You can click New and have 2 text fields: Variable Name and Variable Value.

You can give as follows:

Variable Name: classpath
Variable Value: .;C:\mysql-connector-java-5.0.8\mysql-connector-java-5.0.8-bin.jar;

Note: If you have a different version of Connector, you need to give appropriate connector name.
After this, create a JSP page with the following code. The Variable Value should also contain .; in the beginning and ; in the end. Also copy the jar file of Connector inside the lib directory of Tomcat.

Now you can write a JSP page with the following code as follows:

<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”
“http://www.w3.org/TR/html4/loose.dtd”>

<%@ page import=”java.sql.*” %>
<%@ page import=”java.io.*” %>

<html>
<head>
<title>Connection with mysql database</title>
</head>
<body>

<%
try {

String connectionURL = “jdbc:mysql://localhost:3306/Company”;
Connection connection = null;
Class.forName(“com.mysql.jdbc.Driver”).newInstance();
connection = DriverManager.getConnection(connectionURL, “root”, “root”);
Statement st=connection.createStatement();
ResultSet rs=st.executeQuery(“select * from Employee”);
%>

<table cellpadding=”15″ border=”1″ style=”background-color: #ffffcc;”>

<%
while (rs.next()) {
%>
<tr>
<td><%=rs.getString(1)%></td>
<td><%=rs.getString(2)%></td>
<td><%=rs.getString(3)%></td>
</tr>
<% } %>
<% }

catch(Exception ex){
out.println(“Unable to connect to database.”);
}
%>

</body>
</html>

Place the JSP file inside a folder which should be resided in C:\Program Files\Apache Software Foundation\Tomcat 6.0\webapps\MyDatabase

Here I gave the folder name as “MyDatabase”.

Make sure that the Tomcat has been started, Take any browser, type the folowing link.

http://localhost:8080/MyDatabase/DatabaseDemo.jsp

You can see the values in the JSP Page in the form of a Table.