INSERT WITH SELECT query in SQLITE giving me syntax error?

Question

can anyone let me know what m i doing wrong here .....

Here is my query and getting syntax error "near SELECT" when trying to run query in sqlite database manager and as well as its always entering into "errorfunction" during execution in phonegap app

tx.executeSql("INSERT INTO proposal_products(proposal_id,date_created,date_modified,labour_hours,cost_price,sale_price,adj_cost_price,adj_sale_price,service_price,adj_service_price,discipline_products_id) VALUES("
                        + window.localStorage
                                .getItem("assign_proposal_id")
                        + ",'"
                        + getCurrentDateTime()
                        + "','"
                        + getCurrentDateTime()
                        + "',"
                        + selectedLabourHours
                        + ","+ callStd +","
                        + callStd +","
                        + callStd +","
                        + callStd +","
                        + callStd +","
                        + callStd +",(SELECT id FROM discipline_products WHERE discipline_products.product_id = (SELECT id FROM products WHERE c4w_code = "CALSTD")))",
                [],
                function() {
                    console
                            .log("suceessCBinsertIntoProposalProduct when Checkbox unchecked:");
                    window.localStorage.setItem("discipline_product_idCallStd","discipline_IdCallSTD");
                },
                function(err) {
                    console
                            .log("errorCBinsertIntoProposalProduct when Checkbox unchecked:"
                                    + err.message);


});

First thing to fix: *don't build SQL like that*. Use a prepared statement. It'll make it much easier to see the syntax error *and* it'll protect you against conversion errors and SQL injection attacks. — Jon Skeet, Jul 05 '13 at 06:03
see `SELECT id FROM products WHERE c4w_code = "CALSTD"` instead it should be `SELECT id FROM products WHERE c4w_code = "+CALSTD+"` — CRUSADER, Jul 05 '13 at 06:04
@user1668447: Does that prevent you from using parameters? I seriously hope not. — Jon Skeet, Jul 05 '13 at 06:07
If you insist on using a String query I would advise you to have a look at `StringBuilder` for Performance issues. http://stackoverflow.com/questions/4645020/when-to-use-stringbuilder-in-java — Daniel Lerps, Jul 05 '13 at 06:08
@DanielLerps: Absolutely not. Using a StringBuilder wouldn't help performance at all here - there's just one big string concatenation. — Jon Skeet, Jul 05 '13 at 06:10
can anyone describe me what's the solution for that? i stuck here — nida, Jul 05 '13 at 06:13
@user1668447: I've already given you a solution for the syntax error, but it seems you haven't read it carefully enough. — Jon Skeet, Jul 05 '13 at 06:21

score 0 · Answer 1 · answered Jul 05 '13 at 06:06

0

The syntax error is here:

   + callStd +"[...] WHERE c4w_code = "CALSTD")))",

You probably meant:

   + callStd +"[...] WHERE c4w_code = 'CALSTD')))",

As it is, you've closed your string literal and then got CALSTD...

However, you really shouldn't build SQL the way you're doing at the moment. It's very hard to read, prone to conversion errors (particularly for date/time values) and open to SQL injection attacks. Use parameterized SQL, however that's done in your environment. If your environment doesn't support parameterized SQL, I would be very surprised.

answered Jul 05 '13 at 06:06

Jon Skeet

1,421,763
867
9,128
9,194

1

Sir, how can you affirm that `CALSTD` is a value and not a variable...?? OR, may be you just know it... – CRUSADER Jul 05 '13 at 06:10
CALSTD is a value not a variable – nida Jul 05 '13 at 06:11
1

@CRUSADER: It was an educated guess, based on a) being in quotes already, just the wrong kind of quotes; b) not following the same convention as any of the names in the code. – Jon Skeet Jul 05 '13 at 06:13
no its not syntax error at +callStd + this is right and this is variable while syntax error is in inner query its not allowing me to write in select in inner query – nida Jul 05 '13 at 06:17
can anyone pls elaborate me what's the solution pls it s urgent – nida Jul 05 '13 at 06:19
1

@user1668447: Did you actually look at the answer carefully? I didn't say anything about the `+ callStd + ` bits being wrong. This is right at the very end of the query... you're simply using the wrong quotes around the `CALSTD` value. – Jon Skeet Jul 05 '13 at 06:20
2

@user1668447: Well you didn't say that before. I don't know how you expected us to know that. Now what's the error? What's the *exact* error message? – Jon Skeet Jul 05 '13 at 06:22
there is no error message every its entering into errorCB of query and in mozilla sqlite manager its giving me error near SELECT inner query – nida Jul 05 '13 at 06:31
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/32925/discussion-between-user1668447-and-jon-skeet) – nida Jul 05 '13 at 06:37
@user1668447: No, I'm afraid I don't have time to chat now. You should edit your question to explain *eaxctly* what's going on. You should also include a log of the exact query it's running - it may well be a lot easier to spot the mistake then. – Jon Skeet Jul 05 '13 at 07:12
@JonSkeet ok np i got the mistake query was right but i was missing one column to insert and that column have constraints cannot be NULL – nida Jul 05 '13 at 07:20
1

@user1668447: Okay, glad you've cleared it up - although we couldn't have really helped you with that anyway... – Jon Skeet Jul 05 '13 at 07:20

INSERT WITH SELECT query in SQLITE giving me syntax error?

1 Answers1