jeudi 4 juin 2015

Sql database in unity for android application

Here below is my code for basic usage of sql database read write in unity. as it works in unity player,but when i port it for android it doesnt work in it. So please tell me where should i change in my code to work that for android applications.

1)file id dbAccess.js

#pragma strict
import System.Data;  //  import our  data class
import Mono.Data.Sqlite; //  import sqlite
import System.Collections.Generic;

class dbAccess {
    // variables for basic query access
    private var connection : String;
    private var dbcon : IDbConnection;
    private var dbcmd : IDbCommand;
    private var reader : IDataReader;

    function OpenDB(p : String) {
    connection = "URI=file:" + p; // we set the connection to our database
    dbcon = new SqliteConnection(connection);
    dbcon.Open();
    } 
    function BasicQuery(q : String, r : boolean):IDataReader{  // run a basic Sqlite query
        dbcmd = dbcon.CreateCommand(); // create empty command
        dbcmd.CommandText = q; // fill the command
        reader = dbcmd.ExecuteReader(); // execute command which returns a reader
        if(r) { // if we want to return the reader
            return reader; // return the reader
        }
    }

    // This returns a 2 dimensional ArrayList with all the
    //  data from the table requested
    function ReadFullTable(tableName : String) {
        var query : String;
        query = "SELECT * FROM " + tableName;
        dbcmd = dbcon.CreateCommand();
        dbcmd.CommandText = query; 
        reader = dbcmd.ExecuteReader();
        var readArray = new ArrayList();
        while(reader.Read()) { 
            var lineArray = new ArrayList();
            for (var i:int = 0; i < reader.FieldCount; i++)
                lineArray.Add(reader.GetValue(i)); // This reads the entries in a row
            readArray.Add(lineArray); // This makes an array of all the rows
        }
        return readArray; // return matches
    }

    // This function deletes all the data in the given table.  
    function DeleteTableContents(tableName : String) {
    var query : String;
    query = "DELETE FROM " + tableName;
    dbcmd = dbcon.CreateCommand();
    dbcmd.CommandText = query; 
    reader = dbcmd.ExecuteReader();
    }

    function CreateTable(name : String, col : Array, colType : Array) { // Create a table, name, column array, column type array
        var query : String;
        query  = "CREATE TABLE " + name + "(" + col[0] + " " + colType[0];
        for(var i=1; i<col.length; i++) {
            query += ", " + col[i] + " " + colType[i];
        }
        query += ")";
        dbcmd = dbcon.CreateCommand(); // create empty command
        dbcmd.CommandText = query; // fill the command
        reader = dbcmd.ExecuteReader(); // execute command which returns a reader

    }

    function InsertIntoSingle(tableName : String, colName : String, value : String) { // single insert 
        var query : String;
        query = "INSERT INTO " + tableName + "(" + colName + ") " + "VALUES (" + value + ")";
        dbcmd = dbcon.CreateCommand(); // create empty command
        dbcmd.CommandText = query; // fill the command
        reader = dbcmd.ExecuteReader(); // execute command which returns a reader
    }

    function InsertIntoSpecific(tableName : String, col : Array, values : Array) { // Specific insert with col and values
        var query : String;
        query = "INSERT INTO " + tableName + "(" + col[0];
        for(var i=1; i<col.length; i++) {
            query += ", " + col[i];
        }
        query += ") VALUES (" + values[0];
        for(i=1; i<values.length; i++) {
            query += ", " + values[i];
        }
        query += ")";
        dbcmd = dbcon.CreateCommand();
        dbcmd.CommandText = query; 
        reader = dbcmd.ExecuteReader();
    }

    function InsertInto(tableName : String, values : Array) { // basic Insert with just values
        var query : String;
        query = "INSERT INTO " + tableName + " VALUES (" + values[0];
        for(var i=1; i<values.length; i++) {
            query += ", " + values[i];
        }
        query += ")";
        dbcmd = dbcon.CreateCommand();
        dbcmd.CommandText = query; 
        reader = dbcmd.ExecuteReader(); 
    }

