samedi 31 octobre 2015

How to Execute UpdateAsync with Where statement

I have a table with 1 row. log => 1 | flagLog => 0

 public async void updateFlag()
    {
        var local = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "hinos.sqlite");
        SQLiteAsyncConnection con = new SQLiteAsyncConnection(local, SQLiteOpenFlags.Create | SQLiteOpenFlags.ReadWrite);

        var updateFlagLog = await con.FindAsync<logon>(u => u.log == 1);
        if (updateFlagLog != null)
        {
             await con.UpdateAsync(?????);
        // update logon set flagLog = 1 where log = 1;

        }
    }

I got the row with this comand

var updateFlagLog = await con.FindAsync<logon>(u => u.log == 1);

But i dont know how i use to update this row.

How to insert values into database only when created ?(SQLite Android Studio)

Im using SQLite database in my android studio project , I need to insert some default values to columns in a spesific table in my database only when it created for the first time ! ("for the first time" -I mean only when the database(and the table I want set the default values) is created! not when it opens(but not created ))

Thats my dbHelper Class :

public class DBhelper extends SQLiteOpenHelper {
//Table Name
public static final String TABLE_NAME = "MyShifts";
public static final String Table_NAME2="MySettings";
//Table coloumns
public static final String _ID = "_id";
public static final String MyShifts_EnteyHour = "enteryhour";
public static final String MyShifts_ExitHour = "exithour";
public static final String MyShifts_EnteryDate = "enterydate";
public static final String MyShifts_ExitDate = "exitdate";
public static final String MyShifts_Taarif="Tarrif";
public static final String MyShifts_Bonus="Bonus";
public static final String MyShifts_Comments="Comments";
public static final String MyShifts_Month="month";
public static final String MyShifts_year="year";
public static final String MyShifts_Duration="duration";
public static final String MyShifts_WeekDay="weekday";

public static final String MySettings_ID="id";
public static final String MySettings_TaarifHolDay="TaarifHol";
public static final String MySetting_TarrifFriday="TaarifFriday";
public static final String MySetting_TarrifSaturday="TaarifSatudrday";
//DataBase Information
static final String DB_NAME="Mydb.db";
// database version
static final int DB_VERSION = 1;

// Creating table query
private static final String CREATE_TABLE = "create table " + TABLE_NAME + "                 (" + _ID
        + " INTEGER PRIMARY KEY AUTOINCREMENT, " + MyShifts_EnteyHour
        + " TEXT NOT NULL, " + MyShifts_ExitHour + " TEXT NOT NULL, " + MyShifts_EnteryDate + " TEXT NOT NULL, "
        + MyShifts_ExitDate + " TEXT NOT NULL, "
        + MyShifts_Month + " INTEGER NOT NULL, " + MyShifts_year + " INTEGER NOT NULL, " + MyShifts_Taarif + " REAL NOT NULL, " + MyShifts_Bonus +

 " INTEGER NOT NULL, " + MyShifts_Comments +" TEXT, " + MyShifts_Duration + " TEXT NOT NULL, " + MyShifts_WeekDay+ " TEXT NOT NULL);";

private static final String CREATE_TABLE2= "create table " + Table_NAME2 + "("+ MySettings_ID + "INTEGER PRIMARY KEY AUTOINCREMENT, " +
         MySettings_TaarifHolDay + " REAL NOT NULL, " + MySetting_TarrifFriday + " REAL NOT NULL," + MySetting_TarrifSaturday + "REAL NOT NULL);";

public DBhelper(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_TABLE);
}
public void OnCreate(SQLiteDatabase db){db.execSQL(CREATE_TABLE2);}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    db.execSQL("DROP TABLE IF EXISTS " + Table_NAME2);
    onCreate(db);
}

}

which and where code should I add in order to add default values to "MySettings" table when its created for the first time(created and not open) ? Thank you!

getting single cell from sqlite into textview

I'm trying to get single data cell from my DB into text view

this is the function i wrote it in my sqliteopenhelper class

public Cursor getMainData(){
SQLiteDatabase db = this.getWritableDatabase();
Cursor result = db.rawQuery("select NAMEPPL from "+Table_name2,null);
return result;}

and this what i did it in the main activity class ,

its crashing once i lunch the app and giving me something related to buffer while I'm not using any buffers

is this the write way?

Thank you in advance.

public class MainActivity extends AppCompatActivity {

DB db;
Button addmed,addpl;
TextView PPLNAMERES;
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    PPLNAMERES = (TextView)findViewById(R.id.PPLAGETXTVIEW);

    db = new DB(this);
    listView();
}

public void addmedView(View view){
    Intent ADDMEDVIEW = new Intent(this,ADDMEDCINEVIEW.class);
    startActivity(ADDMEDVIEW);

}
public void addpplview(View view){
    Intent ADDPPLVIEWS = new Intent(this,ADDPPLVIEW.class);
    startActivity(ADDPPLVIEWS);
}
 public void listView(){
Cursor res= db.getMainData();
if(res.getCount()==0){
    Toast.makeText(MainActivity.this,"No Data",Toast.LENGTH_LONG).show();
    return;
}
else{
    PPLNAMERES.setText(res.getString(0));

}}

SQLite performance on large tables

I've done quite a bit of research on my following issue, but couldn't find any helpful hint so far.

I'm using sqlite3 in python in an application running both under windows and linux. My database file is currently in the range of 700 MB.

I recognized one special performance issue regarding the number of entries in my largest table. It consists of 10 columns being integer and float numbers and one varchar.

The table has 1.6 Mio rows. For that size each SELECT or UPDATE command takes 327ms. That is by far too long for my application, since it mainly waits on sqlite now.

I recognized, that performance drastically increases with table size dropping. I found:

  • 1.6 Mio entries 327 ms
  • 670k entries 149 ms
  • 280k entries 71 ms
  • 147k entries 44 ms
  • 19k entries 15 ms

So access time almost linearily increases with table size.

Is there any way to improve performance here? Do I really need to split my data to multiple identical tables, even if this is very bad style?!?

Thanks in advance!

How to Initialize other WP8.1 xaml?

I have a login screen, and i am inserting a flag with SQLite, for example.

conn.CreateTableAsync<logon>();
if (data[0].findmember.ToString() == "1")
{
    logon log = new logon();
    log.log = 1; // get flag
    await conn.InsertAsync(log); // insert flag
    Frame.Navigate(typeof(Home)); // change xaml page
}

> i need get the flag value, because, i need to change automatically in the next time

public MainPage()
{
    if (sqlite.table == 1 ) //example
    {
      Frame.Navigate(typeof(Home));
    }
    else //login page
    {
    this.InitializeComponent();
    this.NavigationCacheMode = NavigationCacheMode.Required;
    numInput();
    }
}

I need to show the login screen only 1 time

No information stored in SQLite Database

I've been working on a Login-Register application and is currently facing some problem. The problem I encountered was no data is stored into my SQLite Database even though I was able to enter information and click on the "sign up" button. And whenever I press on the "Login" button, the application will always show "Unfortunately this application stopped". Anyone know why and can help? I hope to get help soon as I am doing this for my final year project. Million of thanks to all of you.

logcat:

11-01 02:52:06.893  31336-31336/? E/Zygote﹕ MountEmulatedStorage()
11-01 02:52:06.893  31336-31336/? E/Zygote﹕ v2
11-01 02:52:06.893  31336-31336/? I/libpersona﹕ KNOX_SDCARD checking this for 10139
11-01 02:52:06.893  31336-31336/? I/libpersona﹕ KNOX_SDCARD not a persona
11-01 02:52:06.893  31336-31336/? I/SELinux﹕ Function: selinux_compare_spd_ram, SPD-policy is existed. and_ver=SEPF_GT-I9505_5.0.1 ver=27
11-01 02:52:06.893  31336-31336/? I/SELinux﹕ Function: selinux_compare_spd_ram , priority [2] , priority version is VE=SEPF_GT-I9505_5.0.1-1_0032
11-01 02:52:06.903  31336-31336/edu.np.ece.information E/SELinux﹕ [DEBUG] get_category: variable seinfo: default sensitivity: NULL, cateogry: NULL
11-01 02:52:06.903  31336-31336/edu.np.ece.information I/art﹕ Late-enabling -Xcheck:jni
11-01 02:52:06.933  31336-31343/edu.np.ece.information E/art﹕ Failed writing handshake bytes (-1 of 14): Broken pipe
11-01 02:52:06.933  31336-31343/edu.np.ece.information I/art﹕ Debugger is no longer active
11-01 02:52:06.983  31336-31336/edu.np.ece.information D/ResourcesManager﹕ creating new AssetManager and set to /data/app/edu.np.ece.information-1/base.apk
11-01 02:52:07.203  31336-31336/edu.np.ece.information D/Activity﹕ performCreate Call secproduct feature valuefalse
11-01 02:52:07.203  31336-31336/edu.np.ece.information D/Activity﹕ performCreate Call debug elastic valuetrue
11-01 02:52:07.213  31336-31353/edu.np.ece.information D/OpenGLRenderer﹕ Render dirty regions requested: true
11-01 02:52:07.263  31336-31353/edu.np.ece.information I/Adreno-EGL﹕ <qeglDrvAPI_eglInitialize:410>: EGL 1.4 QUALCOMM build:  ()
    OpenGL ES Shader Compiler Version: E031.25.03.06
    Build Date: 01/24/15 Sat
    Local Branch: AF11_RB1_AU15
    Remote Branch:
    Local Patches:
    Reconstruct Branch:
