jeudi 30 avril 2015

getting limited rows at each page when clicking next button

This is code from phone gap index HTML page function querySuccess(tx, results){ var len = results.rows.length; var output = ''; for (var i=0; i' + results.rows.item(i).list_action + '';}messageElement.html('

There are ' + len + ' items in your list:

'); listElement.html('
' + output + '
');}

After populating this database I need that how to get limited rows at each page, for ex 5 rows after clicking to next button. But the button is not present there. Then how to create it. Also how to pass the current ID to another HTML page.

Is it possible to compare form submitted strings in array to string values in a model database?

This is more going into rails theory. I want to compare strings submitted by a user to strings in a populated model. Is this possible? Even if it is possible would it be better to create a user in putted model and compare the strings from there?

Is there any documentation on how to achieve this

How to implement unique user data

I have an android app that organises a list of items. Each item has an id field that has to be user input and unique. This id is stored in an SQlitedatabase.
I'm wondering whats the best way to validate user input to make sure the ID's are unique?

Android Database Writing error

So i got an app that as an internal database, and the app crashes with this error :

04-30 20:46:30.836    1647-1647/prosis.guiatour E/SQLiteLog﹕ (1) no such column: Basílica_Santa_Luzia

and the code that this is refering to is :

public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_QUERRY);
    Log.e("Database Operations"," Table Created....");
    db.execSQL(addingInfo("Basílica_Santa_Luzia", "Stuff", "10.43597", "-10.5747"));
Log.e("Database Operations"," Data Inserted");
}
public String addingInfo(String nome, String cat, String lat, String longi){
    String Querry = "INSERT INTO "+ Table_name+" VALUES("+nome+","+cat+","+lat+","+longi+");";
    return Querry;
}

And this is my contructer querry :

private static final String CREATE_QUERRY = "CREATE TABLE "+ Contract.NewLocalInfo.Table_name+" ("+ Contract.NewLocalInfo.Nome+" TEXT,"+ Contract.NewLocalInfo.Categoria+" TEXT,"+ Contract.NewLocalInfo.Latitude+" TEXT,"+ Contract.NewLocalInfo.Longitude+" TEXT);";

Issues with changing sqlite database method names and strings

What I am doing:

I am creating an app. Some of the names of the items in my sqlitedatabase are being and some are being removed. I am using android studio and "Refactor" to make these changes. Everytime I make these changes though, I start getting issues within my manifest. What I am saying is let's say I have items A, B, C, D, E and don't want D and E. Also I want to rename A, B and C to X, Y, Z. Also I want to rename some of the methods in the DataBaseManager file, like updating items in the DB, changing items, etc. So if the method is called updateX, I want to rename it to updateZ.

Question: How do I remove elements of my database without these errors? Below are the errors I am getting:

Gradle gives me errors such as :

Missing "name" key attribute on element activity at AndroidManifest.xml:22:9
Execution failed for task ":app:processDebugManifest"

Also, it points parts of my manifest like these:

    android:label="@string/app_name"
    android:theme="@style/AppTheme"

and tells me the references are not there. However, they are there in my strings and styles xmls.

The other problem is the app pretends my main activity no longer exists.

ArrayList files for each date android sqlite

I'm doing a Android project and I need help with this:

When user click on the date in the calendar shown(custom created),it should bring up next screen with listview of the files that was created earlier and allow the user to add new files.I'm doing this with database and I can CRUD files but I don't know how to create new database for each date (when user click a date on the calendar).My database table has key ID,filename,userinput. Any help or suggestions will be appreciated. Thank you.

How do I get the return value of the EXISTS operator with QtSql?

The EXISTS operator always evaluates to one of the integer values 0 and 1. I tried to get them via QSqlQuery::value(int index). But somehow this result is not related to a column. How do I get the return value of the EXISTS operator with QtSql?

Implement SQLite command on GPU using CUDA.Net and windows OS

how to implement a subset of the SQLite command processor directly on the GPU using CUDA.Net on windows OS.

thank you.

PDO won't return results from a SQLite database, or any exceptions

So I have a bit of code that I just cannot figure out. I have error reporting turned on, and I have the appropriate try/catch blocks, but the code below is not functioning. When I run this code, all I get is bool(false) from the var_dump($result).

try
{
    $db = new PDO('sqlite:norming_database.db');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
    echo $e->getMessage();
}

/*... some other stuff in between ...*/

try
{
    $query = "SELECT stimulusName
            FROM responses
            WHERE workerId LIKE :workerId";
    $stmt = $db->prepare($query);
    $stmt->bindParam(':workerId', $_GET['testWorkerId']);
    $stmt->execute();
    $result = $stmt->fetch();
}
catch(PDOException $e)
{
    echo "PDO Error: " . $e->getMessage();
}

echo "<pre>" . var_dump($result) . "</pre>";

I'm getting no exceptions or errors. On top of that, if I run the query itself in my database manager (Valentia Studio), it returns exactly the result I'm expecting.

I suspect that there's some typo or something in my code that I'm failing to see after reading over it repeatedly. Is there anything blatantly wrong with how this is written?

Displaying items in listView from sqlite database (Android)

I am trying to populate a list view on android. I've written the code and it runs without errors, however it crashes when i try to run it. Below is the Populate list view method:

 private void populateListView() {
    Cursor cursor = db.getAllRows();
    String[] fromFieldNames = new String[]{MySQLiteHelper.KEY_TITLE, MySQLiteHelper.KEY_AUTHOR};
    int[] toViewIDs = new int[]{R.id.textView2, R.id.textView3};
    SimpleCursorAdapter myCursorAdapter;
    myCursorAdapter = new SimpleCursorAdapter(getBaseContext(), R.layout.itemlayout,cursor,fromFieldNames,toViewIDs,0);
    listView.setAdapter(myCursorAdapter);
}

Here is the getAllRows function from my SQLiteHelper class

 public Cursor getAllRows()
{
    SQLiteDatabase db = this.getReadableDatabase();
    String query = "SELECT * FROM " + TABLE_BOOKS;
    Cursor cursor = db.rawQuery(query, null);
    if(cursor!=null)
    {
       cursor.moveToFirst();
    }
   return cursor;
}

From the crash report, the error seems to be from :

 myCursorAdapter = new SimpleCursorAdapter(getBaseContext(), R.layout.itemlayout,cursor,fromFieldNames,toViewIDs,0);

I cannot seem to figure out what the problem is. Thank you

Altering db file is causing SQLiteDatabase.openDatabase() to crash

I have a normal opening of a database file, it works perfectly fine when opening a blank db file with only the metadata table, but as soon as I make another table, it causes the app to crash. Maybe it's different version db files? Please comment if you need more of the code

private static String DB_PATH = "/data/data/com.example.andrew.ubair4/databases/";    
private static String DB_NAME = "coordinates";
String myPath = DB_PATH + DB_NAME;

private SQLiteDatabase db;
public DataBaseHelper(Context context){

    super(context, DB_NAME, null, 1);
    this.myContext = context;
    Log.d("TAGG","enter constructor");

    db = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);      //<---- crashes right here IF I have a second table in the database
    Log.d("TAGG","2");

My metadata table:

CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US')
INSERT INTO "android_metadata" VALUES ('en_US')

Add 1 more table:

CREATE TABLE something (
  column1, 
  column2, 
  column3, 
  PRIMARY KEY (column1)
);

DELETE FROM table fails in QtSql but not in sqlite3 cli

The following fails:

QSqlQuery query;
if (query.exec("DELETE FROM files")){
    qCritical() << query.lastError().driverText();
    qCritical() << query.lastError().databaseText();
    qCritical() << query.lastError().isValid();
    qCritical() << query.lastError().nativeErrorCode();
    qCritical() << query.lastError().text();
    qCritical() << query.lastError().type();
    qFatal(SQLERR);
}

Outputs

Critical: ""  --  [void Files::FileIndex::reset()]
Critical: ""  --  [void Files::FileIndex::reset()]
Critical: false  --  [void Files::FileIndex::reset()]
Critical: ""  --  [void Files::FileIndex::reset()]
Critical: " "  --  [void Files::FileIndex::reset()]
Critical: 0  --  [void Files::FileIndex::reset()]

Funny because 0 is QSqlError::NoError 0 No error occurred. The database exists the error message is empty... any help. In the cli this works well.

Sqlite using Python3 error?

I am trying to insert some data into a sqlite database by following : -

param = '("593863695396044801","Ivan F. Siahaan","307783731","None","65","83","Thu Apr 30 19:45:13 +0000 2015","xyz")'

>>> conn.execute("INSERT INTO data "+param) Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: near ")": syntax error

I initialized connection to database as follows :-

from sqlite3 import dbapi2 as sqlite

conn = sqlite.connect('dataStore')

What wrong I am doing here ?

How to check/limit a sqlite database size efficiently?

I would like to check the size of a SQLite database and if it is too big, it will notify the user to stop insert data into it.

But how could I check the total number of rows, or the file size of the database efficiently? It is good to have some O(1) method so that I could check for every insertion the user submitted.

I am working on Qt mainly for Windows.

Can not use sqlite (swift) commands outside viewDidLoad()

Im using the framework SQLite from stephencelis

I have the problem that the commands can be only used within viewDidLoad(). But i have to use the defined variables (e.g. var carlist) in functions outside viewDidLoad.

Where is my logical problem?

My code snipped:

    override func viewDidLoad() {
     super.viewDidLoad()
     let db = Database("/Users/**/Desktop/NEW/car/car.sqlite")      
     let cars = db["cars"]
     let car_id = Expression<Int64>("car_id")
     let car_model = Expression<String?>("car_model")

     for car_list in cars {
        println("car_model: \(car_list[car_model])")
        var carlist[car_list[car_model]] // array
     }
    }

The error, if i move the lines after "let db" to outside: 'ViewController.Type' does not have a member named "db"

SQLite (select): i don't get it

i don't get the solution for my needs:

my code:

select vocbox._id, vocbox.name, vocbox.native, vocbox.foreign, count(_id_vocbox) as count
from vocbox
left join vocable on vocable._id_vocbox=vocbox._id
group by vocbox._id;

...only delivers this: enter image description here

but, what i want is this: enter image description here

and this are my tables:

enter image description here

i hope somebody knows the right code.. :)

SQLite adding a numerical var to existing value in js

I have the following table

buildingcode buildingreceiptno buildingaddress buildingpay
1012         2                 address 1       0
1001         3                 address 2       0
1003         0                 address 3       0

I want to update buildingreceipt of a certain buildingcode by a numerical var

I use the following code but it does not work

t.executeSql('UPDATE buildings SET buildingpay = ?, buildingreceiptno = buildingreceiptno + numericalValue WHERE buildingaddress = ?', 
     [myrow.payamount.toFixed(2), myrow.buildingaddress]);

myrow.payamount.toFixed(2), myrow.buildingaddress are correct vars

When trying to migrate I get the error bellow: 'PDOException'

