B4J Question Help with sql command with primary key

strupp01 · Feb 4, 2018

I want to create a SQL DB with a Primary Key. Have written the following:

B4X:

sql_PLD.ExecNonQuery("CREATE TABLE IF NOT EXISTS BRP (id_nr INTEGER not null PRIMARY KEY(id_nr) unique, Datum_Aufzeichnung Text, Datum TEXT, Uhrzeit TEXT, Plus_Zeit Float, Flow_Rate Float, Mask_Pressure Float)")

It does not work. What am I doing wrong or who has an example?
Searching the net has yielded nothing.

Gianni M · Feb 4, 2018

usually i split as:

B4X:

CREATE TABLE IF NOT EXISTS BRP (id_nr INTEGER not null, Datum_Aufzeichnung Text, Datum TEXT, Uhrzeit TEXT, Plus_Zeit Float, Flow_Rate Float, Mask_Pressure Float);
CREATE UNIQUE INDEX if not exists id_nr ON BRP (id_nr)

strupp01 · Feb 4, 2018

The Create commands were accepted that way. I now get an error number with the insert command.
The command looks like this:

B4X:

sql_PLD.ExecNonQuery2("INSERT INTO BRP VALUES (?,?,?,?,?,?,?)", Array As Object(i, Allgemeine_Prozesse.Datum_der_Aufzeichnung, DB_Daten(0), DB_Daten(1),DB_Daten(2),DB_Daten(3),DB_Daten(4)))

Fehlermeldung :

Fehler in Zeile: 129
java.sql.SQLException: [SQLITE_CONSTRAINT] Abort due to constraint violation (column id_nr is not unique)
at org.sqlite.DB.newSQLException(DB.java:383)
at org.sqlite.DB.newSQLException(DB.java:387)
at org.sqlite.DB.execute(DB.java:342)
at org.sqlite.PrepStmt.execute(PrepStmt.java:65)
at anywheresoftware.b4j.objects.SQL.ExecNonQuery2(SQL.java:200)
at sun.reflect.GeneratedMethodAccessor18.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:657)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:234)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:93)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:77)
at anywheresoftware.b4a.shell.DebugResumableSub$RemoteResumableSub.resume(DebugResumableSub.java:19)
at anywheresoftware.b4a.keywords.Common$2$1.run(Common.java:1013)
at com.sun.javafx.application.PlatformImpl.lambda$null$173(PlatformImpl.java:295)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.application.PlatformImpl.lambda$runLater$174(PlatformImpl.java:294)
at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.java:191)
at java.lang.Thread.run(Thread.java:745)

Eme Fibonacci · Feb 5, 2018

(column id_nr is not unique)

Harris · Feb 5, 2018

Sqlite:

SQL1.ExecNonQuery("CREATE TABLE IF NOT EXISTS scalemast (pk INTEGER PRIMARY KEY, loadtype INTEGER, acttype INTEGER, recdate INTEGER, drvid INTEGER, trkid TEXT, trl1id TEXT, trl2id TEXT, trl3id TEXT, gross INTEGER, tare INTEGER, rectype TEXT, lat REAL, lon REAL )")

Of course, every insert must have a unique pk field.

EnriqueGonzalez · Feb 5, 2018

strupp01 said:
sql_PLD.ExecNonQuery2("INSERT INTO BRP VALUES (?,?,?,?,?,?,?)",

Use the name of the columns, if you do not, SQL will try to insert in the id field.

strupp01 · Feb 5, 2018

Sorry, the error (column id_nr is not unique) was caused by incorrect programming by me. Double id_nr was created.
Thank you for your help.
Now I want to evaluate the DB with select command. I have a table with about 750000 records. Structure of the DB is

My evaluation Without PRIMARY KEY in DB and with PRIMARY KEY in DB takes the same amount of time.

B4X:

Flow_Rate_Max_Ausg = sql_Tag_Dat.ExecQuerySingleResult("Select max(Flow_Rate) FROM BRP where id_nr >= " & l & " and id_nr <= " & Such_Breite)

Does the SELECT command have to look different for faster editing?

strupp01

keirS · Feb 5, 2018

You should not explicitly create an index for the primary key. An index is generated automatically by SQLite. A INTEGER PRIMARY KEY in SQLite is twice as fast as any index because it becomes a alias for the ROWID. See http://www.sqlite.org/lang_createtable.html#rowid

strupp01 · Feb 5, 2018

My first attempt was without an index for the PRIMARY KEY. But that did not bring any faster results. Do not know how to continue?

Harris · Feb 5, 2018

You will need to teach yourself SQL.

The example shows what kind of result you will get from your statement...
Dim NumberOfMatches As Int
NumberOfMatches = SQL1.ExecQuerySingleResult("SELECT count(*) FROM table1 WHERE col2 > 300")

A single result is returned with the count of records greater than 300.

In your case, it shall be the highest flow rate where id is > than "x" and id is less than "x"

And good advise from @keirS...

Harris · Feb 5, 2018

strupp01 said:
But that did not bring any faster results

What is faster? How much time does it take?

What is the range of id's you are searching? This is the determining speed factor.
The id is integer and indexed - along with index on flow_rate (float may rob performance - don't know...)... It should be pretty quick... depending on the range, your hardware and SQLite performance. I use MySQL for server DB and only use SQLite on mobile devices for small short term storage.

strupp01 · Feb 5, 2018

The hint of @keirS I have implemented. Since the runtime was still too high, I have hidden all other lines in the loop. Behold, the running time has fallen to 0.4 seconds . Then I searched for the line that created the big runtime and changed it. Now I am very satisfied with the running time.
Thanks to all for your efforts and suggestions.

Greeting strupp01

B4J Question Help with sql command with primary key

strupp01

Active Member

Gianni M

Well-Known Member

strupp01

Active Member

Eme Fibonacci

Well-Known Member

Harris

Expert

EnriqueGonzalez

Expert

strupp01

Active Member

keirS

Well-Known Member

strupp01

Active Member

Harris

Expert

Harris

Expert

strupp01

Active Member

Similar Threads

B4J Question Help with sql command with primary key

Active Member

Well-Known Member

Active Member

Well-Known Member

Expert

Expert

Active Member

Well-Known Member

Active Member

Expert

Expert

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency