mercredi 22 avril 2015

Date and Time formatting from sqlite database

I have formatted the timepicker and Datepicker data into the following formats hh:mm AM/PM and dd/MM/yyyy, and stored both the variables into sqlite database.

Now, in another activity I need to retrieve these values from the database, and pass a pendingintent alarm through alarmmanager.

My problem is that I am getting these values in the activity from database, but I can't figure out how to compare these values to current calendar time, and pass the pendingIntent like cal.getTimeInMillis().

I tried using SimpleDateFormat, but it catches ParseException: Unparseable Date

Can someone help me out with this problem? Thanks in advance

getting value from sqltable into string

i NEED fetch the data from the database and then i need to compare it with other string i am not able to assign the value to an string

NEED TO FETCH THE 5555 AND STRORE INTO ANY STRING OR INTEGER

HOW TO INSERT THE FETCHED VALUE INTO STRING

SQLiteDatabase db =openOrCreateDatabase("mydb",MODE_PRIVATE,null); db.execSQL("CREATE TABLE IF NOT EXIST DATA(ID NUMBER,PASS NUMBER);"); db.execSQL("INSERT INTO DATA VALUES(1,5555);");

db.execSQL("SELECT PASS FROM DATA WHERE ID=1;");

Change value of column to a value from a foreign column

Hello my table looks like this:

CREATE TABLE my_eav(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  k TEXT NOT NULL,
  v TEXT,
  v_link INTEGER,
  UNIQUE(k, v) ON CONFLICT REPLACE,
  FOREIGN KEY(v_link) REFERENCES posts(id) ON UPDATE ???
);

v_link is the id column of another table, "posts". When it's not null, the "v" column should take the value of the "title" column from "posts".

When the referenced post record is updated, I'd like to change the "v" column on this table to the "title" column of the posts table. Is that possible?

Something like

... ON UPDATE SET v = posts.title

but of course this doesn't work :(

I made this trigger:

CREATE TRIGGER my_trigger
AFTER INSERT ON my_eav
WHEN v_link IS NOT NULL
BEGIN
  UPDATE my_eav SET v = (SELECT title
              FROM posts
              WHERE id = v_link);
END;

But I don't know if it's correct

SQLite elements in CREATE VIEW duplicating

This is my view which puts together columns from 3 tables

        db.execSQL("CREATE VIEW " + viewComps +
            " AS SELECT " + COMPANY + "." + colCompID + " AS _id," +
            " " + ACCOUNTS + "." + colName + "," +
            " " + COMPANY + "." + colCompClass + "," +
            " " + PAYMENTS + "." + colGroupID + "," +
            " " + PAYMENTS + "." + colPayDue + "," +
            " " + PAYMENTS + "." + colDateDue + "" +
            " FROM " + PAYMENTS + ", " + COMPANY +
            " JOIN " + ACCOUNTS + " ON " + PAYMENTS + "." + colGroupID + " = " + ACCOUNTS + "." + colID );

PROBLEM

  1. I have 3 companies A, B and C
  2. Acc 1 is assigned to A, 1 Acc = 1 Company ONLY
  3. Acc 1 is inserted and assigned to A, problem is even if I assign it to A it will be duplicated for B and C as well.

The Result:

  Acc 1 | Company A | Payment | Date
  Acc 1 | Company B | Payment | Date
  Acc 1 | Company C | Payment | Date

What it should be :

  Acc 1 | Company A | Payment | Date

Every other account I insert into the database does the same and results in my companies containing a duplicate of all the accounts irregardless of which company the account is assigned to.

QUESTION

What am I doing wrong with my VIEW? I don't understand why it duplicates all the entries putting a copy of each account in each company. Can someone show me where my mistake is? I'm fairly new to this and could use a few pointers in this area. I'm fairly confident it's just a problem with this view as I have another activity that displays accounts based on company and it all works fine there at least.

I'll leave the 3 tables the VIEW references down below in case it's needed:

    db.execSQL("CREATE TABLE " + COMPANY + " (" + colCompID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            colCompClass + " TEXT)");

    db.execSQL("CREATE TABLE " + ACCOUNTS + " (" + colID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            colName + " TEXT, " +
            colComp + " INTEGER NOT NULL," +
            colAmount + " INTEGER, " +
            colPurpose + " TEXT, " +
            colTerms + " INTEGER NOT NULL, " +
            colPeriod + " INTEGER NOT NULL, " +
            colBalance + " INTEGER, "+
            colStatus + " INTEGER NOT NULL," +
            colDate + " TEXT, " +
            colEditDate + " TEXT, " +
            colRemarks + " TEXT, " +
            "FOREIGN KEY (" + colComp + ") REFERENCES " + COMPANY + " (" + colCompID + " )" + "ON DELETE CASCADE," +
            "FOREIGN KEY (" + colTerms + ") REFERENCES " + TERMS + " (" + colTermsID + " )" + "ON DELETE CASCADE," +
            "FOREIGN KEY (" + colPeriod + ") REFERENCES " + PERIODS + " (" + colPeriodID + ") " + "ON DELETE CASCADE,"+
            "FOREIGN KEY (" + colStatus + ") REFERENCES " + STATUS + " (" + colStatusID + ") ON DELETE CASCADE);");

    db.execSQL("CREATE TABLE " + PAYMENTS + " (" + colPayID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            colGroupID + " INTEGER NOT NULL, " +
            colPayBal + " TEXT, " +
            colInterest + " TEXT, " +
            colPayDue + " TEXT, " +
            colDateDue + " TEXT, " +
            colPayDate + " TEXT, " +
            "FOREIGN KEY (" + colGroupID + ") REFERENCES " + ACCOUNTS + " (" + colID + ") ON DELETE CASCADE);");

How to get total number of rows/records in an sqlite table

i want to know how to get the total number of rows/records in an sqlite table and assign it to a variable. I did this;

    QSqlQuery query("SELECT COUNT(*) FROM class1_bills");

But the problem is, how do i assign the result to a variable? I tried;

int rows = query.seek(0);

and;

int rows = query.value(0).toInt();

Yes, I know doing that accesses just the record at field position 0 in the table. But it seems Qt's query methods are for accessing particular records at field positions only. If i'm wrong please correct me. So how do i get the total row count in the table?

SQLAlchemy: how exchange unique values in one transaction?

My versions:

$ python
Python 3.4.0 (default, Apr 11 2014, 13:05:11) 
[GCC 4.8.2] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy
>>> sqlalchemy.__version__
'1.0.0'

Let's look to the example for sqlite (in production I use mysql, but it does not matter here):

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Integer, String

Base=declarative_base()
engine = create_engine('sqlite:///:memory:', echo=True)

class User(Base):
   __tablename__ = 'user'
   id      = Column(Integer, primary_key=True)
   name    = Column(String(16))
   tech_id = Column(Integer, unique=True, nullable=True)

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
s = Session()

Now we add two records:

u1=User(name="User1", tech_id=None)
u2=User(name="User2", tech_id=10)
s.add(u1)
s.add(u2)
s.commit()

Next try to modify them:

u1=s.query(User).filter(User.name=="User1").first()
u2=s.query(User).filter(User.name=="User2").first()

u2.tech_id=None
u1.tech_id=10
s.commit()

After commit I've got the exception:

<-- cut -->
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: user.tech_id [SQL: 'UPDATE user SET tech_id=? WHERE user.id = ?'] [parameters: ((10, 1), (None, 2))]

If I do like this:

u2.tech_id=None
s.commit()
u1.tech_id=10
s.commit()

It's all right.

Is it possible to do requests by only one commit (by only one transaction)?

How to add information to a sqlite database and then display the information using javafx?

public static void addToQueueTable(Patient p) {

    try {

        // open connection to database
        conn = ConnectionFactory.getConnection();

        // create statement
        stmt = conn.createStatement();

        String sql = "insert into queueTime values('" + p.getNhsNumber()
                + "', CURRENT_TIMESTAMP)";

        // execute update
        stmt.executeUpdate(sql);

    } catch (Exception e) {
        e.printStackTrace();
        System.out.println("Error on Building Data");
    } finally {
        // close all open resources in the database
        DbUtil.close(stmt);
        DbUtil.close(conn);
    }

}

The above is my code to add details (NhsNumber and Current timestamp) to my database - just wondering how I then display this using javafx?

Patient class is as follows:

    public Patient(String nhsNumber, String firstName,
            String lastName, String postCode) {
        super(nhsNumber,firstName, lastName
                , postCode);

    }

With javafx I am able to display certain details (see below), but I'm not sure how I can get the timestamp back from the database as well? Any help would be greatly appreciated!

    public class QueueTabPageController implements Initializable {

    @FXML
    private TableView<Patient> tableView;

    @FXML
    private TableColumn<Patient, String> firstNameColumn;

    @FXML
    private TableColumn<Patient, String> lastNameColumn;

    @FXML
    private TableColumn<Patient, String> postCodeColumn;

    @FXML
    private QueueTabPageController queueTabPageController;

    private ObservableList<Patient> tableData;

    // public static LinkedList<Patient> displayQueue;

    @Override
    public void initialize(URL arg0, ResourceBundle arg1) {

        assert tableView != null : "fx:id=\"tableView\" was not injected: check your FXML file 'FXMLQueueTabPage.fxml'";

        firstNameColumn.setCellValueFactory(new PropertyValueFactory<Patient, String>("firstName"));
        lastNameColumn.setCellValueFactory(new PropertyValueFactory<Patient, String>("lastName"));
        postCodeColumn.setCellValueFactory(new PropertyValueFactory<Patient, String>("postCode"));

    }

    @FXML
    private void btnRefreshQueueClick(ActionEvent event) throws IOException{
        displayQueue(Queue.queue);
    }

    public void displayQueue(LinkedList<Patient> queue) {
        tableData = FXCollections.observableArrayList(queue);
        tableView.setItems(tableData);
    }

}

insert blob into sqlitedatabase java

i have created a data base and gui which collects data from user, now i want to add collected data to database. Connection with db is good, however function which is called on button click is failing, reurning me message "null pointer exception: null"

system output:

 Opened database successfully 
 add to  database successfully
 java.lang.NullPointerException: null

addtoBase:

    private void addtoBase(String name, int row, BufferedImage img) throws Exception, IOException, SQLException {
    PreparedStatement st = null;
    FileInputStream fis = null; 
    Connection con = null;

    try {
    Class.forName("org.sqlite.JDBC").newInstance();
    con = DriverManager.getConnection("jdbc:sqlite:heores.sqlite");
    con.setAutoCommit(false);
    System.out.println("Opened database successfully");
    FileInputStream inputStream= new FileInputStream(selectedFile.getAbsolutePath());
    String sql = "INSERT INTO hero (name, row, img) values (?, ?, ?)";
    st = con.prepareStatement(sql);

    st.setString(1, name);
    st.setInt(2, row);
    st.setBlob(3,inputStream);
    st.executeUpdate();
    con.commit();
    System.out.println("add to  database successfully");
    }

    catch(Exception e) {
    System.err.println( e.getClass().getName() + ": " + e.getMessage() );
    System.exit(0);
    }


    finally {
    st.close();
    fis.close();
    }

}

button action listener:

    private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {                                         
    String name;
    int row;
    BufferedImage img;

    switch(Row.getSelectedItem().toString()){
        case "Front":{
            row =1;
        }
        case "Middle":{
            row =2;
        }
        case "Back":{
            row =3;
        }
        default:{
            row=0;
        }

    }
    name=name1.getText();
    img = toBufferedImage(image);

    try {
        addtoBase(name, row, img);

    } catch ( Exception e ) {
    System.err.println( e.getClass().getName() + ": " + e.getMessage() );
    System.exit(0);
    }
}

Any ideas whats wrong? after opening database it seems like all colums are filled except for img collumn (Blob), file that im insetring is decleared in main:

public class Main extends javax.swing.JFrame {

Image image;
File selectedFile = new File("avatar.png");
//...

How to store and large text data from SQLite database?

I am working on an android project. Its a tutorials app in which I am providing 100 chapters. But I am not getting exactly how to store these chapters in my app's local database and how to fetch it on button click. I want to provide the facility on button click the next activity must be opened and the contents of the chapters must be loaded on that activity. For Example. If I click on Button "Chapter 1" then 1st chapter must be loaded from SQlite database. I have followed lots of tutorials but I am not satisfied. Please Somebody Help Me. THANK YOU!!! :)

django on jython using django-jython

I would appreciate it if you read my poor English

I use :

windows

Jython 2.7rc2

jdk-8u45

django 1.8

django-jython 1.7.0b2

I try jython startproject mysite, and succeed

then I try jython manage.py runserver 8080 and fail

Detail:

In settings.py :

     in DATABASES :
                     'ENGINE': 'doj.db.backends.sqlite3'
     in INSTALLED_APPS:
                      I add  'doj',

The same with http://ift.tt/1HtGQbS

The results:

raise ImproperlyConfigured(error_msg)django.core.exceptions.ImproperlyConfigured:'doj.db.backends.sqlite' isn't an a vailable database backend. Try using 'django.db.backends.XXX', where XXX is one of: u'base', u'mysql', u'oracle', u'postgresql_psycopg2', u'sqlite3' Error was: cannot import name BaseDatabaseWrapper

so I try django.db.backends.sqlite instead of doj.db.backends.sqlite in settings.py at DATABASES

and unluckily:

raise ImproperlyConfigured("Error loading either pysqlite2
or sqlite3 modules (tried in that order): %s" % exc) django.core.exceptions.ImproperlyConfigured: Error loading either pysqlite2 or sqlite3 modules (tried in that order): No module named sqlite3

I have searched and try all day already but still can not solve.

thanks for your help!!!

SQLite floating point issue

This query returns 1.7763568394002505e-15 when it should return 0.00:

SELECT st.id
, Sum(
    CASE sa.Type 
    WHEN 4 THEN sa.quantity * (st.price - st.commission)
    WHEN 5 THEN -sa.quantity * (st.price - st.commission)
    ELSE 0.0 END
) Sales
FROM sales sa
JOIN stock st
  ON sa.stockid = st.id
WHERE st.id = 1
GROUP BY st.id

http://ift.tt/1biOUzH

It's looks like a classic floating point calculation issue, but how can I fix it? I've tried casting the various columns to REAL but it doesn't make a difference.

You can simulate the result using this query:

SELECT 26.3 - 10.52 - 15.78 AS Result

unsupported type error during pandas DataFrame.to_sql

I am trying using pandas to analyze some json data and later write to a sqlite3 database.

The input data is loaded from here and I only care about the 'bugs' information. Since there are list(for blocks, depends_on, flags, keywords) and null (for dupe_of), I use json_normalize to generate the DataFrame df. But when I use to_sql:

engine = create_engine('sqlite:///mydb.db')
df.to_sql("mydb", engine, index=False, if_exists='append')

It reports error:

  File "/usr/local/lib/python2.7/dist-packages/pandas/core/generic.py", line 966, in to_sql
    dtype=dtype)
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 538, in to_sql
    chunksize=chunksize, dtype=dtype)
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 1172, in to_sql
    table.insert(chunksize)
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 717, in insert
    self._execute_insert(conn, keys, chunk_iter)
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 692, in _execute_insert
    conn.execute(self.insert_statement(), data)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 841, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 938, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1070, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1271, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1063, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 442, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.InterfaceError: (InterfaceError) Error binding parameter 0 - probably unsupported type. u'INSERT INTO "MozillaRepo" (blocks, classification, component, creatio
n_time, depends_on, dupe_of, flags, id, is_confirmed, is_open, keywords, last_change_time, op_sys, platform, priority, product, resolution, severity, status, summary, version) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' ([], u'Client Software', u'Preferences', u'1999-07-30T22:55:51Z', [], None, []  ... displaying 10 of 22 total bound parameter sets ...  u'Dialup properties needs to be exposed in prefs', u'Trunk')

From the last line of the message, it should be the data type issue. But I don't know how to correct it.

The result of df.dtypes is:

blocks              object
classification      object
component           object
creation_time       object
depends_on          object
dupe_of             object
flags               object
id                   int64
is_confirmed          bool
is_open               bool
keywords            object
last_change_time    object
op_sys              object
platform            object
priority            object
product             object
resolution          object
severity            object
status              object
summary             object
version             object
dtype: object

And I can see that the database table is created as:

CREATE TABLE "mydb" (
        blocks TEXT, 
        classification TEXT, 
        component TEXT, 
        creation_time TEXT, 
        depends_on TEXT, 
        dupe_of TEXT, 
        flags TEXT, 
        id BIGINT, 
        is_confirmed BOOLEAN, 
        is_open BOOLEAN, 
        keywords TEXT, 
        last_change_time TEXT, 
        op_sys TEXT, 
        platform TEXT, 
        priority TEXT, 
        product TEXT, 
        resolution TEXT, 
        severity TEXT, 
        status TEXT, 
        summary TEXT, 
        version TEXT, 
        CHECK (is_confirmed IN (0, 1)), 
        CHECK (is_open IN (0, 1)), 
        CHECK (is_open IN (0, 1)), 
        CHECK (is_confirmed IN (0, 1))
)

Database present in simulator but not on device build (Copying files from App Bundle to Documents Directory in Swift)

I've got a problem that I'm not able to find an answer to.

I have an app that has a sqlite database (called "myDatabase.sqlite"). In order to get this to work in the simulator build, I found the path for it by using the following code:

        let fileManager = NSFileManager.defaultManager()
    let dirPaths = NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true)

    let docsDir = dirPaths[0] as! String

    databasePath = docsDir.stringByAppendingPathComponent("myDatabase.sqlite")

    let database = FMDatabase(path: databasePath as String)

This works in the simulator, but I had to physically put the file in that path.

For on-device testing, I'd like this to transfer with the bundle. I have that same database copied into the app bundle (oops, I don't have enough reputation to post images, but it's underneath one of my view controllers in the list of files in the app bundle).

When I run the build on the device, though, I get the following readout from the console:

Opened database at /var/mobile/Containers/Data/Application/64ECF088-1AA8-4B9F-AF65-4E36EF10AF9C/Documents/myDatabase.sqlite 2015-04-20 11:39:15.991 TestApp[15882:2776428] DB Error: 1 "no such table: cards" 2015-04-20 11:39:15.992 TestApp[15882:2776428] DB Query: SELECT * FROM cards 2015-04-20 11:39:15.992 TestApp[15882:2776428] DB Path: /var/mobile/Containers/Data/Application/64ECF088-1AA8-4B9F-AF65-4E36EF10AF9C/Documents/myDatabase.sqlite Error: no such table: cards

Does anyone know if I'm doing something obviously wrong?

Thanks.

absoluteString "handleOpenURL " to match email attachment name?

I have a working code, but now find the need to import & save two different Sqlite files via email "Open in" option, I tried using UIAlertView to create to different save file paths but it only seems to work with one way

- (void)handleOpenURL:(NSURL *)urlx {
NSData *dbimport = [[NSData alloc] initWithContentsOfURL:urlx];
NSUserDefaults *defaults = [NSUserDefaults standardUserDefaults];
[defaults setObject:dbimport forKey:@"import"];
[defaults synchronize];
NSLog(@"import saved");
databaseName = @"svrStoreData2.sql";
NSString *documentsPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
NSError *writeError = nil;
NSString *filePathx = [documentsPath stringByAppendingPathComponent:databaseName]; [dbimport writeToFile:filePathx atomically:YES];
if (writeError)
{
    NSLog(@"Error writing file: %@", writeError);
}
}

Both file have specific file names so was wonder if I could use "if equal to string" & use an if statement after I have synchronised NSUserDefaults to give two file path save options

Thanks for any help in advance

This was my UIALertView try

UIAlertView *alertdata = [[UIAlertView alloc]
                    initWithTitle:@"What type of data do you want to import?"
                      message:@"Choose data type"
                    delegate:self
                     cancelButtonTitle:@"Import RM Name Data"
                     otherButtonTitles:@"Import Store Visit Data", nil];

NSInteger *buttonIndex = NULL;
NSLog(@"Button Index =%ld",(long)buttonIndex);
if (buttonIndex == 0)
{
}
else if (buttonIndex == 1)
{
    databaseExportName = @"export.sql";
       NSArray *pathssqlite3 = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *pathssqliteDir2 = [pathssqlite3 objectAtIndex:0];
        filePathsqlite3 = [pathssqliteDir2 stringByAppendingPathComponent:databaseExportName];
       NSData *dbimport = [[NSData alloc] initWithContentsOfURL:urlx];
      NSMutableData *dbimport = [[NSMutableData alloc] initWithContentsOfURL:urlx];
        NSError *writeError = nil;
        [dbimport writeToFile:filePathsqlite3 atomically:YES];
       if (writeError)
      {
           NSLog(@"Error writing file: %@", writeError);
       } } [alertdata show];

Still trying to resolve, was hoping this would work, but still cannot crack it

 `- (void)handleOpenURL:(NSURL *)urlx {
NSData *dbimport = [[NSData alloc] initWithContentsOfURL:urlx];
NSUserDefaults *defaults = [NSUserDefaults standardUserDefaults];
[defaults setObject:dbimport forKey:@"import"];
[defaults synchronize];
NSLog(@"import saved");
if([[urlx absoluteString] isEqualToString:@"svrStoreData2.sql"])
{
     NSLog(@"If statement called");
    //
    databaseName = @"svrStoreData2.sql";
    NSString *documentsPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
    NSError *writeError = nil;
    NSString *filePathx = [documentsPath stringByAppendingPathComponent:databaseName];
    [dbimport writeToFile:filePathx atomically:YES];
    if (writeError)
    {
        NSLog(@"Error writing file: %@", writeError);
    }
    //
    else {

        databaseExportName = @"export.sql";
        NSString *documentsPathy = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
         NSError *writeError = nil;
        NSString *filePathy = [documentsPathy stringByAppendingPathComponent:databaseExportName];
        [dbimport writeToFile:filePathy atomically:YES];

           if (writeError)
           {
               NSLog(@"Error writing file: %@", writeError);
           }
        NSLog(@"If statement Not called");
    }
}

}

`

Xamarin SQLite PCL implementation

I'm creating a Xamarin application with a PCL and I would like to use SQLite in the PCL to share the database code within the platform specific projects.

I've already looked into the Tasky Portable project, where they are using an abstract class to reach this goal. I've had some trouble implementing this method, which is why I was looking for another solution to use SQLite within a PCL. I stumbled upon an Xamarin Forms article where they are using the SQLite PCL NuGet package to implement it in a PCL.

Since I'm not using Xamarin Forms, what is the difference between these two methods? Can the NuGet package solution also be used to implement SQLite within the PCL without using Xamarin Forms? This method seems a lot simpler than the abstract class.

Retrieving username and password from SQlite database

I'm using the below to check an SQLite database for a username and password, but for some strange reason, it's bringing back an error that the password is incorrect when I login on the website:

private function checkPasswordCorrectnessAndLogin()
{
    $sql = 'SELECT Username, Password
            FROM users
            WHERE Username = :Username
            LIMIT 1';
    $query = $this->db_connection->prepare($sql);
    $query->bindValue(':Username', $_POST['Username']);
    $query->execute();
    $result_row = $query->fetchObject();
    if ($result_row) {
        if (password_verify($_POST['Password'], $result_row->Password)) {
            $_SESSION['Username'] = $result_row->Username;
            $_SESSION['user_is_logged_in'] = true;
            $this->user_is_logged_in = true;
            return true;
        } else {
            $this->feedback = "Incorrect Password.";
        }
    } else {
        $this->feedback = "User Does Not Exist.";
    }

    //default return
    return false;
}

I have no error with the username, it just states that the password is incorrect yet it is not; could anybody shine any light on why I'm receiving this error? I'm using the db browser to insert username and passwords into the table...

get null records in Sqlite from a Json

I am a VERY new android user. I try to save a Json object into a sqlite data base in my android. I saw the Json array ok in android log but, in my slqlite db there is no info (all null). ¿What I am doing wrong? (beside my bad english redaction)

AllStatActivity

public class AllStatActivity extends Activity {

// Progress Dialog
private ProgressDialog pDialog;

// Creating JSON Parser object
JSONParser jParser = new JSONParser();


// url to get all products list
private static String url_all_exams = "http://myweb.php";

// JSON Node names
private static final String TAG_SUCCESS = "success";
private static final String TAG_EXAMS = "exams";
private TextView txtResultado;

private SQLiteDatabase db;
// products JSONArray
JSONArray exams = null;

@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.all_stat);

    //Abrimos la base de datos 'DBUsuarios' en modo escritura
   ExamsSQLiteHelper usdbh = new ExamsSQLiteHelper(this, "DBAAExams", null, 1);
   db = usdbh.getWritableDatabase();

    txtResultado = (TextView)findViewById(R.id.txtResultado);


    // Loading products in Background Thread
    new LoadAllExams().execute();

}



/**
 * Background Async Task to Load all exams by making HTTP Request
 */
class LoadAllExams extends AsyncTask<String, String, String> {


    /**
     * Before starting background thread Show Progress Dialog
     */
    @Override
    protected void onPreExecute() {
        super.onPreExecute();
        pDialog = new ProgressDialog(AllStatActivity.this);
        pDialog.setMessage("Loading exams. Please wait...");
        pDialog.setIndeterminate(false);
        pDialog.setCancelable(false);
        pDialog.show();
    }

    /**
     * getting All products from url
     */
    protected String doInBackground(String... args) {

        // Building Parameters
        List<NameValuePair> params = new ArrayList<NameValuePair>();
        // getting JSON string from URL
        JSONObject json = jParser.makeHttpRequest(url_all_exams, "GET", params);

        // Check your log cat for JSON reponse
        Log.d("All Exams: ", json.toString());

        try {
            // Checking for SUCCESS TAG
            int success = json.getInt(TAG_SUCCESS);

            if (success == 1) {
                // products found
                // Getting Array of Products
                exams = json.getJSONArray(TAG_EXAMS);

                // looping through All Products
                for (int i = 0; i < exams.length(); i++) {

                    JSONObject c = exams.getJSONObject(i);

                    ContentValues values = new ContentValues();
                    values.put("exa_id", c.getString("exa_id"));
                    values.put("exa_date", c.getString("exa_date"));
                    values.put("tye_name", c.getString("tye_name"));
                    db.insert("TableExam", null, values);

                }
            } else {

            }
        } catch (JSONException e) {
            e.printStackTrace();
        }

        return null;
    }

    /**
     * After completing background task Dismiss the progress dialog
     * *
     */
    protected void onPostExecute(String file_url) {
        // dismiss the dialog after getting all products
        pDialog.dismiss();
        // updating UI from Background Thread
        runOnUiThread(new Runnable() {
            public void run() {

                //método rawQuery()
                Cursor cur = db.rawQuery("SELECT exa_id, exa_date, tye_name FROM TableExam", null);
                //int total = 0;


                //Recorremos los resultados para mostrarlos en pantalla
                txtResultado.setText("");
                if (cur.moveToFirst()) {
                    //Recorremos el cursor hasta que no haya más registros
                    do {
                        String pos0 = cur.getString((c.getColumnIndex("exa_id")));
                        String pos1 = cur.getString((c.getColumnIndex("exa_date")));
                        String pos2 = cur.getString((c.getColumnIndex("tye_name")));
                        txtResultado.append(" " + pos0 + " - " + pos1 + " - " + pos2 + " \n");
                    } while(cur.moveToNext());
                }

            }
        });

    }

}

}

ExamsSQLiteHelper class

public class ExamsSQLiteHelper extends SQLiteOpenHelper {

//Sentencia SQL para crear la tabla de Usuarios
String sqlCreate = "CREATE TABLE TableExam (exa_id INTEGER, exa_date DATE, tye_name VARCHAR)";

public ExamsSQLiteHelper (Context contexto, String nombre,
                            CursorFactory factory, int version) {
    super(contexto, nombre, factory, version);
}

@Override
public void onCreate(SQLiteDatabase db) {
    //Se ejecuta la sentencia SQL de creación de la tabla
    db.execSQL(sqlCreate);
}

@Override
public void onUpgrade(SQLiteDatabase db, int versionAnterior,
                      int versionNueva) {
    //NOTA: Por simplicidad del ejemplo aquí utilizamos directamente
    //      la opción de eliminar la tabla anterior y crearla de nuevo
    //      vacía con el nuevo formato.
    //      Sin embargo lo normal será que haya que migrar datos de la
    //      tabla antigua a la nueva, por lo que este método debería
    //      ser más elaborado.

    //Se elimina la versión anterior de la tabla
    db.execSQL("DROP TABLE IF EXISTS TableExam");

    //Se crea la nueva versión de la tabla
    db.execSQL(sqlCreate);
}
}

How to access sqlite database from the service class

I am fairly new to Android, and I am having some confusion on how to perform a certain process, and I would really appreciate is someone can help me out.

I have created a sqlitedatabase in an activity, and passing some data into the database. I am also starting a service in the same activity, which uses the data from the sqlite database which I created in this activity. I am starting the service in the following way.

 Intent intents = new Intent (SendActivity.this,ServiceClass.class);
    startService(intents);

Now I am confused how to access the database I created in this activity in the service class. I created the database like this

SqliteController db = new SqliteController(this);
    db.addGreetings(new Greetings(dt,txtPhoneNo,t,tm));

cannot fetch value from sqlite in IOS

Table values:

ID=1 CUSTOMERID=1 NAME=John EMAIL=email USERNAME=usernaeme

I am using this code to fetch customerId from Usertable with this code

@try {
    //CustomerIdField=@"admin";
   // customerUsername=@"admin";
    NSLog(@"the value of customerusername is %@",customerUsername);
    NSArray *dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    NSString *docsDir = [dirPaths objectAtIndex:0];
NSLog(@"inside function");
databasePath  = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: @"EBook.db" ]];
    const char *dbpath;
    @try {
        dbpath = [databasePath UTF8String];
        NSLog(@"the  const char is %s",dbpath);
    }
    @catch (NSException *exception) {
        NSLog(@"the exception3 is %@",exception);
    }


    if (sqlite3_open(dbpath, &Ebookreaderdb) == SQLITE_OK)

    {
    NSString *selectSQL = [NSString stringWithFormat: @"SELECT CUSTOMERID FROM Usertable WHERE EMAIL=\"%@\"",customerUsername];


        sqlite3_stmt  *selstatement;
    const char *select_stmt = [selectSQL UTF8String];
    //NSMutableArray *resultArray = [[NSMutableArray alloc]init];
    if (sqlite3_prepare_v2(Ebookreaderdb,
                           select_stmt, -1, &selstatement, NULL ) == SQLITE_OK)
    {

        NSLog(@"inside sqlite OK"); //this prints in log
        if (sqlite3_step(selstatement) == SQLITE_ROW)
        {

            NSLog(@"inside sqlite ROW"); // this is also printing in log
            NSString *userInfoStr = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selstatement,1)];
            NSLog(@"val is %@",userInfoStr);
          char *tmp = sqlite3_column_text(selstatement,1);
            if (tmp == NULL)
                CustomerIdField = nil;
            else
                CustomerIdField = [[NSString alloc] initWithUTF8String:tmp];



             CustomerIdField = [[NSString alloc] initWithUTF8String:
                             (const char *) sqlite3_column_text(selstatement,1)];



            NSLog(@"inside customer is %@",CustomerIdField);
           // [resultArray addObject:name];

        }

        else{
            NSLog(@"Not found");
          //  return nil;
        }
        sqlite3_reset(selstatement);
    }

But i am getting this exception Newpjtonfriday[2165:84017] the exception is 2 *** +[NSString stringWithUTF8String:]: NULL cString

I googled with the above result and everywhere it is saying that the value is null that is why the exception occurs. but in my case the value is there.

Because the code NSLog(@"inside sqlite ROW");

is coming in log meaning that a row exists in table. But cannot fetch it.

Please help

SqliteException busy iOS/Android Xamarin MVVMCross

In our Android and iOS MVVMCross app we are experiencing occasional SQLiteException: busy exceptions.

