SQL - Syntax Error in valid sql, but only in CF?

[marksta80]

Hi CF-people

As a php coder, I'm having quite a hard time getting used to cf :)

I've coded a component which generates a SQL-Insert Statement and (tries to)
execute it. When I cfout and copy/paste it to the (ms-sql) query analyzer it
works but if I want CF to do it, it throws a Syntax Error? Does anybody of you
know where my problem is?

I'd appreciate any kind of hint!
Markus

PS: Thats the Statement:

INSERT INTO Person (id,Name,Vorname,Eintrittsdatum,Email) VALUES (235,
'TestName', 'TestFirstname', '12.07.05', 'test@email.com')

And the Error Statement (unfortunatelly in german)

[Macromedia][SQLServer JDBC Driver][SQLServer]Zeile 1: Falsche Syntax in der
N?he von 'TestName'.

[mpwoodward *TMM*]

On 2005-07-17 10:39:39 -0500, "marksta80" <webforumsuser@macromedia.com> said:

> Hi CF-people
>
> As a php coder, I'm having quite a hard time getting used to cf :)
>
> I've coded a component which generates a SQL-Insert Statement and
> (tries to) execute it. When I cfout and copy/paste it to the (ms-sql)
> query analyzer it works but if I want CF to do it, it throws a Syntax
> Error? Does anybody of you know where my problem is?
>
> I'd appreciate any kind of hint!
> Markus
>
> PS: Thats the Statement:
>
> INSERT INTO Person (id,Name,Vorname,Eintrittsdatum,Email) VALUES (235,
> 'TestName', 'TestFirstname', '12.07.05', 'test@email.com')
>
> And the Error Statement (unfortunatelly in german)
>
> [Macromedia][SQLServer JDBC Driver][SQLServer]Zeile 1: Falsche Syntax
> in der N?he von 'TestName'.

My first guess would be that the date format is throwing it. Try
building your statement up one piece at a time to see where it breaks.
For example, can you just insert the ID? Just ID and first name? etc.
(Your ability to do this might be affected by how you have your
nullable fields set in your database.)

Matt
--
Matt Woodward
[email]mpwoodward@gmail.com[/email]
Team Macromedia - ColdFusion

[MikerRoo]

Use the PreserveSingleQuotes() function in your cfquery...

<CFQUERY ...>
#PreserveSingleQuotes (GeneratedSQL_Text)#
</CFQUERY>

[MikerRoo]

Wrap your query string in PreserveSingleQuotes().

[marksta80]

Thank you for your help. I've set up all fields as text fields, so I don't need
to worry (yet) about the data-format.

But I keep getting these syntax errors... but copy&past to query analyzer does
still work.

I'd be really happy if may have another tip.
Thanks
Markus

Here's my code, did I make an mistake?

<cfset insVals= ArrayToList(#values#,", ")>
<cfset insVals= #PreserveSingleQuotes(insVals)#>
<cfset insStr = #insStr# & " (" & ArrayToList(#columns#,",") & ") VALUES (" &
#insVals# & ")">

[MikerRoo]

That's not quite right.

Use PreserveSingleQuotes() like this:

<CFSET sSQL_Str = "INSERT INTO SomeTable VALUES ('Some text', 25)">
<CFQUERY ... ...>
#PreserveSingleQuotes (sSQL_Str)#
</CFQUERY>

If that's not enough, Post your entire CFQuery code.

Regards,
-- MikeR

[marksta80]

That's how it's done! Thank you very much!