Skip to main content
A
Anonymous
August 3, 2010
Answered

Help! Using CFQUERYPARAM in INSERT statements causes NullPointerException?!

  • August 3, 2010
  • 1 reply
  • 8708 views

Hi all,

I've never used cfqueryparam with INSERT statement in cfquery before, but now whenever I try it, I get an NullPointerException.

My code:

   <cfquery name="insertDocEntry"
            result="docEntry"
            datasource="#Application.PrimaryDataSource#">
    INSERT INTO tbl_conference_document
          (SessionID, DocTypeID, Symbol, Language, Title)
    VALUES (
      <cfqueryparam value="#Form.SessionID#" cfsqltype="cf_sql_numeric">,
      <cfqueryparam value="#Form.DocTypeID#" cfsqltype="cf_sql_numeric">,
      <cfqueryparam value="#Form.Symbol#" cfsqltype="cf_sql_varchar">,
      <cfqueryparam value="#Form.Language#" cfsqltype="cf_sql_varchar">,
      <cfqueryparam value="#Form.Title#" cfsqltype="cf_sql_varchar">
    )
   </cfquery>

I cfdump the form scope and all the values are set, but I still get this error:

The web site you are accessing has experienced an unexpected error.
Please contact the website administrator.

The following information is meant for the website developer for debugging purposes.
Error Occurred While Processing Request
The system has attempted to use an undefined value, which usually indicates a programming error, either in your code or some system code.

Null Pointers are another name for undefined values.        

The error occurred in D:\ColdFusion9\wwwroot\Infobase\Document\execConferenceDocUpload.cfm: line 28
26 :       <cfqueryparam value="#Form.Symbol#" cfsqltype="cf_sql_varchar">,
27 :       <cfqueryparam value="#Form.Language#" cfsqltype="cf_sql_varchar">,
28 :       <cfqueryparam value="#Form.Title#" cfsqltype="cf_sql_varchar">
29 :     )
30 :    </cfquery>
Resources:

Browser  Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.8) Gecko/20100722 Firefox/3.6.8
Remote Address  127.0.0.1
Referrer  http://localhost:8501/Infobase/Document/uploadConferenceDoc.cfm
Date/Time  03-Aug-10 11:48 AM
Stack Trace
at  cfexecConferenceDocUpload2ecfm1876641633.runPage(D:\ColdFusion9\wwwroot\Infobase\Document\execConferenceDocUpload.cfm:28)       at  cfApplication2ecfc1086350920$funcONREQUEST.runFunction(D:\ColdFusion9\wwwroot\Infobase\Application.cfc:230)                   

java.lang.NullPointerException
     at coldfusion.sql.QueryTable.populate(QueryTable.java:325)
     at coldfusion.sql.QueryTable.populate(QueryTable.java:261)
     at coldfusion.sql.Executive.getRowSet(Executive.java:466)
     at coldfusion.sql.Executive.executeQuery(Executive.java:1248)
     at coldfusion.sql.Executive.executeQuery(Executive.java:1008)
     at coldfusion.sql.Executive.executeQuery(Executive.java:939)
     at coldfusion.sql.SqlImpl.execute(SqlImpl.java:341)
     at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:843)
     at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:533)
     at cfexecConferenceDocUpload2ecfm1876641633.runPage(D:\ColdFusion9\wwwroot\Infobase\Document\execConferenceDocUpload.cfm:28)
     at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:231)
     at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:416)
     at coldfusion.runtime.CfJspPage._emptyTcfTag(CfJspPage.java:2722)
     at cfApplication2ecfc1086350920$funcONREQUEST.runFunction(D:\ColdFusion9\wwwroot\Infobase\Application.cfc:230)
     at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472)
     at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405)
     at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368)
     at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55)
     at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321)
     at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:220)
     at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:490)
     at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:336)
     at coldfusion.runtime.AppEventInvoker.invoke(AppEventInvoker.java:88)
     at coldfusion.runtime.AppEventInvoker.onRequest(AppEventInvoker.java:280)
     at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:338)
     at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48)
     at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40)
     at coldfusion.filter.PathFilter.invoke(PathFilter.java:87)
     at coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:27)
     at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:70)
     at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28)
     at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)
     at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46)
     at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38)
     at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)
     at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:53)
     at coldfusion.filter.RequestThrottleFilter.invoke(RequestThrottleFilter.java:126)
     at coldfusion.CfmServlet.service(CfmServlet.java:200)
     at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89)
     at jrun.servlet.FilterChain.doFilter(FilterChain.java:86)
     at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42)
     at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46)
     at jrun.servlet.FilterChain.doFilter(FilterChain.java:94)
     at jrun.servlet.FilterChain.service(FilterChain.java:101)
     at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:106)
     at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
     at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:286)
     at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:543)
     at jrun.servlet.http.WebService.invokeRunnable(WebService.java:172)
     at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:320)
     at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:428)
     at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:266)
     at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)