Given the code below, we have several repositories each of which construct a instance of the below and an associated connection to the Sqlite database. Imagine we have a Stocks Repository and a Valuations Repository, two instances of SqliteDataService will be created: SqliteDataService and SqliteDataService, each of which have a connection to the Sqlite database.

Actions on the repositories may operate on background threads which means that we may attempt to insert Stocks into the database at the same time as Valuations.

Now given each repository creates its own SqliteDataService the connectionObject lock will only protect the same repository types from accessing the database at the same time rather than protecting Stocks and Valuations from accessing the database at the same time.

My questions are:

Is it valid to create a connections per repository and if so, how do we guard against SqliteException: busy?

Is there a better pattern? i.e. Should we create a non-generic SqliteDataService class that shares the same connection across threads? We have tried this but on Android we experience fatal exceptions.

Does anybody have a solid Sqlite DAL pattern for Tamarin MVVMCross?

public class SqliteDataService<T> : IDataService<T> where T : new()
{
    private static object lockObject = new object();

    private static object connectionObject = new object();

    private static ISQLiteConnection _connection;

    private static SqliteDataService<T> _instance;

    public SqliteDataService(ISQLiteConnectionFactory connectionFactory, string dbPath)
    {
        if (_connection == null)
        {
            _connection = connectionFactory.Create (dbPath);
            _connection.CreateTable<T> ();
        }
    }

    public static SqliteDataService<T> GetInstance(ISQLiteConnectionFactory connectionFactory, string dbPath)
    {

        if (_instance == null)
        {
            lock (lockObject)
            {
                _instance = new SqliteDataService<T> (connectionFactory, dbPath);
            }
        }

        return _instance;
    }

    public void CreateTable<T> ()
    {

    }

    public void Insert(T value)
    {
        lock (connectionObject) {
            _connection.Insert (value, typeof(T));
        }
    }

    public void InsertAll(IEnumerable<T> values)
    {
        lock (connectionObject) {
            _connection.Insert (values, typeof(T));
        }
    }

    public IEnumerable<T> Read(Expression<Func<T, bool>> predicate)
    {
        lock (connectionObject) {
            return _connection.Table<T> ().Where (predicate);
        }
    }

    public T ReadFirst(Expression<Func<T, bool>> predicate)
    {
        lock (connectionObject) {
            return Read (predicate).FirstOrDefault ();
        }
    }

    public void Update(T value)
    {
        lock (connectionObject) {
            _connection.Update (value, typeof(T));
        }
    }

    public void Delete(Expression<Func<T, bool>> predicate)
    {
        lock (connectionObject) {
            var valuesToDelete = Read (predicate);

            if (valuesToDelete == null)
                return;

            foreach (var value in valuesToDelete) {
                _connection.Delete (value);
            }
        }

Storing items from listView to application's SqLite database

I'm working on an app that selects contacts from phone contacts and displays them in a listView. I wanted to store the listView items into SQLite Database but the app is crashing every time. I do not have the LogCat report as the AVD is acting up and I'm using my android device (Moto G2) instead. Please be patient with me I'm just a beginner looking for guidance.

Here are the classes I'm using :

DatabaseHandler Class

public class DatabaseHandler extends SQLiteOpenHelper {

// database details
public static final int DATABASE_VERSION = 1;
public static final String DATABASE_NAME = "contactsManager";

// table name
public static final String TABLE_CONTACTS = "contacts";

// public static final String KEY_NAME="name";

// column names
// public static final String KEY_ID="id";
public static final String KEY_ListItem = "listitem";

public DatabaseHandler(Context context, String name, CursorFactory factory,
        int version) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    // TODO Auto-generated constructor stub
}

// table creation
@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    String CREATE_CONTACTS_TABLE = "CREATE TABLE" + TABLE_CONTACTS + "("
            + KEY_ListItem + "TEXT" + ")";
    db.execSQL(CREATE_CONTACTS_TABLE);

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub
    db.execSQL("DROP TABLE IF EXISTS" + TABLE_CONTACTS);
    onCreate(db);
}

}

PickAdapter Class

public class PickAdapter {

SQLiteDatabase database_obj;
DatabaseHandler handler_obj;
Context context;

public PickAdapter(Context c) {
    context = c;
}

void addListItem(ArrayList<String> numbers) {
    handler_obj = new DatabaseHandler(context,
            DatabaseHandler.DATABASE_NAME, null,
            DatabaseHandler.DATABASE_VERSION);
    database_obj = handler_obj.getWritableDatabase();

    ContentValues values = new ContentValues();
    for (int i = 0; i < numbers.size(); i++) {

        Log.e("vlaue inserting==", "" + numbers.get(i));
        values.put(DatabaseHandler.KEY_ListItem, numbers.get(i));
        database_obj.insert(DatabaseHandler.TABLE_CONTACTS, null, values);

    }

    database_obj.close(); // Closing database connection
}

Cursor getListItem() {
    String selectQuery = "SELECT  * FROM " + DatabaseHandler.TABLE_CONTACTS;

    handler_obj = new DatabaseHandler(context,
            DatabaseHandler.DATABASE_NAME, null,
            DatabaseHandler.DATABASE_VERSION);
    database_obj = handler_obj.getWritableDatabase();
    Cursor cursor = database_obj.rawQuery(selectQuery, null);

    return cursor;
}

}

PickActivity Class

public class PickActivity extends Activity {
    /** Called when the activity is first created. */
    ListView nlist;
    Button b, saveButton;
    DatabaseHandler handler;
    PickAdapter pickAdapterObj;
    ArrayList<String> phoneNumbers = new ArrayList<String>();
    ArrayList<String> names = new ArrayList<String>();
@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    saveButton = (Button) findViewById(R.id.button2);
    b = (Button) findViewById(R.id.button1);
    b.setOnClickListener(new View.OnClickListener() {

        @Override
        public void onClick(View v) {
            // TODO Auto-generated method stub
            Intent intent = new Intent(Intent.ACTION_PICK);
            intent.setType(ContactsContract.CommonDataKinds.Phone.CONTENT_TYPE);
            startActivityForResult(intent, 1);

        }
    });

    saveButton.setOnClickListener(new View.OnClickListener() {

        @Override
        public void onClick(View v) {
            // TODO Auto-generated method stub
            pickAdapterObj.addListItem(phoneNumbers);
            Cursor cursor = pickAdapterObj.getListItem();

            Log.e("count", "" + cursor.getCount());
            if (cursor != null) {
                cursor.moveToNext();

                do {

                    Log.e("value==", "" + cursor.getString(1));

                } while (cursor.moveToNext());
            }

        }
    });

}

@Override
protected void onActivityResult(int requestCode, int resultCode, Intent data) {
    // TODO Auto-generated method stub
    if (data != null) {
        Uri uri = data.getData();
        if (uri != null) {
            Cursor c = null;
            try {
                c = getContentResolver()
                        .query(uri,
                                new String[] {
                                        ContactsContract.CommonDataKinds.Phone.DISPLAY_NAME,
                                        ContactsContract.CommonDataKinds.Phone.NUMBER,
                                        ContactsContract.CommonDataKinds.Phone.TYPE },
                                null, null, null);
                if (c != null && c.moveToFirst()) {
                    String name = c.getString(0);
                    String number = c.getString(1);
                    // int type = c.getInt(2);
                    storeInArrays(name, number);

                }
            } finally {
                if (c != null) {
                    c.close();
                }
            }
        }
    }
}

public void storeInArrays(String name, String number) {

    ListView noList = (ListView) findViewById(R.id.listView2);
    ListView nameList = (ListView) findViewById(R.id.listView1);
    names.add(name);
    phoneNumbers.add(number);
    ArrayAdapter<String> numberAdapter = new ArrayAdapter<String>(this,
            android.R.layout.simple_list_item_1, phoneNumbers);
    noList.setAdapter(numberAdapter);

    ArrayAdapter<String> nameAdapter = new ArrayAdapter<String>(this,
            android.R.layout.simple_list_item_1, names);
    nameList.setAdapter(nameAdapter);

    Toast.makeText(getApplicationContext(), "added", Toast.LENGTH_LONG)
            .show();

}

}

Add widget to an existing application

I am new in android and maybe my question is a bit stupid but I'd like to know if it's possible to add a widget to my activity application???

I already have my app, it's a kind of calculator, having DB (sqlite) and multiple screens but I need to give to user opportunity to interact with my app via an widget (kind of snack for my app).

Here is a schematic diagram: enter image description here

I have to mention that step "2.4 Calculate result" will calculate how many times did we insert current day for all week.

update SQLite with content of push notification when the app is not running

Is there a way to update SQLite as soon as the push notification is received but the app is not running currently(or the app was killed).

If the notification arrives when the app is running or in background - "didReceiveRemoteNotification" method handles my update.

If the notification arrives when the app is not running I want to update the content of push notification to my sqlite. The update works when the user taps on the notification but it doesn't work when the user taps on the app icon. Please help. Thank you.

Adding all the values of a column if another column matches

I created a view to combine two tables( COMPANY and PAYMENTS ) like so where COMPANY comes from COMPANY and the PAYDUE and DATEDUE come from PAYMENTS

COMPANY | PAYDUE | DATEDUE

Now I need to basically sum up ALL the values in PAYDUE if their COMPANY and DATEDUE matches.

So let's say I have these entries:

COMPANY | PAYDUE | DATEDUE
Comp 1  | 8000   | 4/30/2015
Comp 1  | 7000   | 5/15/2015
Comp 1  | 6000   | 4/30/2015
Comp 1  | 5000   | 5/15/2015
Comp 2  | 4000   | 4/30/2015
Comp 2  | 3000   | 5/15/2015
Comp 2  | 2000   | 4/30/2015
Comp 2  | 1000   | 5/15/2015

What I need is to add the PAYDUE of all the rows with identical COMPANY and DATEDUE so what I need the display to become is:

COMPANY | PAYDUE | DATEDUE
Comp 1  | 14000  | 4/30/2015   <- from- 8000+6000 
Comp 1  | 12000  | 5/15/2015   <- from- 7000+5000   
Comp 2  | 6000   | 4/30/2015   <- from- 4000+2000   
Comp 2  | 4000   | 5/15/2015   <- from- 3000+1000   

I don't know how to add the ones that have matching COMPANY and DATEDUE. Can anyone suggest methods for this?

Also forgot to mention I was hoping to do this all in a query, but if there's no way to do this within a query I'll be happy with any solution.

FINAL WORKING QUERY

    Cursor cur = db.rawQuery("SELECT  " + " _id, " + colCompClass + "," + colName + ", SUM(" + colPayDue + ") AS " + colPayDue + "," + colDateDue + " FROM " + viewComps + " WHERE " + colDateDue + "=" + "( SELECT MIN (" + colDateDue + ") FROM " + viewComps + " WHERE " + colDateDue + ">=?)" + " GROUP BY " + colDateDue + "," + colCompClass, params);

