?
Solved

sql syntax error

Posted on 2009-02-16
24
Medium Priority
?
370 Views
Last Modified: 2013-12-24
Hi,

im getting a sql exception thrown from the line of code below.

The error is "SQL Exception: A syntax error has occurred."

both 'fav_price' and 'surface' are doubles in the java code and decimals in the informix db.

can anybody see an obvious error?
"select surface,st_momemtum,mom_ampli,momasy_p1,momasy_p2,set_to_set,score_inf,st_sts,effect_11_22 from guide_params_3_set where fav_price = "+fav_price +" and surface = "+ inputs.SURFACETYPE+";"

Open in new window

0
Comment
Question by:alzzz
  • 11
  • 6
  • 5
  • +2
24 Comments
 

Author Comment

by:alzzz
ID: 23649603
also if i want to us 'as' in the select query how can i place the quotations within the java string variable, what character escape do i use?
0
 
LVL 43

Assisted Solution

by:TimCottee
TimCottee earned 200 total points
ID: 23649622
Hello alzzz,

"select surface,st_momemtum,mom_ampli,momasy_p1,momasy_p2,set_to_set,score_inf,st_sts,effect_11_22 "from guide_params_3_set where fav_price = "+fav_price +" and surface = "+ inputs.SURFACETYPE+";"

Has an extra " before the word "from" which is incorrect syntax.

"select surface,st_momemtum,mom_ampli,momasy_p1,momasy_p2,set_to_set,score_inf,st_sts,effect_11_22 from guide_params_3_set where fav_price = "+fav_price +" and surface = "+ inputs.SURFACETYPE+";"

Is better, using as should be a straightforward as:

"select surface,st_momemtum as [Momentum],mom_ampli As [Momentum Amplitude] ,momasy_p1,momasy_p2,set_to_set,score_inf,st_sts,effect_11_22 "from guide_params_3_set where fav_price = "+fav_price +" and surface = "+ inputs.SURFACETYPE+";"

For example.

Regards,

TimCottee
0
 

Author Comment

by:alzzz
ID: 23649633
sorry that extra " was there because i copied and pasted from eclipse and the line was over several lines, it isn't actually in the executed sql
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 800 total points
ID: 23649699
The escape would be \" -- http://research.cs.queensu.ca/home/cisc101/winter2005/webnotes/strings.html

You said Java code, right?

Using that on the first field:
"select surface as \"MySurface\",st_momemtum,mom_ampli,momasy_p1,momasy_p2,set_to_set,score_inf,st_sts,effect_11_22 from guide_params_3_set where fav_price = "+fav_price +" and surface = "+ inputs.SURFACETYPE+";"

Nothing looks like a reserved Informix word ... I'm not seeing the original error!
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 23649726
Would you output the actual SQL string after it's put together?  I suspect that will show something is just not being assembled in the way you expect ... and will make the error obvious.
0
 

Author Comment

by:alzzz
ID: 23649820
this is the actual sql string that is ran on the db (i added the as's)

 select surface as "SURFACE",st_momemtum as "STMOM",mom_ampli as "MOM_AMPLITUTDE",momasy_p1 as "MOM1",momasy_p2 as "MOM2",set_to_set as "SS",score_inf as "SCORE_INFLUENCE",st_sts as "STSTS",effect_11_22 as "EFFECT" from guide_params_3_set where fav_price = 1.02 and surface = 0.56;
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 200 total points
ID: 23649893
You should place your as "ALIAS" in single quotes or brackets.
"select surface as 'SURFACE',st_momemtum as 'STMOM',mom_ampli as 'MOM_AMPLITUTDE',momasy_p1 as 'MOM1',momasy_p2 as 'MOM2',set_to_set as 'SS',score_inf as 'SCORE_INFLUENCE',st_sts as 'STSTS',effect_11_22 as 'EFFECT' 
from guide_params_3_set where fav_price = "+fav_price +" and surface = "+ inputs.SURFACETYPE+";"

Open in new window

0
 

Author Comment

by:alzzz
ID: 23649950
changed to the below string and still getting the same error
 select surface as [SURFACE],st_momentum as [STMOM],mom_ampli as [MOM_AMPLITUTDE],momasy_p1 as [MOM1],momasy_p2 as [MOM2],set_to_set as [SS],score_inf as [SCORE_INFLUENCE],st_sts as [STSTS],effect_11_22 as [EFFECT] from guide_params_3_set where fav_price = 1.02 and surface = 0.56;

Open in new window

0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 23649983
Try dropping the ;

The Informix guides I'm finding are not terminating statements with that.  Not sure ... but that's just what I'm seeing ...

Also, what I found said aliases should be double-quoted ... again, not sure,just going on the documentation I'm finding.
0
 

Author Comment

by:alzzz
ID: 23650002
i tried it with double quotes also - thats the documentation i found too!
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 23650013
Have you tried dropping the trailing semicolon?
0
 

Author Comment

by:alzzz
ID: 23650024
just did that and got the same error
0
 
LVL 5

Assisted Solution

by:flutophilus
flutophilus earned 800 total points
ID: 23650281
I built a your table and added your columns to it, of course I don't knowwhat data types you're using, anyway I ran the following query against it with no problems

select surface ,st_momentum as STMOM,mom_ampli as MOM_AMPLITUTDE,momasy_p1 as MOM1,momasy_p2 as MOM2,set_to_set as SS,score_inf as SCORE_INFLUENCE,st_sts as STSTS,effect_11_22 as EFFECT from guide_params_3_set where fav_price = 1.02 and surface = 0.56;

It errored if I put square brackets around the aiases. I'm running an old version of Informix here (7.3) so perhaps the square brackets were not supported in that version.
Anyway you don't need any quotes or brackets around an alias and the "as" is optional. I guessi f you use a reserve word as an alias it will fail but why would you do that anyway?

Cheers





select surface ,st_momentum as STMOM,mom_ampli as MOM_AMPLITUTDE,momasy_p1 as MOM1,momasy_p2 as MOM2,set_to_set as SS,score_inf as SCORE_INFLUENCE,st_sts as STSTS,effect_11_22 as EFFECT from guide_params_3_set where fav_price = 1.02 and surface = 0.56;

Open in new window

0
 
LVL 5

Expert Comment

by:flutophilus
ID: 23650288
I should say I ran that query in dbaccess.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 23650350
I think perhaps you need the quotes around the column name being aliased ... if it is a reserved word.
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.esqlj.doc/esqlj45.htm


"select \"surface\" as MySurfaceAlias,st_momemtum,mom_ampli,momasy_p1,momasy_p2,set_to_set,score_inf,st_sts,effect_11_22 from guide_params_3_set where fav_price = "+fav_price +" and surface = "+ inputs.SURFACETYPE+";"

Open in new window

0
 

Author Comment

by:alzzz
ID: 23650362
when running a query in dbaccess how do you get the editor to allow you to enter over x amount of characters?

it doesnt allow me to type the whole of the above query
0
 
LVL 5

Expert Comment

by:flutophilus
ID: 23651336
Ho alzzz,
I always use the "Use editor" option.

Cheers
0
 
LVL 5

Expert Comment

by:flutophilus
ID: 23651361
... having said that - I've just typed the whole query in using the Modify option. You do have to hit enter at the end of each line though !!! What OS are you working on ?


0
 

Author Comment

by:alzzz
ID: 23651368
right so ive been cutting down the sql string until just 'select * from table;'

i still receive the same error but have now noticed this in the server log

 'RecoverAtomicAction: transaction -53efbd1a:c608:49898c71:cf not activated, unable to replay phase 2 commit'
0
 

Author Comment

by:alzzz
ID: 23651380
ah enter does it - that will be handy in the future!
0
 
LVL 5

Expert Comment

by:flutophilus
ID: 23651551
Out of my depth now - but I Googled the error and found this
http://www.jboss.org/community/docs/DOC-9582
0
 

Author Comment

by:alzzz
ID: 23651612
yup i found that too - judging by that scenario, my app didnt cause that error, must  have been a different one on the server.

0
 
LVL 5

Expert Comment

by:flutophilus
ID: 23651920
So a trivial query such as
select * from guide_params_3_set;
is failing with a syntax error in dbaccess  or Java?.

0
 

Author Comment

by:alzzz
ID: 23652412
yes - just spend last hr debugging the connection library i was using - bad assumption in the code caused the error.

sorry guys - thanks for your time!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question