jameskane
asked on
Coldusion - DATA insert syntax problem
I have two databases mounted on mysql server and fully connected to coldfusion server as datasources.
Pages work correctly, so no problem with databases.
DATASOURCE office_17 has a table members2 with data which I need to insert into the members2 table of DATASOURCE office_18.
Both data sources are identical - except for table members2 /office_17 which has data and members2/office_18 which is empty.
I need to populate members2/office_18 with data from members2/office_17.
The coldfusion code is below along with the error message. Its a syntax problem and I just can't see where it is. If someone could
talke a look - I'm sure the problem is obvious. Many thanks.
CODE
ERROR MESSAGE
Error Executing Database Query.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES (
121513,
'M',
'RAMEL',
'Jean Lo' at line 15
The error occurred in C:/ColdFusion2016/cfusion/ wwwroot/of fice_18/ne wyear_memb ers2.cfm: line 45
43 : '#nomresponsible#',
44 : '#prenomresponsible#',
45 : #Naissance#,
46 : </CFQUERY>
47 :
SQLSTATE 42000
DATASOURCE office_18
VENDORERRORCODE 1064
SQL
INSERT INTO members2(
memberID,
civilites,
Nom,
Prenom,
Adresse,
ZIP,
Ville,
tel,
mobile,
email,
nomresponsible,
prenomresponsible,
Naissance
VALUES (
121513,
'M',
'RAMEL',
'Jean Loup',
'Le Prince d''Azur_ 662, route de Serra Capeou',
'06110',
'LE CANNET',
'0489826477',
'0687984634',
'jeanloup.ramel@sfr.fr',
'not available',
'not available',
1000,
Resources:
Check the ColdFusion documentation to verify that you are using the correct syntax.
Search the Knowledge Base to find a solution to your problem.
Browser Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36
Remote Address 0:0:0:0:0:0:0:1
Referrer
Date/Time 16-Feb-17 12:02 PM
Stack Trace
at cfnewyear_members22ecfm195 9874268.ru nPage(C:/C oldFusion2 016/cfusio n/wwwroot/ office_18/ newyear_me mbers2.cfm :45)
at cfnewyear_members22ecfm195 9874268.ru nPage(C:/C oldFusion2 016/cfusio n/wwwroot/ office_18/ newyear_me mbers2.cfm :45)
com.mysql.jdbc.exceptions. jdbc4.MySQ LSyntaxErr orExceptio n: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES (
121513,
'M',
'RAMEL',
'Jean Lo' at line 15
at sun.reflect.NativeConstruc torAccesso rImpl.newI nstance0(N ative Method)
at sun.reflect.NativeConstruc torAccesso rImpl.newI nstance(Na tiveConstr uctorAcces sorImpl.ja va:62)
at sun.reflect.DelegatingCons tructorAcc essorImpl. newInstanc e(Delegati ngConstruc torAccesso rImpl.java :45)
at java.lang.reflect.Construc tor.newIns tance(Cons tructor.ja va:423)
at com.mysql.jdbc.Util.handle NewInstanc e(Util.jav a:425)
at com.mysql.jdbc.Util.getIns tance(Util .java:408)
at com.mysql.jdbc.SQLError.cr eateSQLExc eption(SQL Error.java :943)
at com.mysql.jdbc.MysqlIO.che ckErrorPac ket(MysqlI O.java:397 0)
at com.mysql.jdbc.MysqlIO.che ckErrorPac ket(MysqlI O.java:390 6)
at com.mysql.jdbc.MysqlIO.sen dCommand(M ysqlIO.jav a:2524)
at com.mysql.jdbc.MysqlIO.sql QueryDirec t(MysqlIO. java:2677)
at com.mysql.jdbc.ConnectionI mpl.execSQ L(Connecti onImpl.jav a:2545)
at com.mysql.jdbc.ConnectionI mpl.execSQ L(Connecti onImpl.jav a:2503)
at com.mysql.jdbc.StatementIm pl.execute Internal(S tatementIm pl.java:83 9)
at com.mysql.jdbc.StatementIm pl.execute (Statement Impl.java: 927)
at coldfusion.server.j2ee.sql .JRunState ment.execu te(JRunSta tement.jav a:359)
at coldfusion.sql.Executive.e xecuteQuer y(Executiv e.java:153 1)
at coldfusion.sql.Executive.e xecuteQuer y(Executiv e.java:128 1)
at coldfusion.sql.Executive.e xecuteQuer y(Executiv e.java:121 1)
at coldfusion.sql.SqlImpl.exe cute(SqlIm pl.java:40 6)
at coldfusion.tagext.sql.Quer yTag.execu teQuery(Qu eryTag.jav a:1202)
at coldfusion.tagext.sql.Quer yTag.start QueryExecu tion(Query Tag.java:8 15)
at coldfusion.tagext.sql.Quer yTag.doEnd Tag(QueryT ag.java:76 8)
at cfnewyear_members22ecfm195 9874268.ru nPage(C:\C oldFusion2 016\cfusio n\wwwroot\ office_18\ newyear_me mbers2.cfm :45)
at coldfusion.runtime.CfJspPa ge.invoke( CfJspPage. java:251)
at coldfusion.tagext.lang.Inc ludeTag.ha ndlePageIn voke(Inclu deTag.java :737)
at coldfusion.tagext.lang.Inc ludeTag.do StartTag(I ncludeTag. java:573)
at coldfusion.filter.Cfinclud eFilter.in voke(Cfinc ludeFilter .java:65)
at coldfusion.filter.IpFilter .invoke(Ip Filter.jav a:45)
at coldfusion.filter.Applicat ionFilter. invoke(App licationFi lter.java: 505)
at coldfusion.filter.RequestM onitorFilt er.invoke( RequestMon itorFilter .java:43)
at coldfusion.filter.Monitori ngFilter.i nvoke(Moni toringFilt er.java:40 )
at coldfusion.filter.PathFilt er.invoke( PathFilter .java:153)
at coldfusion.filter.LicenseF ilter.invo ke(License Filter.jav a:30)
at coldfusion.filter.Exceptio nFilter.in voke(Excep tionFilter .java:94)
at coldfusion.filter.ClientSc opePersist enceFilter .invoke(Cl ientScopeP ersistence Filter.jav a:28)
at coldfusion.filter.BrowserF ilter.invo ke(Browser Filter.jav a:38)
at coldfusion.filter.NoCacheF ilter.invo ke(NoCache Filter.jav a:60)
at coldfusion.filter.GlobalsF ilter.invo ke(Globals Filter.jav a:38)
at coldfusion.filter.Datasour ceFilter.i nvoke(Data sourceFilt er.java:22 )
at coldfusion.filter.CachingF ilter.invo ke(Caching Filter.jav a:62)
at coldfusion.CfmServlet.serv ice(CfmSer vlet.java: 219)
at coldfusion.bootstrap.Boots trapServle t.service( BootstrapS ervlet.jav a:89)
at org.apache.catalina.core.A pplication FilterChai n.internal DoFilter(A pplication FilterChai n.java:292 )
at org.apache.catalina.core.A pplication FilterChai n.doFilter (Applicati onFilterCh ain.java:2 07)
at coldfusion.monitor.event.M onitoringS ervletFilt er.doFilte r(Monitori ngServletF ilter.java :42)
at coldfusion.bootstrap.Boots trapFilter .doFilter( BootstrapF ilter.java :46)
at org.apache.catalina.core.A pplication FilterChai n.internal DoFilter(A pplication FilterChai n.java:240 )
at org.apache.catalina.core.A pplication FilterChai n.doFilter (Applicati onFilterCh ain.java:2 07)
at coldfusion.inspect.weinre. MobileDevi ceDomInspe ctionFilte r.doFilter (MobileDev iceDomInsp ectionFilt er.java:12 1)
at coldfusion.bootstrap.Boots trapFilter .doFilter( BootstrapF ilter.java :46)
at org.apache.catalina.core.A pplication FilterChai n.internal DoFilter(A pplication FilterChai n.java:240 )
at org.apache.catalina.core.A pplication FilterChai n.doFilter (Applicati onFilterCh ain.java:2 07)
at org.apache.tomcat.websocke t.server.W sFilter.do Filter(WsF ilter.java :52)
at org.apache.catalina.core.A pplication FilterChai n.internal DoFilter(A pplication FilterChai n.java:240 )
at org.apache.catalina.core.A pplication FilterChai n.doFilter (Applicati onFilterCh ain.java:2 07)
at org.apache.catalina.core.S tandardWra pperValve. invoke(Sta ndardWrapp erValve.ja va:213)
at org.apache.catalina.core.S tandardCon textValve. invoke(Sta ndardConte xtValve.ja va:106)
at org.apache.catalina.authen ticator.Au thenticato rBase.invo ke(Authent icatorBase .java:502)
at org.apache.catalina.core.S tandardHos tValve.inv oke(Standa rdHostValv e.java:141 )
at org.apache.catalina.valves .ErrorRepo rtValve.in voke(Error ReportValv e.java:79)
at org.apache.catalina.core.S tandardEng ineValve.i nvoke(Stan dardEngine Valve.java :88)
at org.apache.catalina.connec tor.Coyote Adapter.se rvice(Coyo teAdapter. java:536)
at org.apache.coyote.http11.A bstractHtt p11Process or.process (AbstractH ttp11Proce ssor.java: 1095)
at org.apache.coyote.Abstract Protocol$A bstractCon nectionHan dler.proce ss(Abstrac tProtocol. java:672)
at org.apache.tomcat.util.net .NioEndpoi nt$SocketP rocessor.d oRun(NioEn dpoint.jav a:1500)
at org.apache.tomcat.util.net .NioEndpoi nt$SocketP rocessor.r un(NioEndp oint.java: 1456)
at java.util.concurrent.Threa dPoolExecu tor.runWor ker(Thread PoolExecut or.java:11 42)
at java.util.concurrent.Threa dPoolExecu tor$Worker .run(Threa dPoolExecu tor.java:6 17)
at org.apache.tomcat.util.thr eads.TaskT hread$Wrap pingRunnab le.run(Tas kThread.ja va:61)
at java.lang.Thread.run(Threa d.java:745 )
Pages work correctly, so no problem with databases.
DATASOURCE office_17 has a table members2 with data which I need to insert into the members2 table of DATASOURCE office_18.
Both data sources are identical - except for table members2 /office_17 which has data and members2/office_18 which is empty.
I need to populate members2/office_18 with data from members2/office_17.
The coldfusion code is below along with the error message. Its a syntax problem and I just can't see where it is. If someone could
talke a look - I'm sure the problem is obvious. Many thanks.
CODE
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>copy code</title>
</head>
<CFQUERY NAME="CopyTask" DATASOURCE="office_17">
SELECT *
FROM members2
</CFQUERY>
<CFLOOP Query="CopyTask">
<CFQUERY NAME="PasteTask" DATASOURCE="office_18">
INSERT INTO members2(
memberID,
civilites,
Nom,
Prenom,
Adresse,
ZIP,
Ville,
tel,
mobile,
email,
nomresponsible,
prenomresponsible,
Naissance
VALUES (
#memberID#,
'#civilites#',
'#Nom#',
'#Prenom#',
'#Adresse#',
'#ZIP#',
'#Ville#',
'#tel#',
'#mobile#',
'#email#',
'#nomresponsible#',
'#prenomresponsible#',
#Naissance#,
</CFQUERY>
</CFLOOP>
<body>
</body>
</html>
<!---<cfset memberID = #CopyTask.memberID#>
<cfoutput> #memberID# </cfoutput>
<cfset civilities = '#CopyTask.civilities#'>
<cfoutput> '#civilities#' </cfoutput>--->
ERROR MESSAGE
Error Executing Database Query.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES (
121513,
'M',
'RAMEL',
'Jean Lo' at line 15
The error occurred in C:/ColdFusion2016/cfusion/
43 : '#nomresponsible#',
44 : '#prenomresponsible#',
45 : #Naissance#,
46 : </CFQUERY>
47 :
SQLSTATE 42000
DATASOURCE office_18
VENDORERRORCODE 1064
SQL
INSERT INTO members2(
memberID,
civilites,
Nom,
Prenom,
Adresse,
ZIP,
Ville,
tel,
mobile,
email,
nomresponsible,
prenomresponsible,
Naissance
VALUES (
121513,
'M',
'RAMEL',
'Jean Loup',
'Le Prince d''Azur_ 662, route de Serra Capeou',
'06110',
'LE CANNET',
'0489826477',
'0687984634',
'jeanloup.ramel@sfr.fr',
'not available',
'not available',
1000,
Resources:
Check the ColdFusion documentation to verify that you are using the correct syntax.
Search the Knowledge Base to find a solution to your problem.
Browser Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36
Remote Address 0:0:0:0:0:0:0:1
Referrer
Date/Time 16-Feb-17 12:02 PM
Stack Trace
at cfnewyear_members22ecfm195
at cfnewyear_members22ecfm195
com.mysql.jdbc.exceptions.
121513,
'M',
'RAMEL',
'Jean Lo' at line 15
at sun.reflect.NativeConstruc
at sun.reflect.NativeConstruc
at sun.reflect.DelegatingCons
at java.lang.reflect.Construc
at com.mysql.jdbc.Util.handle
at com.mysql.jdbc.Util.getIns
at com.mysql.jdbc.SQLError.cr
at com.mysql.jdbc.MysqlIO.che
at com.mysql.jdbc.MysqlIO.che
at com.mysql.jdbc.MysqlIO.sen
at com.mysql.jdbc.MysqlIO.sql
at com.mysql.jdbc.ConnectionI
at com.mysql.jdbc.ConnectionI
at com.mysql.jdbc.StatementIm
at com.mysql.jdbc.StatementIm
at coldfusion.server.j2ee.sql
at coldfusion.sql.Executive.e
at coldfusion.sql.Executive.e
at coldfusion.sql.Executive.e
at coldfusion.sql.SqlImpl.exe
at coldfusion.tagext.sql.Quer
at coldfusion.tagext.sql.Quer
at coldfusion.tagext.sql.Quer
at cfnewyear_members22ecfm195
at coldfusion.runtime.CfJspPa
at coldfusion.tagext.lang.Inc
at coldfusion.tagext.lang.Inc
at coldfusion.filter.Cfinclud
at coldfusion.filter.IpFilter
at coldfusion.filter.Applicat
at coldfusion.filter.RequestM
at coldfusion.filter.Monitori
at coldfusion.filter.PathFilt
at coldfusion.filter.LicenseF
at coldfusion.filter.Exceptio
at coldfusion.filter.ClientSc
at coldfusion.filter.BrowserF
at coldfusion.filter.NoCacheF
at coldfusion.filter.GlobalsF
at coldfusion.filter.Datasour
at coldfusion.filter.CachingF
at coldfusion.CfmServlet.serv
at coldfusion.bootstrap.Boots
at org.apache.catalina.core.A
at org.apache.catalina.core.A
at coldfusion.monitor.event.M
at coldfusion.bootstrap.Boots
at org.apache.catalina.core.A
at org.apache.catalina.core.A
at coldfusion.inspect.weinre.
at coldfusion.bootstrap.Boots
at org.apache.catalina.core.A
at org.apache.catalina.core.A
at org.apache.tomcat.websocke
at org.apache.catalina.core.A
at org.apache.catalina.core.A
at org.apache.catalina.core.S
at org.apache.catalina.core.S
at org.apache.catalina.authen
at org.apache.catalina.core.S
at org.apache.catalina.valves
at org.apache.catalina.core.S
at org.apache.catalina.connec
at org.apache.coyote.http11.A
at org.apache.coyote.Abstract
at org.apache.tomcat.util.net
at org.apache.tomcat.util.net
at java.util.concurrent.Threa
at java.util.concurrent.Threa
at org.apache.tomcat.util.thr
at java.lang.Thread.run(Threa
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi
I think there is one more syntax error at in the last line in the query :
#Naissance#,
The comma should be not there
I think there is one more syntax error at in the last line in the query :
#Naissance#,
The comma should be not there
ASKER
ASKER
Hi _agx_ thanks for the response. I have tried the following version of your recommendation, but have syntax errors. Maybe you can point out what the silly mistake it ?
attached is the code
just to recap, both databases are on the same server, no problem there. The datasource with the data I want to share is office. The target datasourse is office_18. In the code I have a section for each of the columns (13 in all)
attached is the code
just to recap, both databases are on the same server, no problem there. The datasource with the data I want to share is office. The target datasourse is office_18. In the code I have a section for each of the columns (13 in all)
<cfquery datasource="office_18">
INSERT INTO office_18.members2 (memberID)
SELECT memberID
FROM office.members2
INSERT INTO office_18.members2 (civilites)
SELECT civilites
FROM office.civilites
INSERT INTO office_18.members2 (Nom)
SELECT Nom
FROM office.Nom
INSERT INTO office_18.members2 (Prenom)
SELECT Prenom
FROM office.Prenom
INSERT INTO office_18.members2 (Adresse)
SELECT Adresse
FROM office.Adresse
INSERT INTO office_18.members2 (ZIP)
SELECT ZIP
FROM office.ZIP
INSERT INTO office_18.members2 (Ville)
SELECT Ville
FROM office.members2
INSERT INTO office_18.members2 (tel)
SELECT tel
FROM office.tel
INSERT INTO office_18.members2 (mobile)
SELECT mobile
FROM office.mobile
INSERT INTO office_18.members2 (email)
SELECT email
FROM office.email
INSERT INTO office_18.members2 (nomresponsible)
SELECT nomresponsible
FROM office.nomresponsible
INSERT INTO office_18.members2 (prenomresponsible)
SELECT prenomresponsible
FROM office.prenomresponsible
INSERT INTO office_18.members2 (Naissance)
SELECT Naissance
FROM office.Naissance
</cfquery>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
wrapped the code in <cfquery datasource="office_18"> </cfquery> and it worked.
Thanks very much _agx_
james
Thanks very much _agx_
james
ASKER
woops !!!!!! made a mistake !!!!!!!!!!!!!!!!!
so sorry about that _agx _ will contact the coordinator to have it corrected. Should be 500 points of course !!!!!
james
so sorry about that _agx _ will contact the coordinator to have it corrected. Should be 500 points of course !!!!!
james
No worries. BTW, you may want to do a split because several of the answers made valid points about fixing problems that may have caused the error in original code (extra comma, etc..)
ASKER
need to share points
ASKER
thanks again _agx_ and also Raffaele
james
james
1. This is one of the many reasons for using cfqueryparam. It handles quoting issues like this automatically. It also improves performance by encouraging the db to reuse execution plans when a statement is executed multiple times, like within a loop.
2. IF the 2 databases are on the same server, and the DSN user has permissions to both of them, a simpler way to insert the data is to combine INSERT and SELECT, ie
INSERT INTO databaseName2.TableName (ColumnName)
SELECT ColumnName
FROM databaseName1.TableName