View Issue Details

Jump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0007177OXID eShop (all versions)4.01. Database handlingpublic2020-09-24 13:202020-09-25 09:35
Reporterwael golli Assigned To 
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionopen 
Product Version6.2.2 
Summary0007177: Doctrine QueryBuilder INSERT
DescriptionUsing Doctrine QuerBuilder to insert a new row using an array of values,fails and throws Syntax error,
Values should be treated and checked
Steps To Reproduce1/create a new Array() contains different values of int/date/strings, it can be empty values
2/ use query builder as described below
$queryBuilder
            ->insert( ' TableName ' )
            ->values(
                $aKeyValues
            );
Additional Information3/ generated SQL :
INSERT INTO Table name (Name1,Name2,Name3...) VALUES(94, ,Val3 ... )
4/ this SQL throws error
empty string should be ' '
TagsDatabase, doctrine, MySQL, QueryBuilder
ThemeNot defined
BrowserNot defined
PHP Version7.1
Database VersionMySQL 5.5

Activities

QA

2020-09-24 15:19

administrator   ~0013305

Dear Wael Golli,

i cannot reproduce the same failure msg.
I created querys according to https://www.doctrine-project.org/projects/doctrine-dbal/en/2.10/reference/query-builder.html#values-clause

Could you please provide your query in a whole?

Best Regards

QA - SG -

wael golli

2020-09-24 22:33

reporter   ~0013306

Dear QA,
Ill write for you some testing data and steps to reproduce it:
1/
$var1=' ';$var2='2020-01-02 18:30:00';$var3='(45)test(23)';
$aList={
"columnA" => $var1,
"columnB"=> $var2,
"columnC3=>$var3
}
2)
$queryBuilder
            ->insert( ' TableTest ' )
            ->values(
                $aList
            );

3) the query generated would be = INSERT INTO TableTest( ColumnA,ColumnB,ColumnC) VALUES ( ,2020-01-02 18:30:00, (45)test(23))
executing this query would give an exception,

florian.engelhardt

2020-09-25 08:40

reporter   ~0013307

Hey Wael Golli,

this is expected behaviour from Doctrine DBAL. If you'd like to insert the values via the call to values() directly, you need to prepare those values on your own, alternatively bind those values via calls to setParameter(). Your code could look like this:

$var1 = ' ';
$var2 = '2020-01-02 18:30:00';
$var3 = '(45)test(23)';

$list= [
    "columnA" => "?",
    "columnB" => "?",
    "columnC" => "?"
];

$queryBuilder->insert('TableTest')
    ->values($list)
    ->setParameter(0, $var1)
    ->setParameter(1, $var2)
    ->setParameter(2, $var3);


You can find out more on this topic in the official Doctrine documentation at https://www.doctrine-project.org/projects/doctrine-dbal/en/2.10/reference/query-builder.html#values-clause

Hope I could help

Florian

QA

2020-09-25 09:35

administrator   ~0013308

Dear Wael Golli,

as Florian explained:
We use the original syntax of doctrine, please check the references.

Best Regards

QA -SG-