Click here to Skip to main content
15,867,594 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Good day everyone, I have been watching this video series as a guide line for my own netbeans e commerce project but at 42:00 the teacher adds a DB Query Database but when I added this code to my index.jsp file I got this error message in my browser “jakarta.servlet.jsp.JspException: Unable to get connection, DataSource invalid: java.sql.SQLException: Error in allocating a connection. Cause: Connection could not be allocated because: The connection property 'zeroDateTimeBehavior' only accepts values of the form: 'exception', 'round' or 'convertToNull'. The value 'CONVERT_TO_NULL' is not in this set." I have the jdbc/OkStore resource reference in web.xml. I changed the resource type to javax.sql.DataSource. I changed the datasource classname from com.mysql.jdbc.jdbc2.optional.MysqlDataSource to com.mysql.cj.jdbc.MysqlDataSource. Do you guys have any ideas to fix this? By the way I will be updating the post with failed solutions so that I don’t get repeats.
https://www.youtube.com/watch?v=JlLGrqHSSjk&list=PLWS58wV27xi5AFrSfru26h4KWNMv1h2XA&index=16&ab_channel=CS.Math.Educator

The program versions I’m using are Apache Netbeans IDE 15, MySQL WorkBench 8.0 CE, Eclipse GlassFish 6.2.5 (build 6.x-b66-g0159b68 2022-02-12T17:39:59+0000). My driver is mysql-connector-j-8.0.31.

Error message https://i.stack.imgur.com/LgYcw.png

DB Query Database code in index.jsp file that caused the error message
<sql:query var="categories" datasource="jdbc/OkStore">
SELECT * FROM category

The jdbc/OkStore resource reference code in web.xml file
<resource-ref>
<description>Connects to database for OkStore application
<res-ref-name>jdbc/OkStore
<res-type>javax.sql.ConnectionPoolDataSource
<res-auth>Container
<res-sharing-scope>Shareable



Here's the code for for index.jsp
[code=xml]<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%>
<%-- 
    Document   : index.jsp
    Created on : Dec 10, 2022, 2:51:39 PM
    Author     : Bredy
--%>
      <sql:query var="categories" dataSource="jdbc/OkStore">
        SELECT * FROM category
    </sql:query>        
        <div id="indexLeftColumn">            
            <div id="welcomeText">
        <p>[ welcome text ]</p>
     <!-- test to access context parameters -->
        categoryImagePath: ${initParam.categoryImagePath}
        productImagePath: ${initParam.productImagePath}   
    </div>
</div>
   
        <div id="indexRightColumn">           
          <div class="categoryBox">
        <a href="#">
            Under Armour
        </a>
    </div>
    <div class="categoryBox">
        <a href="#">
            Nike
        </a>
    </div>
    <div class="categoryBox">
        <a href="#">
            Adidas
        </a>
    </div>
    <div class="categoryBox">
        <a href="#">
            Reebok
        </a>
    </div>
</div>  [/code]
            
      
Here's the code for for web.xml
[code=xml]<?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
	 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	 xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
	 version="4.0">
<context-param>
    <description>The relative path to product images</description>
    <param-name>productImagePath</param-name>
    <param-value>img/products/</param-value>
</context-param>
<context-param>
    <description>The relative path to category images</description>
    <param-name>categoryImagePath</param-name>
    <param-value>img/categories/</param-value>
</context-param>    
    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>
<jsp-config>
<jsp-property-group>
    <description>header and footer settings</description>
        <url-pattern>/index.jsp</url-pattern>
        <url-pattern>/WEB-INF/view/</url-pattern>
         <url-pattern>/WEB-INF/view/cart.jsp</url-pattern>
          <url-pattern>/WEB-INF/view/category.jsp</url-pattern>
           <url-pattern>/WEB-INF/view/checkout.jsp</url-pattern>
            <url-pattern>/WEB-INF/view/confirmation.jsp</url-pattern>            
        <include-prelude>/WEB-INF/jspf/header.jspf</include-prelude>
        <include-coda>/WEB-INF/jspf/footer.jspf</include-coda>
    </jsp-property-group>
</jsp-config>
<resource-ref>
    <description>Connects to database for OkStore application</description>
    <res-ref-name>jdbc/OkStore</res-ref-name>
    <res-type>javax.sql.ConnectionPoolDataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>
</web-app>[/code]


What I have tried:

List of failed solutions
https://stackoverflow.com/questions/17378840/glassfish-no-suitable-driver-for-mysql-when-trying-to-connect-with-datasource
https://stackoverflow.com/questions/30871627/java-sql-sqlexception-no-suitable-driver-found-for-connection-url
https://stackoverflow.com/questions/23845408/class-name-is-wrong-or-classpath-is-not-set-for-com-mysql-jdbc-driver/52957026#52957026
https://stackoverflow.com/questions/20405273/javax-servlet-jsp-jspexception-unable-to-get-connection-datasource-invalid-j
https://stackoverflow.com/questions/22696902/jdbc-converting-timestamp-to-null-zerodatetimebehavior-issue
I changed the datasource name from com.mysql.cj.jdbc.MysqlDataSource to com.mysql.cj.jdbc.Driver in the Glassfish Domain Admin Console and I got a new error. "Ping Connection Pool failed for OkStorePool. Class name is wrong or classpath is not set for : com.mysql.cj.jdbc.Driver." So I swap backed to com.mysql.cj.jdbc.MysqlDataSource.
The datasource name change error: https://i.stack.imgur.com/vAdD7.png
Posted
Updated 27-Dec-22 12:05pm
v2
Comments
Richard MacCutchan 27-Dec-22 5:47am    
Instead of all those links to external websites, please use the Improve question link above, and add the actual code you are using.
Makito Bredy 27-Dec-22 17:15pm    
Alright I have added some code.
[no name] 27-Dec-22 12:19pm    
Did your find the "CONVERT_TO_NULL" value it's complaining about? Sounds like a "constant name" and not a "value".
Makito Bredy 27-Dec-22 18:04pm    
THANK YOU! I figured out what I need to do.

1 solution

Guys I fixed the problem. I went to the glassfish-resources.xml file and changed the value jdbc:mysql://localhost:3306/okstore?zeroDateTimeBehavior=CONVERT_TO_NULL to jdbc:mysql://localhost:3306/okstore?zeroDateTimeBehavior=convertToNull. I also changed the database connection to jdbc:mysql://localhost:3306/okstore?zeroDateTimeBehavior=convertToNull just in case.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900