DAO generated file error: Incorrect syntax near ')'.

[rip747]

Seems that the generated SQL for the create method in the dao is wrong and causing a syntax error.

Script to create testing table called tblFastFact:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblFastFact]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblFastFact]
GO

CREATE TABLE [dbo].[tblFastFact] (

[pk] [uniqueidentifier] NOT NULL ,
[fact] [varchar] (7000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[id] [int] IDENTITY (1, 1) NOT NULL

) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblFastFact] ADD

CONSTRAINT [DFTemporaryUpspk239E4DCF] DEFAULT (newid()) FOR [pk],
CONSTRAINT [aaaaatblFastFact_PK] PRIMARY KEY NONCLUSTERED
(

[pk]

) WITH FILLFACTOR = 90 ON [PRIMARY]

GO

CREATE INDEX [id] ON [dbo].[tblFastFact]([id]) WITH FILLFACTOR = 90 ON [PRIMARY]

GO

CFML template code that will cause the error:

<cfset Reactor = CreateObject("Component", "reactor.reactorFactory").init(expandPath("reactor.xml")) />
<cfset ff = Reactor.createRecord("tblFastFact")>
<cfset ff.setFact("testing this out")>
<cfset ff.save()>

Reactor.xml file configuration:

<reactor>

<config>

<project value="testing" />
<dsn value="testing" />
<type value="mssql" />
<mapping value="/_model" />
<mode value="always" />

</config>

</reactor>

Method generated with error:

<cfquery name="qCreate" datasource="#_getConfig().getDsn()#" username="#_getConfig().getUsername()#" password="#_getConfig().getPassword()#">
INSERT INTO #Convention.FormatObjectName(getObjectMetadata())#
(

#Convention.formatInsertFieldName('pk', 'tblFastFact')#
,
#Convention.formatInsertFieldName('fact', 'tblFastFact')#
,
<cfif (NOT Convention.supportsIdentity()) OR (false AND Convention.supportsSequences())>

#Convention.formatInsertFieldName('id', 'tblFastFact')#

</cfif>

) VALUES (

<cfqueryparam cfsqltype="cf_sql_idstamp" value="#Left(arguments.to.pk, 23)#-#Right(arguments.to.pk, 12)#" />
,
<cfqueryparam cfsqltype="cf_sql_varchar" maxlength="7000" value="#arguments.to.fact#" />
,
<cfif (NOT Convention.supportsIdentity()) OR (false AND Convention.supportsSequences())>

<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.to.id#" />

</cfif>

)
<cfif Convention.supportsMultiStatementQueries() AND Convention.supportsIdentity() >

#Convention.lastInsertedIdSyntax(getObjectMetadata())#

</cfif>
</cfquery>

Proposal to correct error:

<cfquery name="qCreate" datasource="#_getConfig().getDsn()#" username="#_getConfig().getUsername()#" password="#_getConfig().getPassword()#">
INSERT INTO #Convention.FormatObjectName(getObjectMetadata())#
(

<cfif (NOT Convention.supportsIdentity()) OR (false AND Convention.supportsSequences())>

#Convention.formatInsertFieldName('id', 'tblFastFact')#
,

</cfif>
#Convention.formatInsertFieldName('pk', 'tblFastFact')#
,
#Convention.formatInsertFieldName('fact', 'tblFastFact')#

) VALUES (

<cfif (NOT Convention.supportsIdentity()) OR (false AND Convention.supportsSequences())>

<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.to.id#" />
,

</cfif>
<cfqueryparam cfsqltype="cf_sql_idstamp" value="#Left(arguments.to.pk, 23)#-#Right(arguments.to.pk, 12)#" />
,
<cfqueryparam cfsqltype="cf_sql_varchar" maxlength="7000" value="#arguments.to.fact#" />

)
<cfif Convention.supportsMultiStatementQueries() AND Convention.supportsIdentity() >

#Convention.lastInsertedIdSyntax(getObjectMetadata())#

</cfif>
</cfquery>

Explanation of proposed correction:

Seems that the code that is generating the method is generating a "," outside of the <cfif> statement. What could make this an easy fix is to move the identity/sequence checks to the beginning of the statements.

[wakeless]

I'm attaching a patch for this, it is just putting the comma in the wrong place.

[wakeless]

moves the comma to the top of the check, inside the if is identity

[anonymous]

Milestone Beta deleted