When trying to migrate I get the error bellow: 'PDOException' with message could not find driver I'm on an Ubuntu Linux environment. I've configured "sqlite" and if I run sqlite3 from the command line I get to the sqlite shell, so it is correctly installed, but Laravel does not connect to the database!

enter image description here

Upgrade SQLite Database with Transactions

I have a website solution that uses on SQLite database for each tenant account. Without going into much depth about why we chose this solution, we chose it due to SQLite support on distributed/offline systems.

All databases are manipulated using the same PHP file structure. I wish to update the database version iteratively for all accounts so that they are all at the same version number.

I have a script that loops over each, and can use either PHP(Yii) or the shell to execute queries.

I would like to wrap the manipulation to each database in a transaction. It appears as though DDL commands may already be auto-commit in nature.

Question: How to accomplish a SQLite DB upgrade which, if it fails, will report a failure? Using that report, I could prompt the system to re-attempt or report an error to an admin. Ideally, I would like to wrap the whole upgrade in a transaction to prevent inconsistencies, but I'm fairly certain that this is not possible.

One though I had was to backup the database temporarily during upgrade, and delete it on success.

next() in QSqlQuery returns the last record in the table

When search button is clicked, it shows the first record in the table like this;

void EditEntry::on_search_button_clicked() {
    searchText = search->text();
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("database.db");
    bool ok = db.open();
    if (ok) {

        QSqlQuery query1("SELECT * FROM table_name WHERE last_name LIKE '%Nana%'");
        if (query1.first()) {
            //show first record in the table where last name like Nana
        }
}

I am trying to make the query move to the next record in the table by the click of a button like this;

void EditEntry::on_next_button_clicked() {
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("database.db");
    bool ok = db.open();
    if (ok) {
        QSqlQuery query1("SELECT * FROM table_name WHERE last_name LIKE '%Nana%'");
        if (query1.isActive()) {
            while (query1.next()) {
                //show the next record in the table with last name like Nana
            }
} 

And showing the previous record with the name like 'Nana' like this;

void EditEntry::on_previous_button_clicked() {
    searchText = search->text();
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("database.db");
    bool ok = db.open();
    if (ok) {
        QSqlQuery query1("SELECT * FROM table_name WHERE last_name LIKE '%Nana%'");
        if (query1.isActive()) {
            while (query1.previous()) {
                //show previous record in the table where last name like Nana
            }
}

The on_search_button_clicked() works well by showing the 1st record with last name like 'Nana'. The on_next_button_clicked() shows the last record in the table with name like 'Nana' instead of the next record after the 1st (the table has 3 records with last name like 'Nana'). The on_previous_button_clicked() button doesn't work at all, it shows nothing even though i expect it to show the previous record with last name like 'Nana'. How do i get these buttons to work as i expect?

ON DELETE SET NULL For Existing SQLite Table

From what I've read in the docs, SQLite does not have an alter table option?

Do you know how I can add an ON DELETE SET NULL condition to a foreign key column for a table already created?

SQL - select the data between two dates?

I want to select my data by date - from a date until another date, so I have this query,

SELECT * FROM mytalbe WHERE date BETWEEN '2014-10-09' AND '2014-10-10'

But this query only return the data in '2014-10-09', excluding the data in '2014-10-10', unless I change the query to this below,

SELECT * FROM mytalbe WHERE date BETWEEN '2014-10-09' AND '2014-10-11'

This is not an ideal solution. How can I select the data including the data in '2014-10-10'?

NOTE:

I think my problem is different from other duplicate questions becos,

  1. my date type is TEXT
  2. I need to select the date's data without its time.

My data sample...

    sid     nid timestamp   date    
1   20748   5   1412881193  2014-10-09 14:59:53 
2   20749   5   1412881300  2014-10-09 15:01:40 
3   20750   5   1412881360  2014-10-09 15:02:40

How to use older sqlite version in iOS app

I want to use a previous version of sqlite in my iOS app as I have a collation in my database that is deprecated with the new version of sqlite that iOS comes with.

I haven't seen anywhere this is asked or a solution talked about and i'm not sure where to start other then I think I need to preload the sqlite version with the app upon download but as to tell the app to not use the main iOS sqlite and use the preloaded one, I really have no idea.

Any push in the right direction would be much appreciated!

Ensuring safe SQLite database manipulation (not referring to thread safe)

I have an app that inserts and or updates multiple (100's) of rows into a table. There's only one thread running at a time in my app. Each row is being calculated and checked before each insert/update in several functions.

I'm concerned that if the app gets interrupted between the start of inserting the rows and the end my data will be corrupted/incomplete. Is this possible to happen?

I tried several times to interrupt the process with the home key, it didn't but this is not exhaustive approach.

how can i upload image from phone memory to android sqlite database

I am trying to develop an android app in which there is an option to upload image from phone memory and then it will display in a list View with some heading given by user.
I have created layout of it but I don't know how to implement this concept.

Simulate ON DUPLICATE KEY UPDATE in SQLITE Android

I am trying to find a way to get INSERT...ON DUPLICATE KEY UPDATE from MySQL working in SQLite. The problem is that my current attempts are always failing due to the fact, that SQLite always deletes the row first and inserts it with the new values and the same primary key. When I now have a foreign key constraint ON DELETE CASCADE on this primary key in another table, the entries in the other tables always get deleted.

What I've tried:

db.insertWithOnConflict(tableName, null, values, SQLiteDatabase.CONFLICT_REPLACE);
db.replace(tableName, null, values);

Both methods first remove the entry and re-insert it.

Is there any method to prevent this kind of behaviour and just update the entry's values, except for the primary?

How to delete a record from relational table using coredata?

I am new to coredata database. I have created two tables 1. UserTable 2. ActivityTable

I have created a relation ship between two tables UserTable have a relationship with ActivityTable and the inverse of UserTable ActivityTable have a relationship with UserTable and the inverse of ActivityTable I have implemented like this

    NSManagedObjectContext *context = [appDelegate managedObjectContext];
    // Test listing all users from the store
    NSFetchRequest *fetchRequest = [[NSFetchRequest alloc] init];
    NSEntityDescription *entity = [NSEntityDescription entityForName:@"UserTable" inManagedObjectContext:context];
    [fetchRequest setEntity:entity];
    NSError *error;
    NSArray *fetchedObjects_Login = [context executeFetchRequest:fetchRequest error:&error];


    if ( fetchedObjects_Login != nil &&  [fetchedObjects_Login count] > 0){
        for(NSManagedObject *managedObject in fetchedObjects_Login){
            [context deleteObject:managedObject];
        }

        if (![context save:&error]) {
            NSLog(@"Error deleting %@ - error:",[error localizedDescription]);
        }
    }

Note: I am unable to delete the record from database. Please let us know how to resolve this. Here, I got the error. I Want's to delete the record from userTable.

C# Android How to check if Database exists or not

I ve made a registration page for my app, it creates a database when submitted. The registration page should only open once, if it is filled then it should move on to the main activity. Here I have used File.Exists command to check if the database file exists or not.

namespace Mind
{
 using System.Threading;

using Android.App;
using Android.OS;

[Activity(Theme = "@style/Theme.Splash", Icon = "@drawable/icon", MainLauncher = true, NoHistory = true)]
public class SplashActivity : Activity
{

    protected override void OnCreate(Bundle bundle)
    {
        base.OnCreate(bundle);
        Thread.Sleep(500); // Simulate a long loading process on app startup.

        const string fileName = "@/data/data/http://ift.tt/1bhnQ3C";

            if (File.Exists (fileName)) {
            StartActivity (typeof(MainActivity));
        } else {
            StartActivity (typeof(Registration));
        } 


    }
  }
}

but no success, there are no errors but it always shows the Registration page after the splash screen

Which solution is better in my Almquist Shell script (under busybox) : Pure SQLite or (sort + diff + SQLite)?

I need some advices regarding the tools that I should use in my SH scrip. The purpose of this script is to display the new/deleted/edited/renamed files of a directory between to executions.

My approach is to store a sorted "file + attributes" list and compare it with the previous execution. My implementation uses a "find", a "sort" and a "diff" commands, and on top of that I use SQLite for storing the differences and do SQL requests for displaying what I want.

There's an other implementation that would be to store the files + attributes directly in a SQLite database and make SQL requests for getting what I need.

My question is : Which solution is better for supporting a loooot of files ? the slow sort command with the fast diff command ? Or letting SQLite doing all that for me ?

NB: I discard "inotify" because it is not available by default

Thanks for your advices

Database locked issue while Inserting in same table the Array of more than 1000 records by multiple client

I am facing the big issue. I have created the service stack web services in C# DotNet and Database is SQLite (v4.0.30319). My System hang / Database locked issue, some time SQlite database file also corrupted while inserting/ updating the database table by number of clients (more than one connection). All are update the same table same time. Whle One is updating other try to connect than the system hang. I have tried all the ways but not able to resolve this issue.

How can I force that One One client can update the table at one time other clients can readonly mode.

My code as below

public IDbConnection Db { get; private set; }

OrmLiteConnectionFactory factory = new OrmLiteConnectionFactory("Data Source= C:/Database/db.sqlite;Integrated Security=True;User Instance=True", ServiceStack.OrmLite.Sqlite.SqliteOrmLiteDialectProvider.Instance);

    public bool CreateAnother2(LTHR record)
    {
        try
        {
            using (Db = factory.OpenDbConnection())
            {
                using (var trans = Db.BeginTransaction()  
                {                         
                        // If no error insert data
                        var id = (int)Db.Insert(record, selectIdentity: true);
                        trans.Commit();
                        Db.Close();  
                        return true;                       
                }
            }
        }
        catch (Exception ex)
        {
            if (Db.State == ConnectionState.Open)
            {
                Db.Close();  
            } 
        }
        finally
        {
            if (Db.State == ConnectionState.Open)
            {
                Db.Close();  
            }
        }
    }

Upload and Download files from OneDrive using C#

Upload and Download files from OneDrive.Can you please send me sample code.

SQLite Db Connection Error from Visual Studio

I am using SQLite Database for my application. The database has been encrypted with "certain" password. When I am going to create a new connection in Visual Studio to add this database, its throwing the error...

Following steps are ensured by me...

a) Open "Server Explorer" & Right-Click on "Data Connections".

b) "Add Connection" window will appear, browse the database file & enter the password.

c) Click on "Data Source" and select the option "SQLite Database File"

After this, when I click on "Test Connection", I get this error...

================================

Microsoft Visual Studio

================================

File opened is not a database file

file is encrypted or not a database.

================================

Please suggest if I am missing something.

Thanks.

select distinct sum of item that can exists in several tables

Let's say that i have 3 tables: Articles1,Articles2,Articles3.

It's possible that same articlegroup exists in two of theese tables. I only want to sum amount by each articlegroup existing in Articles1 and does not exists in the other tables.

Tables:

Articles1

| Id    |    ArticleName    |    Amount |
-----------------------------------------
  '1'         'Apple'              '2'
  '2'         'Orange'             '2'
  '3'         'Banana'             '3'

Articles2

| Id    |    ArticleName    |    Amount |
-----------------------------------------
  '1'         'Apple'              '2'
  '2'         'Orange'             '2'

Articles3

| Id    |    ArticleName    |    Amount |
-----------------------------------------
  '1'         'Apple'              '2'
  '2'         'Orange'             '2'

My code:

SELECT SUM(a1.Amount) 
FROM Articles1 a1
LEFT OUTER JOIN Articles2 a2
   ON a1.Id = a2.Id
LEFT OUTER JOIN Articles3 a3
   ON a1.Id = a3.Id
WHERE a1.Id <> a2.Id OR a1.Id <> a3.Id
GROUP BY a1.ArticleName

Fiddle

Android SQLite data is not being added

I am using the following code to add JSON Arrays to SQLite database :

QuestionORM Class :

public static void insertQuestion(Context c,JSONArray jarr,String search) throws JSONException {
    DatabaseWrapper databaseWrapper = new DatabaseWrapper(c);
    if(isDatabaseOpened())
    {
        myDataBase = databaseWrapper.getWritableDatabase();
        ContentValues values = postToContentValues2(jarr);
        values.put(QuestionORM.COLUMN_SEARCH,search);
        long questionId = myDataBase.insert(QuestionORM.TABLE_NAME, "null", values);
        Log.e(TAG, "Inserted new Question with ID: " + questionId);
        myDataBase.close();
    }
}

private static ContentValues postToContentValues2(JSONArray jsonArray) throws JSONException {
    ContentValues values = new ContentValues();
    for(int i=0;i<jsonArray.length();i++)
    {
        JSONObject job1 = jsonArray.getJSONObject(i);
        if(job1!=null)
        {
            JSONObject job2 = job1.getJSONObject("owner");
            values.put(QuestionORM.COLUMN_ID, job1.getString("question_id"));
            values.put(QuestionORM.COLUMN_TITLE,job1.getString("title"));
            values.put(QuestionORM.COLUMN_AUTHOR,job2.getString("display_name"));
            values.put(QuestionORM.COLUMN_VOTES,job1.getString("score"));
        }
    }
    return values;
}

public static boolean isDatabaseOpened() {
    if (myDataBase == null) {
        return false;
    }
    return myDataBase.isOpen();
}

This is used like this in another activity :

        QuestionORM.insertQuestion(MainActivity.this,mJSONArr,url);

However, the log is never displayed, and the database is empty. I don't get any errors in my logcat either.

What is wrong ? How do I fix this ?

Thanks !

Android SQLITE Illegal State Exception [duplicate]

This question already has an answer here:

I am using the following code to add items to the Database :

public static void insertQuestion(Context c,JSONArray jarr,String search) throws JSONException {
        DatabaseWrapper databaseWrapper = new DatabaseWrapper(c);
        SQLiteDatabase database = databaseWrapper.getWritableDatabase();
        ContentValues values = postToContentValues2(jarr);
        values.put(QuestionORM.COLUMN_SEARCH,search);
        long questionId = database.insert(QuestionORM.TABLE_NAME, "null", values);
        Log.e(TAG, "Inserted new Question with ID: " + questionId);
        database.close();
    }

But I get an error saying

"attempt to re-open an already-closed object"

on this line :

SQLiteDatabase database = databaseWrapper.getWritableDatabase();

How do I resolve this ?

Thanks !

mercredi 29 avril 2015

Display all data from sqlite tables in Console C# VisualStudio2012

So I just completed a project that collects data from the web and writes it to a sqlite database. I want to be able to print the tables that were just added to the sqlite database in the console once the program is done so the data is easy to see, but haven't found a way to do this online. Any help would be appreciated!

In Android which line of code throws SQLiteDatabaseLockedException exception

I want to know which class and which method in Android SDK throws this exception "SQLiteDatabaseLockedException" with message "database is locked".

Recently I had encountered SQLiteDatabaseLockedException while accessing the SQLite database from multiple threads.

After searching on the internet and reading about SQLite Locking and multithreading support, I solve it by making a singleton SQLiteOpenHelper instance.

But I want to know how the things are working in background. I had gone through the source code of classes SQLiteOpenHelper and SQLiteDatabase. The SQLiteOpenHelper has an instance of SQLiteDatabase, which iteself maintains a lock by using an Object "mLock". Both these classes are Thread-safe as the methods are synchronized (or using synchronized block).

I did not find any line which says "throw new SQLiteDatabaseLockedException("database is locked). I also read the classes which are refered in SQLiteDatabase like SQLiteConnection, SQLiteConnectionPool, but did'nt find anything useful. I read somewhere on the internet that SQLite maintains its internal locking. Is it means the native libraries of SQLite?

Please someone explains in depth what is happening behind the secenes. Thanks.

Unable to open asset URL: file:///android_asset/www/submit?UserName=Admin&Password=super%401234 in Phonegap Android?

I am completely new to Phonegap and Javascript. I am trying to save the username value and password value from the login form to sqlite database.

This is my login.html file:-

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://ift.tt/kkyg93">
<html xmlns="http://ift.tt/lH0Osb">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title>Login</title>
<link href="css/style.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="js/jquery-2.1.3.min.js"></script>
<script type="text/javascript" src="js/iscroll.js"></script>
<script type="text/javascript" src="js/login.js"></script>

</head>

<body>
<div class="wrapper">
    <form action="submit" id="login" name="login_form">
        <input type="text" id="uName" name = "UserName" placeholder="Username" value="Admin"/>
        <br/>
        <input type="password" id="password" name = "Password" placeholder="Password" value="super@1234"/>
        <br/>
        <button type="submit" id="submit" value="Submit">Login</button>
    </form>
<script type="text/javascript">
    $(document).ready(function() {
        $("#submit").click(function() {
            if($("#uName").val()==""){
                alert("Please fill username field.");
                //$("#uName").focus();
            }
            else if($("#password").val()==""){
                alert("Please fill password field.");
                //$("#password").focus();
            }
            else {
                onDeviceReady();
            }
        });
    });
</script>

</div>
</body>
</html>

and this is my login.js file:-

var db;

document.addEventListener("deviceready", onDeviceReady, false);

function onDeviceReady() {
db = window.openDatabase("UserDB", "1.0", "Login", 10000);
db.transaction(populateDB, transaction_err, populateDB_success);
}

function populateDB(tx) {

var userName = document.getElementById("uName").value;
var password = document.getElementById("password").value;

tx.executeSql('CREATE TABLE IF NOT EXISTS users (ID INT, USERNAME VARCHAR UNIQUE, PASSWORD VARCHAR)');
tx.executeSql('INSERT INTO users (ID,USERNAME,PASSWORD) VALUES ("'+1+'","'+userName+'","'+password+'")');
alert("" + userName + " " + password);
}

function transaction_err(tx, error) {
alert("Database Error: " + error);
}

function populateDB_success(tx) {
alert("Data successfully entered.");
window.open("file:///assets/www/view_login.html");
}

My problem is when I am running this code in my android device it gives me application error : There is a network error and error log is:-

04-30 10:16:35.080  31868-31868/com.itpp.trt D/CordovaWebViewImpl onPageDidNavigate(file:///android_asset/www/submit?UserName=Admin&Password=super%401234)
04-30 10:16:35.080  31868-31934/com.itpp.trt E/AndroidProtocolHandler﹕ Unable to open asset URL: file:///android_asset/www/submit?UserName=Admin&Password=super%401234
04-30 10:16:35.090  31868-31868/com.itpp.trt D/SystemWebViewClient﹕ CordovaWebViewClient.onReceivedError: Error code=-1 Description=There was a network error. URL=file:///android_asset/www/submit?UserName=Admin&Password=super%401234

I cant find where is the problem please help me. Thanks and sorry for the long question. :)

Loading in a new database, error: sqlite3 Cannot operate on a close Database

So I added an option to my GUI(Tkinter) that allows you to load in a new database (via filedialog), but it gives me this:

sqlite3.ProgrammingError: Cannot operate on a closed database.

Here is my code:

def load_database(self):
    file_path = filedialog.askopenfilename( filetypes = ( ("Database Files", "*.dB"), ("All files", "*.*") ) )
    print (file_path)
    if (file_path == "" or file_path == ()):
        return
    else:
        conn.close()
        self.reconnect_to_database(file_path)

def reconnect_to_database(self, file_path):
    conn = sqlite3.connect(file_path)  
    cursor = conn.cursor()
    cursor.execute("""CREATE TABLE if not exists Cards
              (trackOne text, trackTwo text, trackThree text) 
           """)
    conn.commit()

conn = sqlite3.connect("database.db")  
cursor = conn.cursor()

Just wondering how to close the sqlite3 connection and load + opening a new database.

thanks

How can I sort ensuing columns according to value in first column? (sqlite)

Let's call the first column below the guide key, which is 1 in all of these rows.

1|5|bagels|1|coffee|1|eggs|5|espresso|1|muffins 1|5|bagels|1|coffee|1|eggs|5|espresso|2|muffins 1|5|bagels|1|coffee|1|eggs|5|espresso|3|muffins 1|5|bagels|1|coffee|1|eggs|6|espresso|1|muffins 1|5|bagels|1|coffee|1|eggs|6|espresso|2|muffins 1|5|bagels|1|coffee|1|eggs|6|espresso|3|muffins 1|5|bagels|1|coffee|1|eggs|7|espresso|1|muffins 1|5|bagels|1|coffee|1|eggs|7|espresso|2|muffins 1|5|bagels|1|coffee|1|eggs|7|espresso|3|muffins 1|5|bagels|1|coffee|1|eggs|8|espresso|1|muffins

The ensuing rows have the format: key|value. For example, 5 is a key, bagels is a value, etc. I want to keep only the values of the row with the most keys matching the guide key. So the output for this data set should look like: 1|coffee|eggs|muffins

How can I do this in sqlite3?

Populating iOS UITableView from SQLite (Swift)

I am a new developer and I am trying to figure out how to populate a UITableView with data from a row of my SQLite database. I found an example in Objective-C but I was wondering if anyone had any access to a good tutorial.

Set Foreign Key Column In Child Table To Null If Parent Foreign Key Deleted

I have an SQLite database.

I have learned how to insert a foreign key and now I would like to do this: 1. Delete the row which contains the foreign key in the parent table 2. Have any other table which references that foreign key set to null.

I have read about cascading deletes but that seems to delete any row which had that foreign key. Instead, I want to just null the value in any table that has a column value referencing the foreign key.

Can you advise what I can do to do this and perhaps what the terminology is?

If/then statement and sql count rows

I'm attempting to perform an if then statement based on the results of a sql count rows query. The query is returning the proper values however the if/then statement seems to be ignoring the returned value of the sql statement.

Here is my code.

import sqlite3

# Define SQL statement to select all Data from Column Name
sql = "SELECT Count(*) FROM arbtable WHERE Name = ?"

book_name = 'Winged Coat'

class PriceCheck(object):
    def __init__(self, db):
        self.conn = sqlite3.connect(db)
        self.c = self.conn.cursor()


    def query(self, arg, cardname):
        self.c.execute(arg, cardname)
        return self.c

    def __del__(self):
        self.conn.close()

def display():
#Connect To DB and Get Price of Matched book.
    getbookprice = PriceCheck("arbbase.sqlite")
    for row in getbookprice.query(sql, ([book_name])):
        if row == 0:
            print 'no row was found'
        else: 
           print 'Yep its there!'
            print row



display()

The program here is that my result, as evidenced by "print row" at the bottom of the code is 0. Since it is zero according to my if statement it should print 'no row was found', but instead it prints the else statement value of 'yes its there!'

I've researched extensively and think that possibly the fetchone statement is what I need, but I can't seem to figure out how to properly apply this to my code to test it.

Any help would be highly appreciated.

Sour Jack

C# program throwing exception when adding data to sqlite database

So I have this program that I am working on for my university. The program takes data from a webpage (in this case Reddit), and adds it into a SQlite server. Everything seems to be running smoothly except the part where it inserts the data into a table. Here is the code:

    class Program
{

    static string connString = @"Data Source=C:\SQLite\mydatabase;Version=3;";
    static SQLiteConnection conn = new SQLiteConnection(connString);
    public SQLiteDataAdapter da = new SQLiteDataAdapter();
    DataTable dt = new DataTable();



    static void Main(string[] args)
    {
        FirefoxDriver driver = new FirefoxDriver();
        driver.Navigate().GoToUrl("http://www.reddit.com/");;
        Console.WriteLine("collecting data");
        String date = DateTime.Now.ToString("M/d/yyyy");
        String title1 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[1]/div[2]/p[1]/a").Text;
        String title2 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[3]/div[2]/p[1]/a").Text;
        String title3 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[5]/div[2]/p[1]/a").Text;
        String title4 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[7]/div[2]/p[1]/a").Text;
        String title5 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[9]/div[2]/p[1]/a").Text;
        String title6 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[11]/div[2]/p[1]/a").Text;
        String title7 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[13]/div[2]/p[1]/a").Text;
        String title8 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[15]/div[2]/p[1]/a").Text;
        String title9 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[17]/div[2]/p[1]/a").Text;
        String title10 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[19]/div[2]/p[1]/a").Text;

        String user1 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[1]/div[2]/p[2]/a[1]").Text;
        String user2 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[3]/div[2]/p[2]/a[1]").Text;
        String user3 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[5]/div[2]/p[2]/a[1]").Text;
        String user4 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[7]/div[2]/p[2]/a[1]").Text;
        String user5 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[9]/div[2]/p[2]/a[1]").Text;
        String user6 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[11]/div[2]/p[2]/a[1]").Text;
        String user7 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[13]/div[2]/p[2]/a[1]").Text;
        String user8 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[15]/div[2]/p[2]/a[1]").Text;
        String user9 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[17]/div[2]/p[2]/a[1]").Text;
        String user10 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[19]/div[2]/p[2]/a[1]").Text;

        String subreddit1 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[1]/div[2]/p[2]/a[2]").Text;
        String subreddit2 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[3]/div[2]/p[2]/a[2]").Text;
        String subreddit3 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[5]/div[2]/p[2]/a[2]").Text;
        String subreddit4 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[7]/div[2]/p[2]/a[2]").Text;
        String subreddit5 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[9]/div[2]/p[2]/a[2]").Text;
        String subreddit6 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[11]/div[2]/p[2]/a[2]").Text;
        String subreddit7 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[13]/div[2]/p[2]/a[2]").Text;
        String subreddit8 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[15]/div[2]/p[2]/a[2]").Text;
        String subreddit9 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[17]/div[2]/p[2]/a[2]").Text;
        String subreddit10 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[19]/div[2]/p[2]/a[2]").Text;

        String comments1 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[1]/div[2]/ul/li[1]/a").Text;
        String comments2 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[3]/div[2]/ul/li[1]/a").Text;
        String comments3 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[5]/div[2]/ul/li[1]/a").Text;
        String comments4 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[7]/div[2]/ul/li[1]/a").Text;
        String comments5 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[9]/div[2]/ul/li[1]/a").Text;
        String comments6 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[11]/div[2]/ul/li[1]/a").Text;
        String comments7 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[13]/div[2]/ul/li[1]/a").Text;
        String comments8 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[15]/div[2]/ul/li[1]/a").Text;
        String comments9 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[17]/div[2]/ul/li[1]/a").Text;
        String comments10 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[19]/div[2]/ul/li[1]/a").Text;


        Console.WriteLine("Data Collected. Writing to flat file");
        //driver.Close();

        //SQLiteCommand RedditPageTable = new SQLiteCommand("create table RedditPageData(ID INT, Date varchar(25), title varchar(300), user varchar(50), subreddit varchar(50), comments varchar(50));", conn);
        //conn.Open();
        //RedditPageTable.ExecuteNonQuery();
        //conn.Close();


        using (StreamWriter writer = new StreamWriter("inputstream1.txt", true) ){
            writer.WriteLine(title1 + ";" + user1 + ";" + subreddit1 + ";" + comments1 + ";" + title2 + ";" + user2 + ";" + subreddit2 + ";" + comments2 + ";" + 
                title3 + ";" + user3 + ";" + subreddit3 + ";" + comments3 + ";" + title4 + ";" + user4 + ";" + subreddit4 + ";" + comments4 + ";" + title5 + ";" + 
                user5 + ";" + subreddit5 + ";" + comments5 + ";" + title6 + ";" + user6 + ";" + subreddit6 + ";" + comments6 + ";" + user7 + ";" + title7 + ";" + 
                subreddit7 + ";" + comments7 + ";" + title8 + ";" + user8 + ";" + subreddit8 + ";" + comments8 + ";" + title9 + ";" + user9 + ";" + subreddit9 + ";" + 
                comments9 + ";" + title10 + ";" + user10 + ";" + subreddit10 + ";" + comments10);               
        }

        StringBuilder sb1 = new StringBuilder();
        using (StreamReader sr1 = new StreamReader("inputstream.txt"))
        {
            String line;
            // Read and display lines from the file until the end of 
            // the file is reached.
            while ((line = sr1.ReadLine()) != null)
            {
                sb1.AppendLine(line);
            }
        }
        string allines1 = sb1.ToString();
        int counter1 = 0;
        int ID = 0;
        string[] dataSplit1 = allines1.Split(';');
        while (true)
        {                              
            counter1 += 1;
            String titleEnter = dataSplit1[counter1];
            counter1 += 1;
            String userEnter = dataSplit1[counter1];
            counter1 += 1;
            String subredditEnter = dataSplit1[counter1];
            counter1 += 1;
            String commentsEnter = dataSplit1[counter1];
            ID++;
            Console.WriteLine(counter1);

            SQLiteCommand InsertRedditInfo = new SQLiteCommand("INSERT INTO RedditPageData(ID, Date, title, user, subreddit, comments) VALUES('"+ ID + "','" + date + "','" + titleEnter + "','" + userEnter + "','" + subredditEnter + "','" + commentsEnter + "')", conn);
            conn.Open();
            InsertRedditInfo.ExecuteNonQuery();
            conn.Close();    

            if (counter1 == 39)
            {
                break;
            }
        }



        Console.WriteLine("Data written successfully");
    }


}

And here are the details of the exception:

System.Data.SQLite.SQLiteException was unhandled
  HResult=-2147467259
  Message=SQL logic error or missing database
near "s": syntax error
  Source=System.Data.SQLite
  ErrorCode=1
  StackTrace:
       at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
       at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
       at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
       at System.Data.SQLite.SQLiteDataReader.NextResult()
       at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
       at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
       at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
       at ConsoleApplication5.Program.Main(String[] args) in c:\Users\Chambers\Documents\Visual Studio 2012\Projects\ConsoleApplication5\ConsoleApplication5\Program.cs:line 125
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:     

It says there could be a syntax error or a missing database, but I'm sure that the database is there, and I can't seem to find the syntax error. Any pair of fresh eyes would help!

Why am I getting an error executing this statement in DB Browser for Sqlite?

I am trying to execute this statement in DB Browser

UPDATE p SET SourceId = s.Id 
FROM Practice p INNER JOIN Source s ON  p.Source = s.Name

and it is refusing to execute complaining that there is a syntax error near "FROM"

What am I doing incorrectly here?

near synatx error (code 1) insert Android SQLite

I am creating a Contact Book app where users input name, email address and number. I want this data to be saved in a database, but I can't seem to get the insert method to work: The error I'm getting is:

android.database.sqlite.SQLiteException: near "Number": syntax error (code 1): , while compiling: INSERT INTO CONTACTSTABLE(Phone Number,Email Address,Name) VALUES (?,?,?)

Here is where I put my database:

public class DatabaseAdapter{

MySQLiteHelper dbhelper;

public DatabaseAdapter(Context context){
    dbhelper=new MySQLiteHelper(context);
}

public long insertData(String name, String phone, String email){
    SQLiteDatabase db=dbhelper.getWritableDatabase();
    ContentValues contentValues=new ContentValues();
    contentValues.put(dbhelper.NAME, name);
    contentValues.put(dbhelper.NUMBER, phone);
    contentValues.put(dbhelper.EMAIL, email);
    long id=db.insert(MySQLiteHelper.TABLE_NAME, null, contentValues);
    return id;
}
static class MySQLiteHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "contacts.db";
    private static final String TABLE_NAME = "CONTACTSTABLE";
    private static final String UID = "_id";
    private static final String NAME = "Name";
    private static final String EMAIL = "Email Address";
    private static final String NUMBER = "Phone Number";
    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_CREATE = "CREATE TABLE "
            + TABLE_NAME + " ( " + UID
            + " INTEGER PRIMARY KEY AUTOINCREMENT, " + NAME
            + " TEXT, " + NUMBER
            + " TEXT, " + EMAIL
            + " TEXT" + " ) ";

    public MySQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase database) {
        database.execSQL(DATABASE_CREATE);
    }

    //onupgrade calls database needs to be upgraded. use to drop tables, called when you update data version
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(MySQLiteHelper.class.getName(),
                "Upgrading database from version " + oldVersion + " to "
                        + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }}}

Here is my MainActivity where I call the "insertData" method (I took a bunch of other things out for simplicity):

public class MainActivity extends ActionBarActivity implements View.OnClickListener {
EditText name;
EditText number;
EditText email;
Button submit;
DatabaseAdapter dbHelper;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    name=(EditText) findViewById(R.id.name);
    number=(EditText) findViewById(R.id.number);
    email=(EditText) findViewById(R.id.email);
    submit=(Button) findViewById(R.id.button);


    dbHelper=new DatabaseAdapter(this);
    submit.setOnClickListener(this);
}


@Override
public void onClick(View v) {
    if (isEmpty(name) || isEmpty(number) || isEmpty(email))
        preview.setText("Please fill out all of the boxes before submitting");
    else {
        String n=name.getText().toString();
        String p=number.getText().toString();
        String e=email.getText().toString();

       Contact c = new Contact(n,p,e);
       ContactArray.contacts.add(c);
       dbHelper.insertData(n,p,e);
}}

connect sqlite to real server on the internet

hi i need to connect my java program with the internet sql server my last version was with the file sqlite which take the database information from sql file in the same program folder my last code was

public static Connection createConn() throws SQLException, ClassNotFoundException{
    Class.forName("org.sqlite.JDBC");
    Connection c = DriverManager.getConnection("jdbc:sqlite:mhs.sqlite");
    c.setAutoCommit(false);
    return c;

}

how can i put my server link Allowance to my file ?? i dont need to use sql file on project i need to connect to the internet and take the data from my sql server

I have a database that has a table with 4 columns : ID, Contact(name), Phone, Message.

ID is auto incremented. Contact name and number comes from an ArrayList> Message comes from an EditText.

I am have problems trying to create the part to bring in the ArrayList> to the Database.

I have the part for the message but later I will need to either put that message in every slot? Or maybe not save it in the database but somewhere else.

Also, should I keep the contact name? or just the number since the number is what will be used to send the SMS message?

Thanks in advance! Below is my code.

Contacts.java - gets the contacts from the contact list and saves them in an ArrayList>

import android.app.Activity;
import android.content.ContentResolver;
import android.content.Intent;
import android.database.Cursor;
import android.net.Uri;
import android.provider.ContactsContract;
import android.support.v7.app.ActionBarActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.TextView;
import android.widget.Toast;

import com.example.deptofpharmacology.test.R;

import java.util.ArrayList;
import java.util.HashMap;


public class Contacts extends ActionBarActivity {
private static final int PICK_CONTACT = 1;
private static final String TAG = "The Goods: ";
DatabHelper myDb;

private static ArrayList<HashMap<String, String>> getContacts = new ArrayList<HashMap<String, String>>();

private static ArrayList<HashMap<String, String>> data1 = new ArrayList<HashMap<String, String>>();



private static HashMap<String, String> contacts = new HashMap<String,String>();

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_contacts);
    myDb = new DatabHelper(this);
   // ArrayList<HashMap<String, String>> getD =  myDb.insertRecord();

}