Display SQLite query result in Listview in Android

In my Android app I have an activity with a listview that displays about 4000 contacts that are stored locally in a SQLite Database. If I make an edit to a contact, how can I get only this change in the listview, without having to refresh all the contact list (which means a new query for 4000 results)? This query slows down the performance.

SQLite.swift: Best way to implement model classes representing rows of a given table?

This is only question I've asked (so far) that doesn't need much/any explanation.

I had got as far as this with solving it:

  • each class might have an initialiseTable() kind of like function which would be called when the DB schema is initialised?
  • Static constants in each class for column Expression<T>

I guess the part I'm pondering about most is filling instances of model classes with Query results.

As always, thanks in advance for help :)

Link to the awesome SQLite.swift for those who haven't come across it yet.

Play a sound file on mediaplayer with button within ListView item in Android with filepath loaded from SQLite

I record some sounds and I save them in /storage/emulated/0/AudioRecorder folder. Then I save the filepath in SQLite database. So when I populate my listview based on a baseadapter in which my getview has the button clicklistener. So far everything seems good. But when I click on button nothing happens. The debugging shows that button finds the position of the proper list item, method which plays the sound finds the filepath but nothing is playing.

 public void playSoundItem(int id)
 {

    final String SoundFile = MyItems.get(id).getItemFilepath();
    mMediaPlayer = new MediaPlayer();
    try {
        //Uri mp4 = Uri.parse(file + "/" + myList.get(soundPosition));
        //Uri mp4 = Uri.parse(SoundFile);

        mMediaPlayer = new MediaPlayer();
        mMediaPlayer.setAudioStreamType(AudioManager.STREAM_MUSIC);
        mMediaPlayer.setDataSource(SoundFile);
        mMediaPlayer.prepare(); 
        mMediaPlayer.start();
        mMediaPlayer.setOnCompletionListener(onCompletionListener);
    } catch (Exception e) {
    }
}

I have tested it with uri parsing and again the same thing. The value of SoundFile is: /storage/emulated/0/AudioRecorder/MySound21-04-2015 20:38.mp4 Do I have to parse it to something else

Why are my sqlite calls on separate threads at the same time?

I have a class at http://ift.tt/1y4SKHy that I am trying to make sure is thread safe. Sqlite requires that calls be made by only 1 thread at a time.

I have a queue called _dbQueue

private let _dbQueue = dispatch_queue_create("com.AaronLBratcher.ALBNoSQLDBQueue", nil)

and I open the db synchronously on this queue

    ...
    var openDBSuccessful = true

    //create task closure
    let openFile:() = {
        openDBSuccessful = self.openDBFile(dbFilePath)
        }()

    dispatch_sync(_dbQueue) {
        openFile
    }
    ...

I also run commands and queries synchronously on the same queue

private func sqlExecute(sql:String)->Bool {
    var successful = true

    //create task closure
    let command:() = {
        successful = self.runCommand(sql)
        }()

    dispatch_sync(_dbQueue) {
        command
    }

    return successful
}

func sqlSelect(sql:String)->[DBRow]? { // DBRow is an array of AnyObject
    var recordset:[DBRow]?

    let query:() = {
        recordset = self.runSelect(sql)
        }()

    dispatch_sync(_dbQueue) {
        query
    }

    return recordset
}

When doing some testing however, I got this. Two separate threads running sqlite at the same time. How do I keep this from happening?

exception error shown with stack

Making my SQLite printing method dynamic

I am using SQLite to store the data for my application. The application has a UI that loads the data from the SQLite database to display it table by table to the user. Basically the user can click left or right and view the other tables one by one.

The user can also click a button that will show a print preview of the data and let them print it. I have this working, but I am having some issues devising a dynamic way to display ANY table on the print preview screen perfectly. My concerns are if some column titles are too long etc, basically how to calculate the size of each column etc. Should I loop through and find the max character size of any text in the entire column and then set the column width to just wider than that or is there an easier way to do this?

I also write the data-table to a comma separated csv file so it might be a better alternative to use an existing solution to print from a csv file if any of you know such a solution then please suggest it!

Anyway here is the currently existing code:

// ------------------------ code that gets called when the print button is clicked ----------------------------

// holds the row data
List<string[]> myList = new List<string[]>();

if(ReportPage == 1)
{
    int rowCount = MyTestTable.RowCount;
    for(int i = 0; i <rowCount; i++)
    {
         MyTestTable.SelectedRowIndex = i;
         var row1 = MyTestTable.GetSelectedRow();
         var cols1 = row1.ItemArray;

        string Col1 = cols1[row1.FindIndexOfColumn("Col1")].ToString();
        string Col2 = cols1[row1.FindIndexOfColumn("Col2")].ToString();
        string Col3 = cols1[row1.FindIndexOfColumn("Col3")].ToString();
        string Col4 = cols1[row1.FindIndexOfColumn("Col4")].ToString();
        string Col5 = cols1[row1.FindIndexOfColumn("Col5")].ToString();
        string Col6 = cols1[row1.FindIndexOfColumn("Col6")].ToString();
        string Col7 = cols1[row1.FindIndexOfColumn("Col7")].ToString();
        myList.Add(new string[] { Col1, Col2, Col3, Col4, Col5, Col6, Col7 });
    }

string[] cols = new string[7];
cols[0] = "Col1";
cols[1] = "Col2";
cols[2] = "Col3";
cols[3] = "Col4";
cols[4] = "Col5";
cols[5] = "Col6";
cols[6] = "Col7";

PrintUtility.SetUpDocument("TEST", cols, myList);


}
PrintUtility.TestNewReport();






// ---------------------- plugin code that gets called from the application 

namespace PrintUtility
{

     public class PrintUtility : UserComponentBase
    {
        public PrintDocument document;
        public DataGridView dataGridView;

        public PrintUtility()
        {
            document = new PrintDocument();
            dataGridView = new DataGridView();
        }



        public void SetUpDocument(string title, string[] cols,  List<string[]> rows)
        {
            document = new PrintDocument();
            dataGridView = new DataGridView();
            document.DocumentName = title;
            document.DefaultPageSettings.Landscape = true;
            document.PrintPage += PrintPage;

            DataGridView dataGrid = new DataGridView();
            dataGrid.ColumnCount = cols.Length;
            for (int i = 0; i < cols.Length; i++ )
            {
                dataGrid.Columns[i].Name = cols[i];
            }

            foreach(string[] row in rows)
            {
                dataGrid.Rows.Add(row);
            }

            this.dataGridView = dataGrid;
            document.DocumentName = title;
            document.PrintPage += PrintPage;
        }


        public PrintDocument GetDocument()
        {
            return this.document;
        }

        private DataTable ConvertListToDataTable(List<string[]> list)
        {
            // New table.
            DataTable table = new DataTable();

            // Get max columns.
            int columns = 0;
            foreach (var array in list)
            {
                if (array.Length > columns)
                {
                    columns = array.Length;
                }
            }

            // Add columns.
            for (int i = 0; i < columns; i++)
            {
                table.Columns.Add();
            }

            // Add rows.
            foreach (var array in list)
            {
                table.Rows.Add(array);
            }

            return table;
        }

        public void TestNewReport()
        {
            Report report = new Report(new PdfFormatter());
            FontDef fd = new FontDef(report, "Helvetica");
            FontProp fp = new FontPropMM(fd, 4);
            FontProp fp_Title = new FontPropMM(fd, 6);
            FontProp fp_Word = new FontPropMM(fd, 3);
            fp_Title.bBold = true;

            List<string> columns = new List<string>();
            foreach (DataGridViewColumn column in dataGridView.Columns)
            {
                columns.Add(column.Name.ToString());
            }

            List<List<string>> rows = new List<List<string>>();
            foreach (DataGridViewRow row in dataGridView.Rows)
            {
                List<string> rowSingle = new List<string>();
                foreach (DataGridViewCell cell in row.Cells)
                {
                    try
                    {
                        rowSingle.Add(cell.Value.ToString());
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.ToString());
                    }
                }
                rows.Add(rowSingle);
            }

            // AUDIT TABLE ( This way of doing things is not dynamic )
            Page page = new Page(report);
            page.SetLandscape();
            int x = 10;
            int y = 40;
            int pastLength = 0;
            foreach(string col in columns)
            {
                x += ((pastLength * 14) + 31);
                page.Add(x, y, new RepString(fp_Title, col));
                pastLength = col.Length;

            }
            page.Add(0, 52, new RepString(fp_Title, "_________________________________________________________________"));

            /* Dynamic way starting
            int rowX = 10;
            int rowY = 105;
            foreach (List<string> row in rows)
            {
                int pastLength2 = 0;
                foreach (string rowItem in row)
                {
                    page.Add(rowX, rowY, new RepString(fp_Word, rowItem));
                    rowX += ((pastLength * 14) + 31);
                    pastLength2 = rowItem.Length;
                }
                rowY += 30;
            }
             */

            fp_Title.rSizeMM = 8;
            int amountRowsPerPageSoFar = 0;
            int rowY = 80;

            foreach (List<string> row in rows)
            {
                try
                {
                    string iDItem = row[0];
                    page.Add(40, rowY, new RepString(fp_Word, iDItem));

                    string typeItem = row[1];
                    page.Add(120, rowY, new RepString(fp_Word, typeItem));

                    string descriptionItem = row[2];
                    page.Add(190, rowY, new RepString(fp_Word, descriptionItem));

                    string timestampItem = row[3];
                    page.Add(375, rowY, new RepString(fp_Word, timestampItem));

                    string userItem = row[4];
                    page.Add(555, rowY, new RepString(fp_Word, userItem));

                    string stationItem = row[5];
                    page.Add(655, rowY, new RepString(fp_Word, stationItem));

                    string activeItem = row[6];
                    page.Add(775, rowY, new RepString(fp_Word, activeItem));
                    amountRowsPerPageSoFar++;

                    rowY += 30;
                    if (amountRowsPerPageSoFar >= 17)
                    {
                        page = new Page(report);
                        page.SetLandscape();
                        amountRowsPerPageSoFar = 0;
                        rowY = 40;
                    }

                }
                catch (Exception ex)
                {

                }
            }

            RT.ViewPDF(report, "TestReport.pdf");
        }

    }
}

Why is my SQLite database operation "updateinformation2" not changing my "name" value?

I have made a database operation that should accept: new name, new email, old password and new password as arguments. It should then update the database with the new information, that has been entered by the user, but it doesn't work! I honestly do not know why, here is my code:

    public void updateInformation2(DatabaseOperations dop, String nname, String nemail, String npass, String opass) {
    SQLiteDatabase SQ = dop.getWritableDatabase();

    ContentValues cv = new ContentValues();
    cv.put(TableData.TableInfo.USER_NAME, nname);
    cv.put(TableData.TableInfo.USER_PASS,npass);
    cv.put(TableData.TableInfo.USER_EMAIL,nemail);

    String[] args = new String[]{opass};
    SQ.update(TableData.TableInfo.TABLE_NAME, cv, TableData.TableInfo.USER_PASS+" =?", args);

    SQ.close();
}

if anymore details are needed let me know in the comments and I will update the code.

Based on user comments I have updated the code, but the new function just makes my app crash, here is the code:

    public void  updateInformation3(DatabaseOperations dop, String nname,String nemail, String npass, String opass)
{
    SQLiteDatabase SQ = dop.getWritableDatabase();
    // Define the updated row content.
    ContentValues updatedValues = new ContentValues();
    // Assign values for each row.
    updatedValues.put("TableData.TableInfo.USER_NAME", nname);
    updatedValues.put("TableData.TableInfo.USER_PASS",npass);
    updatedValues.put("TableData.TableInfo.USER_EMAIL",nemail);

    String where="TableData.TableInfo.USER_PASS = ?";
    SQ.update(TableData.TableInfo.TABLE_NAME,updatedValues, where, new String[]{opass});
    SQ.close();
}

Here is the complete LogCat as requested, up until the point of the application crashing.

04-22 02:38:51.158    2001-2001/com.example.wolfe_000.final_final_zeno E/libprocessgroup﹕ failed to make and chown /acct/uid_10102: Read-only file system
04-22 02:38:51.158    2001-2001/com.example.wolfe_000.final_final_zeno W/Zygote﹕ createProcessGroup failed, kernel missing CONFIG_CGROUP_CPUACCT?
04-22 02:38:51.158    2001-2001/com.example.wolfe_000.final_final_zeno I/art﹕ Not late-enabling -Xcheck:jni (already on)
04-22 02:38:51.242    2001-2016/com.example.wolfe_000.final_final_zeno D/OpenGLRenderer﹕ Render dirty regions requested: true
04-22 02:38:51.243    2001-2001/com.example.wolfe_000.final_final_zeno D/﹕ HostConnection::get() New Host Connection established 0xa6c511d0, tid 2001
04-22 02:38:51.255    2001-2001/com.example.wolfe_000.final_final_zeno D/Atlas﹕ Validating map...
04-22 02:38:51.301    2001-2016/com.example.wolfe_000.final_final_zeno D/﹕ HostConnection::get() New Host Connection established 0xa6c51360, tid 2016
04-22 02:38:51.338    2001-2016/com.example.wolfe_000.final_final_zeno I/OpenGLRenderer﹕ Initialized EGL, version 1.4
04-22 02:38:51.389    2001-2016/com.example.wolfe_000.final_final_zeno D/OpenGLRenderer﹕ Enabling debug mode 0
04-22 02:38:51.398    2001-2016/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:38:51.398    2001-2016/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa6c50580, error=EGL_SUCCESS
04-22 02:39:06.938    2001-2016/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:39:06.938    2001-2016/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa6c500c0, error=EGL_SUCCESS
04-22 02:39:12.811    2001-2001/com.example.wolfe_000.final_final_zeno D/Database Operations﹕ Database Created
04-22 02:39:12.846    2001-2001/com.example.wolfe_000.final_final_zeno D/Database Operations﹕ One Row Inserted
04-22 02:39:12.915    2001-2016/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:39:12.915    2001-2016/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5b5ccc0, error=EGL_SUCCESS
04-22 02:39:12.993    2001-2016/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:39:12.993    2001-2016/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5b5cca0, error=EGL_SUCCESS
04-22 02:39:15.068    2001-2001/com.example.wolfe_000.final_final_zeno D/Database Operations﹕ Database Created
04-22 02:39:15.170    2001-2016/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:39:15.170    2001-2016/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xb0a9b6a0, error=EGL_SUCCESS
04-22 02:39:16.445    2001-2016/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:39:16.445    2001-2016/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5b5ccc0, error=EGL_SUCCESS
04-22 02:39:16.505    2001-2016/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:39:16.505    2001-2016/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5b5ccc0, error=EGL_SUCCESS
04-22 02:39:17.484    2001-2016/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:39:17.484    2001-2016/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5dadb60, error=EGL_SUCCESS
04-22 02:39:24.591    2001-2001/com.example.wolfe_000.final_final_zeno D/Database Operations﹕ Database Created
04-22 02:39:24.664    2001-2016/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:39:24.664    2001-2016/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa589a500, error=EGL_SUCCESS
04-22 02:39:26.386    2001-2016/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:39:26.386    2001-2016/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa589af60, error=EGL_SUCCESS
04-22 02:39:27.492    2001-2016/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:39:27.493    2001-2016/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5dadb60, error=EGL_SUCCESS
04-22 02:39:28.787    2001-2001/com.example.wolfe_000.final_final_zeno D/Database Operations﹕ Database Created
04-22 02:39:28.844    2001-2016/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:39:28.844    2001-2016/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5844840, error=EGL_SUCCESS
04-22 02:39:28.888    2001-2016/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:39:28.888    2001-2016/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5dad180, error=EGL_SUCCESS
04-22 02:39:28.987    2001-2016/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:39:28.987    2001-2016/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5844840, error=EGL_SUCCESS
04-22 02:40:43.655    2115-2115/com.example.wolfe_000.final_final_zeno D/﹕ HostConnection::get() New Host Connection established 0xa6c401d0, tid 2115
04-22 02:40:43.724    2115-2115/com.example.wolfe_000.final_final_zeno D/Atlas﹕ Validating map...
04-22 02:40:43.803    2115-2130/com.example.wolfe_000.final_final_zeno D/﹕ HostConnection::get() New Host Connection established 0xa6c404a0, tid 2130
04-22 02:40:43.813    2115-2130/com.example.wolfe_000.final_final_zeno I/OpenGLRenderer﹕ Initialized EGL, version 1.4
04-22 02:40:43.861    2115-2130/com.example.wolfe_000.final_final_zeno D/OpenGLRenderer﹕ Enabling debug mode 0
04-22 02:40:43.867    2115-2130/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:40:43.867    2115-2130/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa6c47260, error=EGL_SUCCESS
04-22 02:40:50.228    2115-2130/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:40:50.228    2115-2130/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5efb760, error=EGL_SUCCESS
04-22 02:40:53.298    2115-2115/com.example.wolfe_000.final_final_zeno D/InputEventConsistencyVerifier﹕ KeyEvent: ACTION_UP but key was not down.
in android.widget.EditText{28c7ebdf VFED..CL .F...... 32,633-736,682 #7f08007c app:id/editText4}
0: sent at 145274000000, KeyEvent { action=ACTION_UP, keyCode=KEYCODE_TAB, scanCode=15, metaState=0, flags=0x8, repeatCount=0, eventTime=145274, downTime=145175, deviceId=0, source=0x101 }
04-22 02:40:54.478    2115-2115/com.example.wolfe_000.final_final_zeno D/InputEventConsistencyVerifier﹕ KeyEvent: ACTION_UP but key was not down.
in android.widget.EditText{1477902c VFED..CL .F...... 32,731-736,780 #7f08007e app:id/editText5}
0: sent at 146453000000, KeyEvent { action=ACTION_UP, keyCode=KEYCODE_TAB, scanCode=15, metaState=0, flags=0x8, repeatCount=0, eventTime=146453, downTime=146300, deviceId=0, source=0x101 }
04-22 02:40:56.040    2115-2115/com.example.wolfe_000.final_final_zeno D/Database Operations﹕ Database Created
04-22 02:40:56.082    2115-2115/com.example.wolfe_000.final_final_zeno D/Database Operations﹕ Table Created
04-22 02:40:56.089    2115-2115/com.example.wolfe_000.final_final_zeno D/Database Operations﹕ One Row Inserted
04-22 02:40:56.156    2115-2130/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:40:56.156    2115-2130/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5dde900, error=EGL_SUCCESS
04-22 02:40:56.243    2115-2130/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:40:56.243    2115-2130/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5dde7c0, error=EGL_SUCCESS
04-22 02:40:58.391    2115-2115/com.example.wolfe_000.final_final_zeno D/Database Operations﹕ Database Created
04-22 02:40:58.475    2115-2130/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:40:58.476    2115-2130/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5dde6e0, error=EGL_SUCCESS
04-22 02:40:58.557    2115-2127/com.example.wolfe_000.final_final_zeno I/art﹕ Background partial concurrent mark sweep GC freed 842(50KB) AllocSpace objects, 0(0B) LOS objects, 39% free, 2MB/3MB, paused 17.937ms total 94.451ms
04-22 02:40:59.665    2115-2130/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:40:59.665    2115-2130/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5dde900, error=EGL_SUCCESS
04-22 02:40:59.742    2115-2130/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:40:59.742    2115-2130/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5dde900, error=EGL_SUCCESS
04-22 02:40:59.806    2115-2130/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:40:59.806    2115-2130/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5b33440, error=EGL_SUCCESS
04-22 02:41:07.133    2115-2115/com.example.wolfe_000.final_final_zeno D/Database Operations﹕ Database Created
04-22 02:41:07.187    2115-2130/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:41:07.187    2115-2130/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa58b93e0, error=EGL_SUCCESS
04-22 02:41:09.150    2115-2130/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:41:09.151    2115-2130/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5b30aa0, error=EGL_SUCCESS
04-22 02:41:10.119    2115-2130/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:41:10.119    2115-2130/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5b33440, error=EGL_SUCCESS
04-22 02:41:11.436    2115-2115/com.example.wolfe_000.final_final_zeno D/Database Operations﹕ Database Created
04-22 02:41:11.481    2115-2130/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:41:11.481    2115-2130/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5b33060, error=EGL_SUCCESS
04-22 02:41:11.513    2115-2130/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:41:11.514    2115-2130/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5b33060, error=EGL_SUCCESS
04-22 02:41:11.587    2115-2130/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:41:11.587    2115-2130/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5809360, error=EGL_SUCCESS
04-22 02:43:51.516    2115-2130/com.example.wolfe_000.final_final_zeno W/EGL_emulation﹕ eglSurfaceAttrib not implemented
04-22 02:43:51.516    2115-2130/com.example.wolfe_000.final_final_zeno W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5b33060, error=EGL_SUCCESS

multiple rows in SQLite (WIndows phone 8)

I am creating a table named "Task" with four columns by taking a new class

 public sealed class Task
{

    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public DateTime CreationDate { get; set; }

}

To insert single row..i followed this..it working perfectly

private void Insert_Click(object sender, RoutedEventArgs e)    
    {
        using (var db = new SQLiteConnection(DB_PATH))
        {

            db.RunInTransaction(() =>
            {
                db.Insert(new Task() { Id = 1, FirstName = "Ken", LastName = d1[2], CreationDate = DateTime.Now });

          });

        }
    }

..but in my requirement ..i have to insert multiple rows at a time..i tried the following.

private void Insert_Click(object sender, RoutedEventArgs e)    //perfectly  working
    {
        using (var db = new SQLiteConnection(DB_PATH))
        {

            db.RunInTransaction(() =>
            {
                db.Insert(new Task() { Id = 1, FirstName = "Ken", LastName = d1[2], CreationDate = DateTime.Now });
                 db.Insert(new Task() { Id = 2, FirstName = "Justin", LastName = "Bieber", CreationDate = DateTime.Now });

                db.Insert(new Task() { Id = 3, FirstName = "king", LastName = "john", CreationDate = DateTime.Now });
            });

        }
    }

when i followed this,no error popped..but only first row is inserting.. no sign of second row details.. please help me in this regard..

iOS) fmdb : insert

What I coded is below:

[db executeUpdate:@"INSERT INTO usercart (id, productid, name, description, campaignid, startdate, enddate, martid, martname, price, update) VALUES (?,?,?,?,?,?,?,?,?,?,?)",(int)cartItem.databaseId,(int)cartItem.itemId,cartItem.itemName,cartItem.itemDescription,(int)cartItem.campaignId,cartItem.campaignStartDate,cartItem.campaignEndDate,(int)cartItem.martId,cartItem.martName,(int)cartItem.itemPrice,cartItem.lastUpdate]

And I got an erorr and message below :

DB Error: 1 "near "update": syntax error"
DB Query: INSERT INTO usercart (id, productid, name, description, campaignid, startdate, enddate, martid, martname, price, update) VALUES (?,?,?,?,?,?,?,?,?,?,?)
DB Path: file:///var/mobile/Containers/Data/Application/2F08F458-BAB3-4523-B942-68ABF67CACF6/Documents/HaffleMonster.sqlite
Error Domain=FMDatabase Code=1 "near "update": syntax error" UserInfo=0x170e65740 {NSLocalizedDescription=near "update": syntax error}
near "update": syntax error

I don't know why. I think i am right with syntax of sqlite and fmdb Can you explain why?

How to implement an SQLite Update command properly

I am looking to use an update command to change all my users details, depending on the users email address. I have been told to use an update command, but I don't properly understand how to use one, and the online documentation is pretty poor. Can anyone help me out? Here is my code:

public class DatabaseOperations extends SQLiteOpenHelper {

    public static int database_version = 1;
    public String CREATE_QUERY = "CREATE TABLE " + TableData.TableInfo.TABLE_NAME + "(" + TableData.TableInfo.USER_NAME + " TEXT," + TableData.TableInfo.USER_PASS + " TEXT," + TableData.TableInfo.USER_EMAIL + " TEXT," + TableData.TableInfo.USER_WEIGHT + " INTEGER," + TableData.TableInfo.USER_GOAL + " INTEGER );";


    public DatabaseOperations(Context context) {
        super(context, TableData.TableInfo.DATABASE_NAME, null, database_version);
        Log.d("Database Operations", "Database Created");
    }

    @Override
    public void onCreate(SQLiteDatabase sdb) {
        sdb.execSQL(CREATE_QUERY);
        Log.d("Database Operations", "Table Created");
    }

    @Override
    public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {

    }

    public void putInformation(DatabaseOperations dop, String name, String pass, String email, int weight, int goal) {
        SQLiteDatabase SQ = dop.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(TableData.TableInfo.USER_NAME, name);
        cv.put(TableData.TableInfo.USER_PASS, pass);
        cv.put(TableData.TableInfo.USER_EMAIL, email);
        cv.put(TableData.TableInfo.USER_WEIGHT, weight);
        cv.put(TableData.TableInfo.USER_GOAL, goal);
        long k = SQ.insert(TableData.TableInfo.TABLE_NAME, null, cv);
        Log.d("Database Operations", "One Row Inserted");
        SQ.close();
    }

    public Cursor getInformation(DatabaseOperations dop) {
        SQLiteDatabase SQ = dop.getReadableDatabase();
        String[] columns = {TableData.TableInfo.USER_NAME, TableData.TableInfo.USER_PASS, TableData.TableInfo.USER_EMAIL, TableData.TableInfo.USER_WEIGHT, TableData.TableInfo.USER_GOAL};
        Cursor CR = SQ.query(TableData.TableInfo.TABLE_NAME, columns, null, null, null, null, null);
        return CR;
    }
}

Why does using SUM(mycolumn) suddenly make mycolumn non-existent

I get an IllegalArgumentException when I try to use the aggregate function SUM() in my query which otherwise works fine without it.

Without using SUM() my colPayDue is referenced properly and I get the proper results but the moment I try to integrate a SUM() in the query it suddenly can't be found. I'm obviously doing something wrong but what?

Can someone tell me what I'm doing wrong ?

    Cursor cur = db.rawQuery("SELECT  " + " _id, " + colCompClass + "," + colName + ", SUM(" + colPayDue + ")," + colDateDue + " FROM " + viewComps + " WHERE " + colDateDue + "=" + "( SELECT MIN (" + colDateDue + ") FROM " + viewComps + " WHERE " + colDateDue + ">=?)" + " GROUP BY " + colDateDue + "," + colCompClass, params);

Combining like clause's - A special wildcard and a column

Im trying to do a select statement that querys my comments column for all data that includes @"username" in a database table. For example if i wanted to find @bobby. The query would search for wildcard @ in combination with checking each element in the username column. e.g username column is

**USERNAME** 
bobby
foo
patrick

This is what I have so far ( I know its wrong but you get the idea what I am trying to achieve )

 "SELECT * FROM posts WHERE content LIKE '%@%' AND content LIKE "entire usename column""

obtaining user prefs specifically for an account

We are trying to make an application in which the user can register their account and then log on with said account. However, right now we are trying to save the user's inputted data to their account.

Basically, when I change something in my settings screen while logged on my account, I want those settings to be saved for that account only.

The Login Activity:

public class LoginActivity extends Activity {
// LogCat tag
private static final String TAG = RegisterActivity.class.getSimpleName();
private Button btnLogin;
private Button btnLinkToRegister;
private EditText inputEmail;
private EditText inputPassword;
private ProgressDialog pDialog;
private SessionManager session;

@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_login);

    inputEmail = (EditText) findViewById(R.id.email);
    inputPassword = (EditText) findViewById(R.id.password);
    btnLogin = (Button) findViewById(R.id.btnLogin);
    btnLinkToRegister = (Button) findViewById(R.id.btnLinkToRegisterScreen);

    // Progress dialog
    pDialog = new ProgressDialog(this);
    pDialog.setCancelable(false);

    // Session manager
    session = new SessionManager(getApplicationContext());

    // Check if user is already logged in or not
    if (session.isLoggedIn()) {
        // User is already logged in. Take him to main activity
        Intent intent = new Intent(LoginActivity.this, MainActivity.class);
        startActivity(intent);
        finish();
    }

    // Login button Click Event
    btnLogin.setOnClickListener(new View.OnClickListener() {

        public void onClick(View view) {
            String email = inputEmail.getText().toString();
            String password = inputPassword.getText().toString();

            // Check for empty data in the form
            if (email.trim().length() > 0 && password.trim().length() > 0) {
                // login user
                checkLogin(email, password);
            } else {
                // Prompt user to enter credentials
                Toast.makeText(getApplicationContext(),
                        "Please enter the credentials!", Toast.LENGTH_LONG)
                        .show();
            }
        }

    });

    // Link to Register Screen
    btnLinkToRegister.setOnClickListener(new View.OnClickListener() {

        public void onClick(View view) {
            Intent i = new Intent(getApplicationContext(),
                    RegisterActivity.class);
            startActivity(i);
            finish();
        }
    });

}

