I have a listView as image below.
When the submit button is clicked, I want the Time In in the first row and Time Out in the second row saved into SQLite. It is possible to achive ?
lundi 30 novembre 2015
Save specific value in first list and last list to SQLite
rest api optional query parameters and sql indexes
I see a lot of rest api's that have at least 5-10 optional query parameters that filter result sets. Easy to use from a client perspective but how are people creating sql indexes for all the query combinations?
For keyword usage I know full text is an option but I am talking about exact filters or ranges etc.
SQLite database not inserting data using singleton Android
I'm trying to follow the instructions in this link: http://ift.tt/1v5sQkp under approach #1 to access my database from different activities. However the data is not being inserted and I'm receiving a couple of errors.
the main class I'm using to insert the data:
import android.content.Intent;
import android.os.Bundle;
import android.support.design.widget.FloatingActionButton;
import android.support.design.widget.Snackbar;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.Toolbar;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
public class newCharacter extends AppCompatActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_new_character);
Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
setSupportActionBar(toolbar);
Button submit_button = (Button) findViewById(R.id.char_submit);
DatabaseHelper.getInstance(getApplicationContext());
EditText name_text = (EditText) findViewById(R.id.nameText);
EditText health_text = (EditText) findViewById(R.id.editText);
EditText strength_text = (EditText) findViewById(R.id.editText2);
EditText agility_text = (EditText) findViewById(R.id.editText3);
EditText intelligence_text = (EditText) findViewById(R.id.editText4);
EditText charisma_text = (EditText) findViewById(R.id.editText5);
EditText endurance_text = (EditText) findViewById(R.id.editText6);
EditText carry_weight_text = (EditText) findViewById(R.id.editText7);
EditText zprep_text = (EditText) findViewById(R.id.editText8);
EditText inventory_text = (EditText) findViewById(R.id.editText9);
final String name = name_text.toString();
final String health = health_text.toString();
final String strength = strength_text.toString();
final String agility = agility_text.toString();
final String intelligence = intelligence_text.toString();
final String charisma = charisma_text.toString();
final String endurance = endurance_text.toString();
final String carryweight = carry_weight_text.toString();
final String zprep = zprep_text.toString();
final String inventory = inventory_text.toString();
submit_button.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
ERROR HERE boolean isInserted = DatabaseHelper.getInstance(getApplicationContext()).insertData(name, health, strength,
endurance, carryweight, agility, intelligence, charisma, zprep, inventory);
if (isInserted == true)
Toast.makeText(newCharacter.this, "Data Inserted", Toast.LENGTH_LONG).show();
else
Toast.makeText(newCharacter.this, "Data NOT Inserted", Toast.LENGTH_LONG).show();
}
});
}
}
My DatabaseHelper:
import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* Created by Matt on 11/23/2015.
*/
public class DatabaseHelper extends SQLiteOpenHelper {
private static DatabaseHelper sInstance;
public static final String DATABASE_NAME = "character_info.db";
public static final String TABLE_NAME = "characters_table";
public static final String COL_1 = "NAME";
public static final String COL_3 = "HEALTH";
public static final String COL_2 = "STRENGTH";
public static final String COL_4 = "ENDURANCE";
public static final String COL_5 = "CARRY_WEIGHT";
public static final String COL_6 = "AGILITY";
public static final String COL_7 = "INTELLIGENCE";
public static final String COL_8 = "CHARISMA";
public static final String COL_9 = "ZPREP";
public static final String COL_10 = "INVENTORY";
public static synchronized DatabaseHelper getInstance(Context context) {
if (sInstance == null) {
sInstance = new DatabaseHelper(context.getApplicationContext());
}
return sInstance;
}
private DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {
String SQL_String = "create table " + TABLE_NAME +" (NAME TEXT PRIMARY KEY, HEALTH TEXT, STRENGTH TEXT" +
", ENDURANCE TEXT, CARRY_WEIGHT TEXT, AGILITY TEXT, INTELLIGENCE TEXT, CHARISMA TEXT, ZPREP TEXT, INVENTORY TEXT)";
db.execSQL(SQL_String);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);
onCreate(db);
}
public boolean insertData(String name, String health, String strength, String endurance, String carryweight,
String agility, String intelligence, String charisma, String zprep, String inventory) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put(COL_1, name);
contentValues.put(COL_2, health);
contentValues.put(COL_3, strength);
contentValues.put(COL_4, endurance);
contentValues.put(COL_5, carryweight);
contentValues.put(COL_6, agility);
contentValues.put(COL_7, intelligence);
contentValues.put(COL_8, charisma);
contentValues.put(COL_9, zprep);
contentValues.put(COL_10, inventory);
ERROR HERE: long result = db.insert(TABLE_NAME, null, contentValues);
if (result == -1)
return false;
else
return true;
}
}
logcat:
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: Error inserting INVENTORY=android.support.v7.widget.AppCompatEditText@45f78508 INTELLIGENCE=android.support.v7.widget.AppCompatEditText@45f9e0f0 CHARISMA=android.support.v7.widget.AppCompatEditText@45f7ab58 NAME=android.support.v7.widget.AppCompatEditText@45f663f0 ZPREP=android.support.v7.widget.AppCompatEditText@45f7ed60 HEALTH=android.support.v7.widget.AppCompatEditText@45f8dfd0 CARRY_WEIGHT=android.support.v7.widget.AppCompatEditText@45f634f0 ENDURANCE=android.support.v7.widget.AppCompatEditText@45f8ef60 STRENGTH=android.support.v7.widget.AppCompatEditText@45f7dda8 AGILITY=android.support.v7.widget.AppCompatEditText@45f9db10
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: android.database.sqlite.SQLiteException: table characters_table has no column named INVENTORY: , while compiling: INSERT INTO characters_table(INVENTORY, INTELLIGENCE, CHARISMA, NAME, ZPREP, HEALTH, CARRY_WEIGHT, ENDURANCE, STRENGTH, AGILITY) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:80)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:36)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at android.database.sqlite.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1145)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1536)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1410)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at proctor.csit.project.DatabaseHelper.insertData(DatabaseHelper.java:70)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at proctor.csit.project.newCharacter$1.onClick(newCharacter.java:55)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at android.view.View.performClick(View.java:2408)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at android.view.View$PerformClick.run(View.java:8816)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at android.os.Handler.handleCallback(Handler.java:587)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at android.os.Handler.dispatchMessage(Handler.java:92)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at android.os.Looper.loop(Looper.java:123)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at android.app.ActivityThread.main(ActivityThread.java:4627)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at java.lang.reflect.Method.invokeNative(Native Method)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at java.lang.reflect.Method.invoke(Method.java:521)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
12-01 01:05:45.049 274-274/proctor.csit.project E/Database: at dalvik.system.NativeStart.main(Native Method)
I have put notes next to the apparent errors in my java code. My first time using sqlite and I'm pretty lost on this, so any help is appreciated!
Android Sqlite cursor initialization error
I have a simple sqlite class which saves the two parameters NAME and BEHAVE of a person with button a button.But the activity crashes when i click the button to save data.Here is my logcat saying- make sure the cursor is initialized properly.
12-01 10:38:05.589 9336-9336/? E/CursorWindow﹕ Failed to read row 0, column -1 from a CursorWindow which has 8 rows, 3 columns.
12-01 10:38:05.589 9336-9336/? D/AndroidRuntime﹕ Shutting down VM
-01 10:38:05.599 9336-9336/? E/AndroidRuntime﹕ FATAL EXCEPTION: main
Process: androphile.alpha, PID: 9336
java.lang.RuntimeException: Unable to start activity ComponentInfo{androphile.alpha/androphile.alpha.sqlview}: java.lang.IllegalStateException: Couldn't read row 0, col -1 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it.
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2790)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2855)
at android.app.ActivityThread.access$900(ActivityThread.java:181)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1474)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:145)
at android.app.ActivityThread.main(ActivityThread.java:6117)
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:1399)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1194)
Caused by: java.lang.IllegalStateException: Couldn't read row 0, col -1 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it.
at android.database.CursorWindow.nativeGetString(Native Method)
at android.database.CursorWindow.getString(CursorWindow.java:451)
at android.database.AbstractWindowedCursor.getString(AbstractWindowedCursor.java:51)
at androphile.alpha.behave.getData(behave.java:80)
at androphile.alpha.sqlview.onCreate(sqlview.java:24)
at android.app.Activity.performCreate(Activity.java:6374)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1119)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2743)
Here is my Handler class.It also show cursor should be freed up after use.
public class behave {
public static final String KEY_ROWID="_id ";
public static final String KEY_NAME="persons_name";
public static final String KEY_BEHAVE="persons_behave";
private static final String DATABASE_NAME="behaveDB";
private static final String DATABASE_TABLE="personTable";
private static final int DATABASE__VERSION=1;
private Dbhelper ourHelper;
private final Context ourContext;
private SQLiteDatabase ourDatabase;
private class Dbhelper extends SQLiteOpenHelper{
public Dbhelper(Context context){
super(context, DATABASE_NAME, null, DATABASE__VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE "+ DATABASE_TABLE+"("+
KEY_ROWID+" INTEGER PRIMARY KEY AUTOINCREMENT,"+
KEY_NAME+" TEXT NOT NULL,"+
KEY_BEHAVE+" TEXT NOT NULL);");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS "+ DATABASE_TABLE);
onCreate(db);
}}
public behave( Context c) {
ourContext =c;
}
public behave open() throws SQLException{
ourHelper=new Dbhelper(ourContext);
ourDatabase=ourHelper.getWritableDatabase();
return this;
}
public void close(){
ourHelper.close();
}
public long createEntry(String sqlname, String sqlbehave) {
ContentValues cv=new ContentValues();
cv.put(KEY_NAME,sqlname);
cv.put(KEY_BEHAVE, sqlbehave);
return ourDatabase.insert(DATABASE_TABLE,null,cv);
} public String getData() {
String[] column = new String[]{KEY_ROWID, KEY_NAME, KEY_BEHAVE};
Cursor c = ourDatabase.query(DATABASE_TABLE, column, null, null, null, null, null);
String data = "";
int iRow = c.getColumnIndex(KEY_ROWID);
int iName = c.getColumnIndex(KEY_NAME);
int iBehave = c.getColumnIndex(KEY_BEHAVE);
for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
data = data + c.getString(iRow) + " " + c.getString(iName)+" "+c.getString(iBehave)+ "/n";
}
return data;
}
}
SQL selection problems
I am working with SQLite and I think I have a problem getting table ID, my table stores book categories, their images details here is a graphical representation of my table
| mSubCategory_id | mCategory_id_FK | mSubCategory_description | mSubCategory_image|
my problem is getting the ID field values, below is a query am using to match books in my sub Cateogory table with a certain Category, its working perfectly but I cannot seem to access value of id, I am getting the same value for id and mSubCateogory_id which in the actual sense is the value for mSubCateogory_id and not id below is the query am using:
String sQuery = " SELECT "+mCategory_id+", "+mCategory_id_FK+", "+Constant.SUB_DESCRIPTION+
", "+mSubCategory_image+" FROM "+CATEGORY_TABLE+
" INNER JOIN "+SUB_CATEGORY_TABLE+" ON "+mCategory_id +" = " +mCategory_id_FK
+ " WHERE "+mCategory_id +" = ?";
String[] args = new String[1];
args[0] = String.valueOf(mtargetID);
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(sQuery, args);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
ExpandableCategoryItem mCateogory= new ExpandableCategoryItem();
//I am getting the same value here whether I use
//getString(0) or getString(1)
mCateogory.setId(Integer.parseInt(cursor.getString(1)));
mCateogory.setmDescription(cursor.getString(2));
mCateogory.setmCategoryImgPath(cursor.getString(3));
mCategoryImgPost.add(mCateogoryPost);
} while (cursor.moveToNext());
Android Cursor Adapter for Custom ListView Layout - Null Pointer Exception Error
First post on this site, so forgive me my sins. I am attempting to create a custom ListView layout that is populated by fields from my SQLite Database Adapter class. Below is the method I tried to use for this, and it is called in the onCreate method of its class, as well as the onClick method of a button to save to the Database:
//Method to re-populate custom list view songlist_layout when a new entry is added
private void populateSongList() {
//Cursor to navigate through records of the database
Cursor cursor = myDb.getAllRows();
//Need two arrays to work with the Cursor. First is from field names
String [] fromFieldNames = new String[] {DBAdapter.KEY_ROWID,
DBAdapter.KEY_SONGTITLE,
DBAdapter.KEY_SONGDURATION};
//Second is int array
int [] toViewIDs = new int [] {R.id.textViewSongNumber, R.id.textViewSongName,
R.id.textViewSongDuration};
//Cursor Adapter Object
SimpleCursorAdapter myCursorAdapter;
myCursorAdapter = new SimpleCursorAdapter(getBaseContext(),
R.layout.songlist_layout, cursor, fromFieldNames, toViewIDs,0);
//Need to grab ListView from activity_add_song to set the adapter to it
ListView songList = (ListView)findViewById(R.id.songsListView);
songList.setAdapter(myCursorAdapter);
}
//Method to handle the click event of the Save button, adding the data into the database
public void onClickSaveSong (View v) {
//Song Title and duration are essential fields, so we want to check if they
// have text before saving to the database
if(!TextUtils.isEmpty(etSongTitle.getText().toString()) &&
!TextUtils.isEmpty(etSongDuration.getText().toString())) {
myDb.insertRow(etSongTitle.getText().toString(),
etSongKey.getText().toString(),
etSongTuning.getText().toString(),
etSongDuration.getText().toString());
//Pop-up to inform user the Data has been saved
Toast.makeText(getBaseContext(), "Song Added!", Toast.LENGTH_LONG).show();
}//if
//Otherwise a pop-up to tell the user to enter the essential info
else {Toast.makeText(getBaseContext(), "Enter Title and Duration",
Toast.LENGTH_LONG).show();}
//Call to repopulate songs ListView
populateSongList();
}//onClickSaveSong()
The custom XML Layout for the ListView contains three TextViews to hold the songNumber, the songName and the songDuration:
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="New Text"
android:id="@+id/textViewSongNumber"
android:paddingRight="10dp"/>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="New Text"
android:id="@+id/textViewSongName"
android:paddingRight="60dp"/>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="New Text"
android:id="@+id/textViewSongDuration"
android:paddingRight="70dp"/>
I have read elsewhere that the problem could be because the ListView is grabbing from the wrong ListView ID ListView songList = (ListView)findViewById(R.id.songsListView); However comparing it to the XML Layout for the ListView, I don't see how this would be the case:
<ListView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:id="@+id/songsListView"
android:layout_below="@+id/imageView"
android:layout_centerHorizontal="true"
android:layout_above="@+id/addSongButton" />
Finally, the logcat:
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.blob.gigstrofinal/com.blob.gigstrofinal.AddSong}: java.lang.NullPointerException
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2184)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2233)
at android.app.ActivityThread.access$800(ActivityThread.java:135)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1196)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:136)
at android.app.ActivityThread.main(ActivityThread.java:5001)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:785)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:601)
at dalvik.system.NativeStart.main(Native Method)
Caused by: java.lang.NullPointerException
at com.blob.gigstrofinal.AddSong.populateSongList(AddSong.java:121)
at com.blob.gigstrofinal.AddSong.onCreate(AddSong.java:45)
I cannot figure this out. I haven't been using Android for long, and a lot of this is over my head, so I would appreciate any help at all on this matter. The App is for a University Project, and it is due next week!
How to change the value of a cell in a databse using SQLITE and Python
def update_account_balance(db, clientnum, account, changebalance):
return run_command(db, "UPDATE Accounts SET ? = ? + ? WHERE Number is ?",
(account, account, changebalance, clientnum))
Where run_command is:
con = sqlite3.connect(db)
cur = con.cursor()
if args == None:
cur.execute(command)
else:
cur.execute(command, args)
result_L = cur.fetchall()
cur.close()
con.commit()
con.close()
return result_L
So basically what I'm trying to do is set a bunch of '?' to values after my sql statement but when ever I run it says, "sqlite3.OperationalError: near "?": syntax error" How do I fix this? Thank you
Multiple ALTER column queries in SQLite with SQLAlchemy
I need to make some of the columns NOT NULL in the tables in my database. I only then discovered that SQLite has limited ALTER support, and the common workaround is to create a duplicate table and copy the data over.
However this would be a very time consuming task due to the large number of tables I need to change. Since all I'm changing in my SQLAlchemy models is adding nullable=False to the column definitions, is there a simple way to apply the change to the current database? I'm working under the assumption that there are no null values in the columns.
sql not calculating sum correctly
I've got the following sql query, it seems to be returning data, but the sum of the invoice_total (4th column) is not calculating properly. At the moment although I have about 50 records in purchase files with the duplicate supplierID, I currently only have 4 individual supplier records filled with the invoice_total. The returned result is not what I have put in.
SELECT p.orderID, s.supplier, SUM(IFNULL(pl.line_price,0)) AS total_order, SUM(IFNULL(p.invoice_total,0)) AS invoice_total
FROM purchase p
LEFT JOIN purchase_line pl
ON p.orderID = pl.orderID
LEFT JOIN supplier s
ON p.supplierID = s.supplierID
WHERE (p.date BETWEEN '2015-01-01' AND '2015-12-30')
GROUP BY p.supplierID
ORDER BY p.supplierID ASC
Flask - Apache Internal Server Error without log entry
I built a webapp using the flask framework, sqlalchemy and sqlite and recently deployed it on a VPS. It works perfectly for the cover page and the legal page, however I get a "500 Internal Server Error" when trying to open the blog page. I guess it must have something to do with the fact that the function I wrote, used by flask to serve the blog page uses database queries, whereas the other (working) pages do not. The weird thing is however, that the blog page loads correctly when I visit it the first time after executing "apachectl restart" on my VPS. When I click the link again, I get the "500 Internal Server error" error page. I looked at the log (/var/log/apache2/error.log), but there is no information/error in that file. I tried setting the LogLevel of the Apache server to debug, but that doesn't help, either. I use absolute paths to my database in both scripts containing database queries as suggested in other threads on stackoverflow. I also did some research on this error but didn't find any useful information.
The webserver.py file:
from flask import Flask, render_template, url_for, request, redirect, flash
from sqlalchemy import create_engine, desc
from sqlalchemy.orm import sessionmaker
from database_setup import Base, Entry, Comment
app = Flask(__name__)
engine = create_engine('sqlite:////var/www/homepage/blog.db')
Base.metadata.bind = engine
DBSession = sessionmaker(bind = engine)
session = DBSession()
# Obviously just a temporary password, change later!
app.secret_key = ''
@app.route('/')
@app.route('/home')
@app.route('/index')
@app.route('/home.html')
@app.route('/index.html')
def index():
return render_template('index.html')
@app.route('/legal')
@app.route('/legal.html')
def legal():
return render_template('legal.html')
@app.route('/blog')
@app.route('/blog.html')
def blog():
# Fetch all entrys from database in correct chronological order (newest first)
entrys = session.query(Entry).order_by(desc(Entry.timestamp)).all()
return render_template('blog.html', blog_entrys = entrys)
@app.route('/comment/<int:entry_id>', methods=['GET', 'POST'])
def comment(entry_id):
if request.method == 'GET':
entry = session.query(Entry).filter(Entry.id == entry_id).first()
_path = request.path
if not entry:
flash('Unknown blog-post ID')
return render_template('blog.html')
return render_template('comment.html', blog_entry = entry, path = _path)
elif request.method == 'POST':
name = request.form['name']
comment = request.form['comment']
if name == '':
flash('Please fill in your name')
return redirect(url_for('comment'))
if comment == '':
flash('Comment text cannot be empty')
return redirect(url_for('comment'))
new_comment = Comment(entry_id = entry_id, text = comment, name = name)
session.add(new_comment)
session.commit()
return redirect(url_for('blog'))
else:
flash('Bad request')
return redirect(url_for('index'))
if __name__ == '__main__':
app.debug = True
app.run(host = 'localhost', port = 5000)
The database_setup.py file:
from sqlalchemy import Table, Column, ForeignKey, Integer, String, Boolean, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine
Base = declarative_base()
class Entry(Base):
__tablename__ = 'entry'
id = Column(Integer, primary_key = True)
title = Column(String(100), nullable = False)
body = Column(String, nullable = False)
timestamp = Column(DateTime, nullable = False)
featured = Column(Boolean, nullable = False)
comments = relationship('Comment')
def is_featured(self):
return self.featured
class Comment(Base):
__tablename__ = 'comment'
id = Column(Integer, primary_key = True)
entry_id = Column(Integer, ForeignKey('entry.id'))
text = Column(String(500), nullable = False)
name = Column(String(80))
engine = create_engine('sqlite:////var/www/homepage/blog.db')
Base.metadata.create_all(engine)
The Apache error log:
[Tue
Dec 01 02:33:45 2015] [error] python_init: Python version mismatch, expected '2.6.5+', found '2.6.6'.
[Tue Dec 01 02:33:45 2015] [error] python_init: Python executable found '/usr/bin/python'.
[Tue Dec 01 02:33:45 2015] [error] python_init: Python path being used '/usr/lib/python2.6/:/usr/lib/python2.6/plat-linux2:/usr/lib/python2.6/lib-tk:/usr/lib/python2.6/lib-old:/usr/lib/python2.6/lib-dynload'.
[Tue Dec 01 02:33:45 2015] [notice] mod_python: Creating 8 session mutexes based on 10 max processes and 0 max threads.
[Tue Dec 01 02:33:45 2015] [notice] mod_python: using mutex_directory /tmp
[Tue Dec 01 02:33:45 2015] [debug] mod_wsgi.c(9971): mod_wsgi (pid=7459): Socket for 'homepage' is '/var/run/apache2/wsgi.7459.42.1.sock'.
[Tue Dec 01 02:33:45 2015] [info] mod_wsgi (pid=9824): Starting process 'homepage' with uid=1000, gid=1000 and threads=5.
[Tue Dec 01 02:33:45 2015] [info] mod_wsgi (pid=9824): Attach interpreter ''.
[Tue Dec 01 02:33:45 2015] [notice] Apache/2.2.16 (Debian) mod_python/3.3.1 Python/2.6.6 mod_wsgi/3.3 configured -- resuming normal operations
[Tue Dec 01 02:33:45 2015] [info] Server built: Feb 1 2014 21:22:42
[Tue Dec 01 02:33:45 2015] [debug] prefork.c(1013): AcceptMutex: sysvsem (default: sysvsem)
[Tue Dec 01 02:33:45 2015] [info] mod_wsgi (pid=9832): Attach interpreter ''.
[Tue Dec 01 02:33:48 2015] [info] [client 31.19.64.54] mod_wsgi (pid=9824, process='homepage', application=''): Loading WSGI script '/var/www/homepage/homepage.wsgi'.
[Tue Dec 01 02:33:48 2015] [info] mod_wsgi (pid=9833): Attach interpreter ''.
[Tue Dec 01 02:33:48 2015] [debug] mod_deflate.c(615): [client 31.19.64.54] Zlib: Compressed 1132 to 618 : URL /blog.html
[Tue Dec 01 02:33:51 2015] [debug] mod_deflate.c(615): [client 31.19.64.54] Zlib: Compressed 291 to 200 : URL /blog.html
Python TypeError: an integer is required while working with Sockets on the client side
#!/usr/bin/python
import os import sys import socket import sqlite3 as lite
global s s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
class client:
#functions on the client side only send the data to the server side (their only job)
def login(ID, password):
try:
s.send(self, ID)
s.send(password)
except IOError:
print "Error! Cannot execute statement."
def signup(self, ID, Name, Email, Password):
try:
s.send(ID, Name)
s.send(Email, Password)
except IOError:
print "Error! Cannot execute statement."
def addContact(self, ID, name, email):
try:
s.send(ID, name)
s.send(email)
except IOError:
print "Error! Cannot execute statement."
class main: # create a socket object c = client() Register = "Register" Login = "Login"
# get local machine name
host = socket.gethostname()
port = 9999
# connection to hostname on the port.
s.connect((host, port))
Message = input("Login if you are a user. If you are new, register here so you could play checkers! \t")
if Message == Login:
ID = input("ID \t")
password = input("Password \t")
c.login(ID, password)
elif Message == Register:
ID = input("ID \t")
Name = input("Name \t")
Email = input("Email \t")
Password = input("Password \t")
c.signup(ID, Name, Email, Password)
elif Message == add:
ID = input("ID \t")
Name = input("Name \t")
Email = input("Email \t")
c.addContact(ID, name, email)
else:
exit()
# Receive no more than 1024 bytes
data = s.recv(1024)
s.close()
print("The time got from the server is %s" % data.decode('ascii'))
RecylerView won't populate
I'm trying to populate a RecyclerView from an SQLite database however I'm not seeing anything being displayed when the code runs. There are no error messages, so there's no logcat to show. I initially thought it had something to do with List<> and ArrayList<> differences, but I had no luck with that idea, so I've come here to hopefully get some help from more experienced developers.
I know there's a lot of code down there, but I wanted to post everything that's related to the one RecyclerView, so we don't miss anything at all. I tried to think of everything you'd need, but if you need more, please don't hesitate to speak up.
Thank you all for your time.
MainActivity.java:
import android.content.Intent;
import android.database.Cursor;
import android.support.design.widget.FloatingActionButton;
import android.support.v4.view.ViewPager;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.Toast;
import com.example.michael.budgetapp.Database.CategoryLoad;
import com.example.michael.budgetapp.Database.TransactionLoad;
import com.example.michael.budgetapp.Tabs.SlidingTabLayout;
import com.example.michael.budgetapp.Tabs.ViewPagerAdapter;
public class MainActivity extends AppCompatActivity
implements CategoryLoad.categoryLoadComplete,
TransactionLoad.LoadComplete {
private android.support.v7.widget.Toolbar toolbar;
private ViewPager mPager;
private SlidingTabLayout mTabs;
ViewPagerAdapter mAdapter;
CharSequence Titles[]={"Budgets", "Transactions"};
int Numboftabs = 2;
private Cursor data = null;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
toolbar = (android.support.v7.widget.Toolbar) findViewById(R.id.app_bar);
setSupportActionBar(toolbar);
mAdapter = new ViewPagerAdapter(getSupportFragmentManager(),Titles,Numboftabs);
mPager = (ViewPager) findViewById(R.id.pager);
mPager.setAdapter(mAdapter);
mTabs = (SlidingTabLayout) findViewById(R.id.tabs);
mTabs.setDistributeEvenly(true);
mTabs.setCustomTabColorizer(new SlidingTabLayout.TabColorizer() {
@Override
public int getIndicatorColor(int position) {
return getResources().getColor(R.color.accent);
}
});
mTabs.setViewPager(mPager);
FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab);
fab.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
startActivity(new Intent(MainActivity.this, AddTransaction.class));
}
});
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
getMenuInflater().inflate(R.menu.menu_main_activity, menu);
return true;
}
@Override
public boolean onOptionsItemSelected(MenuItem item) {
int id = item.getItemId();
if (id == R.id.action_settings) {
startActivity(new Intent(this, Settings.class));
return true;
}
if (id == R.id.category) {
startActivity(new Intent(this, AddCategory.class));
return true;
}
if (id == R.id.search) {
}
return super.onOptionsItemSelected(item);
}
@Override
public void categoryLoadComplete(Cursor cursor) {
data = cursor;
}
@Override
public void transactionLoadComplete(Cursor cursor) {
}
}
AdapterTransactions.java:
import android.content.Context;
import android.support.v7.widget.RecyclerView;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.TextView;
import com.example.michael.budgetapp.R;
import java.util.Collections;
import java.util.List;
public class AdapterTransactions extends RecyclerView.Adapter<AdapterTransactions.MyViewHolder> {
private LayoutInflater inflater;
List<TransactionInfo> transactionData = Collections.emptyList();
public AdapterTransactions(Context context, List<TransactionInfo> transactionData) {
inflater = LayoutInflater.from(context);
this.transactionData = transactionData;
}
@Override
public MyViewHolder onCreateViewHolder(ViewGroup parent, int viewType) {
View view = inflater.inflate(R.layout.transaction_row, parent, false);
MyViewHolder holder = new MyViewHolder(view);
return holder;
}
@Override
public void onBindViewHolder(MyViewHolder holder, int position) {
TransactionInfo current = transactionData.get(position);
holder.itemName.setText(current.itemName);
holder.itemCat.setText(current.itemCat);
holder.itemPrice.setText(current.itemPrice);
holder.itemDate.setText(current.date);
holder.itemCurrency.setText(current.itemCurrency);
}
@Override
public int getItemCount() {
return transactionData.size();
}
class MyViewHolder extends RecyclerView.ViewHolder {
TextView itemName, itemCat, itemPrice, itemDate, itemCurrency;
public MyViewHolder(View itemView) {
super(itemView);
itemName = (TextView) itemView.findViewById(R.id.itemName);
itemCat = (TextView) itemView.findViewById(R.id.itemCat);
itemPrice = (TextView) itemView.findViewById(R.id.itemPrice);
itemDate = (TextView) itemView.findViewById(R.id.itemDate);
itemCurrency = (TextView) itemView.findViewById(R.id.itemCurrency);
}
}
}
TransactionsTab.java:
import android.database.Cursor;
import android.os.Bundle;
import android.support.annotation.Nullable;
import android.support.v4.app.Fragment;
import android.support.v7.widget.LinearLayoutManager;
import android.support.v7.widget.RecyclerView;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import com.example.michael.budgetapp.Database.DatabaseHelper;
import com.example.michael.budgetapp.Database.TransactionLoad;
import com.example.michael.budgetapp.R;
import java.util.ArrayList;
import java.util.List;
public class TransactionsTab extends Fragment
implements TransactionLoad.LoadComplete {
RecyclerView transactionsRecycler;
AdapterTransactions adapter;
TransactionLoad load = null;
List<TransactionInfo> adapterData = new ArrayList<>();
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
if (load == null) {
load = new TransactionLoad(getActivity());
load.execute();
}
}
@Override
public View onCreateView(LayoutInflater inflater,
@Nullable ViewGroup container,
@Nullable Bundle savedInstanceState) {
View view = inflater.inflate(R.layout.tab_transactions,container,false);
transactionsRecycler = (RecyclerView) view.findViewById(R.id.transactionRecycler);
adapter = new AdapterTransactions(getActivity(), adapterData);
transactionsRecycler.setAdapter(adapter);
transactionsRecycler.setLayoutManager(new LinearLayoutManager(getActivity()));
return view;
}
private static ArrayList<TransactionInfo> convertCursorToBudgetInfoList(Cursor cursor) {
if (cursor == null) {
return new ArrayList<TransactionInfo>();
}
TransactionInfo returnListValue = new TransactionInfo();
ArrayList<TransactionInfo> returnList = new ArrayList<>(cursor.getCount());
if (cursor.moveToFirst()) {
for (int i = 10; i < cursor.getCount(); i++) {
returnListValue.itemName = cursor.getString(cursor.getColumnIndex(DatabaseHelper.TRANSACTION_NAME));
returnListValue.itemCat = cursor.getString(cursor.getColumnIndex(DatabaseHelper.TRANSACTION_CATEGORY));
returnListValue.itemCurrency = cursor.getString(cursor.getColumnIndex(DatabaseHelper.TRANSACTION_CURRENCY));
returnListValue.itemPrice = cursor.getString(cursor.getColumnIndex(DatabaseHelper.TRANSACTION_PRICE));
returnListValue.date = cursor.getString(cursor.getColumnIndex(DatabaseHelper.TRANSACTION_DATE));
returnList.add(returnListValue);
}
}
return returnList;
}
@Override
public void transactionLoadComplete(Cursor cursor) {
adapterData = convertCursorToBudgetInfoList(cursor);
adapter.notifyDataSetChanged();
adapter.transactionData.notifyAll();
}
}
TransactionInfo.java:
public class TransactionInfo {
public String itemName, itemPrice, itemCurrency, itemCat, date;
}
transaction_row.xml:
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
xmlns:android="http://ift.tt/nIICcg"
android:orientation="horizontal"
android:layout_width="match_parent"
android:layout_height="match_parent">
<TableLayout
android:orientation="horizontal"
android:layout_width="match_parent"
android:layout_height="match_parent">
<TableRow>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textAppearance="?android:attr/textAppearanceLarge"
android:text="Lunch"
android:layout_gravity="center_vertical"
android:id="@+id/itemName"
android:textColor="@color/primary_dark"
android:textStyle="bold"
android:padding="5dp"/>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textAppearance="?android:attr/textAppearanceMedium"
android:text="Food"
android:textStyle="bold"
android:layout_gravity="center_vertical"
android:id="@+id/itemCat"
android:textColor="@color/primary_text"
android:padding="5dp"/>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textAppearance="?android:attr/textAppearanceMedium"
android:text="87"
android:layout_gravity="center_vertical"
android:id="@+id/itemPrice"
android:textColor="@color/primary_text"
android:padding="5dp"/>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textAppearance="?android:attr/textAppearanceMedium"
android:text="USD"
android:layout_gravity="center_vertical"
android:id="@+id/itemCurrency"
android:textColor="@color/primary_text"
android:padding="5dp"/>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textAppearance="?android:attr/textAppearanceSmall"
android:text="2015/11/25; 20:34"
android:layout_gravity="center_vertical"
android:id="@+id/itemDate"
android:textColor="@color/primary_text"
android:padding="5dp"/>
</TableRow>
</TableLayout>
tab_transactions.xml:
<RelativeLayout
xmlns:android="http://ift.tt/nIICcg"
xmlns:tools="http://ift.tt/LrGmb4"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".Tabs.Transactions.TransactionsTab">
<android.support.v7.widget.RecyclerView
android:layout_width="match_parent"
android:layout_height="match_parent"
android:id="@+id/transactionRecycler">
</android.support.v7.widget.RecyclerView>
TransactionLoad.java:
import android.content.Context;
import android.database.Cursor;
import android.os.AsyncTask;
import android.util.Log;
import java.lang.ref.WeakReference;
public class TransactionLoad extends AsyncTask<Void, Void, Cursor> {
private static final String TAG = "LoadTask";
private LoadComplete loadComplete;
private WeakReference<Context> transactionWeakReference;
private DatabaseHelper tt;
public interface LoadComplete {
void transactionLoadComplete(Cursor cursor);
}
public TransactionLoad(Context context) {
transactionWeakReference = new WeakReference<>(context);
tt = DatabaseHelper.getInstance(transactionWeakReference.get());
try {
loadComplete = (LoadComplete) transactionWeakReference.get();
} catch (ClassCastException e) {
Log.e(TAG, context.toString() + " must implement LoadComplete");
}
}
@Override
protected Cursor doInBackground(Void... params) {
Cursor result = tt.getReadableDatabase().query(
DatabaseHelper.TRANSACTIONS_TABLE, // table name
null, // columns
null, // where
null, // where arguments
null, // group by
null, // having
DatabaseHelper.TRANSACTION_KEY_ID); // order by
result.getCount();
return result;
}
@Override
protected void onPreExecute() {
}
@Override
protected void onPostExecute(Cursor cursor) {
loadComplete.transactionLoadComplete(cursor);
}
}
Caching data in local database and API communication
I have Android application communicating with a server using REST API.
And caching is done using SQLite database and GreenORM.
Let me explain how it works.
Consider endpoint http://ift.tt/1NYOjzw
-
I have ETage interceptor (OkHTTP interceptor).
a) Firstly, before making request, it checks in local table if request(URL) is in etag table and has correspondingETag hashfrom previous request-response. If it hasIf-None-Matchheader is added.
b) Server checks if data for this request has the same hash it responses with 304 code, othrwise data and etag header goes into response. Simple ETag mechanism -
All retrieved data from request is stored in local db using insertOrReplace method ( I think the name is self-descriptive) primary key comes from the server.
-
In case of no internet connection application can work in offline mode(just navigating through cached data)
The problem I have just noticed is that consider following example
Application requested all news but news entity has field verified if this field is not equal true news article should not be displayed.
By default http://ift.tt/1NYOjzw returns all news without filtering, but you can filter data using get parameters for example
http://ift.tt/1Iknhpz should return all verified news articles.
Sequence of steps
- Request all verified news
http://ift.tt/1Iknhpz - All data is saved into DB, for instance articles with ids from 1 to 10 were saved into db
- Article with id 5 was changed on the server to non-verified.
- Request again all verified articles
http://ift.tt/1Iknhpzserver will return -1 article (in our case ID 5). - BUT here is the problem the record with ID 5 is stored in local DB and as far as I use inert or update it won't be deleted or updated. and will be displayed
- Another simple example is simple removal of item on the server.
My question is what is the best to solve this problem, and all possible problems that can appear in the future. It is obviously the problem in architecture of communication.
I have some ideas but they seems not as good as real solution should.
- Send each time IDs of articles stored in the local db and server should return the data and in case of removed somehow notify about this in response.
- Each time request is sent and received do following steps remove all data from local DB that satisfies the request before inserting new data (better solution)
- Simply remove data from the table before each request, but requests can be different ( but still in scope of one table) for instance different filters. So only the data from the last request will be saved in a table (bad solution, no benefits).
Please advice what is the best way in this case, maybe you can suggest something else according to the architecture.
Thanks.
i want to show id column from database in jcombobox please help me as soon as possible
jcombobox keep repeating result every time whenever i select item, i want to show id column from database in jcombobox please help me as soon as possible studentidd is variable of jcombobox
private void studentiddActionPerformed(java.awt.event.ActionEvent evt) {
// it working but results keep repeating same row again an again
try {
Class.forName("org.sqlite.JDBC");
Connection conn =(Connection) DriverManager.getConnection("jdbc:sqlite:studentdb.sqlite");
Statement stat = conn.createStatement();
ResultSet rs;
rs = stat.executeQuery("SELECT id from student_table");
while (rs.next())
{
String name = rs.getString("id");
studentidd.addItem(rs.getString("id"));
}//end while
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
iOS: SQLite error from writing to core data
I'm getting these errors from writing to core data and I'm not sure how to debug this.
Unknown error calling sqlite3_step (19: NOT NULL constraint failed: changes.customer_id) eu DB Query: INSERT INTO changes (value, customer_id, org_scope) VALUES (?, ?, ?)
Unknown error finalizing or resetting statement (19: NOT NULL constraint failed: changes.customer_id) DB Query: INSERT INTO changes (value, customer_id, org_scope) VALUES (?, ?, ?)
Android SQL Lite help: creating databasetable
I've been trying to reference the old code to create a table. I changed or the key/column names for the new table and everything was working fine. However, when I tried to change the name from "expense" (from the old code) to "transaction" (new code). I got
E/SQLiteLog: (1) near "transaction": syntax error D/AndroidRuntime: Shutting down VM E/AndroidRuntime: FATAL EXCEPTION: main Process: com.cs465.groceryrun.groceryrun, PID: 7199 java.lang.RuntimeException: Unable to start activity ComponentInfo{com.cs465.groceryrun.groceryrun/com.cs465.groceryrun.groceryrun.Transactions}: android.database.sqlite.SQLiteException: near "transaction": syntax error (code 1): , while compiling: CREATE TABLE transaction (_id INTEGER PRIMARY KEY,title TEXT,person TEXT,role TEXT,date TEXT,due_date TEXT,status TEXT,rating REAL,amount REAL);
All I did was change the TABLE_NAME from:
public static abstract class TransactionTable implements BaseColumns {
public static final String TABLE_NAME = "expense";
public static final String COLUMN_NAME_TITLE = "title";
public static final String COLUMN_NAME_PERSON = "person";
public static final String COLUMN_NAME_ROLE = "role";
public static final String COLUMN_NAME_DATE = "date";
public static final String COLUMN_NAME_DUE_DATE = "due_date";
public static final String COLUMN_NAME_STATUS = "status";
public static final String COLUMN_NAME_RATING = "rating";
public static final String COLUMN_NAME_AMOUNT = "amount";
public static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (" +
_ID + " INTEGER PRIMARY KEY," +
COLUMN_NAME_TITLE + TEXT_TYPE + COMMA_SEP +
COLUMN_NAME_PERSON + TEXT_TYPE + COMMA_SEP +
COLUMN_NAME_ROLE + TEXT_TYPE + COMMA_SEP +
COLUMN_NAME_DATE + TEXT_TYPE + COMMA_SEP +
COLUMN_NAME_DUE_DATE + TEXT_TYPE + COMMA_SEP +
COLUMN_NAME_STATUS + TEXT_TYPE + COMMA_SEP +
COLUMN_NAME_RATING + REAL_TYPE + COMMA_SEP +
COLUMN_NAME_AMOUNT + REAL_TYPE + ");";
public static final String DELETE_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;
}
to:
public static abstract class TransactionTable implements BaseColumns {
public static final String TABLE_NAME = "transaction"; //?????
public static final String COLUMN_NAME_TITLE = "title";
public static final String COLUMN_NAME_PERSON = "person";
public static final String COLUMN_NAME_ROLE = "role";
public static final String COLUMN_NAME_DATE = "date";
public static final String COLUMN_NAME_DUE_DATE = "due_date";
public static final String COLUMN_NAME_STATUS = "status";
public static final String COLUMN_NAME_RATING = "rating";
public static final String COLUMN_NAME_AMOUNT = "amount";
public static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (" +
_ID + " INTEGER PRIMARY KEY," +
COLUMN_NAME_TITLE + TEXT_TYPE + COMMA_SEP +
COLUMN_NAME_PERSON + TEXT_TYPE + COMMA_SEP +
COLUMN_NAME_ROLE + TEXT_TYPE + COMMA_SEP +
COLUMN_NAME_DATE + TEXT_TYPE + COMMA_SEP +
COLUMN_NAME_DUE_DATE + TEXT_TYPE + COMMA_SEP +
COLUMN_NAME_STATUS + TEXT_TYPE + COMMA_SEP +
COLUMN_NAME_RATING + REAL_TYPE + COMMA_SEP +
COLUMN_NAME_AMOUNT + REAL_TYPE + ");";
public static final String DELETE_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;
}
Anyone knows why this is happening?
Image not loading from SQLite
In my application I am trying to take an image and display it in an ImageView, the image is being taken and added to the ImageView but not being stored or loaded into SQLite. I'm not sure why the image won't store as it does load into the ImageView.
Code to take the image:
Intent intent = new Intent(MediaStore.ACTION_IMAGE_CAPTURE);
// Take a picture and pass the image along to onActivityResult
startActivityForResult(intent, REQUEST_IMAGE_CAPTURE);
startActivityForResult():
static final int REQUEST_IMAGE_CAPTURE = 1;
callingActivity = (MainActivity) getActivity();
@Override
public void onActivityResult(int requestCode, int resultCode, Intent data) {
if (requestCode == REQUEST_IMAGE_CAPTURE && resultCode == Activity.RESULT_OK) {
// Get the photo
Bundle extras = data.getExtras();
Bitmap photo = (Bitmap) extras.get("data");
imageView.setImageBitmap(photo);
// Convert to byte array
ByteArrayOutputStream stream = new ByteArrayOutputStream();
photo.compress(Bitmap.CompressFormat.PNG, 100, stream);
byte[] byteArray = stream.toByteArray();
// Add image to db here
(callingActivity.dbManager).addImageToRecipe(byteArray, recipe);
}
}
addImageToRecipe() is a method in my DBManager class:
public void addImageToRecipe(byte[] image, Recipe recipe) {
SQLiteDatabase db = this.getWritableDatabase();
db.beginTransaction();
try {
ContentValues values = new ContentValues();
values.put(COLUMN_IMAGE_ID, image);
db.update(TABLE_RECIPES, values, COLUMN_RECIPE_NAME + "=\"" + recipe.getRecipeTitle() + "\"", null);
} finally {
db.endTransaction();
}
db.close();
}
Setting the ImageView (getImageId returns a byte[]). It never goes into the if statement in all the times I have run it, suggesting that the value is null.
if (recipe.getImageId() != null) {
Bitmap bmp = BitmapFactory.decodeByteArray(recipe.getImageId(), 0, recipe.getImageId().length);
imageView.setImageBitmap(bmp);
} else {
}
Im not sure what to do to fix it to get it to load the image into the database and back from it into the ImageView.
Show Loading Screen while multiple AJAX calls with SQlite transactions haven't finished
I have got multiple AJAX calls with SQlite transactions to add data. For some reasons one of the AJAX calls not executing but hideLoadingScreen() fires fine.
I am using this code to count callbacks:
showLoadingScreen();
var callbackCount = 0;
function ajaxCallback() {
++callbackCount;
if (callbackCount >= 3) {
hideLoadingScreen();
}
}
And three of my AJAX calls with SQlite transactions look like:
$.ajax({
url: url,
jsonp: "callback",
dataType: "jsonp",
success: function (data) {
db.transaction(function (tx) {
$.each(data.result, function (i, item) {
tx.executeSql('INSERT OR REPLACE INTO db_table (id, type, title) VALUES (?,?,?)', [item.ID, "update", item.post_title]);
});
ajaxCallback();
});
}
});
How can I make count callbacks
rake db:migrate issue duplicate but doesn't exist
I have tried db:setup db:rollback db:drop db:migrate VERSION="0" and practically everything I could think of, but I can't figure out this error. I even attempted to run it and drop the tables before initializing the create method. Even when I create a new one, like a products db, it's telling me that it's there but then it's telling me that it doesn't exist when I try to drop it. I've spent 2 days going through forums and videos to figure out what's occurring. This happens with both sqlite and mysql.
E:\RailsApplications\Blog>rake db:migrate
== 20151130191336 CreateTablenames: migrating =================================
-- create_table(:tablenames)
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:
SQLite3::SQLException: duplicate column name: ID: CREATE TABLE "tablenames" ("id
" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "ID" varchar, "col1" varchar, "col
2" varchar, "col3" varchar, "created_at" datetime NOT NULL, "updated_at" datetim
e NOT NULL) E:/RailsApplications/Blog/db/migrate/20151130191336_create_tablename
s.rb:3:in `change'
C:in `migrate'
ActiveRecord::StatementInvalid: SQLite3::SQLException: duplicate column name: ID
: CREATE TABLE "tablenames" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "I
D" varchar, "col1" varchar, "col2" varchar, "col3" varchar, "created_at" datetim
e NOT NULL, "updated_at" datetime NOT NULL)
E:/RailsApplications/Blog/db/migrate/20151130191336_create_tablenames.rb:3:in `c
hange'
C:in `migrate'
SQLite3::SQLException: duplicate column name: ID
E:/RailsApplications/Blog/db/migrate/20151130191336_create_tablenames.rb:3:in `c
hange'
C:in `migrate'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
E:\RailsApplications\Blog>rake db:migrate
== 20151130191336 CreateTablenames: migrating =================================
-- drop_table(:tablenames)
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:
SQLite3::SQLException: no such table: tablenames: DROP TABLE "tablenames"E:/Rail
sApplications/Blog/db/migrate/20151130191336_create_tablenames.rb:3:in `change'
C:in `migrate'
ActiveRecord::StatementInvalid: SQLite3::SQLException: no such table: tablenames
: DROP TABLE "tablenames"
E:/RailsApplications/Blog/db/migrate/20151130191336_create_tablenames.rb:3:in `c
hange'
C:in `migrate'
SQLite3::SQLException: no such table: tablenames
E:/RailsApplications/Blog/db/migrate/20151130191336_create_tablenames.rb:3:in `c
hange'
C:in `migrate'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
Saving Map Markers
I have a task to make a map app that puts markers on long click and display marker info on marker click. I also have to save it into SQLite. The problem is i am stuck and seems something is wrong. SQLite appeared empty after leaving app. I have no other idea how to save them. ANY help will be welcome. The targer is on app exit the markers to be saved in DB and when launched again to be reloaded and displayed on the map. Here is my code so far: MainActivity:
private GoogleMap mMap;
private JSONObject httpResponse;
private String markerCountry;
private String markerStreetAddress;
private JSONArray addressArray;
private ArrayList<Markers> markerOptionsList;
private Markers markerForDb;
private LatLng markerPosition;
private int counter=0;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_maps);
SupportMapFragment mapFragment = (SupportMapFragment) getSupportFragmentManager()
.findFragmentById(R.id.map);
mapFragment.getMapAsync(this);
}
@Override
protected void onDestroy() {
if (counter==0){
DatabaseHandler db = new DatabaseHandler(getApplicationContext(),null,null,1);
try{
for (Markers marker : markerOptionsList) {
db.addMarker(marker);
}
}catch (NullPointerException e){
e.printStackTrace();
}finally {
db.close();
}
}
super.onDestroy();
}
@Override
protected void onPause() {
if (counter==0){
DatabaseHandler db = new DatabaseHandler(getApplicationContext(),null,null,1);
try{
for (Markers marker : markerOptionsList) {
db.addMarker(marker);
}
}catch (NullPointerException e){
e.printStackTrace();
}finally {
db.close();
}
}
super.onPause();
}
@Override
protected void onStop() {
if (counter==0){
DatabaseHandler db = new DatabaseHandler(getApplicationContext(),null,null,1);
try{
for (Markers marker : markerOptionsList) {
db.addMarker(marker);
}
}catch (NullPointerException e){
e.printStackTrace();
}finally {
db.close();
}
}
super.onStop();
}
@Override
public void onMapReady(GoogleMap googleMap) {
mMap = googleMap;
mMap.setMyLocationEnabled(true);
mMap.setOnMapLongClickListener(this);
mMap.setOnMarkerClickListener(this);
markerOptionsList = new ArrayList<>();
}
@Override
public void onMapLongClick(LatLng latLng) {
markerPosition = latLng;
mMap.addMarker(new MarkerOptions().position(markerPosition));
getAddress(latLng);
AsyncSaveToDb async2 = new AsyncSaveToDb();
async2.execute(latLng);
}
private void getAddress(LatLng latLng) {
URL url = null;
try {
url = new URL("http://ift.tt/1kDofho" + latLng.latitude + "," + latLng.longitude + "&key=AIzaSyBWH6gioY47pZjtCy-lz3rmyYNKVBOZnaA");
} catch (MalformedURLException e) {
e.printStackTrace();
}
Async async = new Async();
async.execute(url);
}
@Override
public boolean onMarkerClick(Marker marker) {
for (Markers markers :markerOptionsList) {
double lat = markers.get_lat();
double lng = markers.get_lng();
LatLng latlng = new LatLng(lat,lng);
if (latlng.equals(marker.getPosition())){
Intent intent = new Intent(getApplicationContext(),com.example.fixxxer.mapclicker.MarkerInfoPage.class);
String addressForText = markers.get_address();
String countryForText = markers.get_country();
String locationForText = markers.get_lat() + ", " + markers.get_lng();
intent.putExtra("address", addressForText);
intent.putExtra("country", countryForText);
intent.putExtra("location", locationForText);
startActivity(intent);
}
}
return true;
}
public class Async extends AsyncTask<URL, Void, JSONArray> {
@Override
protected JSONArray doInBackground(URL... params) {
BufferedReader reader;
InputStream inputStream;
JSONArray results = null;
try {
StringBuilder responseBuilder = new StringBuilder();
HttpURLConnection conn = (HttpURLConnection) params[0].openConnection();
conn.setReadTimeout(10000);
conn.setConnectTimeout(15000);
conn.setRequestMethod("GET");
conn.setDoInput(true);
conn.connect();
inputStream = conn.getInputStream();
reader = new BufferedReader(new InputStreamReader(inputStream));
for (String line; (line = reader.readLine()) != null; ) {
responseBuilder.append(line).append("\n");
}
httpResponse = new JSONObject(responseBuilder.toString());
results = httpResponse.getJSONArray("results");
inputStream.close();
reader.close();
} catch (IOException | JSONException e) {
e.printStackTrace();
}
return results;
}
@Override
protected void onPostExecute(JSONArray resultList) {
addressArray = resultList;
JSONArray responseComponents;
try {
if (addressArray != null) {
JSONObject firstResult = addressArray.getJSONObject(0);
if (firstResult != null) {
responseComponents = firstResult.getJSONArray("address_components");
if (responseComponents != null) {
for (int i = 0; i < responseComponents.length(); i++) {
JSONObject currentComponent = responseComponents.getJSONObject(i);
if (currentComponent.getString("types").contains("country")) {
markerCountry = currentComponent.getString("long_name");
markerStreetAddress = firstResult.getString("formatted_address");
if (markerStreetAddress==null||markerStreetAddress.isEmpty()){
markerStreetAddress="No address available for this location";
}else{
String[] split = markerStreetAddress.split(",");
markerStreetAddress="";
for (int j =0;j<split.length-1;j++){
markerStreetAddress+=split[j];
}
}
}
}
}
}
}
} catch (JSONException e) {
e.printStackTrace();
}
if (markerStreetAddress==null||markerStreetAddress.isEmpty()) {
markerStreetAddress = "No address available for this location";
}
Toast.makeText(getApplicationContext(), "Marker added", Toast.LENGTH_SHORT).show();
}
}
public class AsyncSaveToDb extends AsyncTask<LatLng, Void, Markers>{
@Override
protected Markers doInBackground(LatLng... params) {
markerForDb = new Markers();
markerForDb.set_address(markerStreetAddress);
markerForDb.set_country(markerCountry);
markerForDb.set_lat(params[0].latitude);
markerForDb.set_lng(params[0].longitude);
markerForDb.set_image("image");
return markerForDb;
}
@Override
protected void onPostExecute(Markers markers) {
markers = markerForDb;
markerOptionsList.add(markerForDb);
super.onPostExecute(markers);
}
}
DatabaseHelper class:
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "locations.db";
public static final String TABLE_LOCATIONS = "locations";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_ADDRESS = "_address";
public static final String COLUMN_COUNTRY = "_country";
public static final String COLUMN_LAT = "_lat";
public static final String COLUMN_LNG = "_lng";
public static final String COLUMN_IMAGE = "_image";
public DatabaseHandler(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_LOCATIONS + "(" +
COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
COLUMN_ADDRESS + " TEXT, " +
COLUMN_COUNTRY + " TEXT, " +
COLUMN_LAT + " REAL, " +
COLUMN_LNG + " REAL, " +
COLUMN_IMAGE + " TEXT " +
");";
db.execSQL(query);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_LOCATIONS);
onCreate(db);
}
public void addMarker(Markers marker) {
ContentValues values = new ContentValues();
values.put(COLUMN_ADDRESS, marker.get_address());
values.put(COLUMN_COUNTRY, marker.get_country());
values.put(COLUMN_LAT, marker.get_lat());
values.put(COLUMN_LNG, marker.get_lng());
values.put(COLUMN_IMAGE, marker.get_image());
SQLiteDatabase db = getWritableDatabase();
db.insert(TABLE_LOCATIONS, null, values);
db.close();
}
public void deleteMarker(int id) {
SQLiteDatabase db = getWritableDatabase();
db.execSQL("DELETE FROM " + TABLE_LOCATIONS + " WHERE " + COLUMN_ID + "=\"" + id + "\";");
}
public ArrayList<Markers> getExistingMarkers() {
ArrayList<Markers> list = new ArrayList<>();
SQLiteDatabase db = getWritableDatabase();
String query = "SELECT * FROM " + TABLE_LOCATIONS;
Cursor curs = db.rawQuery(query, null);
curs.moveToFirst();
while (!curs.isAfterLast()) {
Markers marker = new Markers(
curs.getString(1),
curs.getString(2),
Double.parseDouble(curs.getString(3)),
Double.parseDouble(curs.getString(4)),
curs.getString(5));
list.add(marker);
}
curs.close();
return list;
}
public Markers getOneMarker(double lat, double lng) {
SQLiteDatabase db = this.getReadableDatabase();
String query = "SELECT * FROM " + TABLE_LOCATIONS + " WHERE " + COLUMN_LAT + "=?" + " AND " + COLUMN_LNG + "=?";
Cursor curs = db.rawQuery(query, new String[]{String.valueOf(lat), String.valueOf(lng)});
if (curs != null)
curs.moveToFirst();
Markers marker = null;
if (curs != null) {
try{
marker = new Markers(curs.getString(1), curs.getString(2), Double.parseDouble(curs.getString(3)), Double.parseDouble(curs.getString(4)), curs.getString(5));
}catch (CursorIndexOutOfBoundsException e){
e.printStackTrace();
}
}
if (curs != null) {
curs.close();
}
return marker;
}
public Markers getfirstMarker() {
SQLiteDatabase db = this.getReadableDatabase();
Markers marker=null;
String query = "SELECT * FROM " + TABLE_LOCATIONS + " LIMIT 1";
Cursor curs = db.rawQuery(query, null);
if (curs != null) {
curs.moveToFirst();
try {
marker = new Markers(curs.getString(1), curs.getString(2), Double.parseDouble(curs.getString(3)), Double.parseDouble(curs.getString(4)), curs.getString(5));
} catch (CursorIndexOutOfBoundsException e) {
e.printStackTrace();
}
}
return marker;
}
DB entity is standard:
private int _id;
private String _address;
private String _country;
private double _lat;
private double _lng;
private String _image;
public Markers(int _id, String _address, String _country, double _lat, double _lng, String _image) {
this._id = _id;
this._address = _address;
this._country = _country;
this._lat = _lat;
this._lng = _lng;
this._image = _image;
}
public Markers(String _address, String _country, double _lat, double _lng, String _image) {
this._address = _address;
this._country = _country;
this._lat = _lat;
this._lng = _lng;
this._image = _image;
}
public Markers() {
}
public int get_id() {
return _id;
}
public void set_id(int _id) {
this._id = _id;
}
public String get_address() {
return _address;
}
public void set_address(String _address) {
this._address = _address;
}
public String get_country() {
return _country;
}
public void set_country(String _country) {
this._country = _country;
}
public double get_lat() {
return _lat;
}
public void set_lat(double _lat) {
this._lat = _lat;
}
public double get_lng() {
return _lng;
}
public void set_lng(double _lng) {
this._lng = _lng;
}
public String get_image() {
return _image;
}
public void set_image(String _image) {
this._image = _image;
}
And finally MarkersInfoPage:
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_marker_info_page);
TextView addressText = (TextView) findViewById(R.id.addressView);
TextView countryText = (TextView) findViewById(R.id.countryView);
TextView position = (TextView) findViewById(R.id.latlngText);
String address = getIntent().getStringExtra("address");
String country =getIntent().getStringExtra("country");
String location =getIntent().getStringExtra("location");
addressText.setText(address);
countryText.setText(country);
position.setText(location);
Button backButton = (Button) findViewById(R.id.backButton);
backButton.setOnClickListener(this);
}
@Override
public void onClick(View v) {
super.finish();
}
I am stuck here for a long time. Any help is priceless.
SQLite returning "no such table" using FMDB and RTree on iOS
I am building an iOS app and using SQLite using R*Tree indexing and FMDB for client-side data. When I run executeQuery against one of my virtual tables SQLite is returning DB Error: 1 "no such table: HuntEvent_index". However, I am able to insert records into the virtual table without any trouble (see code and image below) so I know it exists. Here's my code:
Table creation
CREATE VIRTUAL TABLE IF NOT EXISTS HuntEvent_index USING rtree( ID, minX, maxX, minY, maxY );
CREATE TABLE IF NOT EXISTS "HuntEvent_index_rowid"(rowid INTEGER PRIMARY KEY, nodeno INTEGER);
CREATE TABLE IF NOT EXISTS "HuntEvent_index_parent"(nodeno INTEGER PRIMARY KEY, parentnode INTEGER);
CREATE TABLE IF NOT EXISTS "HuntEvent_index_node"(nodeno INTEGER PRIMARY KEY, data BLOB);
Insert statement
INSERT INTO HuntEvent_index VALUES
(1,SomeValue,SomeValue,SomeValue,SomeValue)
As I said, I am confident that both the CREATE statement and the INSERT statement are functioning properly because I can see the table and record inside the database I created on the device (Actual values removed):
Query method
-(void)getEventsWithinBoundingBox:(WSBoundingBox*)boundingBox
withCompletion:(SearchCompletionBlock)completion
{
FMDatabase* db = [[WSSQLiteHelper sharedHelper] getFMAppDB];
if (![db openWithFlags:SQLITE_OPEN_READONLY]) {
completion(nil, [NSError errorWithDomain:@"WSHP"
code:0
userInfo:[NSDictionary dictionaryWithObject:@"Could not open User Database."
forKey:@"message"]]);
}
//Find events IDs within the bounding box
//
NSMutableArray *activeEventIDs = [NSMutableArray array];
NSMutableString* query = [NSMutableString string];
[query appendString:@"SELECT ID FROM HuntEvent_index "];
[query appendFormat:@"WHERE minX >= %f AND maxX <= %f ",
[[boundingBox.boundingBoxDictionary objectForKey:kBoundingBoxxMinFieldName] doubleValue],
[[boundingBox.boundingBoxDictionary objectForKey:kBoundingBoxxMaxFieldName] doubleValue]];
[query appendFormat:@"AND minY >= %f AND maxY <= %f",
[[boundingBox.boundingBoxDictionary objectForKey:kBoundingBoxyMinFieldName] doubleValue],
[[boundingBox.boundingBoxDictionary objectForKey:kBoundingBoxyMaxFieldName] doubleValue]];
FMResultSet* rs = [db executeQuery:query];
while ([rs next])
{
[activeEventIDs addObject:[NSNumber numberWithInt:[rs intForColumnIndex:0]]];
}
[rs close];
[db close];
if ([activeEventIDs count] == 0)
{
completion(nil, [NSError errorWithDomain:@"WSHP"
code:1
userInfo: [NSDictionary dictionaryWithObject:@"No events found within region."
forKey:@"message"]]);
return;
}
//Code continues...
So why is SQLite returning the no such table error? I am following the example from the SQLite R*Tree article as far as I can tell. Am I missing the obvious here?
Unable to pass image URI, Values are null for Insert function
i have a function which is taking image path from the gallery and set it in the text view so that i can send it to my other DB_class for insert function. but i am not able to send it As it seem to have problem with my setter or getter functions.. please have a look on the code and LogCat below its been days now and i have seen so many related problems but none of their solution worked.
class imagePath{
public void onActivityResult(int requestCode, int resultCode, Intent data) {
super.onActivityResult(requestCode, resultCode, data);
String realPath;
//FOR IMAGE PATH SAVING
if (requestCode == RESULT_LOAD_IMAGE && resultCode == RESULT_OK && null != data) {
String selectedImagePath;
Uri selectedImageUri = data.getData();
//MEDIA GALLERY
selectedImagePath = ImageFilePath.getPath(getApplicationContext(), selectedImageUri);
Log.i("Image-->File Path-->", " " + selectedImagePath);
setImgTextViews(Build.VERSION.SDK_INT, selectedImagePath);
SET_saveImg(selectedImagePath);\\send it setter_metthod
}
else if(Build.VERSION.SDK_INT < 19)
{
realPath = this.getRealPathFromURI_FromAPI11_18(this, data.getData());
setImgTextViews(Build.VERSION.SDK_INT, realPath);
}
//----------->>>>>>>>>>>---------->>>>FOR VIDEO PATH SAVING------>>>>>>------------>>>>>>>>>>>-------->>>>>>>>>---------->>>>
if (requestCode == RESULT_LOAD_VIDEO && resultCode == RESULT_OK && null != data)
{
//FOR VIDEO PATH SAVING
String selectedVidPath;
Uri selectedVidUri = data.getData();
//VIDEO____PLAY___LIST
selectedVidPath = ImageFilePath.getPath(getApplicationContext(), selectedVidUri);
Log.i("VIDEO-->File Path-->", "" + selectedVidPath);
setVIDTextViews(Build.VERSION.SDK_INT, selectedVidPath);
SET_saveVid(selectedVidPath);\\ send it setter_metthod
}
}
//---->>------>>>------->>>>SETTERS--FOR IMAGE-->SET_saveImg(selectedImagePath)For&VIDEO-->>SET_saveVid(selectedVidPath)--->>AND--->>GETTERS FOR IMAGE AND VIDEO--------->>>>>
String realPath; String vidPath;
public String GET_saveImg()//IS CALLED FROM DATABASE_HELPER CLASS TO PROVIDE THE SETTER VALUES
{
return realPath;
}
public void SET_saveImg(String realPath)
{
this.realPath=realPath;
}
public String GET_saveVid()
{
return vidPath;
}
public void SET_saveVid(String vidPath)
{
this.vidPath= vidPath;
}
}
class DatabasHelper extends SqliteOpenHelper{
SQLiteDatabase db; User_overlay_save obj = new User_overlay_save();
User_overlay_save obj1 = new User_overlay_save() ;
// Database Name
public static String DATABASE_NAME = "MY_database";
// Current version of database
private static final int DATABASE_VERSION = 1;
// Name of table
private static final String TABLE_Images = "images";
// All Keys used in table
static final String KEY_ID = "id";
static final String KEY_IMAGE = "image";
static final String KEY_FILE = "file";
public static String TAG = "tag";
private static final String CREATE_TABLE_Images = "CREATE TABLE "
+ TABLE_Images + "(" + KEY_ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_IMAGE + " TEXT," + KEY_FILE + " TEXT" +");";
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
/*This method is called by system if the database is accessed but not yet created*/
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_TABLE_Images); // create Image table
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_Images); // drop table if exists
onCreate(db);
}
public boolean Insert() throws SQLException {
//boolean success = false;
String imgpath = obj.GET_saveImg();
String vidPath = obj1.GET_saveVid();
Log.i("IMAGE-->Path from DB-->", "" + imgpath);\\ i added these lines so i would know if my values are being transferred from one activity to another
Log.i("VIDEO-->File Path-->", "" + vidPath);
db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_IMAGE, imgpath);
values.put(KEY_FILE, vidPath);
db.insert(TABLE_Images, null, values);
// success=true;
db.close();
return true;}}
---------------------------LOG--CAT------------------------------------------
11-30 20:42:01.687 26765-26765/com.example.sanya.ar_expeditor W/IInputConnectionWrapper: showStatusIcon on inactive InputConnection
11-30 20:42:05.549 26765-26765/com.example.sanya.ar_expeditor I/Image-->File Path-->: /storage/sdcard1/Download/aries.png
11-30 20:42:05.601 26765-26765/com.example.sanya.ar_expeditor I/Timeline: Timeline: Activity_idle id: android.os.BinderProxy@94939d4 time:24961584
11-30 20:42:07.939 26765-26765/com.example.sanya.ar_expeditor W/IInputConnectionWrapper: showStatusIcon on inactive InputConnection
11-30 20:42:10.645 26765-26765/com.example.sanya.ar_expeditor I/VIDEO-->File Path-->: /storage/sdcard1/DCIM/100ANDRO/MOV_1035.mp4
11-30 20:42:10.702 26765-26765/com.example.sanya.ar_expeditor I/Timeline: Timeline: Activity_idle id: android.os.BinderProxy@94939d4 time:24966685
11-30 20:42:11.713 26765-26765/com.example.sanya.ar_expeditor I/IMAGE-->Path from DB-->: null
11-30 20:42:11.713 26765-26765/com.example.sanya.ar_expeditor I/VIDEO-->File Path-->: null
11-30 20:42:14.799 26765-26765/com.example.sanya.ar_expeditor W/IInputConnectionWrapper: showStatusIcon on inactive InputConnection
c++/Qt: How to get a reference to database in the MainWindow class?
In Qt-creator, I created SQLite database in a class called databaseManager, as follow:
QString DatabaseManager::open_db()
{
QSqlDatabase db;
QString path = "/Users/me/Documents/workspace/Muasaa/";
db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(path+"Database v.1");
if (db.open()){
return "Database is created, open, and ready ...";
} else {
return db.lastError().text();
}
}
Then I define the following in the header file of the MainWindow class:
Public:
DatabaseManager *db_manager;
In the source file, I call it as follow:
db_manager->open_db();
which creates and open the database.
However, I would like to use a reference to same database to use it in many functions in the MainWindow source file. How can I do that ?!
Correct Date to appear in JSpinner and data formatted correctly to insert to database
I am making a employee scheduling program for a school project. The employee will be able to make their availability on a weekly basis. I am having trouble making a JSpinner show the date for a Monday of each week. Also, when the user clicks up or down on the arrows, I would like it to only cycle for the dates of upcoming or prior Mondays. When inserting the data into a database i would like the start and finish times of each day to be in Military time. For the week, i would like it to just be the number of the week of the year. When it is inserted into the database, the week is a primary key and should stay constant with the same week, i.e. two employees should be able to create availability for week 5. The employee ID is also a primary and foreign key. The weekID in the database is being created in ascending order i.e. 1,2,3. Start and End times are being shown as this: Mon Nov 30 09:19:29 CST 2015 when I want it to just show as 09:19.
here is the code for the week spinner:
Date datef = new Date();
Calendar c = Calendar.getInstance();
c.setFirstDayOfWeek(Calendar.MONDAY);
SpinnerDateModel ehm = new SpinnerDateModel(datef, null, null,
c.WEEK_OF_YEAR);
weekOf = new javax.swing.JSpinner(ehm);
JSpinner.DateEditor wo = new JSpinner.DateEditor(weekOf,"MM/dd/yyyy");
weekOf.setEditor(wo);
weekOf.setModel(new javax.swing.SpinnerDateModel());
The code for the time spinner is as follows(the times show correctly in these spinners, but just the output isn't correct)
Date date = new Date();
SpinnerDateModel sm = new SpinnerDateModel(date, null, null, Calendar.HOUR_OF_DAY);
monStart = new javax.swing.JSpinner(sm);
JSpinner.DateEditor ms = new JSpinner.DateEditor(monStart,"H:mm");
monStart.setEditor(ms);
monStart.setModel(new javax.swing.SpinnerDateModel());
That is how i have the code set for all of the time selectors, with different variables obviously.
here is the code for the action performed:
public void btnAvailUpdateActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnAvailUpdateActionPerformed
Date weekID = (Date) weekOf.getValue();
Date mstart = (Date) monStart.getValue();
Date mend = (Date) monEnd.getValue();
Date tstart = (Date) tuesStart.getValue();
Date tend = (Date) tuesEnd.getValue();
Date wstart = (Date) wedStart.getValue();
Date wend = (Date) wedEnd.getValue();
Date thstart = (Date) thursStart.getValue();
Date thend = (Date) thursEnd.getValue();
Date fstart = (Date) friStart.getValue();
Date fend = (Date) friEnd.getValue();
try {
PreparedStatement st = conn.prepareStatement("INSERT INTO Availability (Week_ID, Employee_ID, monStart, monFinish, tuesStart, tuesFinish, wedStart, wedFinish, thursStart, thursFinish, friStart, friFinish) Values(?,?,?,?,?,?,?,?,?,?,?,?)");
st.setString(1, String.valueOf(weekID));
st.setInt(2, loginScreen.dbid);
st.setString(3, String.valueOf(mstart));
st.setString(4, String.valueOf(mend));
st.setString(5, String.valueOf(tstart));
st.setString(6, String.valueOf(tend));
st.setString(7, String.valueOf(wstart));
st.setString(8, String.valueOf(wend));
st.setString(9, String.valueOf(thstart));
st.setString(10, String.valueOf(thend));
st.setString(11, String.valueOf(fstart));
st.setString(12, String.valueOf(fend));
st.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
//System.out.println(weekID);
}//GN-LAST:event_btnAvailUpdateActionPerformed
Any help or suggestions would be greatly appreciated.
Do i need to change code in rails when i migrate database from sqlite to Postgresql or someother
I am new to ruby on rails framework and build some Blogs,portfolio like applications using rails.For that i used sqlite as my database. I would like to create a scalable applications and though sqlite is a lightweight database. I would like to change the database as postgresql , Mysql and mongodb etc.
So my question is if i change the database ,did i need to change rails code or it will be same as using sqlite?
How to set SQLite isolation levels, using Python
I know (or at least, I think that I know), that in standard there are four isolation levels when dealing with transactions:
READ UNCOMMITTED - will allow everything
READ COMMITTED - will not allow dirty reads
REPEATABLE READ - will not allow dirty, non-repearable reads
SERIALIZABLE - will not allow dirty, non-repearable, phantom reads
I know that and, for example, when dealing with MySQL I can do something like:
cursor = db.cursor()
cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")
Or, if I'm dealing with Postgre, I can do something like:
db.set_isolation_level(3) # corresponds to SERIALIZABLE
So, I wonder, if I can do something similar to that when dealing with SQLite. I've only seen:
db.isolation_level = None
but I'm not sure what it means and how I can set other isolation levels (if they exist in the context of SQLite). Thanks!
Handling fairly large amounts of data over multiple fragments to read and write from a Sqlite DB in Android
Looking for some advice on the best way to go about this as I'm new to this.
I have an android app that is basically a huge form split over around 30 fragments. I have approximately 800 fields split over these fragments that I need to output to a database.
I need to store these forms on the device so they can open up previous ones and these will of course then populate from the database and when saved will update the db. I've decided to go down the Sqlite route rather than the content provider.
I have two activities, my main which is a list of forms on the device and the second is the edit form activity which uses a ViewPager to navigate the fragments.
So my question is, how am I best going about getting the data to save/update from the fragments? I know I need to use an interface and I was thinking that each fragment will need an interface to pass the values from the editText fields (using getText and findViewbyID) through to the edit form activity which will have the method for the the save button which will then pass through all the variables to the Update query sitting on the dbHelper class.
Does this seem the right way to do things or does anyone have a better suggestion? Seems like this will be quite messy.
Thanks,
How to convert EditText field to a DateTime in Android?
I'm building an application in Android with Xamarin and I have a problem.. I want to convert an EditText field string to a DateTime. I'm using a SQLITE database. I have this code:
btnSave.Click += (object IntentSender, EventArgs e) => {
ConcertDB cdb = new ConcertenDB();
Concert dataInput = new Concerten();
dataInput.Date= Convert.ToDateTime(dt);
cdb.insertUpdateData(dataInput);
};
database helper not able to create database
I am a beginner to android.
While trying to implement a SQLite database, I'm unable to insert into the database.
I'm also unable to figure out what is wrong with my database helper?
here is my helper class code with methods for insering and finding a user
package com.example.tilmac.dbsql;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* Created by TILMAC on 28-11-15.
*/
public class DatabaseHelper extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "User.db";
private static final String TABLE_NAME = "users"; //final variables
private static final String COLUMN_NAME = "name";
private static final String COLUMN_EMAIL = "email";
private static final String COLUMN_UNAME = "uname";
private static final String COLUMN_PASS = "pass";
SQLiteDatabase db;
public DatabaseHelper(Context context) //Constructor
{
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
The CREATE TABLE statement is hardcoded:
private static final String TABLE_CREATE = "create table users (name text not null, email text not null, uname text not null, pass text not null)";
I get an error which says Column not found while inserting into database
public void insertUser(Contact c){
db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(COLUMN_NAME, c.getName());
values.put(COLUMN_EMAIL, c.getEmail());
values.put(COLUMN_UNAME, c.getUname());
values.put(COLUMN_PASS, c.getPass());
db.insert(TABLE_NAME, null, values);
db.close();
}
public String findPass(String s){
db = this.getReadableDatabase();
String query = "select uname, pass from "+TABLE_NAME;
Cursor cursor = db.rawQuery(query, null);
String a, b="not found";
if(cursor.moveToFirst())
{
do{
a = cursor.getString(0);
if(a.equals(s))
{
b=cursor.getString(1);
break;
}
}while(cursor.moveToNext());
}
return b;
}
@Override
public void onCreate(SQLiteDatabase db) { //OnCreate method
db.execSQL(TABLE_CREATE);
this.db = db;
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { //onUpgrade Method
String query = "DROP TABLE IF EXISTS " + TABLE_NAME;
db.execSQL(query);
this.onCreate(db);
}
}
//and here is my main activity
package com.example.tilmac.dbsql;
import android.content.Context;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.Toolbar;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;
/**
* Created by TILMAC on 27-11-15.
*/
public class RegisterActivity extends AppCompatActivity {
DatabaseHelper helper = new DatabaseHelper(this);
@Override
protected void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_register);
///Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
//setSupportActionBar(toolbar);
}
public void onRBClick(View v){
if(v.getId()==R.id.Rbutton)
{
EditText n = (EditText)findViewById(R.id.RETname);
EditText e = (EditText)findViewById(R.id.RETemail);
EditText u = (EditText)findViewById(R.id.RETuname);
EditText p1 = (EditText)findViewById(R.id.RETpass);
EditText p2 = (EditText)findViewById(R.id.RETrepass);
String name, email, uname, pass, repass;
name = n.getText().toString();
email = e.getText().toString();
uname = u.getText().toString();
pass = p1.getText().toString();
repass = p2.getText().toString();
if(pass.equals(repass))
{
Contact c = new Contact();
c.setName(name);
c.setEmail(email);
c.setUname(uname);
c.setPass(pass);
helper.insertUser(c);
}
else
{
Context context = getApplicationContext();
Toast toast = Toast.makeText(context, "Passwords dont match", Toast.LENGTH_SHORT);
toast.show();
}
}
}
}
ActiveAndroid foreign key query returns empty, manual selection works correctly
So I have quite a complex SQL Structure, but for a small example:
@Table(name = "Events", id = "_id")
public class DBEvent extends Model implements Event {
@Column(name = "Id", unique = true)
protected Long id;
@Column(name = "name")
protected String name;
@Column(name = "start_time")
protected Date startTime;
@Column(name = "end_time")
protected Date endTime;
@Column(name = "version")
protected int version = -1;
@Column(name = "visited")
protected boolean visited = false;
@Column(name = "venue")
private DBVenue venue;
}
@Table(name = "Surveys", id = "_id")
public class DBSurvey extends Model implements Versionable, Dateable, Survey {
@Column(name = "Id", unique = true)
protected Long id;
@Column(name = "name")
protected String name;
@Column(name = "start_time")
protected Date startTime;
@Column(name = "end_time")
protected Date endTime;
@Column(name = "version")
protected int version = -1;
@Column(name = "done")
protected boolean done = false;
@Column(name = "scheduled")
protected boolean scheduled = false;
}
The following Query always returns empty. And it's only with this model, my other model works fine.
List<DBSurvey> surveys = new Select().from(DBSurvey.class).where("event=?", id).execute();
However, if I do:
List<DBSurvey> surveys = new ArrayList<>();
for (DBSurvey survey : EntityUtil.listAll(DBSurvey.class)) {
if (survey.getEvent().getEntityId() == this.getEntityId()) {
surveys.add(survey);
}
}
return surveys;
I get the correct objects. Any help is appreciated. I realize that event is a keyword in regular sql, however it is not in SQLLite and I have changed the name to event_id to no avail.
how to update primary key with violating values
say i have table collection, with primary key (col_id, item_id). item_id is a value which ideally should come from server mapping, but until that value is generated from server, we use a local unique value. however, upon mapping, there are possibility that multiple local_ids match with single remote_id. now I want to update those value in table, but that would fail the primary key constraint. how this can be achieved while keeping position of at least 1 item intact (i don't want to remove and add at the end)
Select from table based on count
I have table like this:
id | name | type
-----------------
0 | firs | 2
1 | secs | 3
2 | this | 9
1 | thus | 3
I know id (it is not unique id) and type and I want to select records only if there is specified number of records with that id and type.
For one record I tried for example this:
select * from myTable
where
(select count(*) from myTable where myTable.id = 0 and myTable.type = 2) = 1;
This returns me all rows, not just the one row I want. Can anyone please tell me, what is the right way how to get the right result?
Inscription à :
Articles (Atom)