    // This function reads a single column
       //  Ex. - SingleSelectWhere("puppies", "breed", "earType", "=", "floppy")
    //  returns an array of matches from the command: SELECT breed FROM puppies WHERE earType = floppy;
    //function SingleSelectWhere(tableName : String, itemToSelect : String, wCol : String, wPar : String, wValue : String):Array { // Selects a single Item
    function SingleSelectWhere(tableName : String, itemToSelect : String, wCol : String, wPar : String, wValue : String):List.<String>{ // Selects a single Item
        var query : String;
        query = "SELECT " + itemToSelect + " FROM " + tableName + " WHERE " + wCol + wPar + wValue;  
        //  wCol is the WHERE column, wPar is the operator you want to use to compare with, 
         //  and wValue is the value you want to compare against.
        dbcmd = dbcon.CreateCommand();
        dbcmd.CommandText = query; 
        reader = dbcmd.ExecuteReader();
        //var readArray = new Array();
        var readArray:List.<String> = new List.<String>();
        while(reader.Read()) { 
            //readArray.Push(reader.GetString(0)); // Fill array with all matches
            var jp:String = reader.GetString(0);
            Debug.Log(jp);
            readArray.Add(jp); // Fill array with all matches
            var url:String = reader.GetString(1);
            Debug.Log(url);
            readArray.Add(url); // Fill array with all matches
        }
        return readArray; // return matches
    }

    function CloseDB() {
        reader.Close(); // clean everything up
        reader = null; 
        dbcmd.Dispose(); 
        dbcmd = null; 
        dbcon.Close(); 
        dbcon = null; 
    }
}

2)ScriptThatUsesTheDatabase.js (attached to camera)

#pragma strict
// Right now, it'll load TestDB.sqdb in the project's root folder.
// If one doesn't exist, it will be automatically created.
public var DatabaseName : String = "TestDB.sqdb";

// This is the name of the table we want to use
public var TableName : String = "TestTable";
var db : dbAccess;

function Start() {
     db = new dbAccess();   ///creates object of dbAccess
    db.OpenDB(DatabaseName);    // Let's make sure we've got a table to work with as well!
    var tableName = TableName;
    var columnNames = new Array("firstName","lastName");
    var columnValues = new Array("text","text");
    try {
        db.CreateTable(tableName,columnNames,columnValues);
        }
    catch(e) {// Do nothing - our table was already created
        //- we don't care about the error, we just don't want to see it
    }
}

// These variables just hold info to display in our GUI
var firstName : String = "First Name";
var lastName : String = "Last Name"; 
var DatabaseEntryStringWidth = 100;
var scrollPosition : Vector2;
var databaseData : ArrayList = new ArrayList();

// This GUI provides us with a way to enter data into our database
//  as well as a way to view it
function OnGUI() {
    GUI.Box(Rect (25,25,Screen.width - 50, Screen.height - 50),""); 
    GUILayout.BeginArea(Rect(50, 50, Screen.width - 100, Screen.height - 100));
    // This first block allows us to enter new entries into our table
        GUILayout.BeginHorizontal();
            firstName = GUILayout.TextField(firstName, GUILayout.Width (DatabaseEntryStringWidth));
            lastName = GUILayout.TextField(lastName, GUILayout.Width (DatabaseEntryStringWidth));
        GUILayout.EndHorizontal();

        if (GUILayout.Button("Add to database")) {
            // Insert the data
            InsertRow(firstName,lastName);
            // And update the readout of the database
            databaseData = ReadFullTable();
        }
        // This second block gives us a button that will display/refresh the contents of our database
        GUILayout.BeginHorizontal();
            if (GUILayout.Button ("Read Database")) 
                databaseData = ReadFullTable();
            if (GUILayout.Button("Clear"))
                databaseData.Clear();
        GUILayout.EndHorizontal();

        GUILayout.Label("Database Contents");
        scrollPosition = GUILayout.BeginScrollView(scrollPosition, GUILayout.Height(100));
            for (var line : ArrayList in databaseData) {
                GUILayout.BeginHorizontal();
                for (var s in line) {
                    GUILayout.Label(s.ToString(), GUILayout.Width(DatabaseEntryStringWidth));
                }
                GUILayout.EndHorizontal();
            }

        GUILayout.EndScrollView();
        if (GUILayout.Button("Delete All Data")) {
            DeleteTableContents();
            databaseData = ReadFullTable();
        }

    GUILayout.EndArea();
}

// Wrapper function for inserting our specific entries into our specific database and table for this file
function InsertRow(firstName:String, lastName:String) {
    var values = new Array(("'"+firstName+"'"),("'"+lastName+"'"));
    db.InsertInto(TableName, values);
}

// Wrapper function, so we only mess with our table.
function ReadFullTable() {
    return db.ReadFullTable(TableName);
}

// Another wrapper function...
function DeleteTableContents() {
    db.DeleteTableContents(TableName);
}

please do help,

Aucun commentaire:

Enregistrer un commentaire