Error Executing Database Query. Syntax error: Encountered "(" at line 10, column 37.

I have to learn cold fusion for and upcoming project ... I'm trying to set up a simple database coldfsion11 apache derby embedded (I have no experience with apache derby) ... But I keep getting the same syntax error ... maybe a fresh set of eyes can tell me what I am doing wrong


 Error Executing Database Query.
Syntax error: Encountered "(" at line 10, column 37.
 
The error occurred in C:/ColdFusion11/cfusion/wwwroot/SRPT/DatabaseCreator.cfc: line 80
Called from C:/ColdFusion11/cfusion/wwwroot/SRPT/createdatabase.cfm: line 7
Called from C:/ColdFusion11/cfusion/wwwroot/SRPT/DatabaseCreator.cfc: line 80
Called from C:/ColdFusion11/cfusion/wwwroot/SRPT/createdatabase.cfm: line 7

78 : 		<cffunction name="createevent" access="public" returnType="String" >
79 : 			<cfset  droptable('locTab')>
80 : 		<cfquery datasource = "Reports">
81 : 		CREATE TABLE
82 : 			 locationTable(loctabID INTEGER
                                                    NOT NULL 
                                                 GENERATED ALWAYS AS IDENTITY,

<cffunction name="createevent" access="public" returnType="String" >
			<cfset  droptable('locTab')>
		<cfquery datasource = "Reports">
		CREATE TABLE
			 locationTable(loctabID INTEGERS NOT NULL GENERATED ALWAYS AS IDENTITY,
		    	usersID VARCHAR(50),
		    	WawaID VARCHAR(50),
		    	AccesLevel VARCHAR(50),
		    	date VARCHAR(50),
		    	name VARCHAR(50),
		    	postalcode VARCHAR(50),
		    	city VARCHAR(50),
		    	state VARHAR(50),
		    	zip VARCAHR(50))
			
		</cfquery>
			</cffunction>


 Error Executing Database Query.
Syntax error: Encountered "(" at line 10, column 43.
 
The error occurred in C:/ColdFusion11/cfusion/wwwroot/SRPT/DatabaseCreator.cfc: line 123
Called from C:/ColdFusion11/cfusion/wwwroot/SRPT/createdatabase.cfm: line 9
Called from C:/ColdFusion11/cfusion/wwwroot/SRPT/DatabaseCreator.cfc: line 123
Called from C:/ColdFusion11/cfusion/wwwroot/SRPT/createdatabase.cfm: line 9

121 : 			
122 : 		
123 : 		<cfquery datasource="Reports">
124 : 		CREATE TABLE
125 : 			Patient(PatientID INTEGER

<cffunction name="createincident" access="public" returntype="String" >
			
		
		<cfquery datasource="Reports">
		CREATE TABLE
			Incident (IncidentID INTEGER
				NOT NULL
			    GENERATED BY DEFAULT AS IDENTITY (START WITH 53)
		    	CONSTRAINT Incident_PK PRIMARY KEY,
		    	usersID VARCHAR(50),
		    	WawaID VARCHAR(50),
		    	loctabID VARCHAR(50),
		    	AccesLevel VARCHAR(50),
		    	date VARCHAR(50),
		    	time VARCHAR(50),
		    	incident_num VARCHAR(50),
		    	lastname VARCHAR(50),
		    	firstname VARCHAR(50)
		    	
			)
		</cfquery>
		
		
		</cffunction>

Open in new window

dolamitejenkinsAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
>> actually using lyndia to learn an the example does it this way

I agree with GD. While technically you can create/drop tables through CF, normally you just connect to your database directly using a GUI tool.

To answer your question, you misspelled a few of the data types. That's the cause of the syntax error.


CREATE TABLE
     locationTable(loctabID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
     ...
     state VARCHAR(50),
     zip VARCHAR(50)
)


>> apache derby

I know you're following an example, but keep in mind you can use any database you want. The examples use Apache Derby database.because it's easy to embed. I've never used it outside of running examples.  If you'll ultimately be working with a different database like SQL Server or MySQL, you might want to start with that instead.
0
 
gdemariaCommented:
I don't even know if you can create a table through coldfusion - do you have "create" checked off for the data source in the CFIDE/Administrator?

Depending on which database there are some things I wonder if are legal, you're using "date" and "time" as column names, that may be reserved words.

I think it's odd to create and drop tables through your application - why not just make the tables and then delete and fill them instead of drop/create them?
0
 
dolamitejenkinsAuthor Commented:
actually using lyndia to learn an the example does it this way but only creates 2 tables .... I will chech the date and time and see if that is the problem ... how else do you create a database in cold fusiom ... server side work in not my thing I'm trying to learn fast
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
gdemariaConnect With a Mentor Commented:
just connect to the database and create the table in there, nothing to do with coldfusion.   Then let coldfusion create and delete the records as needed.

Do you have more than one user accessing these report tables?  If so, you need to add/delete records by user, if you delete all, or drop the table, all the users will be effected, right?
0
 
dolamitejenkinsConnect With a Mentor Author Commented:
great point thanks
0
 
dolamitejenkinsAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.