We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

sql syntax error

alzzz
alzzz asked
on
Medium Priority
384 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

Comment
Watch Question

Author

Commented:
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?
TimCotteeHead of Software Services
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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
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!
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.

Author

Commented:
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;
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

Author

Commented:
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

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.

Author

Commented:
i tried it with double quotes also - thats the documentation i found too!
Have you tried dropping the trailing semicolon?

Author

Commented:
just did that and got the same error
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

I should say I ran that query in dbaccess.
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

Author

Commented:
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
Ho alzzz,
I always use the "Use editor" option.

Cheers
... 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 ?


Author

Commented:
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'

Author

Commented:
ah enter does it - that will be handy in the future!
Out of my depth now - but I Googled the error and found this
http://www.jboss.org/community/docs/DOC-9582

Author

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

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

Author

Commented:
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!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.