0

I'm trying to insert a special character values into my sqlite table. For that i have used following query, that contains octal values. 

 "INSERT INTO \"manufacturers\" VALUES(NULL,NULL,NULL,'1970-01-01 00:00:00','1970-01-01 00:00:00','35','A Priorite\\047');"

when i displaying the names it doesn't converted to corresponding string value, it displays the same octal value inserted as (A Priorite\047);

Now i need an suggestion to convert the octal value to string or char by using sqlite statements.

I know we can easily obtain it by using java code but my requirement is to do it by use of sqlite statements. Here the last value is the name field which is contains the octal value of \047(as string ').

I've also tried to convert the decimal value to char, it works fine. In my case, if i convert the octal value to decimal value then i can easily convert to char. but i can't convert octal to decimal.

Note: I'm having thousands of record, so can't manually insert the special characters into each row of table. And I'm not supposed to insert the values manually.

Thanks in Advance. Any help would be appreciated.

Improve this question
6
  • Why are you escaping slash? 'A Priorite\047' should work. Commented Nov 26, 2013 at 8:58
  • No it doesn't work for me. I'm getting error like this >>>android.database.sqlite.SQLiteException: unrecognized token: "'A Priorite'');" (code 1): , while compiling: INSERT INTO "manufacturers" VALUES(NULL,NULL,NULL,'1970-01-01 00:00:00','1970-01-01 00:00:00','35','A Priorite'');>>> Commented Nov 26, 2013 at 9:15
  • I agree with @CL, use parameters. Correct escaping in Java/C is using \0###, where ### are octal digits. Commented Nov 26, 2013 at 9:42
  • thanks for your reply. Could you please brief about your previous one? it takes next three digits after the backward slash. Now the problem is in placing the correct backward slash in my code. i'm using java program. Commented Nov 26, 2013 at 10:05
  • ### from 1 to 377. But SQLite is complaining about statement, you MUST use parameters as @CL answer. Commented Nov 26, 2013 at 10:11

2 Answers 2

Reset to default
1

SQLite does not have octal escapes.

In Android, you should just use parameters to insert strings:

String sillyDate = "1970-01-01 00:00:00";
String someNumberAsString = "35";
String name = "A Priorite\047";
db.execSQL("INSERT INTO \"manufacturers\" VALUES(NULL,NULL,NULL,?,?,?,?)",
           new Object[] { sillyDate, sillyDate, someNumberAsString, name });

If you really want to do this in SQL, you have to use a hexadecimal blob literal and convert that to text:

"INSERT INTO \"manufacturers\" VALUES(NULL,NULL,NULL,'1970-01-01 00:00:00',"+
"'1970-01-01 00:00:00','35','A Priorite' || x'27');"

Please note that in SQL, quotes inside strings can be escaped by doubling them:

"INSERT INTO \"manufacturers\" VALUES(NULL,NULL,NULL,'1970-01-01 00:00:00',"+
"'1970-01-01 00:00:00','35','A Priorite''');"
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I resolved my problem. Now it's working cool. I just changed my insert query to below one. "INSERT INTO \"manufacturers\" VALUES(NULL,NULL,NULL,'1970-01-01 00:00:00','1970-01-01 00:00:00','35','A Priorite\047'');" Thanks for LS_dev. I accept @CL's answer.
0

I resolved my problem. Now it's working cool. I just changed my insert query to below one.

 "INSERT INTO \"manufacturers\" VALUES(NULL,NULL,NULL,'1970-01-01 00:00:00','1970-01-01 00:00:00','35','A Priorite\047'');"

Thanks to @LS_dev and @CL. I accept @CL's answer.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.