/**
 * function to verify login details in mysql db
 * */
private void checkLogin(final String email, final String password) {
    // Tag used to cancel the request
    String tag_string_req = "req_login";

    pDialog.setMessage("Logging in ...");
    showDialog();

    StringRequest strReq = new StringRequest(Method.POST,
            AppConfig.URL_REGISTER, new Response.Listener<String>() {

        @Override
        public void onResponse(String response) {
            Log.d(TAG, "Login Response: " + response.toString());
            hideDialog();

            try {
                JSONObject jObj = new JSONObject(response);
                boolean error = jObj.getBoolean("error");

                // Check for error node in json
                if (!error) {
                    // user successfully logged in
                    // Create login session
                    session.setLogin(true);

                    // Launch main activity
                    Intent intent = new Intent(LoginActivity.this,
                            MainActivity.class);
                    startActivity(intent);
                    finish();
                } else {
                    // Error in login. Get the error message
                    String errorMsg = jObj.getString("error_msg");
                    Toast.makeText(getApplicationContext(),
                            errorMsg, Toast.LENGTH_LONG).show();
                }
            } catch (JSONException e) {
                // JSON error
                e.printStackTrace();
            }

        }
    }, new Response.ErrorListener() {

        @Override
        public void onErrorResponse(VolleyError error) {
            Log.e(TAG, "Login Error: " + error.getMessage());
            Toast.makeText(getApplicationContext(),
                    error.getMessage(), Toast.LENGTH_LONG).show();
            hideDialog();
        }
    }) {

        @Override
        protected Map<String, String> getParams() {
            // Posting parameters to login url
            Map<String, String> params = new HashMap<String, String>();
            params.put("tag", "login");
            params.put("email", email);
            params.put("password", password);

            return params;
        }

    };

    // Adding request to request queue
    AppController.getInstance().addToRequestQueue(strReq, tag_string_req);
}

private void showDialog() {
    if (!pDialog.isShowing())
        pDialog.show();
}

private void hideDialog() {
    if (pDialog.isShowing())
        pDialog.dismiss();
}}

The SQLite handler:

public class SQLiteHandler extends SQLiteOpenHelper {

private static final String TAG = SQLiteHandler.class.getSimpleName();

// All Static variables
// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "android_api";

// Login table name
private static final String TABLE_LOGIN = "login";

// Login Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_NAME = "name";
private static final String KEY_EMAIL = "email";
private static final String KEY_UID = "uid";
private static final String KEY_CREATED_AT = "created_at";

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

// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
    String CREATE_LOGIN_TABLE = "CREATE TABLE " + TABLE_LOGIN + "("
            + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
            + KEY_EMAIL + " TEXT UNIQUE," + KEY_UID + " TEXT,"
            + KEY_CREATED_AT + " TEXT" + ")";
    db.execSQL(CREATE_LOGIN_TABLE);

    Log.d(TAG, "Database tables created");
}

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_LOGIN);

    // Create tables again
    onCreate(db);
}

/**
 * Storing user details in database
 * */