This error does NOT occur when I'm not using cfqueryparam, but my problem is, I need to insert some uploaded file content into the database as BLOB (code not shown above because this is a problem even without BLOB), so far the only way I know how to do this is using CFQUERYPARAM.

Am I the only one seeing this here? So far I only tried using my local CF9 server (Developer Edition), the CF9 server on my collegue's machine has the same problem.

We have a very tight deadline here and I really didn't expect this kind of trouble, so any help and hint is highly appreciated!

Thanks in advance!

ZHU, Jia

This topic has been closed for replies.
Correct answer -__cfSearching__-

Sorry, maybe my reply was a bit misleading, with "hard-coded" I actually meant #Form.param# and not really "hard-coded values", so there's no data type mismatch.

I could even live with that if it's not for the severe limitation it poses on the usage of BLOB data.

I tried to work-around with a stored procedure, but Sybase clearly tells me:

>[Error] Script lines: 1-56 -------------------------
TEXT, IMAGE and UNITEXT datatypes are invalid for parameters or local variables.
Msg: 2739, Level: 16, State: 1
Procedure: dbo.insertConferenceDoc, Line: 1

So no luck here either.


Yes, I did not think there was a data type mismatch. I was simply trying to obtain more clues about the circumstances under which the error occurs. In other words does it happen every single time cfqueryparam is used... or just when certain data types are used. Because this definitely should not be happening.  Though it may well be a driver level issue, what I usually do is set up a few quick test cases, ideally very simple.  Then use them to determine if there are any exceptions to the main issue.  So for example, things like does the exception occur even with a simple one column insert with a basic varchar column

INSERT INTO TestTable ( VarcharCol )  VALUES ( <cfqueryparam value="Test" cfsqltype="cf_sql_varchar">)

Also, did you experience the same results with the jtds driver?

1 reply

A
Adam Cameron.
Inspiring
August 3, 2010

The query seems OK to me.  Humour me, though, take out the NAME and RESULT attributes of the <cfquery> tag?

Also: what DB are you on?

--
Adam

A
Anonymous
August 3, 2010

Thanks for the reply, I took out the NAME and RESULT as you suggested, but still the same error. And I would need at least the RESULT attribute because I need to access the GeneratedKey from there and insert into another table (blob).

We are using Sybase ASE 15, and your reply did point me to the right direction, somehow;-)

I have just found out that the error occurrs because we are using the Sybase-provided JDBC driver instead of the Adobe-provided one, the reason is because we only have the Standard Edition of ColdFusion 9 for our server and not the Enterprise Edition, and the JDBC driver for Sybase doesn't come from Adobe in Standard Edition.

If I switch to use the Adobe driver on my Developer Edition server, it works without a glitch.

Hm, now I'll see if I can figure out a way around this...

A
Anonymous
August 3, 2010

Hm, it's getting more and more tricky...

From stack trace I figured out the Macromedia driver ColdFusion seems to be using when I just select "Sybase" as driver type, so I configured it using "other" as type:

JDBC URL: jdbc:sybase:Tds:<servername>:1433/<dbname>

Driver Class: macromedia.jdbc.sybase.SybaseDriver

For comparison, I used the following parameter to configure it for the Sybase provided JDBC driver:

JDBC URL: jdbc:sybase:Tds:<servername>:1433/<dbname>

Driver Class: com.sybase.jdbc4.jdbc.SybDriver

To my surprise, the Macromedia driver still does NOT work with cfqueryparam, I got the same error!

As I mentioned before, I cannot select "Sybase" as driver type on our production server because it's Standard Edition, so the only option for me to use Sybase is to configure the driver this way. Now it seems no matter what driver I use, as long as the driver is configured this way, INSERT won't work with CFQUERYPARAM?!

Needless to say, I'm really quite frustrated by this...

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices