How to use rawQuery() method to insert a record?

Question

I need to store a record using rawQuery() method, because I want to insert the current date and time (datetime()), but I also need to insert strings that contain quotes.

So I wrote this code:

String sql="INSERT INTO sms VALUES ( null, ?1, ?2, ?3, datetime())";
dbw.rawQuery(sql, new String[]{str1,str2,str3});

But it doesn't store anything... what's wrong?

[EDIT]

In this way I don't get errors, but the record is not inserted.

String mitt="mitt", dest="dest", text="text";
String sql="INSERT INTO sms VALUES ( null, ?, ?, ?, datetime('NOW'))";
dbw.rawQuery(sql, new String[]{mitt,dest,text});

At this moment, the only method that works to insert a record (with quotes problem) is execSQL(String s).

After issuing a `rawQuery` you need to call `moveToFirst()` so that the query gets executed. — Daniel F, Aug 25 '19 at 10:37

score 21 · Accepted Answer · edited May 23 '17 at 12:00

21

SQLite doesn't have a native datetime data storage type. It can store a string or an integer representation of a date instead.

To convert your values you can use the date time functions detailed in Sqlite Date and Time functions documentation

Your initial attempt is almost correct, but your datetime() function call requires an argument of 'NOW'.

String sql="INSERT INTO sms VALUES ( null, ?, ?, ?, datetime('NOW'))";

Also you should call execSQL instead of rawQuery which is expecting to return a recordset.

dbw.execSQL(sql, new String[]{str1,str2,str3});

You can alo specify individual columns to insert data into by inserting a field list after the table name in your query if not inserting all the values

String sql = "INSERT INTO sms(f1, f2, f3, f4)"
           + "VALUES ( null, ?, ?, ?, datetime('NOW'))";

Another option that may be possible is using a default timestamp in SQLite ,although I have not attempted this in android.

edited May 23 '17 at 12:00

Community

1
1

answered Sep 19 '11 at 18:52

Moog

10,193
2
40
66

Is it possible that rawQuery doesn't work with INSERT? The documentation doesn't say anything about it – supergiox Sep 19 '11 at 20:38
Nothing... this is the method that works (eccept for quotes): `String sql="INSERT INTO sms VALUES ( null , '"+mitt+"', '"+dest+"', '"+text+"', datetime('NOW') )"; dbw.execSQL(sql);` – supergiox Sep 19 '11 at 21:20
1

I have updated the answer ... if you are still experiencing errors please you should look at the output from logcat. Please clean any out of date comments to keep answer tidy – Moog Sep 19 '11 at 21:46
I found another solution as I answer to myself and it seems to work fine! However, thanks a lot for helping me! – supergiox Sep 19 '11 at 23:06
What is amazing is that documentation for execSQL states that `Execute a single SQL statement that is NOT a SELECT/INSERT/UPDATE/DELETE.` – m0skit0 Nov 11 '16 at 10:03
@m0skit0 you are indeed correct, however please note that the original answer was posted 5 years previously. The API and documentation may have both changed since. Current documentation urges developers to use the individual operator helper instructions instead (which is both far simpler to read and easier to implement). – Moog Nov 25 '16 at 13:04
@Merlin Unfortunately the helper operations are not enough in many common cases (e.g. joins). The problem is that both the documentation and solution have not changed. Personally I suggest the use of ORMLite. – m0skit0 Nov 25 '16 at 15:24
1

@m0skit0 The [documentation](https://web.archive.org/web/20100909222827/http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html) has changed, previously it only stated that execsql cannot be used for a query – Moog Jan 27 '17 at 21:15

score 4 · Answer 2 · edited Jul 15 '14 at 04:28

4

I solved the problem with in this way:

String sql="INSERT INTO sms VALUES (null,?,?,?,datetime('NOW'))";
dbw.execSQL(sql,new Object[]{mitt,dest,text});

Finally I can store every char without problems!!!

edited Jul 15 '14 at 04:28

Hamid Shatu

9,664
4
30
41

answered Sep 19 '11 at 23:03

supergiox

1,586
7
19
27

unfortunately I had not seen your answer when I found it...Anyway you deserve the accept! – supergiox Sep 19 '11 at 23:17
And what about manual which says for execSQL: Execute a single SQL statement that is NOT a SELECT/INSERT/UPDATE/DELETE – Enriqe Dec 08 '13 at 18:02

score -2 · Answer 3 · edited Nov 13 '12 at 05:51

-2

Try it like this:

ContentValues values;
values=new ContentValues();

// values.put("field_name",value);
values.put("id", 5); 
values.put("name", name);
dbw.insert("table_name", null, values);

edited Nov 13 '12 at 05:51

user

86,916
18
197
190

answered Sep 19 '11 at 12:19

ilango j

5,967
2
28
25

But in this way I can't use datetime() – supergiox Sep 19 '11 at 12:57
you can save datetime as long value in sqlite db. By getting cal.getTimeinMillis(); and can convert long value into dataTime in java. – ilango j Sep 19 '11 at 13:28
I would like to use datetime() of sqlite to avoid format problems... but if this is the only way, how can I convert getTimeinMillis() into datetime? – supergiox Sep 19 '11 at 14:20
refer this http://www.roseindia.net/java/java-conversion/DateToLong.shtml to convert date to long and long to date. – ilango j Sep 20 '11 at 04:21

score -2 · Answer 4 · answered Sep 19 '11 at 13:05

-2

String sql="INSERT INTO sms VALUES ( null, '"+str1+"', '"+str2+"', '"+str3+"', datetime())";
dbw.rawQuery(sql, null);

Mark the single quotes.

answered Sep 19 '11 at 13:05

Umesh

4,406
2
25
37

1

using direct strings in an SQL query can make your code prone to SQL Injection, do not use it – Anuj Nov 13 '14 at 06:18

How to use rawQuery() method to insert a record?

4 Answers4

Linked