/**
this is what's going to be used to transfer the Arraylist<HashMap> to the database
public void AddConPhon(){
    boolean isInserted = myDb.insertData(Contacts.this, );
}
**/
public void btnAddContacts_Click(View view) {
    Intent intent = new Intent(Intent.ACTION_PICK, ContactsContract.Contacts.CONTENT_URI);
    startActivityForResult(intent, PICK_CONTACT);
}

public void btnDone_Click(View view){
    Intent i = new Intent(Contacts.this, Message.class);
    startActivity(i);
}



@Override
public void onActivityResult(int reqCode, int resultCode, Intent data) {
    super.onActivityResult(reqCode, resultCode, data);

    switch (reqCode) {
        case (PICK_CONTACT):
            if (resultCode == Activity.RESULT_OK) {
                Uri contactData = data.getData();
                Cursor c = managedQuery(contactData, null, null, null, null);
                if (c.moveToFirst()) {
                    String id =
                            c.getString(c.getColumnIndexOrThrow(ContactsContract.Contacts._ID));

                    String hasPhone =
                            c.getString(c.getColumnIndex(ContactsContract.Contacts.HAS_PHONE_NUMBER));

                    if (hasPhone.equalsIgnoreCase("1")) {
                        Cursor phones = getContentResolver().query(
                                ContactsContract.CommonDataKinds.Phone.CONTENT_URI, null,
                                ContactsContract.CommonDataKinds.Phone.CONTACT_ID + " = " + id,
                                null, null);
                        phones.moveToFirst();
                        String phn_no = phones.getString(phones.getColumnIndex("data1"));
                        String name = c.getString(c.getColumnIndex(ContactsContract.CommonDataKinds.StructuredPostal.DISPLAY_NAME));

                            contacts.put(name, phn_no);

                            HashMap<String, String> h = new HashMap<String, String>();
                            h.put("name", name);
                            h.put("phone", phn_no);
                            data1.add(h);

                        Toast.makeText(this, "contact info : " + phn_no + "\n" + name, Toast.LENGTH_LONG).show();
                        Log.d(TAG," " + data1.size());
                        Log.d(TAG, data1.toString());
                    }
                }
            }
    }

}

Contacts XML

<RelativeLayout xmlns:android="http://ift.tt/nIICcg"
xmlns:tools="http://ift.tt/LrGmb4"         android:layout_width="match_parent"
android:layout_height="match_parent"     android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
android:paddingBottom="@dimen/activity_vertical_margin"
tools:context="com.example.deptofpharmacology.test.Contacts">


<Button
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="Contact"
    android:id="@+id/contact1"
    android:layout_alignParentTop="true"
    android:layout_alignParentLeft="true"
    android:layout_alignParentStart="true"
    android:layout_marginTop="82dp"
    android:clickable="true"
    android:onClick="btnAddContacts_Click" />


<Button
    style="?android:attr/buttonStyleSmall"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="Contact"
    android:id="@+id/contact2"
    android:layout_below="@+id/contact1"
    android:layout_alignParentLeft="true"
    android:layout_alignParentStart="true"
    android:layout_marginTop="40dp"
    android:clickable="true"
    android:onClick="btnAddContacts_Click" />

<Button
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="Contact"
    android:id="@+id/contact3"
    android:layout_below="@+id/contact2"
    android:layout_alignParentLeft="true"
    android:layout_alignParentStart="true"
    android:layout_marginTop="49dp"
    android:clickable="true"
    android:onClick="btnAddContacts_Click" />

