oracle to_date : Error due to missing single quotation mark.

jungbaksacom commented 2 weeks ago

Description

A clear and concise description of what the problem is, including relevant error messages and stack trace.

Environment

ADOdb version: v5.22.7 (or Git Commit/Ref)_
Driver or Module: adodb-oci8.inc.php, adodb-oracle.inc.php
Database type and version:
PHP version: 8.3
Platform: not
[ ] I have tested that the problem is reproducible in the latest release / master branch / latest hotfix branch
⚠️ Keep only the applicable option(s), i.e. where you actually tested, and remove the others

Steps to reproduce

Detailed, step-by-step instructions to reproduce the behavior, including:

code snippet
SQL to create and populate database objects used by the code snippet

Expected behavior

A clear and concise description of what you expected to happen.

Additional context

Add any other information relevant to the problem here.

incorrect :

TO_DATE(2024-10-28 14:34:45,'YYYY-MM-DD HH24:MI:SS')
src: 'TO_DATE('.$ds.','".$this->dateformat."')";

correct:

TO_DATE('2024-10-28 14:34:45','YYYY-MM-DD HH24:MI:SS')
src "TO_DATE('".$ds."','".$this->dateformat."')";

etc "TO_DATE('" ...... "', ......

dregad commented 2 weeks ago

Thanks for the report. It looks like this code has been like this since 2009 or even before, I'm surprised that nobody reported the problem before.

dregad commented 2 weeks ago

@jungbaksacom did you get an actual error in your code from this ? If so, please provide a code snippet to reproduce the problem.

As far as I can tell, it works as expected considering that the date formats used in dbDate() and dbTimestamp() methods include the '

<?php
$db = NewADOConnection('oci8');

echo $db->DBDate('2024-10-28') . "\n";
echo $db->DBDate(new DateTimeImmutable()) . "\n";
echo $db->DBDate(time()) . "\n";

echo $db->DBTimeStamp('2024-10-28 12:34:56') . "\n";
echo $db->DBTimeStamp(new DateTimeImmutable()) . "\n";
echo $db->DBTimeStamp(time()) . "\n";

/* Output:
TO_DATE('2024-10-28','YYYY-MM-DD')
TO_DATE('2024-10-29','YYYY-MM-DD')
TO_DATE('2024-10-29','YYYY-MM-DD')
TO_DATE('2024-10-28 12:34:56','RRRR-MM-DD, HH24:MI:SS')
TO_DATE('2024-10-29 18:05:11','RRRR-MM-DD, HH24:MI:SS')
TO_DATE('2024-10-29 18:05:11','RRRR-MM-DD, HH24:MI:SS')
*/

jungbaksacom commented 2 weeks ago

I see an execution error.

ORA-00907: missing right parenthesis

This is Data. Array ( [bbs_subject] => test [writer_id] => master [writer_pw] => [writer_name] => Administrator [writer_email] => [bbs_ip] => ::1 [bbs_sns_type] => [bbs_sns_user] => [movie_url] => [movie_h] => [movie_w] => [extra_6] => [extra_7] => [extra_8] => [extra_9] => [extra_10] => [is_top] => N [is_used] => Y [bbs_parent] => 0 [is_comment] => 0 [comment_cnt] => 0 [bbs_hit] => 0 [bbs_good] => 0 [bbs_nogood] => 0 [file_cnt] => 0 [link_cnt] => 0 [movie_hit] => 0 [bbs_answer] => [reg_dt] => DateTime Object ( [date] => 2024-10-30 07:11:09.094893 [timezone_type] => 3 [timezone] => Asia/Seoul )

[mod_dt] => DateTime Object ( [date] => 2024-10-30 07:11:09.094896 [timezone_type] => 3 [timezone] => Asia/Seoul )

[bbs_no] => 1003 [bbs_num] => 1 [cate_cd] => 0222 )
Query conversion result adodb-lib.inc.php line: 1033

INSERT INTO WEB_BBS ( BBS_NO, BBS_NUM, BBS_PARENT, IS_COMMENT, COMMENT_CNT, CATE_CD, BBS_SUBJECT, BBS_HIT, BBS_GOOD, BBS_NOGOOD, WRITER_ID, WRITER_PW, WRITER_NAME, WRITER_EMAIL, FILE_CNT, LINK_CNT, BBS_IP, BBS_SNS_TYPE, BBS_SNS_USER, MOVIE_URL, MOVIE_HIT, MOVIE_H, MOVIE_W, IS_TOP, EXTRA_6, EXTRA_7, EXTRA_8, EXTRA_9, EXTRA_10, IS_USED,

REG_DT, MOD_DT,

BBS_ANSWER ) VALUES ( 1004, 1, 0, 0, 0, '0222', 'test', 0, 0, 0, 'master', '', 'Administrator', '', 0, 0, '::1', '', '', '', 0, '', '', 'N', '', '', '', '', '', 'Y',

TO_DATE(2024-10-30 07:14:36,'YYYY-MM-DD HH24:MI:SS'), TO_DATE(2024-10-30 07:14:36,'YYYY-MM-DD HH24:MI:SS'), --> An error occurs because there are no quotation marks in this part. - "ORA-00907: missing right parenthesis"

'' )

Currently, there was no problem with php5.6, but it appears that the problem occurred when upgrading to php8.3.

files adodb-db2.inc.php adodb-oci8.inc.php adodb-oracle.inc.php

dregad commented 2 weeks ago

Again, I need a PHP code snippet that will reproduce the problem.

jungbaksacom commented 2 weeks ago

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/TO_DATE.html

Please protect the string by citing it, as shown in the example in the official documentation. People of faith stick to their beliefs and refuse to change them, but we end up getting the results we want to change anyway. The usage task is 11g. However, quotation marks are not used in higher-level bourbons, so I think it is correct to use quotation marks for backward compatibility.

Important information is not provided in relation to the content of questions whose content is financial.

Thank you for answering my questions.

dregad commented 2 weeks ago

I'm sorry but this is not helping. I am aware of the TO_DATE function's syntax. Also, I don't understand your reference to faith and beliefs, that has nothing to do with anything.

I am not questioning the need to add quotation marks. I am only trying to figure out where the problem is, because as far as I can tell (and as shown in my earlier post), the date is correctly quoted.

So there must be some specific usage scenario which is causing the problem, which is why I'm asking you to clearly explain what part of ADOdb is triggering the ORA-00907 error, and how you are calling it For this, a code sample would be ideal (and it does not have to be actual code from your system, as long as it's reproducing the error).

dregad commented 1 week ago

@jungbaksacom without feedback from you and being unable to reproduce the problem, I'm going to close this issue. Feel free to reopen it and provide the requested information.

ADOdb / ADOdb