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);
    }

}