<Button
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="Contact"
    android:id="@+id/contact4"
    android:layout_below="@+id/contact3"
    android:layout_alignParentLeft="true"
    android:layout_alignParentStart="true"
    android:layout_marginTop="52dp"
    android:clickable="true"
    android:onClick="btnAddContacts_Click" />

<Button
    style="?android:attr/buttonStyleSmall"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="Next"
    android:id="@+id/Next1"
    android:layout_alignParentBottom="true"
    android:layout_alignParentRight="true"
    android:layout_alignParentEnd="true"
    android:clickable="true"
    android:onClick="btnDone_Click" />


</RelativeLayout>

the Database

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.HashMap;

public class DatabHelper extends SQLiteOpenHelper {

public static final String DATABASE_NAME = "PostalE.db";
public static final String TABLE_NAME = "emergency_table";
public static final String COL_1= "ID";
public static final String COL_2 = "CONTACTS";
public static final String COL_3 = "NUMBERS";
public static final String COL_4 = "MESSAGE";

public DatabHelper(Context context) {
    super(context, DATABASE_NAME, null, 1);

}

@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table " + TABLE_NAME + "(ID INTEGER PRIMARY KEY AUTOINCREMENT, CONTACTS TEXT ,NUMBERS INTEGER, MESSAGE TEXT)");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
onCreate(db);
}

public boolean insertRecord(HashMap<String, String> queryValues) {
    SQLiteDatabase database = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put("name", queryValues.get("name"));
    values.put("phone", queryValues.get("phone"));
    database.insert(COL_2, null, values);
    database.insert(COL_3, null, values);
    long result = database.insert(TABLE_NAME,null,values);
    if(result == -1){
        return false;
    }else{
        return true;
    }
}

/**
public boolean insertData(String name, String phone){
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COL_2,name);
    contentValues.put(COL_3,phone);
    long result = db.insert(TABLE_NAME,null,contentValues);
    if(result == -1){
        return false;
    }else{
        return true;
    }
}

**/
public boolean insertData(String message){
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COL_4,message);
    long result = db.insert(TABLE_NAME,null,contentValues);
    if(result == -1){
        return false;
    }else{
        return true;
    }
}
}

How to find list of escape character for sqlite

I want list of escape character for sqlite in android to add backslash before those.

runtime error java.lang.ClassNotFoundException: scala.slick.driver.SQLiteDriver$

I am trying to use Scala to create and query an SQLite db. I found out that slick is a good library for this. My code is:

import scala.slick.driver.SQLiteDriver.simple._
import org.sqlite._

object driver {
  def main(args: Array[String]){
    val db2 : org.sqlite.JDBC = null
    val db = Database.forURL("jdbc:sqlite:~/my_db.sqlite", driver = "org.sqlite.JDBC")
  } 
}

~/my_db.sqlite does not exist. It is just an empty file.

I am compiling and running in the following way:

$ scalac -cp "libs/slick_2.11-2.1.0.jar:libs/sqlite-jdbc-3.7.2.jar"  driver.scala
$ scala driver
$ java.lang.ClassNotFoundException: scala.slick.driver.SQLiteDriver$
    at java.net.URLClassLoader$1.run(URLClassLoader.java:372)
    at java.net.URLClassLoader$1.run(URLClassLoader.java:361)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:360)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    at driver$.main(driver.scala:7)
    at driver.main(driver.scala)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at scala.reflect.internal.util.ScalaClassLoader$$anonfun$run$1.apply(ScalaClassLoader.scala:70)
    at scala.reflect.internal.util.ScalaClassLoader$class.asContext(ScalaClassLoader.scala:31)
    at scala.reflect.internal.util.ScalaClassLoader$URLClassLoader.asContext(ScalaClassLoader.scala:101)
    at scala.reflect.internal.util.ScalaClassLoader$class.run(ScalaClassLoader.scala:70)
    at scala.reflect.internal.util.ScalaClassLoader$URLClassLoader.run(ScalaClassLoader.scala:101)
    at scala.tools.nsc.CommonRunner$class.run(ObjectRunner.scala:22)
    at scala.tools.nsc.ObjectRunner$.run(ObjectRunner.scala:39)
    at scala.tools.nsc.CommonRunner$class.runAndCatch(ObjectRunner.scala:29)
    at scala.tools.nsc.ObjectRunner$.runAndCatch(ObjectRunner.scala:39)
    at scala.tools.nsc.MainGenericRunner.runTarget$1(MainGenericRunner.scala:65)
    at scala.tools.nsc.MainGenericRunner.run$1(MainGenericRunner.scala:87)
    at scala.tools.nsc.MainGenericRunner.process(MainGenericRunner.scala:98)
    at scala.tools.nsc.MainGenericRunner$.main(MainGenericRunner.scala:103)
    at scala.tools.nsc.MainGenericRunner.main(MainGenericRunner.scala)

Database file created in unity but not in android

I have used the following code to create a database

string conn= "URI=file:" + Application.persistentDataPath + "/MyDatabase.s3db";
IDbConnection dbconn;
dbconn = (IDbConnection) new SqliteConnection(conn);
dbconn.Open();

this code is creating a database named MyDatabase.s3db in

C:\Documents and Settings\admin\Local Settings\Application Data\myapp\myappname

But when I build the apk and run it in android phone no database file is created.

How to insert binary data (like images/documents) into Sqlite Database

