Question & Answer > View Question

SQL query a db table WHERE date value comes from Form

Confluence User - 27 Apr, 2021

I am attempting to do a SQL query for row/s in a table with a date-only value from a datepicker element
within the form.
The script doing this is a bean shell validator.

So i try to get the value of the datepicker element like this:

Form form = FormUtil.findRootForm(element);
Element fldreservationDate = FormUtil.findElement("reservationDate", form, formData);
String[] strreservationDate = FormUtil.getElementPropertyValues(fldreservationDate, formData);

LogUtil.info("","reservationDate " + Arrays.toString(strreservationDate)  );

Log shows
INFO 26 Apr 2021 06:57:33 - jwc_xxxx : reservationDate [27-04-2021]

However when i execute the SQL thus:

PreparedStatement stmt = con.prepareStatement("SELECT c_reservationDate FROM app_fd_hotdesk_reservation WHERE c_reservationDate=?");
stmt.setObject(1, strreservationDate);
ResultSet rs = stmt.executeQuery();

No rows were returned even though I'm sure there is such a row in the table!

Querying SELECT c_reservationDate FROM app_fd_hotdesk_reservation WHERE c_reservationDate='2021-04-27'
successfully returns the row...

LogUtil.info("", (rs.getObject("c_reservationDate") != null)? rs.getObject("c_reservationDate").toString():"");

show this in the log:
3681INFO 26 Apr 2021 06:57:34 - jwc_xxxx : 2021-04-27

I'm quite sure it's a date format issue...The datepicker's data format is already set to yyyy-MM-dd

How best to construct the SQL to achieve what i want?

forms;sql;datepicker

1

Confluence User •

30 Apr, 2021 •

confluenceUser •

1 •

confluenceUser •

Sorry if this seems more of a JAVA programming exercise.

Turns out

Arrays.toString(strreservationDate)
returns a string with square brackets around it! Had to remove those.

Also had to convert the value from the datepicker into a date 
and then produce a string with the correct format from that date 
before using it in the SQL WHERE clause.

Ugh! Maybe there's a more elegant way to do this...

1

CSV | Excel | XML | PDF

Your answer

Print

SQL query a db table WHERE date value comes from Form

1

RELATED QUESTIONS

Your answer

To answer a question you'll need an account.

1.builder-style-7763c2ef-67de-416f-abad-4f9279c926c5{color:#008C60 !important;}

RELATED QUESTIONS

Your answer.builder-style-135c427f-ea52-4668-a745-6a072fe63697{color:#008C60 !important; margin-left:10px !important;}

To answer a question you'll need an account.

1

Your answer