public void addUser(String name, String email, String uid, String created_at) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_NAME, name); // Name
    values.put(KEY_EMAIL, email); // Email
    values.put(KEY_UID, uid); // Email
    values.put(KEY_CREATED_AT, created_at); // Created At

    // Inserting Row
    long id = db.insert(TABLE_LOGIN, null, values);
    db.close(); // Closing database connection

    Log.d(TAG, "New user inserted into sqlite: " + id);
}

/**
 * Getting user data from database
 * */
public HashMap<String, String> getUserDetails() {
    HashMap<String, String> user = new HashMap<String, String>();
    String selectQuery = "SELECT  * FROM " + TABLE_LOGIN;

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);
    // Move to first row
    cursor.moveToFirst();
    if (cursor.getCount() > 0) {
        user.put("name", cursor.getString(1));
        user.put("email", cursor.getString(2));
        user.put("uid", cursor.getString(3));
        user.put("created_at", cursor.getString(4));
    }
    cursor.close();
    db.close();
    // return user
    Log.d(TAG, "Fetching user from Sqlite: " + user.toString());

    return user;
}

/**
 * Getting user login status return true if rows are there in table
 * */
public int getRowCount() {
    String countQuery = "SELECT  * FROM " + TABLE_LOGIN;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);
    int rowCount = cursor.getCount();
    db.close();
    cursor.close();

    // return row count
    return rowCount;
}

/**
 * Re crate database Delete all tables and create them again
 * */
public void deleteUsers() {
    SQLiteDatabase db = this.getWritableDatabase();
    // Delete All Rows
    db.delete(TABLE_LOGIN, null, null);
    db.close();

    Log.d(TAG, "Deleted all user info from sqlite");
}}

The settings Activity:

public class SettingsActivity extends ActionBarActivity implements View.OnClickListener {

CheckBox notificationsCheckbox;
private SeekBar volumeSeekbar = null;
private AudioManager audioManager = null;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setVolumeControlStream(AudioManager.STREAM_MUSIC);
    setContentView(R.layout.activity_settings);      
    volumeSeekbar = (SeekBar) findViewById(R.id.seekBar);
    Button accountSettingsButton = (Button) findViewById(R.id.accSettingsBtn);

    accountSettingsButton.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            startActivity(new Intent (SettingsActivity.this, AccountSettingsActivity.class));
        }
    });

    Button tagSettingsButton = (Button) findViewById(R.id.tagSettingsbtn);

    tagSettingsButton.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            startActivity(new Intent (SettingsActivity.this, TagsSettingsActivity.class));
        }
    });

    Button bluetoothSettingsButton = (Button) findViewById(R.id.bluetoothBtn);

    bluetoothSettingsButton.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            startActivity(new Intent (SettingsActivity.this, DeviceScanActivity.class));
        }
    });



    volumeSeekbar.setOnSeekBarChangeListener(new SeekBar.OnSeekBarChangeListener(){

        public void onStopTrackingTouch(SeekBar seekBar) {
            // TODO Auto-generated method stub
            loadSavedSlider();
        }
        public void onStartTrackingTouch(SeekBar seekBar) {
            // TODO Auto-generated method stub

        }
        public void onProgressChanged(SeekBar seekBar, int progress,boolean fromUser) {
            // TODO Auto-generated method stub
            saveSlidePreferences();



        }
    });
    seekbarVolume();
    notificationsCheckbox = (CheckBox) findViewById(R.id.notifCheckbox);
    notificationsCheckbox.setOnClickListener(this);
    loadSavedCheckbox();
}

private void loadSavedCheckbox() {

SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(this);
boolean checkBoxValue = sharedPreferences.getBoolean("CheckBox_Value", false);



    if (checkBoxValue) {
    notificationsCheckbox.setChecked(true);

    } else {
    notificationsCheckbox.setChecked(false);

    }} private void loadSavedSlider() {
    SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(this);

    int mProgress = sharedPreferences.getInt("mMySeekBarProgress", 0);
    volumeSeekbar.setProgress(mProgress);
}
private void savePreferences(String key, boolean value) {
 SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(this);
  SharedPreferences.Editor editor = sharedPreferences.edit();
   editor.putBoolean(key, value);

   editor.commit();

  }

private void seekbarVolume() {
    try {
        volumeSeekbar = (SeekBar) findViewById(R.id.seekBar);
        audioManager = (AudioManager) getSystemService(Context.AUDIO_SERVICE);
        volumeSeekbar.setMax(audioManager
                .getStreamMaxVolume(AudioManager.STREAM_MUSIC));
        volumeSeekbar.setProgress(audioManager
                .getStreamVolume(AudioManager.STREAM_MUSIC));


        volumeSeekbar.setOnSeekBarChangeListener(new OnSeekBarChangeListener() {
            @Override
            public void onStopTrackingTouch(SeekBar arg0) {
            }

            @Override
            public void onStartTrackingTouch(SeekBar arg0) {
            }

            @Override
            public void onProgressChanged(SeekBar arg0, int progress, boolean arg2) {
                audioManager.setStreamVolume(AudioManager.STREAM_MUSIC,
                        progress, 0);
            }
        });
    }
    catch (Exception e)
    {
        e.printStackTrace();
    }
}
private void saveSlidePreferences() {
    SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(this);
    SharedPreferences.Editor editor = sharedPreferences.edit();
    int mProgress = volumeSeekbar.getProgress();
    editor.putInt("mMySeekBarProgress", mProgress).commit();

    editor.commit();} @Override public boolean onCreateOptionsMenu(Menu menu) {
    getMenuInflater().inflate(R.menu.menu_settings, menu);
    return true;
}
public void sendNotification(View view) {

    NotificationCompat.Builder builder = new NotificationCompat.Builder(this);
    builder.setAutoCancel(true);
    builder.setContentTitle("Notifications Activaded");
    builder.setContentText("You can now receive notifications");
    builder.setSmallIcon(R.drawable.ic_action_new_event);

    Notification notification = builder.build();
    NotificationManager manager = (NotificationManager)this.getSystemService(NOTIFICATION_SERVICE);
    manager.notify(8, notification);
}

@Override
public boolean onOptionsItemSelected(MenuItem item) {
    int id = item.getItemId();
    if (id == R.id.action_settings) {
        return true;
    }


    return super.onOptionsItemSelected(item);
}
public void onClick(View v) {
savePreferences("CheckBox_Value", notificationsCheckbox.isChecked());
  }

}

in the settings menu, you only have a slider and a checkbox that can be changed. For now I' like to know how you can save the changes of these two only to be saved for the account that the user is logged in to.

My programming knowledge of android studio is somewhat low, and I cannot explain much about the SQLite database, as it was created by another teammate.

Give please , free program for converting Access MDB or SQLite (DB). Tired of looking for already . everywhere one and zero advertising programs [on hold]

Give please , free program for converting Access MDB or SQLite (DB). Tired of looking for already . everywhere one and zero advertising programs.
Decided to write here . Can help here

Sqlite in Windows 8 - Run as administrator [on hold]

I built a simple program that store some data in sqlite.

Program info:
The program is a winform developed in c# using windows xp machine. The database is store in a file name tnt.sqlite. The table/columns/etc is created using sqlitebrowser.

The observation:
When run under windows 8, the program seems to show a different data when run as an admin compare to run normally. As if it is running using 2 different database.

Even when I delete the tnt.sqlite file, the program still runs and show some data from database.

in XP, when tnt.sqlite is deleted, the program will throw an exception.

The Problem:
1. When I made changes to the database files (using sqlitebrowser in xp) and copy it to windows 8, the program seems to still run using the old database.

  1. User who runs as an admin and normally will have 2 different set of databases.

Help Needed:
Can anyone point me on where should I start to study about sqlite running in windows 8?

If condition does not work in Android Spinner

I am facing an issue where the if condition is not validated in android.

I have an UI (the same UI as in previous questions) where in when the user clicks the save button the details are stored in the sqlite database.

Here is the sample image of the UI:

enter image description here

For every task, I have an image associated based on the priority which I store in the drawable folder.

    EditText sd = (EditText)findViewById(R.id.sd);
    EditText desc = (EditText)findViewById(R.id.description);
    Spinner type = (Spinner)findViewById(R.id.type);
    Spinner priority = (Spinner)findViewById(R.id.priority);
    int imageId = 0;
    if(priority.getSelectedItem().toString().equals("Low"))
        imageId = R.drawable.blue;
    else if(priority.getSelectedItem().toString().equalsIgnoreCase("medium"))
        imageId = R.drawable.green;
    else
        imageId = R.drawable.red;
    Log.d("priority",priority.getSelectedItem().toString());
    String query = "insert into tasks values(null,'"+sd.getText()+"','"+type.getSelectedItem().toString()+"','"+priority.getSelectedItem().toString()+"','"+desc.getText()+"',CURRENT_DATE,"+imageId+");";
    db.execSQL(query);
    Intent i = new Intent(this, MainActivity.class);
    startActivity(i);

After inserting into the database, I switchover to another activity, where the details are displayed as a list.

This particular code:

if(priority.getSelectedItem().toString().equals("Low"))
    imageId = R.drawable.blue;
else if(priority.getSelectedItem().toString().equalsIgnoreCase("medium"))
    imageId = R.drawable.green;
else
    imageId = R.drawable.red;

doesn't work. Because I get a deafult red image for all the tasks irrespective of the priority. Here Log.d("priority",priority.getSelectedItem().toString());, I wanted to check if the priority is selected properly, it displays Low but gives a red color which it shouldn't. Where I am doing the mistake?

Both equals() and equalsIgnoreCase() don't seem to work. Even == doesn't seem to work (I know it is wrong to use == to compare strings, but I wanted to give it a try).

For reference: The statement I used to create the table is as follows:

String query = "create table if not exists tasks (id integer primary key autoincrement, shortdesc varchar not null, type varchar not null, priority varchar not null, desc varchar not null, taskdate date not null, imageid int not null);";

Even with Spinner Listener, it doesn't seem to work. Here is the code:

priority.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
                @Override
                public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
                    if(priority.getSelectedItem().toString().equalsIgnoreCase("low"))
                        imageId = R.drawable.blue;
                    else if(priority.getSelectedItem().toString().equalsIgnoreCase("medium"))
                        imageId = R.drawable.green;
                    else
                        imageId = R.drawable.red;
                }

                @Override
                public void onNothingSelected(AdapterView<?> parent) {

                }
            });

And the values from the log for the images,

04-22 06:42:01.414    2418-2418/com.app D/imageId﹕ 0
04-22 06:42:01.416    2418-2418/com.app D/red﹕ 2130837558
04-22 06:42:01.416    2418-2418/com.app D/blue﹕ 2130837555
04-22 06:54:30.795    1942-1942/com.app D/priority﹕ Low

The imageId I get is 0, but I am supposed to get one of the above values. The D/priority is the value from the spinner (priority.getSelectedItem().toString()). Where I am going wrong?