I Want To Insert Word OR PDF Documents In Sqlite Database. Is There Any Query To do that or Any Way.Can Any One Help Me Plz ,Thanks.

Android Account Manager and SyncAdapter

I have a to-do list app just like anydo. The app has store the data in local SQLite DB and sync in back to the server pariodically through API I wrote.

The users has 2 options:

  • Login to the app and be able to share lists and backup data
  • Skip login and only use it locally

Note: Only one user can be logged in at a given time.

I checked a few options to sync the data periodically, and SyncAdapter seems like the best way till now.

After a lot of tutorials which I read, I still have a few unresolved questions:

  1. How can I handle the situation that the user decided to skip authentication? Should I create a fake account? Mark the state with SharedPreferences?
  2. How can I detect that the user removed the account, while the app is running?
  3. There is a way to notify the app (in case it's running) that a sync service is running and data were updated?
  4. Can I make sure only one account will be logged in at given time?

Need help structuring database for nested navigation

I want to create a nested navigation menu/navigation, via my database(SQLite), using PDO (in the end it should work as a dropdown menu). I've been googling around, and looked at different posts in here. But I still don't really know how to do it properly. I really need database structuring advice, and maybe an example. I know it might be a lot to ask, but I thought I'd try anyway.

Thank you in advance!

Use multiple Spinners to search through database and produce a list of result

I am new to android development and trying to build an application. What I plan to do is to use a few spinner as searching criteria to search through sqlite and show a list of names that fulfills the requirement in a new activity with a ListView. Anyone can enlighten me with this idea? Thanks!

NoMethodError in StaticPages#home when attempting to list database query

Ruby noob here, I created a search form and I am trying to query a db and display the results. I am getting NoMethodError in StaticPages#home along with.... /home/action/Projects/CodonCoderTest5/app/views/static_pages/home.html.erb where line #4 raised:

undefined method `each' for nil:NilClass

Where am I going wrong?

layouts/StaticPages/home

<h1>StaticPages#home</h1>
<% @data_bases.each do |list| %>
     <div class="list">
         <h1 class="list-mrnaCodon"><%= link_to list.mrnaCodon %></h1>
     </div>
<% end %>

controller

class DataBaseController < ApplicationController

  def new
  end

  def index
       if params[:search]
          @data_base = Match.search(params[:search]).order("created_at DESC")
      else
          @data_base = Match.order("created_at DESC")
      end
    end

  def index
       @data_bases = Match.all
  end
end

System.Data.Entity.Core.ProviderIncompatibleException - Sqlity trys to create a database

i tried to create a connection to my database. I set my app.config and the other stuff like the standard. I use the entity framework 6 and SQLite in Visual Studio 2013.

app.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://ift.tt/1eigFsq -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="SPContext" connectionString="Server=(localdb)\v11.0;Integrated Security=true;AttachDBFileName=|DataDirectory|SPDatabase.mdf" providerName="System.Data.SqlClient" />
  </connectionStrings>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
</configuration>

context class

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DBTest.Model
{
    public class SPContext : DbContext
    {
        //Declare DBSets for each table
        public DbSet<Student> Students { get; set; }

        public SPContext () : base("name=SPContext")
        {

        }   


    }
}

Error:

System.Data.DataException: An exception occurred while initializing the database. See the InnerException for details. ---> System.Data.Entity.Core.ProviderIncompatibleException: CreateDatabase is not supported by the provider..

My question: The Database SPDatabase.sqlite is in the project folder! How can i set, that the provider of sqlite dont try to create a new database?

Thank you.

SQLite query not work on Android rawQuery

I tried my SQLite query on DB Browser for SQLite and it's worked.

But on Android same query not worked.

 String[] a = new String[1];
 a[0] =   yazilanKelime + '%';
 Cursor friendCursor = db.rawQuery( "SELECT * FROM kelimeler WHERE kelime LIKE ? ORDER BY sayi DESC LIMIT 10", a);

If i remove "ORDER BY sayi DESC" part, it's work. Where am I doing wrong?

Android Studio: SQlite db ListView items disappear on startActivity

I am new to Android Studio and app creation. I followed Johhny Mansons Youtube quide for new apps, and extended the work greatly.

My problem is, logging out of the app removes items from the list view, upon a new login.

When I implemented login functionality, I changed LoginActivity to the main page, following a startActivity to reach MainActivity.java The app is fully functional once you log in. A can submit a picture and text, it is saved to SQLite and display in ListView tab.

However, whenever I log out, or tab back, and login again. The data is wiped. I believe it is because the startActivity executes MainActivity anew, which could be creating a new database and removing the old. But I am not quite sure of the cause and why it happens. Before implmeneting LoginActivity, the app would retain the data fine.

LoginActivity (Login page)

mLogin = (Button) findViewById(R.id.oButton);
    mLogin.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            User user = new User(dbHandlerUsers.getUserCount(), String.valueOf(mUsername.getText()), String.valueOf(mPassword.getText()), null);
            if (validUser(user)) {

                CurrentUser g = CurrentUser.getInstance();
                g.setCurrentUser(String.valueOf(mUsername.getText()));

                Intent intent = new Intent(LoginActivity.this, MainActivity.class);
                startActivity(intent);
                return;
            }
            Toast.makeText(getApplicationContext(), "Wrong username or password", Toast.LENGTH_SHORT).show();
        }
    });

MainActivity (Function handling questions)

It is the populateList() that should get myListView to display Questions. It might also be worth noting that I am using two databases, one for users and one for questions.

public class MainActivity extends ActionBarActivity {

private static final int EDIT = 0, DELETE = 1;

EditText questionTxt; //include picture too
ImageView questionImageImgView;
List<Question> Questions = new ArrayList<Question>();
List<Question> OtherQuestions = new ArrayList<Question>(); //Preparing for incoming data
ListView myListView;
ListView otherListView;
Uri imageUri = Uri.parse("android.http://ift.tt/1PXK2zr"+R.drawable.no_user_logo);
DatabaseHandler dbHandler;
double myLongitude = 0.0d, myLatitude = 0.0d; //Later remove 0.0d
double qLongitude = 0.0d, qLatitude = 0.0d;
int longClickedItemIndex;
ArrayAdapter<Question> questionAdapter;

private float currentValue;
private long lastUpdate;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    dbHandler = new DatabaseHandler(getApplicationContext());

    questionTxt = (EditText) findViewById(R.id.txtQuestion);
    myListView = (ListView) findViewById(R.id.listView);
    otherListView = (ListView) findViewById(R.id.listView2);
    questionImageImgView = (ImageView) findViewById(R.id.imgQuestion);

    //This make our items in listView clickable on an event.
    registerForContextMenu(myListView);
    myListView.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
        @Override
        public boolean onItemLongClick(AdapterView<?> parent, View view, int position, long id) {
            longClickedItemIndex = position; //Position tell where the item was clicked

            return false;
        }
    });

    TabHost tabHost = (TabHost) findViewById(R.id.tabHost);
    tabHost.setup();
    ...


    final Button addBtn = (Button) findViewById(R.id.btnAdd);
    addBtn.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            //Find the currently logged in user
            CurrentUser user = CurrentUser.getInstance();
            String currentUser=user.getCurrentUser();

            //Add question
            Question question = new Question(dbHandler.getQuestionsCount(), String.valueOf(questionTxt.getText()), qLongitude, qLatitude, imageUri, currentUser);
            dbHandler.createQuestion(question);
            Questions.add(question);
            questionAdapter.notifyDataSetChanged();
            Toast.makeText(getApplicationContext(), String.valueOf(questionTxt.getText()) + " has been added", Toast.LENGTH_SHORT).show();
            clearFields();
        }
    });

    questionTxt.addTextChangedListener(new TextWatcher() {
        @Override
        public void beforeTextChanged(CharSequence s, int start, int count, int after) {

        }

        @Override
        public void onTextChanged(CharSequence s, int start, int before, int count) {
            addBtn.setEnabled(String.valueOf(questionTxt.getText()).trim().length() > 0);
        }

        @Override
        public void afterTextChanged(Editable s) {

        }
    });




    //Populate list view with questions
    if (dbHandler.getQuestionsCount() !=0)  //If there are contacts
        Questions.addAll(dbHandler.getAllQuestions()); //Add content

    populateList();

}

...

    dHandler.deleteQuestion(Questions.get(longClickedItemIndex));
            Questions.remove(longClickedItemIndex);
            questionAdapter.notifyDataSetChanged();
            break;
    }

    return super.onContextItemSelected(item);
}

public void onActivityResult(int reqCode, int resCode, Intent data) {
    if (resCode == RESULT_OK) {
        if (reqCode == 1) {
            imageUri = data.getData();
            questionImageImgView.setImageURI(data.getData());

        }
    }
}

private void populateList() {
    questionAdapter = new QuestionListAdapter();
    myListView.setAdapter(questionAdapter);
    //Add method to search for other phone's adapter list
    otherListView.setAdapter(questionAdapter);
}

private class QuestionListAdapter extends ArrayAdapter<Question> { //THIS CREATES A GROUP OF QUESTION, DISTANCE AND IMAGE - AS ONE OBJECT
    public QuestionListAdapter() {
        super (MainActivity.this, R.layout.listview_item, Questions);
    }
    @Override
    public View getView(int position, View view, ViewGroup parent) {
        if (view == null)
            view = getLayoutInflater().inflate(R.layout.listview_item, parent, false);

        Question currentQuestion = Questions.get(position);

        TextView question = (TextView) view.findViewById(R.id.qQuestion);
        question.setText(currentQuestion.getQuestion());
        TextView username = (TextView) view.findViewById(R.id.qLongitude);
        username.setText(currentQuestion.getUser());
        //TextView longitude = (TextView) view.findViewById(R.id.qLongitude);
        //longitude.setText(currentQuestion.getUser());
        TextView latitude = (TextView) view.findViewById(R.id.qLatitude);
        latitude.setText(Double.toString(currentQuestion.getLatitude()) + " meters");
        ImageView  questionImage = (ImageView) view.findViewById(R.id.qImageView);
        questionImage.setImageURI(currentQuestion.getImageURI());

        //TextView distance = (TextView) view.findViewById(R.id.qDistance);
        //distance.setText(Double.toString(currentQuestion.getDistance()));

        return view;
    }
}

Let me know if I should provide more information.

Class overview - CurrentUser - DatabaseHandler - DatabaseHandlerUser - LoginActivity
- MainActivity
- Question
- RegisterActivity - User

SQLitePCL - A SQLite Wrapper assembly for the current platform was not found

I had a previous WP8 cordova project that I would like to run on windows, so i have built my project using the command

cordova build windows

I now have a cordova project that targets windows 8, windows 8.1 and windows phone 8.1. I am using the cordova plugin to access SQLite via my web UI, which works perfectly fine.

In my previous WP8 project, i could call c# code by creating a new plugin, and thus acccessed the SQLite database.

In my new project, i have had to create a new portable class library and to access SQLite in this library, i have added a reference to SQLitePCL via

install-package SQLitePCL

Upon running this command, a readme file appears and tells you to install the following files, which i have done

Windows Store 8.0

Please, verify that the extension SDK SQLite for Windows Runtime v3.8.7.2, from the SQLite.org site (http://ift.tt/1HQUWUN), has been properly installed.

Windows Store 8.1

Please, verify that the extension SDK SQLite for Windows Runtime (Windows 8.1) v3.8.7.2, from the SQLite.org site (http://ift.tt/1bDdUSs), has been properly installed.

Windows Phone 8

Please, verify that the extension SDK SQLite for Windows Phone v3.8.7.2, from the SQLite.org site (http://ift.tt/1HQUWUP), has been properly installed.

Windows Phone 8.1

Please, verify that the extension SDK SQLite for Windows Phone 8.1 v3.8.7.2, from the SQLite.org site (http://ift.tt/1bDdX0z), has been properly installed.

So i am guessing that i have completed the installation successfully?

Now when i try to access this class library via my web ui, i get the following error

System.InvalidOperationException: A SQLite Wrapper assembly for the current platform was not found. Ensure that the current project references both SQLitePCL and the following platform-specific assembly: SQLitePCL.Ext. at System.Runtime.InteropServices.WindowsRuntime.ManagedActivationFactory.ActivateInstance()

and

WinRT information: System.InvalidOperationException: A SQLite Wrapper assembly for the current platform was not found. Ensure that the current project references both SQLitePCL and the following platform-specific assembly: SQLitePCL.Ext. at System.Runtime.InteropServices.WindowsRuntime.ManagedActivationFactory.ActivateInstance()

Why am i getting this error? I am running this on a windows 8.1 laptop.

copy sqlite database from assets folder to android persistentpath in unity3d

I have a squlite database in assets folder with name MyDatabse.s3db and I want to copy it to android Application.persistentdatapath. I have used the following code

string filepath = Application.persistentDataPath + "/MyDatabase.s3db";
if (!File.Exists (filepath))
{
WWW loadDB = new WWW("jar:file://" + Application.dataPath + "!/assets/MyDatabase.s3db");
Debug.Log("yield done");
while(!loadDB.isDone) {}
File.WriteAllBytes(filepath, loadDB.bytes);
Debug.Log("copy done");
}

The database is created in the Application.persistentDataPath path but it is empty.

Trying to connect Andriod app with SQLite to SQL Db via webservice (php)

To clarify on the problem; I'm following an old tutorial to try and learn some basic sync functionality with an Android app (here). Now I have the php webservice in place, I believe the permissions are set right, the POST in the Android code is set to the correct server IP. However when I try to synch the app to the SQL I am getting a AsyncHttpClient statusCode of 0. As far as I can tell, this means it is likely the request is timing out?

If so I'm looking for how to extend the timeout limit, if not then I am stuck and would appreciate any input from someone with an idea of where I'm going wrong. The code below is the relevant part from Android Studio.

public void syncSQLiteMySQLDB(){
    //Create AsycHttpClient object
    AsyncHttpClient client = new AsyncHttpClient();
    RequestParams params = new RequestParams();
    ArrayList<HashMap<String, String>> userList =  controller.getAllUsers();
    if(userList.size()!=0){
        if(controller.dbSyncCount() != 0){
            prgDialog.show();
            params.put("usersJSON", controller.composeJSONfromSQLite());
            client.post("http://ift.tt/1DJOf1U",params ,new AsyncHttpResponseHandler() {
                @Override
                public void onSuccess(String response) {
                    System.out.println(response);
                    prgDialog.hide();
                    try {
                        JSONArray arr = new JSONArray(response);
                        System.out.println(arr.length());
                        for(int i=0; i<arr.length();i++){
                            JSONObject obj = (JSONObject)arr.get(i);
                            System.out.println(obj.get("id"));
                            System.out.println(obj.get("status"));
                            controller.updateSyncStatus(obj.get("id").toString(),obj.get("status").toString());
                        }
                        Toast.makeText(getApplicationContext(), "DB Sync completed!", Toast.LENGTH_LONG).show();
                    } catch (JSONException e) {
                        // TODO Auto-generated catch block
                        Toast.makeText(getApplicationContext(), "Error Occured [Server's JSON response might be invalid]!", Toast.LENGTH_LONG).show();
                        e.printStackTrace();
                    }
                }

                @Override
                public void onFailure(int statusCode, Throwable error,
                                      String content) {
                    // TODO Auto-generated method stub
                    prgDialog.hide();
                    if(statusCode == 404){
                        Toast.makeText(getApplicationContext(), "Requested resource not found", Toast.LENGTH_LONG).show();
                    }else if(statusCode == 500){
                        Toast.makeText(getApplicationContext(), "Something went wrong at server end", Toast.LENGTH_LONG).show();
                    }else{
                        Toast.makeText(getApplicationContext(), "Error: " + statusCode, Toast.LENGTH_LONG).show();

                    }
                }
            });
        }else{
            Toast.makeText(getApplicationContext(), "SQLite and Remote MySQL DBs are in Sync!", Toast.LENGTH_LONG).show();
        }
    }else{
        Toast.makeText(getApplicationContext(), "No data in SQLite DB, please do enter User name to perform Sync action", Toast.LENGTH_LONG).show();
    }

Remote sqlite queries from app

I'm working on an iOS/Android app that uses a sqlite database with sensitive data. I've post this question here in Stackoverflow (Most secure strategy for mobile database) to know that my best option is to have the database in a server, access it with a backend and never download it to the apps.

My problem is that I have to make a process with some input and this database and I'm not sure if it's possible with this design.

The process is the following:

  1. I start with my original database.
  2. The user enters some inputs.
  3. I create a new table called A (a bit large) in the database. Its content depend on the input entered.
  4. I create a new table called B mixing (with some queries, of course) the data from my original database and the table A. This table is my final table and it doesn't contain sensitive data so it's ok for me to have it in my app.

Taking this process into account, if my original database is in a server, and my new table A is in my app, is there any way to perform step 4 without having to download my original database?

Sqlite error WP8.1

I'm developing Windows phone 8.1 app. I have problem with Sqlite database. I created one, and made file. Now i want to use that file in my app, and get data from it.

I did create like this:

 SQLiteConnection.CreateFile(@"C:\ProbaProbe\MySql.sqlite");

 SQLiteConnection m_dbConnection;
        m_dbConnection = new SQLiteConnection("Data Source=C:\ProbaProbe\MySql.sqlite;Version=3;");
        m_dbConnection.Open();
        string sql = "create table highscores (name varchar(20), score int)";
        SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
        command.ExecuteNonQuery();

And i inserted some data with INSERT statment. That works nice. Now, when i want to use that MySql.sqlite in Windows Phone 8.1 app, i have problems. I did this way:

 private async Task CopyDatabase()
    {
        string DB_PATH = Path.Combine(@"C:\ProbaProbe\MySql.sqlite");
        bool isDatabaseExisting = false;

        using (var db = new SQLite.SQLiteConnection(DB_PATH))
        {
            isDatabaseExisting = true;
            grad q = new grad();
            SQLiteCommand sqlComm = new SQLiteCommand(db);   
            string a = (db.Query<grad>("select * from grad")[0]).Ime;
        catch
        {
            isDatabaseExisting = false;
        }


    }

Problem is: Could not open database file: C:\ProbaProbe\MySql.sqlite (CannotOpen)

I think i'm missing something... Anyone? Thx

junit.framework.AssertionFailedError

I get error of this:

junit.framework.AssertionFailedError at com.example.sirivan.myfirstapp.test.TestDb.testCreateDb(TestDb.java:22)

TestDb.java

public class TestDb extends AndroidTestCase {
public static final String LOG_TAG = TestDb.class.getSimpleName();

public void testCreateDb() throws Throwable{
    assertTrue(mContext.deleteDatabase(WeatherDbHelper.DATABASE_NAME));
    SQLiteDatabase db = new WeatherDbHelper(
            this.mContext).getWritableDatabase();
    assertEquals(true, db.isOpen());
    db.close();
}

}

Android Sqlite database encrypt/decrypt or password protected

What I just want to know how can we secure out Sqlite Database. I have gone through many discussion but didn't find any answer suitable. As I feel we cannot secure the database 100%.

I find few option code:

SQLiteConnection conn = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
conn.SetPassword("password");
conn.open();

In the above code I am unable to import SQLiteConnection

Also I try to use Sqlcipher but it leads to me encryption/decryption of data in row(i;e encryption/decryption data one by one).(Worthwhile but still few drawbacks like for hitting a query we need to decyrpt the data first and then match the query, which is obfuscate)

Is there any way we can password protect the sqlite database even if the hacker get the db it will be of no use without password?

But it leads to one more question that where we should save the password?

because we cannot save the password in source code as it can be easily reverse engineer by hackers.

So I just want to confirm what are the possible ways that we can protect the sqlite database at extreme end so that it will be difficult for the hackers to crack.Looking for the good responses.Thanks in advance

NOTE:please don't copy paste any discussion from stackoverflow as I have gone through almost every discussion.

Create SQL table, SQLite Exception

know this is a very elementary question, i keep on getting this error code on my logcat:

android.database.sqlite.SQLiteException: near "SELECT_id": syntax error (code 1): , while compiling: SELECT_id, name,password FROM GANDALF ORDER BY name

Would really need some advice, have tried looking at other create sqlite table post but couldnt solve it.

     public void onCreate(SQLiteDatabase db) {
     db.execSQL("CREATE TABLE gandalf " +
            "(_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,password TEXT, category TEXT);");

}

And try this too

     db.execSQL("CREATE TABLE books ( " +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " + 
                "name TEXT, "+
                "password TEXT, "+
                "category TEXT)");

Full code for datahelper public class DataHelp extends SQLiteOpenHelper{

private static final String dbname = "pass.db"; 
private static final int ver = 1;

public DataHelp(Context context) {
    super(context, dbname, null, ver);
    // TODO Auto-generated constructor stub
}

@Override
public void onCreate(SQLiteDatabase db) {
     db.execSQL("CREATE TABLE gandalf " +
            "(_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,password TEXT, category TEXT);");

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub
}

public void insertDB(String name, String pass){
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.put("Name", name);
    cv.put("Pass", pass);
    db.insert("db", null, cv);
    db.close();
}

public Cursor getAll(){
    return (getReadableDatabase().rawQuery("SELECT_id, name," +
            "password FROM GANDALF ORDER BY name",null));
}

public String getName(Cursor c){
    return(c.getString(1));
}

 public String getPass(Cursor c) {
        return(c.getString(2));
      }

 public String getCat(Cursor c) {
        return(c.getString(3));
      }

}

passing data into internal database

i have managed to create a application that grabs a json array from a website and converts it into several strings, but now i need to create a internal database so that i can store that data.

the json for example would be

{"tblTuts":[{"tutID":"00001","catID":"00002","tutTitle":"edsfadfas","userID":"00002","tutDateCreated":"0000-00-00 00:00:00","tutDateModified":"2015-04-28 12:25:29","tutText":"fdsa","imageID":"00007","videoID":"00001"},{"tutID":"00002","catID":"00001","tutTitle":"html","userID":"00001","tutDateCreated":"2015-04-27 23:32:58","tutDateModified":"2015-04-27 23:32:58","tutText":"sfdvdsvsd","imageID":"00008","videoID":"00004"},{"tutID":"00003","catID":"00002","tutTitle":"pie","userID":"00002","tutDateCreated":"2015-04-28 12:27:01","tutDateModified":"2015-04-28 12:27:28","tutText":"pie is fun","imageID":"00007","videoID":"00001"}],"success":1}

it from their converts them into a string after being passed through a json parser

private static final String TAG_TUTID = "tutID";
private static final String TAG_TUTTITLE = "tutTitle";
private static final String TAG_NAME = "tutText";
private static final String TAG_DATEC = "tutDateCreated";
private static final String TAG_DATEM = "tutDateModified";
private static final String TAG_IMAGE = "imageID";
private static final String TAG_VIDEO = "videoID";

this is my current database controller as far as i have managed to figure out.

public class DBController  extends SQLiteOpenHelper {

public DBController(Context applicationcontext) {
    super(applicationcontext, "androidsqlite.db", null, 1);
}
//Creates Table
@Override
public void onCreate(SQLiteDatabase database) {
    String query;
    query = "CREATE TABLE tblTuts ( tutID INTEGER PRIMARY KEY, catID TEXT, tutTitle TEXT, userID TEXT, tutDateCreated TEXT, tutDateModified TEXT, tutText TEXT, imageID TEXT, videoID TEXT)";
    database.execSQL(query);
}
@Override
public void onUpgrade(SQLiteDatabase database, int version_old, int current_version) {
    String query;
    query = "DROP TABLE IF EXISTS tblTuts";
    database.execSQL(query);
    onCreate(database);
}
/**
 * Inserts User into SQLite DB
 * @param queryValues
 */
public void insertUser(HashMap<String, String> queryValues) {
    SQLiteDatabase database = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put("tutID", queryValues.get("tutID"));
    values.put("catID", queryValues.get("catID"));
    values.put("tutTitle", queryValues.get("tutTitle"));
    values.put("tutDateCreated", queryValues.get("tutDateCreated"));
    values.put("tutDateModified", queryValues.get("tutDateModified"));
    values.put("tutText", queryValues.get("tutText"));
    values.put("imageID", queryValues.get("imageID"));
    values.put("videoID", queryValues.get("videoID"));
    database.insert("tblTuts", null, values);
    database.close();
}

/**
 * Get list of Users from SQLite DB as Array List
 * @return
 */
public ArrayList<HashMap<String, String>> getAllUsers() {
    ArrayList<HashMap<String, String>> wordList;
    wordList = new ArrayList<HashMap<String, String>>();
    String selectQuery = "SELECT  * FROM tblTuts";
    SQLiteDatabase database = this.getWritableDatabase();
    Cursor cursor = database.rawQuery(selectQuery, null);
    if (cursor.moveToFirst()) {
        do {
            HashMap<String, String> map = new HashMap<String, String>();
            map.put("tutID", cursor.getString(0));
            map.put("catID", cursor.getString(1));
            map.put("tutTitle", cursor.getString(2));
            map.put("tutDateCreated", cursor.getString(3));
            map.put("tutDateModified", cursor.getString(4));
            map.put("tutText", cursor.getString(5));
            map.put("imageID", cursor.getString(6));
            map.put("videoID", cursor.getString(7));

            wordList.add(map);
        } while (cursor.moveToNext());
    }
    database.close();
    return wordList;
}

can someone help me figure out how to pass those strings and turn it into a working internal database?

Issue with using Resolve in combination with a SQLite Call and IonicPlatform Ready

I am trying to resolve a state:

  // Authentication & Setup
  .state('auth', {
    url: '/auth',
    templateUrl: 'templates/single-auth.html',
    controller: 'AuthCtrl',  
    resolve: {
      setupResolve: setupResolve
    }
  })

With this function setupResolve, I want to: 1. open the SQlite DB if not open already 2. get a table 'Variables' 3. If one of the values from the table passes, then resolve the state 4. otherwise: redirect to state Setup

I do this as follows in my app.js:

app.js

.run(function($ionicPlatform, $state, $rootScope) {
  $ionicPlatform.ready(function() {
    // Hide the accessory bar by default (remove this to show the accessory bar above the keyboard
    // for form inputs)
    if (window.cordova && window.cordova.plugins.Keyboard) {
      cordova.plugins.Keyboard.hideKeyboardAccessoryBar(true);
    }
    if (window.StatusBar) {
      // org.apache.cordova.statusbar required
      StatusBar.styleDefault();
    }
  });

  //
  // state change error redirection
  $rootScope.$on('$stateChangeError', function(event, toState, toParams, fromState, fromParams, error) {
    //
    event.preventDefault(); // http://goo.gl/se4vxu
    switch (error) {
      case "SETUP_RESOLVE_ERROR":
        //
        $state.go('setup');
        break
      default:
        //
        $state.go('auth');
        break
    }
    console.log("$stateChangeError: error: ", error)
  });
})



.config(function($stateProvider, $urlRouterProvider) {
  //
  $urlRouterProvider.otherwise('/auth');

  //
  var authResolve = function ($q, Auth) {

    // CODE NOT RELEVANT, ONLY setupResolve
  };

  //
  var setupResolve = function(DebugConsole, $q, StorageFactory, $cordovaSplashscreen) {
    var qSetup = $q.defer();

    StorageFactory.getResolveVariable('setid').then(
      function(setId) {

        console.log("setupResolve: success", setId)
        DebugConsole.log("setupResolve: success", setId)

        //
        $cordovaSplashscreen.hide();
        qSetup.resolve(setId);

    }, function(error){

        console.log("setupResolve: error", error)
        DebugConsole.log("setupResolve: error", error)

        //
        $cordovaSplashscreen.hide();
        qSetup.reject("SETUP_RESOLVE_ERROR")

    })
    return qSetup.promise;
  };

  // Ionic uses AngularUI Router which uses the concept of states
  // Learn more here: http://ift.tt/ZzusQR
  // Set up the various states which the app can be in.
  // Each state's controller can be found in controllers.js
  $stateProvider

  // setup an abstract state for the tabs directive
  .state('tab', {
    url: "/tab",
    abstract: true,
    templateUrl: "templates/tabs.html",
    controller: "TabCtrl"
  })

  // Each tab has its own nav history stack:
  .state('tab.home', {
    url: '/home',
    views: {
      'menuContent': {
        templateUrl: 'templates/tab-home.html',
        controller: 'HomeCtrl', 
        resolve: {authResolve: authResolve}
      }
    }
  })

  // -----------------
  // Authentication & Setup
  .state('auth', {
    url: '/auth',
    templateUrl: 'templates/single-auth.html',
    controller: 'AuthCtrl',  
    resolve: {
      setupResolve: setupResolve
    }
  })

  .state('setup', {
    url: '/setup',
    templateUrl: 'templates/single-setup.html',
    controller: 'SetupCtrl'
  })

  .state('setup-accesscode', {
    url: '/setup-accesscode',
    templateUrl: 'templates/single-setup-accesscode.html',
    controller: 'SetupAccessCodeCtrl'
  })


})

SetupFactory

and then call the function getResolveVariable() in the SetupFactory, which looks as follows:

self.getResolveVariable = function(variableName, syncBoolean) {

        window.alert("getResolveVariable: start")

        var qResolve = $q.defer();

        self.getFullTable('Variables').then(function(result){

            window.alert("getResolveVariable: getFullTable: success: " + result)

            console.log("getResolveVariable", variableName, result)
            //return result0[variableName]; 

            //
            var result0 = result[0];
            qResolve.resolve(result0[variableName]) // will throw error if result = {}

        }, function(error){

            window.alert("getResolveVariable: getFullTable: error: " + error)

            console.log("getResolveVariable", error)
            //return error;

            //
            qResolve.reject(error)
        })

        return qResolve.promise;
    };


self.getFullTable = function(tableName) {

        window.alert("getFullTable: start: " + tableName)

        var queryStr = "SELECT * FROM " + tableName 
        return DBA.query(queryStr, [])
        .then(function(result) {
            return DBA.getAllObj(result, '{}'); // inside converts to object
        }, function(error){
            window.alert("getFullTable: error: " + error)
            return error;
        });
    };

query()

This function getFullTable() calls the function query() from the factory DBA and looks as follows:

self.query = function (query, parameters) {

    window.alert("query: " + query)

    window.alert("query: db = " + db)

    var qQuery = $q.defer();
    switch (db) {
      case null:
        //
        openAndProceedQuery();
        break
      default:
        //
        proceedQuery();
        break
    };

    //
    // A
    function openAndProceedQuery() {

      window.alert("query: openAndProceedQuery: start")

      self.openDb().then(function(){
        //
        // --> B
        proceedQuery();
      }, function(error){
        //
        console.log("query: openDb error" + error)
        DebugConsole.log("query: openDb error" + error)
      })
    };

    //
    // B
    function proceedQuery() {

      window.alert("query: proceedQuery: start")

      parameters = parameters || [];
      $ionicPlatform.ready(function () {
        $cordovaSQLite.execute(db, query, parameters)
          .then(function (result) {

            window.alert("query: proceedQuery: success" + result)
            qQuery.resolve(result);

          }, function (error) {
            console.log(query, parameters)
            console.warn('I found an error');
            console.warn(error);
            window.alert("query: proceedQuery: error" + error)

            qQuery.reject(error);
          });
      });
    };

    return qQuery.promise;
  };

openDb()

If the DB is not opened yet, then it calls the function openDb() in the same factory DBA, and this function looks as follows:

self.openDb = function() {

    DebugConsole.log("opening db...")
    window.alert("openDb: start")

    //
    var qOpen = $q.defer();
    $ionicPlatform.ready(function() {

      window.alert("openDb: $ionicPlatform.ready")

      // Open Db
      if(window.cordova) {
        window.alert("openDb: cordova true")
        db = $cordovaSQLite.openDB("starter.db");
      } else {
        window.alert("openDb: cordova false")
        db = window.openDatabase("starter.db", "1.0", "My app", 1024 * 1024 * 100);
      }

      // Create Tables
      self.createTables().then(function(){
        qOpen.resolve(true);
      }, function(error){
        qOpen.reject(error);
      })
    })
    return qOpen.promise;
  };

Issue

The issue is that in the last function openDb(), the ionicPlatform.ready() is not reached. It results that when opening the app, my whole screen stays white. As you can see, I put a lot of window.alerts to be able to debug it, and it all triggers the alerts as expected, with as exception the alerts within the ionicPlatform.ready() in openDb(). So it must be, that somehow the ionicPlatform.ready is the cause of the problem.

How can I bypass this? Moreover, how to properly resolve it eventually?

Note: when testing it Chrome, everything works fine! It is only when testing it on my phone that it does not work...