11-01 02:52:07.263  31336-31353/edu.np.ece.information I/OpenGLRenderer﹕ Initialized EGL, version 1.4
11-01 02:52:07.293  31336-31353/edu.np.ece.information D/OpenGLRenderer﹕ Enabling debug mode 0
11-01 02:52:07.593  31336-31336/edu.np.ece.information I/Timeline﹕ Timeline: Activity_idle id: android.os.BinderProxy@1ddca8e7 time:4384368
11-01 02:52:11.237  31336-31336/edu.np.ece.information D/ViewRootImpl﹕ ViewPostImeInputStage ACTION_DOWN
11-01 02:52:14.110  31336-31336/edu.np.ece.information D/ViewRootImpl﹕ ViewPostImeInputStage ACTION_DOWN
11-01 02:52:16.302  31336-31336/edu.np.ece.information D/ViewRootImpl﹕ ViewPostImeInputStage ACTION_DOWN
11-01 02:52:16.372  31336-31336/edu.np.ece.information E/SQLiteLog﹕ (1) no such column: pass
11-01 02:52:16.372  31336-31336/edu.np.ece.information D/AndroidRuntime﹕ Shutting down VM
11-01 02:52:16.372  31336-31336/edu.np.ece.information E/AndroidRuntime﹕ FATAL EXCEPTION: main
    Process: edu.np.ece.information, PID: 31336
    java.lang.IllegalStateException: Could not execute method of the activity
            at android.view.View$1.onClick(View.java:4253)
            at android.view.View.performClick(View.java:5197)
            at android.view.View$PerformClick.run(View.java:20926)
            at android.os.Handler.handleCallback(Handler.java:739)
            at android.os.Handler.dispatchMessage(Handler.java:95)
            at android.os.Looper.loop(Looper.java:145)
            at android.app.ActivityThread.main(ActivityThread.java:5942)
            at java.lang.reflect.Method.invoke(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:372)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1400)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1195)
     Caused by: java.lang.reflect.InvocationTargetException
            at java.lang.reflect.Method.invoke(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:372)
            at android.view.View$1.onClick(View.java:4248)
            at android.view.View.performClick(View.java:5197)
            at android.view.View$PerformClick.run(View.java:20926)
            at android.os.Handler.handleCallback(Handler.java:739)
            at android.os.Handler.dispatchMessage(Handler.java:95)
            at android.os.Looper.loop(Looper.java:145)
            at android.app.ActivityThread.main(ActivityThread.java:5942)
            at java.lang.reflect.Method.invoke(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:372)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1400)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1195)
     Caused by: android.database.sqlite.SQLiteException: no such column: pass (code 1): , while compiling: select uname, pass from information
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1093)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:670)
            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
            at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
            at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
            at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1440)
            at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1379)
            at edu.np.ece.information.DatabaseHelper.searchPass(DatabaseHelper.java:68)
            at edu.np.ece.information.MainActivity.onButtonClick(MainActivity.java:38)
            at java.lang.reflect.Method.invoke(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:372)
            at android.view.View$1.onClick(View.java:4248)
            at android.view.View.performClick(View.java:5197)
            at android.view.View$PerformClick.run(View.java:20926)
            at android.os.Handler.handleCallback(Handler.java:739)
            at android.os.Handler.dispatchMessage(Handler.java:95)
            at android.os.Looper.loop(Looper.java:145)
            at android.app.ActivityThread.main(ActivityThread.java:5942)
            at java.lang.reflect.Method.invoke(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:372)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1400)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1195)

Here is the project folder link : http://ift.tt/1Wnuyvd

Creating server for android app

Im want to create a server for my android app, the server should download a zip file from a static url (every night). The zip file contains fiew txt files in csv format, the files data should update an sqlite db and the android app should get data from the db. The server need to be secured to some degree and only allow requests from the app. Im a server newbie and work with java. What would be the easyest way to start? Ive read about parse.com but it cant handle file downloads, Google cloud platform seems appropriate but Im not sure about the pricings. What are my options ? Also please add tutorials

ASP.NET 5, EF 7 and SQLite - SQLite Error 1: 'no such table: Blog'

I followed the Getting Started on ASP.NET 5 guide about Entity Framework 7 and I replaced MicrosoftSqlServer with Sqlite, the only difference in the code is in Startup.cs:

services.AddEntityFramework()
    .AddSqlite()
    .AddDbContext<BloggingContext>(options => options.UseSqlite("Filename=db.db"));

When I run the website and navigate to /Blogs, I get an error:

Microsoft.Data.Sqlite.SqliteException was unhandled by user code
ErrorCode=-2147467259 HResult=-2147467259 Message=SQLite Error 1: 'no such table: Blog' Source=Microsoft.Data.Sqlite
SqliteErrorCode=1 StackTrace: at Microsoft.Data.Sqlite.Interop.MarshalEx.ThrowExceptionForRC(Int32 rc, Sqlite3Handle db) at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader() at Microsoft.Data.Entity.Query.Internal.QueryingEnumerable.Enumerator.MoveNext() at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext() at System.Linq.Enumerable.d__1`2.MoveNext() at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext() at Microsoft.Data.Entity.Query.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at EFGetStarted.AspNet5.Controllers.BlogsController.Index() in d:\arthur\documents\visual studio 2015\Projects\EFGetStarted.AspNet5\src\EFGetStarted.AspNet5\Controllers\BlogsController.cs:regel 18 InnerException:

I understand this as if there is no table called 'Blog', but when I open the .db file in DB Browser for SQLite, there actually is a table called 'Blog':

Screenshot from DB Browser for SQLite showing a table called 'Blog'

Does SQLite require other changes in the code, or is this an error in the SQLite connector for Entity Framework?

Downloading data of text from server to SQL database into list view activity

I have different activities containing list views. How can I add paragraphs of text from server to each list view item using SQL database. Please anyone help me with the code.

Insert a product and update its column

I have a table INVENTORY with 2 columns [product(primary key) - quantity]. I want to insert to this table a product with its quantity.

public boolean insertPackage(String product, int quantity) 
            throws SQLException, ClassNotFoundException {

        System.out.println("Insert product to Invetory");
        boolean flag=false;
        sq = "INSERT INTO INVENTORY VALUES (?, ?)";

        try {       
            Class.forName(typeDB);
            c = DriverManager.getConnection(path);            
            stm = c.prepareStatement(sq);

            PreparedStatement stm = c.prepareStatement(sq);

            stm.setString(1, product);
            stm.setInt(2, quantity);
            int rowsAffected = stm.executeUpdate();

        } catch (SQLException e) { 
            //There is already a same product in the Inventory
            flag = true;
            System.out.println(e.getMessage());
        } finally {
        if (stm != null)
                    stm.close();
        if (c != null)
                    c.close();
        }     

        return flag; //if the flag is true, then execute insert.
    }

If it returns true, then I search for this product, retrieve the quantity and then update the table with the new quantity. I am wondering if this way I thought, is a good way to check how to perform the insertion or there is a better one.

image Not Display from drawable with SimpleCursorAdapter.ViewBinder

I Create a List view with Simple Cursor Adapter in that adapter i fetch my Title Column from SQlite now i want to add images to the same adapter from Drawable Folder to the title field How can we Do This using SimpleCursorAdapter.ViewBinder

Here is My Code

 protected void onCreate(Bundle savedInstanceState) {
final int[] imageResource = new int[]{R.drawable.juice,R.drawable.medicinebowl,R.drawable.kam};
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        listView= (ListView) findViewById(R.id.listView);

 dbHelper = new SqlLiteDbHelper(this);
        try {
            dbHelper.openDataBase();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        sqLiteDatabase = dbHelper.getReadableDatabase();
        cursor = dbHelper.gettitles(sqLiteDatabase);
        String[] from = new String[]{dbHelper.TITLE};

 int[] to = new int[]{R.id.title};
        SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, R.layout.title_row, cursor, from, to);
        adapter.notifyDataSetChanged();

         adapter.setViewBinder(new SimpleCursorAdapter.ViewBinder() {

            @Override
            public boolean setViewValue(View view, Cursor cursor, int columnIndex) {
                switch (view.getId()) {
                    case R.id.circle:
                        int imageType = cursor.getInt(columnIndex);
                        int imageToBeShown = 0;
                        switch (imageType) {
                            case 0:
                                imageToBeShown = imageResource[0];
                                break;
                            case 1:
                                imageToBeShown = imageResource[0];
                                break;
                            case 2:
                                imageToBeShown = imageResource[0];
                                break;

                        }
                        ((ImageView) view).setImageResource(imageToBeShown);
                        return true;

                }
                return false;
            }
        });

        listView.setAdapter(adapter);

Custom Layout

<ImageView
    android:layout_width="90dp"
    android:layout_height="190dp"
    android:layout_marginLeft="10dp"
    android:id="@+id/circle"
    />
    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textAppearance="?android:attr/textAppearanceLarge"
        android:text="Large Text "
        android:id="@+id/title"
        android:layout_gravity="center_horizontal"
        android:layout_marginTop="10dp"
        style="@android:style/TextAppearance.Medium"
        android:textColor="#FFFFFF"
        android:layout_toRightOf="@+id/circle"
        android:layout_centerHorizontal="true"
        />

How To load Images from Drawable to SimpleCursorAdapter

I Create a List view with Simple Cursor Adapter in that adapter i fetch my Sqlite Column names now i want to add images to the same adapter from Drawable Folder How can we Do that Give ideas and Sugession thanks..

Here is My Code:

protected void onCreate(Bundle savedInstanceState) {
        int circle[]=new int[]{R.drawable.juice,R.drawable.medicinebowl,R.drawable.kam};
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        listView= (ListView) findViewById(R.id.listView);
 dbHelper = new SqlLiteDbHelper(this);
        try {
            dbHelper.openDataBase();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        sqLiteDatabase = dbHelper.getReadableDatabase();
        cursor = dbHelper.gettitles(sqLiteDatabase);
        String[] from = new String[]{dbHelper.TITLE, String.valueOf(circle)};
int[] to = new int[]{R.id.title,R.id.circle};
        SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, R.layout.title_row, cursor, from, to);
        adapter.notifyDataSetChanged();
        listView.setAdapter(adapter);

SQLite select query to get one row from multiple rows have same id

I'm trying to get one row from table that have an id column that duplicate in several rows of a SQlite database, but I am getting all rows in table.

This is my table creation and selection query.

CREATE TABLE IMAGES ( MID INTEGER , IMAGE BLOB, ID INTEGER );


SELECT DISTINCT ID, IMAGE  FROM IMAGES;

This what I get

mid | image | id
 1  |  m1   |  1
 2  |  m2   |  1
 3  |  m3   |  1
 4  |  m4   |  2
 5  |  m5   |  3
 6  |  m6   |  3

And this what i want

mid | image | id
 1  |  m1   |  1
 4  |  m4   |  2
 5  |  m5   |  3

Android: Sqlite database no such table error

i used DataBaseHelper class to copy Sqlite database file from assets , but when i write this code :

DataBaseHelper dbhelper=new DataBaseHelper(this); SQLiteDatabase db=dbhelper.getReadableDatabase(); Cursor cur=db.rawQuery("SELECT * FROM data", null);

i got this error : android.database.sqlite.SQLiteException: no such table: data

but im sure that table exists...

class code :

class DataBaseHelper extends SQLiteOpenHelper {

    private static String DB_PATH = "/data/data/com.example.amin.rendertest/databases/";
    private static String DB_NAME = "db.db";
    private SQLiteDatabase myDataBase;
    private final Context myContext;

    public DataBaseHelper (Context context) {
        super(context, DB_NAME, null, 1);
        this.myContext = context;

    }

    public void crateDatabase() throws IOException {
        boolean vtVarMi = isDatabaseExist();

        if (!vtVarMi) {
            this.getReadableDatabase();

            try {
                copyDataBase();
            } catch (IOException e) {
                throw new Error("Error copying database");
            }
        }
    }

    private void copyDataBase() throws IOException {

        // Open your local db as the input stream
        InputStream myInput = myContext.getAssets().open(DB_NAME);

        // Path to the just created empty db
        String outFileName = DB_PATH + DB_NAME;

        // Open the empty db as the output stream
        OutputStream myOutput = new FileOutputStream(outFileName);

        // transfer bytes from the inputfile to the outputfile
        byte[] buffer = new byte[1024];
        int length;
        while ((length = myInput.read(buffer)) > 0) {
            myOutput.write(buffer, 0, length);
        }

        // Close the streams
        myOutput.flush();
        myOutput.close();
        myInput.close();
    }

    private boolean isDatabaseExist() {
        SQLiteDatabase kontrol = null;

        try {
            String myPath = DB_PATH + DB_NAME;
            kontrol = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

        } catch (SQLiteException e) {
            kontrol = null;
        }

        if (kontrol != null) {
            kontrol.close();
        }
        return kontrol != null ? true : false;
    }

    public void openDataBase() throws SQLException {

        // Open the database
        String myPath = DB_PATH + DB_NAME;
        myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);

    }

    public Cursor Sample_use_of_helper() {

        return myDataBase.query("TABLE_NAME", null, null, null, null, null, null);
    }

    @Override
    public synchronized void close() {
        if (myDataBase != null)
            myDataBase.close();
        super.close();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }
}

SQL Join Query refactoring

I am trying to Join 3 tables based on specific columns. data_customer(main Table), data_customer_Address(Cust_id as foreign key) and Data_customer_contacts (Cust_id as foreign key) I have successfully achieved the result from below query:

select cst.cust_companyName as 'Company Name', cnt.cntct_fname as 'Contact First Name', 
cnt.cntct_lName as 'Contact Last Name', cnt.cntct_mainPhone as 'Main Phn Number',
cnt.cntct_Mobile as 'Mobile Number', cst.cust_mainEmail as 'Main Email', cnt.cntct_email as 'Contact Email',
adr.addressLine1 as 'Adress line 1',adr.addressLine2 as 'Address Line 2',
adr.City as 'City', adr.State as 'State', adr.pinZip as 'Pin/Zip Code'
 from data_customer as cst,data_customer_Address as adr,data_customer_contacts as cnt where
  cst.[cust_id]='2015Q4'
 and adr.[cust_id] ='2015Q4'
and
cnt.[cust_id]='2015Q4'

However, Cust_id will be passed into query dynamically. If I won't pass cust_id on any of the single place, I will get a Cartesian product. I tried other ways but could not short the query.

Kindly suggests is there any way I can improve the query or improve the performance?

Note*: I am using Sqlite on Windows.

Django-cms 1.7.10 "OperationalError - no such column" after migration

So I know there are already a ton of questions by people who changed a model and then failed to apply the migration to their database. However, in my case, I know for a fact that the migration was applied, as I can see the new table data.

Basically, I installed django-cms, and then I added a field to the djangocms_column plugin's models.py to allow me to add a Bootstrap class name to my columns (e.g. col-md-4, col-md-6, etc.).

if hasattr(settings, "COLUMN_CLASS_CHOICES"):
    CLASS_CHOICES = settings.COLUMN_CLASS_CHOICES
else:
    CLASS_CHOICES = (
        ('col-md-1', _("col-md-1")),
        ('col-md-2', _("col-md-2")),
        ('col-md-3', _('col-md-3')),
        ('col-md-4', _("col-md-4")),
        ('col-md-5', _('col-md-5')),
        ('col-md-6', _("col-md-6")),
        ('col-md-7', _('col-md-7')),
        ('col-md-8', _('col-md-8')),
        ('col-md-9', _('col-md-9')),
        ('col-md-10', _('col-md-10')),
        ('col-md-11', _('col-md-11')),
        ('col-md-12', _('col-md-12')),
        ('', _('none')),
    )

...

@python_2_unicode_compatible
class Column(CMSPlugin):
    """
    A Column for the MultiColumns Plugin
    """

    width = models.CharField(_("width"), choices=WIDTH_CHOICES, default=WIDTH_CHOICES[0][0], max_length=50)

    """
    This is the new field:
    """
    bs_class = models.CharField(_("bs_class"), choices=CLASS_CHOICES, default=CLASS_CHOICES[0][0], max_length=50)

    def __str__(self):
        return u"%s" % self.get_width_display()

I then ran ./manage.py makemigrations and then ./manage.py migrate, and now the table looks like this:

sqlite> select * from djangocms_column_column;
cmsplugin_ptr_id  bs_class    width     
----------------  ----------  ----------
3                 col-md-1    33%       
5                 col-md-1    33%       
7                 col-md-1    33%       
19                col-md-1    33%       
21                col-md-1    33%       
23                col-md-1    33% 

Yet when I try to access the test server, I still get the following error:

OperationalError at /en/
no such column: djangocms_column_column.bs_class
Request Method: GET
Request URL:    http://localhost:8000/en/
Django Version: 1.7.10
Exception Type: OperationalError
Exception Value:    
no such column: djangocms_column_column.bs_class

And, yes, I've tried deleting the database and running ./manage.py migrate, but the site still displays the same error. Is there a special migration procedure one must use to modify plugins installed in the ./env/lib/python2.7/site-packages folder?

Android - SQLiteException: near "=": syntax error (code 1)

I am trying to log id from users table and jobname from jobs table using user id

public String[] getUserJob() {

        String selectQuery = "SELECT id FROM " + TABLE_USERS;
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.rawQuery(selectQuery, null);
                 String[] data = null;

        if (cursor.moveToFirst()) {
            do {

                int myid = cursor.getInt(cursor.getColumnIndex("id"));
                Log.d("TAG","myid:-"+myid);   

                String select = "SELECT jobname FROM " + TABLE_JOBS+ "where userid =" +myid;

                Cursor c = db.rawQuery(select, null);
                String[] d = null;

                if (cursor.moveToFirst()) {
                    do {

                        String jname = c.getString(cursor.getColumnIndex("jobname"));                       
                        Log.d("TAG","jobname:-"+jname);

                    } while (c.moveToNext());
                }       

                return d;

            } while (cursor.moveToNext());
        }

        db.close();
        return data;

    }

May I know, where i am doing mistake and to understand what exactly i am trying to do, have a look at this link

How to display listview by using SQLiteDatabase Android

I have been learning SQLiteDatabase b y refering to thenewboston tuts. I want to modify somethings in my app.

Here, when I add data it covers up the screen and doesn't look oraganied. I would like to add a listview and store these items in the listview only.

Looks like this, I don't like it

So I would like to represent it like this

enter image description here

All my items should go in that listview item box only and i don't what all variable i should tell adapter to fetch from my db file or product file.

Any help would be appreciated ! thanks !

here is my code [MainActivity]:

EditText buckysInput;
TextView buckysText;
MyDBHandler dbHandler;


@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    buckysInput = (EditText) findViewById(R.id.buckysInput);
    buckysText = (TextView) findViewById(R.id.buckysText);
    dbHandler = new MyDBHandler(this, null, null, 1);
    printDatabase();
}

//Add a product to the database

public void addButtonClicked(View view){
    Product product = new Product(buckysInput.getText().toString());
    dbHandler.addProduct(product);
    printDatabase();
}

//Delete items

public void deleteButtonClicked(View view)
{
    String inputText = buckysInput.getText().toString();
    dbHandler.deleteProduct(inputText);
    printDatabase();
}

public void formatButtonClicked(View view)

{

    dbHandler.formatDatabase();
    buckysText.setText("");


    Toast.makeText(getApplicationContext(),"deleted successfully", Toast.LENGTH_SHORT).show();



}

//Print the database

public void printDatabase()

{
    String dbString = dbHandler.databaseToString();
    buckysText.setText(dbString);
    buckysInput.setText("");
}

}

MyDBHandler

 private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "product1DB.db";
public static final String TABLE_PRODUCTS = "products";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_PRODUCTNAME = "productname";

//We need to pass database information along to superclass
public MyDBHandler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
    super(context, DATABASE_NAME, factory, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    String query = "CREATE TABLE " + TABLE_PRODUCTS + "(" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_PRODUCTNAME + " TEXT " +
            ");";
    db.execSQL(query);
}

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

//Add a new row to the database
public void addProduct(Product product){
    ContentValues values = new ContentValues();
    values.put(COLUMN_PRODUCTNAME, product.get_productname());
    SQLiteDatabase db = getWritableDatabase();
    db.insert(TABLE_PRODUCTS, null, values);
    db.close();
}

//Delete a product from the database
public void deleteProduct(String productName){
    SQLiteDatabase db = getWritableDatabase();
    db.execSQL("DELETE FROM " + TABLE_PRODUCTS + " WHERE " + COLUMN_PRODUCTNAME + "=\"" + productName + "\";");
}



public String databaseToString(){
    String dbString = "";
    SQLiteDatabase db = getWritableDatabase();
    String query = "SELECT * FROM " + TABLE_PRODUCTS + " WHERE 1";

    //Cursor points to a location in your results
    Cursor c = db.rawQuery(query, null);
    //Move to the first row in your results
    c.moveToFirst();

    //Position after the last row means the end of the results
    while (!c.isAfterLast()) {
        if (c.getString(c.getColumnIndex("productname")) != null) {
            dbString += c.getString(c.getColumnIndex("productname"));
            dbString += "\n";
        }
        c.moveToNext();






    }
    db.close();
    return dbString;
}


//Delete the whole database HAHAHA !!
public void formatDatabase()

{
    SQLiteDatabase db = getWritableDatabase();
    db.execSQL("delete from " + TABLE_PRODUCTS);


}

}

Product.java

private int _id;
private String _productname;

public Product(){
}

public Product(String productname){
    this._productname = productname;
}

public void set_id(int _id) {
    this._id = _id;
}

public void set_productname(String _productname) {
    this._productname = _productname;
}

public int get_id() {
    return _id;
}

public String get_productname() {
    return _productname;
}

}

Show Data from Multiple Tables with connected id in SQLite

I have two tables in my SQLite database, first users and second jobs, where a user can have multiple jobs to do

Data in users table

id      name
1        Me
2       You

where id generated through : id integer primary key autoincrement

Data in jobs table

id     jobname     userid
1       JobA          2
2       JobB          2
3       JobC          1

where id generated through : id integer primary key autoincrement

where user_id fetched from users table

Now, I would like to loop through all the users and jobs table one by one, if user has some job to do then need to show Toast

JobC assigned to user1

DatabaseHandler.java:

public class DatabaseHandler extends SQLiteOpenHelper {

..................

public DatabaseHandler(Context context) {

   super(context, DATABASE_NAME, null, DATABASE_VERSION);

}

@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub

    db.execSQL("CREATE TABLE " + TABLE_USERS + 
              "(id integer primary key autoincrement," +
              " name text);");

        db.execSQL("CREATE TABLE " + TABLE_JOBS + 
              "(id integer primary key autoincrement," +
              " jobname text" +
              " userid long);");        
}

// Insert data into users table
public long InsertUsers(String name) {

     try {
        SQLiteDatabase db;
        db = this.getWritableDatabase(); // Write Data

        ContentValues Val = new ContentValues();
        Val.put("name", name); 

        long rows = db.insert(TABLE_USERS, null, Val);

        db.close();
        return rows; // return rows inserted.

     } catch (Exception e) {
        return -1;
     }
}

    // Insert data into jobs table
public long InsertJobs(String jobname, long userid) {

     try {
        SQLiteDatabase db;
        db = this.getWritableDatabase(); // Write Data

        ContentValues Val = new ContentValues();
        Val.put("jobname", jobname); 
        Val.put("userid", userid); 

        long rows = db.insert(TABLE_JOBS, null, Val);

        db.close();
        return rows; // return rows inserted.

     } catch (Exception e) {
        return -1;
     }
}   

public String[] getUsersId() {

    String selectQuery = "SELECT id FROM " + TABLE_USERS;
          SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.rawQuery(selectQuery, null);
             String[] data = null;

    if (cursor.moveToFirst()) {
        do {

            int id_row=cursor.getInt(cursor.getColumnIndex("id"));
                Log.d("TAG","id is ::"+id_row);             

        } while (cursor.moveToNext());
    }

    db.close();
    return data;
}

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

}

And in MainActivity.java:

    DatabaseHandler dh;

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

            dh = new DatabaseHandler(MainActivity.this);

            buttonGetData.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                dh.getUsersId();
                }
            });

   }

Login user with SQLite Database table in Android

i am new in android programming. i am doing a practical with a simple android signup and signin program but i have a difficalty in login with my inserted data in table please help. here is my my code

databasehelper code:

public void getuser(String user){
        SQLiteDatabase db = this.getReadableDatabase();
        String query = "select user, password from "+ DATABASE_TABLE;
        Cursor cu = db.rawQuery(query, null);
        String a,b;
        b = "not found";
        if(cu.moveToFirst())
        {
            do{
                a = cu.getString(0);
                if(a.equals(user))
                {
                    cu.getString(1);
                    break;
                }

            }
        while(cu.moveToNext()); 
        }

and my SignIn. java file

user = (EditText) findViewById(R.id.tfuser);
        pass = (EditText) findViewById(R.id.tfpassword);
        signin = (Button) findViewById(R.id.btnsignin);
        signup = (Button) findViewById(R.id.btnsignup);

        final String suser = user.getText().toString();
        String spass = pass.getText().toString();
        //button
        signin.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                db.getuser(suser);

                if(what to do here){
                Intent welcome = new Intent(SingIn.this, WelcomePage.class);
                startActivity(welcome);}
                else{}


            }
        });

Cursor to log id column value in activity

How do I loop to get id column values from users table, where "id" generated through :

id integer primary key autoincrement

I have records like this in my table:

id     name

1       Me

2      You

Here is what i think i should try:

public String[] getUsersId() {

    String selectQuery = "SELECT id FROM " + TABLE_USERS;
          SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.rawQuery(selectQuery, null);
             String[] data = null;

    if (cursor.moveToFirst()) {
        do {

        } while (cursor.moveToNext());
    }

    db.close();
    return data;
}

Now, I would like to know how can I log all the "id" in my activity ?

vendredi 30 octobre 2015

Error when trying to get a single record form SQLite (Ionic/Angularjs/SQLite)

I am trying to display a single record from an SQLite. But got an error from console:

"Error: Failed to execute 'item' on 'SQLResultSetRowList': The index provided (0) is greater than or equal to the maximum bound (0). at Error (native) at Object.self.getById"

The caller

$scope.DisplayMember = function(RegId) {
      Team.get(RegId).then(function(data){  $scope.Singledata= data;});
    };

Called this:

self.get = function(memberId) {
      var parameters = [memberId];
      return DBA.query("SELECT id, name FROM team WHERE id = (?)", parameters)
        .then(function(result) {
          return DBA.getById(result);
        });
    }

And lastly this:

// Proces a single result
    self.getById = function(result) {
      var output = null;
      output = angular.copy(result.rows.item(0));
      return output;
    }

Thanks for your kind help

which database is suitable for manage over 10 lakhs records in xcode

i'm developing the app for iPhone and iPad in which i have to manage over 10 lakhs records.I want to know which database is suitable to use which takes less memory. Sql lite or coredata ?

How to browse sqlite data in xamarin

I have a list view in my app , the data is saving to the database before populating list view . my problem is , i can't see the data in db and how ho verify the data is stored in db

How to provide user defined function for python blaze with sqlite backend?

I connect to sqlite database in Blaze using df = bz.Data("sqlite:///<mydatabase>) everything works fine but I do not know how to provide user-defined functions in my interaction with df. I have a column called IP in df which is text containing IP addresses. I also have a function toSubnet (x, y) which takes in an IP address (x) in text format and return its /y subnet. For example:

out = toSubnet('1.1.1.1',24)
out
1.1.1.0/24

Now if I want to map all IPs to their /14 subnets, I use:

df.IP.map(lambda x:toSubnet(x,14),'string')

Which works when the backend is CSV. But with sqlite backend I get NotImplementedError. What's wrong here?

A rudimentry way to store comments on a proposal webpage using sqlite

I am a software engineer but very new to database and I am trying to hack up a tool to show some demo. I have an apache server which serves a simple web page full of tables. Each Row in the table has a proposal id and a link to a web page where the proposal is explained. So just two columns.

----------------------
|  id    |  proposal |
|--------------------
|  1     |  foo.html |
|  2     |  bar.html |
----------------------

Now, I want to add a third column title Comments where a user can leave comments.

------------------------------------------------
|  id    |  proposal |  Comments               |
|-----------------------------------------------
|  1     |  foo.html | x: great idea !         |
|        |           | y: +1                   |
|  2     |  bar.html | z: not for this release |
------------------------------------------------

I just want to quickly hack up something to show this as a demo and get feedback. I am planning to use sqlite to create a table per id and store the userid, comments in the table. People can add comment at the same time. I am planning to use lock to perform operation on the sqlite database. I am not worried about scaling just want to show and get feedback. Do you guys see any major flaw in this implementation ? There are similar questions. But I am looking for a simplest possible implementation

SQLite and STRFTIME

in my SQLite database, I have a time field with minutes, seconds, and centiseconds:

 00:01:100

I want to select the row with the MAX value in milliseconds and I fount the strftime function. I'm trying with:

  SELECT MAX(strftime("%M:%S:%SSS", field)) FROM table;

But it doesn't works.

Syncing with an app sqlite3 db

Noob alert : I'm a beginner, sorry if my question is dumb

I would like to retrieve data from an sqlite3 db used and updated by a messaging app (on windows). I thought about using PHP to retrieve the info. However I cannot move the database on a server since it is used in the install folder of the app. How can I sync this sqlite3 database with a newly created sql database on a server? Or maybe (even better), is there a way to directly communicate locally with the app data base?

How to print a sqlite database content with genie programming language

Based on previous questions here I managed to create the dataset, print all recipes listed and now I am trying to pick one of the recipes from that list and show its Title, Instructions and Ingredients. The instructions are mapped to the Recipes via the pkID column and the ingredients are mapped to the Recipes through a recipeID column.

I am not being able to "filter" by pkID and by recipeID, so that after picking one recipe, only the appropriate content is shown.

Below is the entire code so far, my concerns are in the if statement response 3 "Show a recipe" and with the function PrintSingleRecipe.

// Trying to do a cookbook program
// raw_imput for Genie included, compile with valac --pkg sqlite3 --pkg gee-0.8 cookbook.gs

[indent=4]
uses Sqlite

init
    db:Database
    if (Sqlite.Database.open ("cookbook.db3", out db) != Sqlite.OK)
        stderr.printf ("Error: %d: %s \n", db.errcode (), db.errmsg ())
        Process.exit (-1)

    while true
        response:string = UserInterface.get_input_from_menu()
        stmt:Statement = PreparedStatements.select_all( db )
        if response == "1" // Show All Recipes
            PrintAllRecipes(db)
        else if response is "2" // Search for a recipe
            pass
        else if response is "3" //Show a Recipe
            res:int = UserInterface.raw_input("Select a recipe -> ").to_int()
            rows:int = 0
            while stmt.step() == ROW
                rows++
            if res < rows
                PrintSingleRecipe(res,db)
            else if res == rows
                print "Back to menu ->"
            else
                print "Unrecognized command"
        else if response is "4"//Delete a recipe
            pass
        else if response is "5" //Add a recipe
            pass
        else if response is "6" //Print a recipe
            pass
        else if response is "0" //Exit
            print "Goodbye"
            break
        else
            print "Unrecognized command. Try again."

def PrintAllRecipes ( db:Database )
    print "%-5s%-30s%-20s%-30s", "Item", "Name", "Serves", "Source"
    print "--------------------------------------------------------------------------------------"
    stmt:Statement = PreparedStatements.select_all( db )
    cols:int = stmt.column_count ()
    var row = new dict of string, string
    item:int = 1
    while stmt.step() == ROW
        for i:int = 0 to (cols - 1)
            row[ stmt.column_name( i ) ] = stmt.column_text( i )
        stdout.printf( "%-5s", item.to_string( "%03i" ))
        stdout.printf( "%-30s", row[ "name" ])
        stdout.printf( "%-20s", row[ "serves" ])
        stdout.printf( "%-30s\n", row[ "source" ])
        item++
    print "Total recipes %s", (item -1).to_string()
    print "--------------------------------------------------------------------------------------"
    UserInterface.raw_input("Press ENTER ->")

def PrintSingleRecipe(which:int, db:Database)
    stmt:Statement = PreparedStatements.select_all( db )
    res:int = UserInterface.raw_input("Select a recipe -> ").to_int()
    cols:int = stmt.column_count ()
    var row = new dict of string, string
    item:int = 1
    print "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
    while res == ROW
        for i:int = 0 to (cols - 1)
            row[ stmt.column_name( i ) ] = stmt.column_text( i )
        stdout.printf( "%-5s", item.to_string( "%03i" ))
        stdout.printf( "%-30s", row[ "Title" ])
        stdout.printf( "%-20s", row[ "Serves" ])
        stdout.printf( "%-30s\n", row[ "Source" ])
    print "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
    print "Ingredient list"
    print " "
    stdout.printf("%-5s", item.to_string( "%03i" ))


namespace UserInterface
    def get_input_from_menu():string
        show_menu()
        return raw_input("Enter a selection -> ")
    def raw_input (query:string = ""):string
        stdout.printf ("%s", query)
        return stdin.read_line ()
    def show_menu()
         print """===================================================
                 RECIPE DATABASE
 1 - Show All Recipes
 2 - Search for a recipe
 3 - Show a Recipe
 4 - Delete a recipe
 5 - Add a recipe
 6 - Print a recipe
 0 - Exit
==================================================="""


namespace PreparedStatements
    def select_all( db:Database ):Statement
        statement:Statement
        db.prepare_v2( """
select name, servings as serves, source from Recipes
""", -1, out statement )
        return statement

And here is the code that I am trying to mimic in python:

  def PrintSingleRecipe(self,which):
    sql = 'SELECT * FROM Recipes WHERE pkID = %s' % str(which)
    print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
    for x in cursor.execute(sql):
      recipeid =x[0]
      print "Title: " + x[1]
      print "Serves: " + x[2]
      print "Source: " + x[3]
    print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
    sql = 'SELECT * FROM Ingredients WHERE RecipeID = %s' % recipeid
    print 'Ingredient List:'
    for x in cursor.execute(sql):
        print x[1]
    print ''
    print 'Instructions:'
    sql = 'SELECT * FROM Instructions WHERE RecipeID = %s' % recipeid
    for x in cursor.execute(sql):
      print x[1]
    print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
    resp = raw_input('Press A Key -> ')

Only get 1st element of TABLE in SQLite

I have this code:

public LinkedList<Product> getAllProduct(String idtask){
    LinkedList<Product> products = new LinkedList<Product>();
    Cursor localCursor = this.getWritableDatabase().rawQuery("SELECT * FROM "+TABLE_PRODUCT+" WHERE "+KEY_ID_PRODUCT_TASK+"='"+idtask+"'", null);
    Product product;
    if (localCursor.moveToFirst())
        do {
            product = new Product();
            product.setKEY_ID_UNIQUE_PRODUCT(localCursor.getString(0));
            product.setKEY_ID_PRODUCT_TASK(localCursor.getString(1));
            product.setKEY_ID_PRODUCT(localCursor.getString(2));
            product.setKEY_NAME_PRODUCT(localCursor.getString(3));
            product.setKEY_QTY_PRODUCT(localCursor.getString(4));
            product.setKEY_SIZE_PRODUCT(localCursor.getString(5));
            products.add(product);
        } while (localCursor.moveToNext());
    return products;
}

I only get 1 row, when with a particular IdTasks, I have to get 2 elements. Please Help me, I don't Know whats the problem. Because, when I debugging I add 2 products with this IdTask in the Table product, but when I try to get these products with this IdTask, I only get the first.

Android Basic Quiz app

I want to make a quiz app but I dont know android.I need support how to make or need examples . App like that

Lesson Name

Question

a- random

b- random

c- random

d- random

e- random

5 answer only 1 correct and 10 wrong answer I can store this data in sql

id #lesson #question #canswer #w1ans... #w10ans #question point

correct answer randomly put abcde and select 4 wrong answer other abcde wrong answers return 0 and correct 1

I try this but cant work http://ift.tt/1e3HL0t

Windows Phone 8.1 Sqlite select Query result error

I am using Windows Universal app project. I want to compare the result return by the select statement but giving me a error.

private async void btnLogin_Click(object sender, RoutedEventArgs e)
    {
        string input = txtMobNo.Text;
        SQLiteAsyncConnection connection = new SQLiteAsyncConnection("People.db");
        var queryVar = connection.Table<User>().Where(x => x.userMbNo.StartsWith(input));

        var queryResult = await queryVar.ToListAsync();


        if (queryResult==0)
        {
            string msg = "Login Failed : Invalid ID or Password";
            MessageDialog dialog = new MessageDialog(msg);
            await dialog.ShowAsync();
        }
        else
        {
            string msg = "User Found";
            MessageDialog dialog = new MessageDialog(msg);
            await dialog.ShowAsync();
        }

    }

Result return by queryresult is in List type how can I compare the results. I want to find the user exist in database or not.

[Table("Users")]
public sealed class User
{
    [PrimaryKey]
    public string userMbNo { get; set; }
    public string userName { get; set; }
    public string userPassword { get; set; }

    public User()
    {

    }

    public User(string usrMbNo,string usrName, string usrPwd)
    {
        userMbNo = usrMbNo;
        userName = usrName;
        userPassword = usrPwd;
    }

android.database.sqlite.SQLiteException: no such column: coin (code 1): , while compiling: SELECT attrib FROM table

i am using Sqlite database android and i got this this problem Caused by: android.database.sqlite.SQLiteException: no such column: coin (code 1): , while compiling: SELECT _id, name, age, coin, log FROM person_tb1 WHERE name = ?

i have no idea why it show me this !

i have tried it before its working fine !

here is the code where it says the problem :

public ArrayList<Person> getPersons (){

    persons.clear();

    SQLiteDatabase dba = this.getReadableDatabase();

    Cursor cursor = dba.query(Constants.TABLE_NAME,
            new String[]{Constants.KEY_ID, Constants.NAME, Constants.AGE , Constants.COIN , Constants.LOG},null,null,null,null,null);

    if(cursor.moveToFirst()){
        do {
            Person p = new Person();
            p.setName(cursor.getString(cursor.getColumnIndex(Constants.NAME)));
            p.setAge(cursor.getInt(cursor.getColumnIndex(Constants.AGE)));
            p.setCoin(cursor.getInt(cursor.getColumnIndex(Constants.COIN)));
            p.setLog(cursor.getInt(cursor.getColumnIndex(Constants.LOG)));
            p.setPersonId(cursor.getInt(cursor.getColumnIndex(Constants.KEY_ID)));

            persons.add(p);

        }while (cursor.moveToNext());

        cursor.close();
        dba.close();
    }
    return persons;
}

and here the select method :

 public void onCreate(SQLiteDatabase db) {
    String CREATE_TABLE = "CREATE TABLE " + Constants.TABLE_NAME + "(" +
            Constants.KEY_ID + " INTEGER PRIMARY KEY, " + Constants.NAME + " TEXT, "+
            Constants.AGE + " INT, " + Constants.COIN + " INT, " + Constants.LOG + " INT);";

    db.execSQL(CREATE_TABLE);
}

SQLAlchemy: Use a function to determine what results a query returns

I want to use a function to determine the which rows are selected in a SQLAlchemy query. Here's the setup...

We have a basic SQLAlchemy mapped object:

class Foo(base):
    foo_id = Column(Integer, primary_key=True)
    foo_data = Column(String(255))

Now if we want to query all the table rows with a specific value for foo_data, we could do something like:

session.query(Foo).filter(Foo.foo_data == 'some data').all()

Now, I want the Foo.foo_data == 'some data' check to take place in a function... so our query looks like:

session.query(Foo).filter(check_foo(Foo.foo_data)).all()

Where check_foo looks like:

def check_foo(foo_data):
    if foo_data == 'foo':
        return True
    else:
        return False

Is this type of thing possible? Obviously for this trivial case, it is really unnecessary, but for a more complex case this would be really useful.

Android Sqlite db.insert with null

The problem is when I go to insert to the database sqlite, because when i return the data of all "persons", only insert the id_persons and all of others fields are null :(.

This is my DatabaseHelper.java:

public class DatabaseHelper extends SQLiteOpenHelper {

// Table Names
    private static final String TABLE_PERSONS = "persons";
// PERSON Table column names
    private static final String KEY_ID_PERSON = "id_person";
    private static final String KEY_FIRST_NAME = "first_name";
    private static final String KEY_LAST_NAME = "last_name";
    private static final String KEY_PHONE1 = "phone1";
    private static final String KEY_PHONE2 = "phone2";
    private static final String KEY_GENRE = "genre";
    private static final String KEY_LEVEL = "level";
    private static final String KEY_EMAIL = "email";

// Persons table create statement
    private static final String CREATE_TABLE_PERSONS = "CREATE TABLE "
            + TABLE_PERSONS + "(" 
            + KEY_ID_PERSON + " INTEGER PRIMARY KEY,"
            + KEY_FIRST_NAME + " TEXT NOT NULL,"
            + KEY_LAST_NAME  + " TEXT NOT NULL,"
            + KEY_PHONE1 + " TEXT NOT NULL,"
            + KEY_PHONE2  + " TEXT NOT NULL,"
            + KEY_GENRE + " INTEGER NOT NULL,"
            + KEY_LEVEL  + " INTEGER NOT NULL,"
            + KEY_EMAIL + " TEXT NOT NULL" + ")";

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

    @Override
    public void onCreate(SQLiteDatabase db) {

        if (!db.isReadOnly()) {
            // Enable foreign key constraints
            db.execSQL("PRAGMA foreign_keys=ON;");
        }
        // creating required tables
        db.execSQL(CREATE_TABLE_PERSONS);
    }

    public void createPerson(Person person, long[] persons_ids) {

            SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_ID_PERSON, person.getId_person());
        values.put(KEY_FIRST_NAME, person.getFirst_name());
        values.put(KEY_LAST_NAME, person.getLast_name());
        values.put(KEY_PHONE1, person.getPhone1());
        values.put(KEY_PHONE2, person.getPhone2());
        values.put(KEY_GENRE, person.getGenre());
        values.put(KEY_LEVEL, person.getLevel());
        values.put(KEY_EMAIL, person.getEmail());

// insert row
        long person_id = db.insert(TABLE_PERSONS, null, values);

        }

    public ArrayList<Person> getAllPersons() {
        ArrayList<Person> persons = new ArrayList<Person>();
        String selectQuery = "SELECT  * FROM " + TABLE_PERSONS;

        Log.e(LOG, selectQuery);

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (c.moveToFirst()) {
            do {
                Person p = new Person();
                p.setId_person(c.getInt((c.getColumnIndex(KEY_ID_PERSON))));

                // adding to list
                persons.add(p);
            } while (c.moveToNext());
        }

        return persons;
    }

    }

Person()

public class Person {

    private int id_person;
    private String first_name;
    private String last_name;
    private String phone1;
    private String phone2;
    private int genre;
    private int level;
    private String email;

    public Person(){


    }

    public Person(int id_person, String first_name, String last_name,
            String phone1, String phone2, int genre, int level, String email) {
        super();
        this.id_person = id_person;
        this.first_name = first_name;
        this.last_name = last_name;
        this.phone1 = phone1;
        this.phone2 = phone2;
        this.genre = genre;
        this.level = level;
        this.email = email;
    }

    public int getId_person() {
        return id_person;
    }

    public void setId_person(int id_person) {
        this.id_person = id_person;
    }

    public String getFirst_name() {
        return first_name;
    }

    public void setFirst_name(String first_name) {
        this.first_name = first_name;
    }

    public String getLast_name() {
        return last_name;
    }

    public void setLast_name(String last_name) {
        this.last_name = last_name;
    }

    public String getPhone1() {
        return phone1;
    }

    public void setPhone1(String phone1) {
        this.phone1 = phone1;
    }

    public String getPhone2() {
        return phone2;
    }

    public void setPhone2(String phone2) {
        this.phone2 = phone2;
    }

    public int getGenre() {
        return genre;
    }

    public void setGenre(int genre) {
        this.genre = genre;
    }

    public int getLevel() {
        return level;
    }

    public void setLevel(int level) {
        this.level = level;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

Only insert id_person field, others fields are null.

Doubts about the SQLite interface in Android

Ok, so I just learnt about the SQLite API in android. All the tutorials talk about creating a helper class which extends the SQLIteOpenHelper. There is a constructor which as I understood creates the database and there is a onCreate callback which is used to create all the tables the first time the database is created. Till here, OK.

My doubts are: Suppose, I create a database from one activity/class. My helper class is called DatabaseHelper. So, if I want to access the database from that creator class itself, then no problem. I just use the instance of DatabaseHelper which I would have created and then use that to access and work on the database.

But, how will I work on the same database from some other activity which needs to access the database. My doubt here is that since in this class I would have to create a new DatabaseHelper object, won't that go through the super constructor call and the onCreate callback again. And won't that be a problem? What effects will it have on the database.

I am basically not able to understand how can I create a database once in my app and then just it anywhere from the app.

query on system.data.sqlite not working

I have a sqlite database with table called File, that has a column called FilePath, of type Text.

On that table there is an entry whose FilePath has the value f9a35e24-bce9-46c8-bbc0-02a005455fe3(the toString of a random GUID).

If I try the following query on SQLiteStudio, it outputs the entry.

SELECT FilePath FROM File WHERE FilePath = 'f9a35e24-bce9-46c8-bbc0-02a005455fe3'

However, using the code below, that uses the System.Data.SQLite external assembly, it never retrieves the entry.

SQLiteConnection conn = new SQLiteConnection(connectionString);

SQLiteCommand cmd = 
    new SQLiteCommand(@"SELECT FilePath FROM File WHERE FilePath = '@Where';", conn);

cmd.Parameters.Add("@Where", DbType.String).Value = 
    "f9a35e24-bce9-46c8-bbc0-02a005455fe3";

conn.Open();

SQLiteDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
    Console.WriteLine("yes");
}
else
{
    Console.WriteLine("not");
}

Am I missing something?

Where is Mono.Data.SqliteClient source code?

I'm going through some problems with SQLite on Linux and I'd like to inspect Mono.Data.Sqlite source code, but it seems Mono's page on SQLite is outdated and does not point to Mono.Data.Sqlite code repository.

I've done my homework searching it at Google but with no success.

Am I missing something?

Can't INSERT into Sqlite3 database

I'm very desperate because I tried everything I could to make it work but no success... I've created a pool which is processing some text lines and then inserting them into the database. The problem is that it does not insert into the table nothing.

I know that it is probably because multiple workers trying to modify the database but I've already tried many things to avoid that.

For example this (this is the method for inserting url into the index table):

def insert_into_index(self, url):
    self.lock.acquire()
    self.cur.execute('INSERT INTO index VALUES(?)', (url,))
    self.conn.commit()
    self.lock.release()

Or this (each new use of the method has it's own cursor):

def insert_into_index(self, url):
    cur = self.conn.cursor()
    cur.execute('INSERT INTO index VALUES(?)', (url,))
    self.conn.commit()

The snippet where I create the Pool:

 """ CREATE A POOL """

    pool = Pool(50)
    for category in categories[1:2]:
        task = pool.apply_async(index, args=(category.strip('\n'),))
        task.get()

    pool.close()
    pool.join()

    """ POOL END """

Index() is a method which creates an instance of class leaf(text), which do some things with the text, obtains list of strings and try to insert them into the database.

class leaf():
    def __init__(self, url):
        self.url = url
        self.results = self.get_all_hrefs(url)
        self.dbm = database_file.manager()

        for res in self.results:
              self.dbm.insert_into_index(res)

But I don't have nothing in table index. I've tried everything I knew about with no success. Could you give me an advice?

How can I package a DLL without managed code into my WPF app?

I've been using LibZ Container for awhile now to bundle DLL files together with my executables, resulting in only needing to copy a single executable file instead of installing a bunch of DLLs. (For those not familiar LibZ Container is an alternative to IL Merge, only that it works with WPF.)

Normally this works great, but today I learned that it doesn't work so great with the SQLite library. In particular, there's one file that fails to package. This is the error message that I get:

Loading '.\SQLite.Interop.dll'

Failed to load assembly from '.\SQLite.Interop.dll'

Assembly '.\SQLite.Interop.dll' could not be loaded

Looking on the SQLite FAQ page I see they state this:

Do I need to add an assembly reference to the "sqlite3.dll" or "SQLite.Interop.dll" in my project?

No, because they are not managed assemblies and contain no managed code.

I must confess I'm not really sure what that means... until now this stuff has mostly been a black box to me that just sort of works. Is there any way to bundle the SQLite.Interop.dll file with the assembly like I'm hoping? Can anyone offer an explanation of why LibZ Container is choking on this DLL file in particular?

What will happen if i'll store travalled distance onLocationChanged to my sqlite upto my tracking going on?

I'm developing a application for vehicle tracking and sending distance, current latitude and longitude on server after every 30 seconds.

My question is i want to store travalled distance, latitude and longitude in sqlite upto my vehicle going on and after completing my tracking then wanted to send all values from sqlite to server. If i'll store all value is these values will hang my application.

SQLITE NEW TABLE android

I'm trying to add new tables to my database.
I changed the db version, but it's not working - still have the same tables

Heres is SQLITEOPENGELPER class

public class DB extends SQLiteOpenHelper {

public final static String DBNAME="MEDCINEDB.db";
public final static String Table_name="MEDCINETable";
public final static String Table_name2="PPLTABLE";
public final static String col1="MEDCINEID";
public final static String col2="MEDCINENAME";
public final static String col3="MEDCINEPURPOSE";
public final static String col4="NOTAPLET";
public final static String col1T2="ID";
public final static String col2T2="NAMEPPL";
public final static String col3T2="AGEPPL";
public final static int DBVersion =2;


public DB(Context context,String DBNAME, int DBVersion) {
    super(context, DBNAME, null, DBVersion);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE " + Table_name + "(MEDCINEID INTEGER PRIMARY KEY AUTOINCREMENT,MEDCINENAME TEXT,MEDCINEPURPOSE TEXT,NOTAPLET INTEGER)");
    db.execSQL("CREATE TABLE " + Table_name2 + "(ID INTEGER PRIMARY KEY AUTOINCREMENT,NAMEPPL TEXT,AGEPPL INTEGER)");
}

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

}}

Sqlite3 error when importing from upper directory

I have a couple of directories with *.py files. One of those directories contains database manager class. This class contains method for removing rows from Sqlite3 database which is situated in the same directory.

When I create a database manager class instance and call db_instance.remove_from_categories() (it removes all rows from table categories), everything works.

The problem is, when I create the instance of the same class in main.py which is in upper directory, it raises

    self.cur.execute('DELETE FROM categories')
sqlite3.OperationalError: no such table: categories

enter image description here

Do you know where is the problem?

Andoid Sqlite Table not created even create table query executed successfully

In my project My create table query executed successfully when i debug with break points.. but when i saw in File explorer->mnt->sdcard->android->Mydbname->mydbname.db no tables are created there.. I also checked in data->data->package name->.... I created db using sqlite admin.

// Database name definitions
    public static final String DATABASE_NAME = "MMPDB.db";

    // Table name definitions
    public static final String LOG_IN_TABLE = "LogIn";
    public static final String FI_GOODS_DETAIL_TABLE = "fiGoodDetail";
    public static final String FI_GOODS_SUMMARY_TABLE = "fiGoodSummary";
    public static final String ITEM_DETAIL_TABLE = "itemDetail";
    public static final String SETTINGS_TABLE = "Setting";

    // LogIn table fields declaration
    public static final String LOGIN_USERNAME = "userName";
    public static final String LOGIN_PASSWORD = "password";

    // fiGoodDetail table fields declaration
    public static final String FIGOODSDETAIL_FINISHGOODS = "fiGoods";
    public static final String FIGOODSDETAIL_ITEMDESC = "itemDesc";
    public static final String FIGOODSDETAIL_FINISHQTY = "fiQty";
    public static final String FIGOODSDETAIL_FINISHUNITWEIGHT = "fiUnitWeight";
    public static final String FIGOODSDETAIL_FINISHTOTALWEIGHT = "fiTotalWeight";
    public static final String FIGOODSDETAIL_LOTBINNUM = "lotBinNum";

    // fiGoodSummary table fields declaration
    public static final String FIGOODSSUMMARY_FINISHGOODS = "fiGoods";
    public static final String FIGOODSSUMMARY_FINISHQTY = "fiQty";
    public static final String FIGOODSSUMMARY_FINISHUNITWEIGHT = "fiUnitWeight";
    public static final String FIGOODSSUMMARY_FINISHTOTALWEIGHT = "fiTotalWeight";
    public static final String FIGOODSSUMMARY_ITEMDESC = "itemDesc";
    public static final String FIGOODSSUMMARY_QTYUM = "qtyUm";
    public static final String FIGOODSSUMMARY_QTY = "qty";
    public static final String FIGOODSSUMMARY_YIELD = "yield";

    // itemDesc table fields declaration
    public static final String ITEMDETAIL_FINISHGOODS = "fiGoods";
    public static final String ITEMDETAIL_ITEMDESC = "itemDesc";
    public static final String ITEMDETAIL_LOCTID = "loctId";
    public static final String ITEMDETAIL_LOTBINNUM = "lotBinNum";
    public static final String ITEMDETAIL_QTYUM = "qtyUm";
    public static final String ITEMDETAIL_QTYUMUSED = "qtyUmUsed";
    public static final String ITEMDETAIL_YIELD = "yield";
    public static final String ITEMDETAIL_ACTUALQTY = "actQty";
    public static final String ITEMDETAIL_ACTUALSCARP = "actScrp";
    public static final String ITEMDETAIL_UNITCOST = "unitCost";
    public static final String ITEMDETAIL_TOTALCOST = "totalCost";

    // Setting table fields declaration
    public static final String SETTINGS_SERVERPATH = "serverPath";
//  public static final String SETTINGS_COMPANY="company";

    // Creation of Query for Each Table.
    // tlogin table creation query
    public static final String LOG_IN_TABLE_CREATE_QUERY = "CREATE TABLE "
            + LOG_IN_TABLE + "(" + LOGIN_USERNAME + " TEXT," 
            + LOGIN_PASSWORD + " TEXT);";

    // tfigodet table creation query
    public static final String FI_GOODS_DETAIL_TABLE_CREATE_QUERY = "CREATE TABLE "
            + FI_GOODS_DETAIL_TABLE + "(" + FIGOODSDETAIL_FINISHGOODS + " TEXT,"
            + FIGOODSDETAIL_ITEMDESC + " TEXT,"
            + FIGOODSDETAIL_FINISHQTY + " TEXT,"
            + FIGOODSDETAIL_FINISHUNITWEIGHT + " TEXT,"
            + FIGOODSDETAIL_FINISHTOTALWEIGHT + " TEXT,"
            + FIGOODSDETAIL_LOTBINNUM + " TEXT);";

    // tfigosum table creation query
    public static final String FI_GOODS_SUMMARY_TABLE_CREATE_QUERY = "CREATE TABLE "
            + FI_GOODS_SUMMARY_TABLE
            + "("
            + FIGOODSSUMMARY_FINISHGOODS
            + " TEXT,"
            + FIGOODSSUMMARY_FINISHQTY
            + " TEXT,"
            + FIGOODSSUMMARY_FINISHUNITWEIGHT
            + " TEXT,"
            + FIGOODSSUMMARY_FINISHTOTALWEIGHT
            + " TEXT,"
            + FIGOODSSUMMARY_ITEMDESC
            + " TEXT,"
            + FIGOODSSUMMARY_QTYUM
            + " TEXT,"
            + FIGOODSSUMMARY_QTY
            + " TEXT,"
            + FIGOODSSUMMARY_YIELD
            + " TEXT);";

    // titemdet table creation query
    public static final String ITEM_DETAIL_TABLE_CREATE_QUERY = "CREATE TABLE "
            + ITEM_DETAIL_TABLE + "(" + ITEMDETAIL_FINISHGOODS + " TEXT,"
            + ITEMDETAIL_ITEMDESC + " TEXT," + ITEMDETAIL_LOCTID + " TEXT,"
            + ITEMDETAIL_LOTBINNUM + " TEXT," + ITEMDETAIL_QTYUM + " TEXT,"
            + ITEMDETAIL_YIELD + " TEXT," + ITEMDETAIL_ACTUALQTY + " TEXT,"
            + ITEMDETAIL_QTYUMUSED + " TEXT," + ITEMDETAIL_ACTUALSCARP + " TEXT,"
            + ITEMDETAIL_UNITCOST + " TEXT," + ITEMDETAIL_TOTALCOST + " TEXT);";

    // tsettings table creation query
    public static final String SETTINGS_TABLE_CREATE_QUERY = "CREATE TABLE "
            + SETTINGS_TABLE + "(" + SETTINGS_SERVERPATH + " TEXT);";

    // Constructor to create Database
    public MmpDBHelper(Context context) {
        DATABASE_FILE_PATH = new File(Supporter.getAppCommonPath(),
                DATABASE_NAME);

        if (!DATABASE_FILE_PATH.exists()) {

            try {

                db = SQLiteDatabase.openOrCreateDatabase(DATABASE_FILE_PATH,
                        null);
                // openDatabase();
                db.beginTransaction();
                db.execSQL(LOG_IN_TABLE_CREATE_QUERY);
                db.execSQL(FI_GOODS_DETAIL_TABLE_CREATE_QUERY);
                db.execSQL(FI_GOODS_SUMMARY_TABLE_CREATE_QUERY);
                db.execSQL(ITEM_DETAIL_TABLE_CREATE_QUERY);
                db.execSQL(SETTINGS_TABLE_CREATE_QUERY);

                db.setTransactionSuccessful();
                Log.i("Success", "All tables created successfully");
            } catch (Exception e) {
                db.endTransaction();
                errCode = "Error 501";
                msg = "Table creation failed";
                errMsg = errCode + " : " + msg;
                LogFileCreator.appendLog(errMsg);
            } finally {
//              db.endTransaction();
                db.close();
            }
        }
    }

android database not open - SQLITE

I am trying to create password manager app that the user can manage her password in any websites.

The user need to fill 3 fields - Website, Username and Password. Behind the scenes, the data should be insert to the database in SQLite but I think that the database not open, because I cant see the database folder in path: /data/data/MY_APP/

this is MySQLiteHelper: http://ift.tt/1P1gF1s

package db_pkg;

import java.io.UTFDataFormatException;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;

public class MySQLiteHelper
{
    public static final String DB_NAME = "mu_dbname";
    public static final String TASKS_TABLE = "passwords";
    public static final int DB_VER = 1;

    public static final String TASK_ID = "_id";
    public static final String TASK_WEB = "web";
    public static final String TASK_USER = "user";
    public static final String TASK_PASSWORD = "password";
    // public static final String TASK_LAT = "lat";
    // public static final String TASK_LNG = "lng";
    private static final String SCRIPT_CREATE_DB = "create table "
            + TASKS_TABLE + "(" + BaseColumns._ID
            + " integer primary key autoincrement, " + TASK_WEB
            + " text not null, " + TASK_USER + " text not null, "
            + TASK_PASSWORD + " text not null" + ");";

    private Context context;
    private MyDBHelper myDBHelper;

    public MySQLiteHelper(Context context)
    {
        this.context = context;
        this.myDBHelper = new MyDBHelper(this.context);

    }

    public void addTaskItem(addPassword item)
    {
        SQLiteDatabase database = myDBHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(TASK_WEB, item.getWebsite());
        values.put(TASK_USER, item.getUsername());
        values.put(TASK_PASSWORD, item.getPassword());
        database.insert(TASKS_TABLE, null, values);
        database.close();
    }

    public boolean updateItemById(int taskID, addPassword item)
    {
        SQLiteDatabase database = myDBHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(TASK_WEB, item.getWebsite());
        values.put(TASK_USER, item.getUsername());
        values.put(TASK_PASSWORD, item.getPassword());
        boolean b = database.update(TASKS_TABLE, values,
                TASK_ID + "=" + taskID, null) > 0;
        database.close();
        return b;

    }

    public boolean deleteTaskItemById(int taskID)
    {
        SQLiteDatabase database = myDBHelper.getWritableDatabase();
        boolean b = database.delete(TASKS_TABLE, TASK_ID + "=" + taskID, null) > 0;
        return b;
    }

    public Cursor getCursorALL()
    {
        Cursor cursor;
        SQLiteDatabase database = myDBHelper.getReadableDatabase();
        cursor = database.query(TASKS_TABLE,
                new String[] { TASK_ID,TASK_WEB,TASK_USER,TASK_PASSWORD},
                null,null, null, null, null);
        return cursor;
    }
    //testing Debugging
    public void printAllCursorDB()
    {
        Cursor cursor=getCursorALL();

        cursor.moveToFirst();
        while(!cursor.isAfterLast())
        {
            StringBuffer st=new StringBuffer();
            st.append("ID:"+cursor.getString(0));
            st.append(" Text:"+cursor.getString(1));
            st.append(" Phone:"+cursor.getString(2));
            st.append(" Priority:"+cursor.getString(3));
            Log.d("MyTasksDBMngr", st.toString());

            cursor.moveToNext();

        }
        cursor.close();
    }

    private class MyDBHelper extends SQLiteOpenHelper
    {

        public MyDBHelper(Context context)
        {
            super(context, DB_NAME, null, DB_VER);
            Log.i("MyDBHelper", "Constructor");
        }

        @Override
        public void onCreate(SQLiteDatabase db)
        {
            String sqlst = String.format("drop table if exists %s;",
                    TASKS_TABLE);// משפט למחיקת הטבלה
            db.execSQL(sqlst);
            db.execSQL(SCRIPT_CREATE_DB);
            Log.i("MyDBHelper", "onCreate");

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
            onCreate(db);

        }

    }

}

this is addPassword: http://ift.tt/1Hg9fiR

package db_pkg;


public class addPassword {
    String website, username, password;
    int id;

    public addPassword()
    {

    }

    public addPassword(String website, String username, String password, int id) {
        this.website = website;
        this.username = username;
        this.password = password;
        this.id = id;
    }

    public addPassword(String website,  String password, int id) {
        this.website = website;
        this.id = id;
        this.password = password;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getWebsite() {
        return website;
    }

    public void setWebsite(String web) {
        if(web.length() < 2)
        {
            this.website = web;
        }
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String user) {
        this.username = user;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String pass) {
        this.password = pass;
    }
}

this is my Activity:

package com.appweb.passwordmanager;

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;

import db_pkg.MySQLiteHelper;
import db_pkg.addPassword;

public class newPassword extends AppCompatActivity {
    private MySQLiteHelper niv;
    private boolean isEmpty(EditText etText) {
        if (etText.getText().toString().trim().length() > 0) {
            return false;
        } else {
            return true;
        }
    }
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_new_password);
        final EditText website    =   (EditText) findViewById(R.id.website);
        final EditText username   =   (EditText) findViewById(R.id.username);
        final EditText password   =   (EditText) findViewById(R.id.password);
        Button   add        =   (Button) findViewById(R.id.add);
        add.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                if(!isEmpty(password))
                {
                    if(!isEmpty(website))
                    {
                        addPassword item = new addPassword(website.getText().toString(),
                                username.getText().toString(),
                                password.getText().toString(),
                                0);
                        niv.addTaskItem(item);
                    }
                }
            }
        });
    }
}

Increment a counter in database SQLite

I'm doing a project on estimote shoe sticker and I'm now doing database for the sticker. My issue is now how to set counter in database sqlite. Every once the sticker is picked up or clicked, in the database it will show an increment in count. I have post the codes for database and main activity below.

NearablesDemoActivity.java

 private void displayCurrentNearableInfo() {
        stickerdb = new Database_sticker(this);
        dbRow = stickerdb.getResult(currentNearable.identifier);
        Integer count = stickerdb.getCount(currentNearable.identifier);
        dbRow.getId();
        dbRow.getIdentifier();
        dbRow.getCount();

        String desc = dbRow.getDesc().toString();
        dbRow.getCa().toString();
        dbRow.getSa().toString();
        String coo = dbRow.getCoo().toString();
        String sm = dbRow.getSm().toString();
        String price = dbRow.getPrice().toString();

        //Set the text to the TextView
        Desc.setText(desc);
        COO.setText(coo);
        SM.setText(sm);
        Price.setText("$" + price);
    }

Database_sticker.java

@Override
    public void onCreate(SQLiteDatabase db){
        String CREATE_SRESULTS_TABLE = "CREATE TABLE " + TABLE_SRESULT + "(" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_IDENTIFIER +
                " TEXT," + KEY_DESC + " TEXT," + KEY_SA + " TEXT," + KEY_CA + " TEXT," + KEY_COO + " TEXT," + KEY_SM + " TEXT," + KEY_PRICE + " REAL," +
                KEY_COUNT + "INTEGER AUTOINCREMENT" +")";
        db.execSQL(CREATE_SRESULTS_TABLE); //create sql sresults table


    }
    //upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
        //drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_SRESULT);
        //create tables again
        onCreate(db);
    }

    public String[] getSize(String identifier){
        String query = "SELECT * FROM " + TABLE_SRESULT + " WHERE " + KEY_IDENTIFIER + "='" + identifier + "'";
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(query, null);
        ArrayList<String> sizes = new ArrayList<String>();
        if(cursor.moveToFirst()){
            do{
               // sizes.add(cursor.getString(3));
                String[] si = cursor.getString(3).split(",");
                sizes.addAll(Arrays.asList(si));


            }while(cursor.moveToNext());
        }
        return sizes.toArray(new String[sizes.size()]);
    }

    public String[] getColour(String identifier){
        String query = "SELECT * FROM " + TABLE_SRESULT + " WHERE " + KEY_IDENTIFIER + "='" + identifier + "'";
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(query, null);
        ArrayList<String> colours = new ArrayList<String>();
        if(cursor.moveToFirst()){
            do{
                //colours.add(cursor.getString(4));
                String[] clr = cursor.getString(4).split(",");
                colours.addAll(Arrays.asList(clr));

            }while(cursor.moveToNext());
        }
        return colours.toArray(new String[colours.size()]);
    }

    public int getCount(String identifier){
        String countQuery = "SELECT * FROM " + TABLE_SRESULT + " WHERE " + KEY_IDENTIFIER + "='" + identifier + "'";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();
        return cursor.getCount();
    }

    public Sresult getResult(String identifier) {
        String selectQuery = "SELECT * FROM " + TABLE_SRESULT + " WHERE " + KEY_IDENTIFIER + "='" + identifier + "'";
        SQLiteDatabase db = this.getWritableDatabase();   //open database
        Cursor cursor = db.rawQuery(selectQuery, null);
        //looping through all rows and adding to list
        Sresult sresult = new Sresult();
        if (cursor.moveToFirst()) {
            do {
                sresult.setId(Integer.parseInt(cursor.getString(0)));
                sresult.setIdentifier(cursor.getString(1));
                sresult.setDesc(cursor.getString(2));
                sresult.setSa(cursor.getString(3));
                sresult.setCa(cursor.getString(4));
                sresult.setCoo(cursor.getString(5));
                sresult.setSm(cursor.getString(6));
                sresult.setPrice(Float.parseFloat(cursor.getString(7)));
                sresult.setCount(Integer.parseInt(cursor.getString(8)));
            } while (cursor.moveToNext());
        }
        return sresult;
    }
}

ListNearablesActivity.java

beaconManager.setNearableListener(new BeaconManager.NearableListener() {
            @Override
            public void onNearablesDiscovered(List<Nearable> nearables) {
                toolbar.setSubtitle("Found shoes: " + nearables.size());
                adapter.replaceWith(nearables);
                for (Nearable nearable : nearables) {
                    if (nearable.isMoving) {
                        try {
                            Class<?> clazz = Class.forName(getIntent().getStringExtra(EXTRAS_TARGET_ACTIVITY));
                            Intent intent = new Intent(ListNearablesActivity.this, clazz);
                            intent.putExtra(EXTRAS_NEARABLE, adapter.getItem(nearables.indexOf(nearable)));
                            startActivity(intent);
                        } //close for try
                        catch (ClassNotFoundException e) {
                            Log.e(TAG, "Finding class by name failed", e);
                        } //close for catch (ClassNotFoundException e)
                    }
                }
            } //for override
        });  //for beaconManager.setNearable



private AdapterView.OnItemClickListener createOnItemClickListener() {
        return new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, final int position, long id) {
                if (getIntent().getStringExtra(EXTRAS_TARGET_ACTIVITY) != null){
                    try {
                        Class<?> clazz = Class.forName(getIntent().getStringExtra(EXTRAS_TARGET_ACTIVITY));
                        Intent intent = new Intent(ListNearablesActivity.this, clazz);
                        intent.putExtra(EXTRAS_NEARABLE, adapter.getItem(position));
                        startActivity(intent);
                    } //close for try
                    catch (ClassNotFoundException e) {
                        Log.e(TAG, "Finding class by name failed", e);
                    } //close for catch (ClassNotFoundException e)
                } //close for getintent.getStringExtra()
            } //close for public void onitemclick
        };   //close for return new adapterview
    }  //close for private adapter

SQLite JSON1 example for JSON extract\set

SQLite has now an experimental JSON1 extention to work with JSON fields. The functions too choose from look promising, but I don't get how to use them in the context of a query.

Suppose I created the following table:

sqlite> create table user(name,phone);
sqlite> insert into user values('oz', json_array(['+491765','+498973']));

The documentation shows how to use json_each in a query, but all other functions lack some in context documentation.

Can someone with SQLite experience provide a few examples of how to use:

  • json_extract
  • json_set

How to make changes to sqlite database for app that is installed in multiple devices?

I started developing an app that uses sqlite that is to be installed on multiple devices and any updates done on sqlite database from any device are to be reflected in other devices as well. I have researched a little and found that Sqlite DB is local to a device and changes done in one device are not reflected in others and for that I have to use external server. Is there any way around it? Is it optimal to directly store data in external server or use sqlite and sync it regularly with external database? Thanks in advance

Android SQLite Database no such table(code 1)

I dont know whats wrong with this Code.. Is there is any Syntax error in creating table query??.. When i compile it throws. no such table.

public static final String DATABASE_NAME = "MMPDB.db";

    // Table name definitions
    public static final String LOG_IN_TABLE = "LogIn";


// LogIn table fields declaration
    public static final String LOGIN_USERNAME = "userName";
    public static final String LOGIN_PASSWORD = "password";

public static final String LOG_IN_TABLE_CREATE_QUERY = "CREATE TABLE "
            + LOG_IN_TABLE + " (" + LOGIN_USERNAME + " TEXT," 
            + LOGIN_PASSWORD + "TEXT);";


public MmpDBHelper(Context context) {
        DATABASE_FILE_PATH = new File(Supporter.getAppCommonPath(),
                DATABASE_NAME);

        if (!DATABASE_FILE_PATH.exists()) {

            try {

                db = SQLiteDatabase.openOrCreateDatabase(DATABASE_FILE_PATH,
                        null);
                // openDatabase();
                db.beginTransaction();
                db.execSQL(LOG_IN_TABLE_CREATE_QUERY);

db.setTransactionSuccessful();
                Log.i("Success", "All tables created successfully");
            } catch (Exception e) {
                db.endTransaction();
                errCode = "Error 501";
                msg = "Table creation failed";
                errMsg = errCode + " : " + msg;
                LogFileCreator.appendLog(errMsg);
            } finally {
//              db.endTransaction();
                db.close();
            }
        }
    }

database for android application

I am trying to create application that reads users (name,position,and their blood group). I will put these info in database to be able to achieve them when I need. do I have to use (Sqlite database) or (Sql server database)?

Syntax error in SQL android

  db.execSQL("create table " + TABLE_WORKDETAILS + "( " + ID2 + " INTEGER PRIMARY KEY , Project TEXT, WorkDescription TEXT, Per Text, TimeIn DATETIME, TimeOut DATETIME, Twf_id INTEGER, FOREIGN KEY(Twf_id) REFERENCES "+TABLE_WORKFORCE+ "(ID))");
  db.execSQL("create table " + TABLE_STAFF_BENEFICT + "( "  + ID3 + " INTEGER PRIMARY KEY , Description TEXT , Amount TEXT, Twd_id INTEGER, FOREIGN KEY(Twd_id) REFERENCES + "+ TABLE_WORKDETAILS+ "(Twf_id))");

LogCat Error

java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.project.project/com.example.project.project.CameraMain}: android.database.sqlite.SQLiteException: near "+": syntax error (code 1): , while compiling: create table StaffBenefict( _id INTEGER PRIMARY KEY , Description TEXT , Amount TEXT, Twd_id INTEGER, FOREIGN KEY(Twd_id) REFERENCES + WorkDetails(Twf_id)) at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2413)

message:DllNotFoundException: sqlite3 on rooted android phones

I have a Unity app which runs great on iOS and Android phones but on rooted nexus phones I get this error.

device:Nexus 4, engine_version:unity 5.2.2, jailbroken:true, manufacturer:LGE, message:DllNotFoundException: sqlite3 

Since the app works on other android phones I believe the problem lies in the fact that the phone is rooted. Any ideas on how to fix this would be appreciated.

jeudi 29 octobre 2015

setting default value while creating table in SQLite

I am working on SQLite. I want to create a table namely user_role with two column role_id and role_name. And there is an another table namely default that contain role_name and default_val.

I want to set Default value of role_id in the time of creating the table user_role and the Default value have to be retrieved from the table default.

I am new to SQLite and have to idea about the way to doing such recursive query. Please help.

error constructor cannot be applied to given types while referencing SQL database

I have created a menu database for a restaurant phone application however I encounter an error:

"Product() in Product cannot be applied to: menuitemid int, menuitemname string, menuitemprice int, menuitemdescription string, menuitemimgname string, menuitemorders int"

Find below my code:

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

public class MenuDbHandler extends SQLiteOpenHelper {

private static final int menudatabaseversion = 1;
private static final String menudatabasename = "KWESPORTS.db";
private static final String menutablename = "MENUTABLE";

private static final String menucolumnid = "menuitemid";
private static final String menucolumnname = "menuitemname";
private static final String menucolumnprice = "menuitemprice";
private static final String menucolumndescription = "menuitemdescription";
private static final String menucolumnimgname = "menucolumnimgname";
private static final String menucolumnorders = "menucolumnorders";

public MenuDbHandler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
    super(context, menudatabasename, factory, menudatabaseversion);
}

@Override
public void onCreate(SQLiteDatabase db) {
    String CREATE_MENU_TABLE = "CREATE TABLE " + menutablename + "(" + menucolumnid + " INTEGER PRIMARY KEY," + menucolumnname + " TEXT," + menucolumnprice + " INTEGER," + menucolumndescription + " TEXT," + menucolumnimgname + " TEXT," + menucolumnorders + "INTEGER" + ")";
    db.execSQL(CREATE_MENU_TABLE);
}

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

public Product findProduct (String menuitemname){
    String query = "Select * FROM " + menutablename + " WHERE " + menucolumnname + " = \"" + menuitemname + "\"";
    SQLiteDatabase db = this.getWritableDatabase();

    Cursor cursor = db.rawQuery(query, null);

    Product product = new Product();

    if (cursor.moveToFirst()) {
            cursor.moveToFirst();
        product.setmenuitemID(Integer.parseInt(cursor.getString(1)));
        product.setmenuitemname(cursor.getString(2));
        product.setmenuitemprice(Integer.parseInt(cursor.getString(3)));
        product.setmenuitemdescription(cursor.getString(4));
        product.setmenuitemimgname(cursor.getString(5));
        product.setmenuitemorders(Integer.parseInt(cursor.getString(6)));
            cursor.close();
        } else {product = null;}

    db.close();
        return product;
  }
}

And the Product java class:

public class Product {

private int _menuitemid;
private String _menuitemname;
private int _menuitemprice;
private String _menuitemdescription;
private String _menuitemimgname;
private int _menuitemorders;

public Product(int menuitemid, String menuitemname, int menuitemprice, String menuitemdescription, String menuitemimgname, int menuitemorders){
    this._menuitemid = menuitemid;
    this._menuitemname = menuitemname;
    this._menuitemprice = menuitemprice;
    this._menuitemdescription = menuitemdescription;
    this._menuitemimgname = menuitemimgname;
    this._menuitemorders = menuitemorders;}

public void setmenuitemID(int menuitemid) {this._menuitemid = menuitemid;}

public void setmenuitemname(String menuitemname) {this._menuitemname = menuitemname;}
public String getmenuitemname () {return this._menuitemname;}

public void setmenuitemprice (int menuitemprice) {this._menuitemprice = menuitemprice;}
public int getmenuitemprice () {return this._menuitemprice;}

public void setmenuitemdescription (String menuitemdescription) {this._menuitemdescription = menuitemdescription;}
public String getmenuitemdescription () {return this._menuitemdescription;}

public void setmenuitemimgname (String menuitemimgname) {this._menuitemimgname = menuitemimgname;}
public String getmenuitemimgname () {return this._menuitemimgname;}

public void setmenuitemorders (int menuitemorders) {this._menuitemorders = menuitemorders;}
public int getmenuitemorders () {return this._menuitemorders;}
}

Thanks, everyone!