PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Tuesday, January 18, 2022

[FIXED] SQL native query with ResultSetMapping object return null

 January 18, 2022     doctrine, php, sql-server, symfony     No comments   

Issue

I'm trying to query SQL Server with pdo_sqlsrv through a Symfony 3/Doctrine 2 application.

I have an SQL query and I would like to bind the result with my entity by using a ResultSetMapping object:

This is the function of my SupplierRepository.php

class SupplierRepository extends EntityRepository
{
public function getSuppliers()
{
    $rsm = new ResultSetMapping;
    $rsm->addEntityResult('AppBundle\Entity\Supplier', 's');
    $rsm->addFieldResult('s', 'T_TIERS', 'code');
    $rsm->addFieldResult('s', 'T_LIBELLE', 'name');
    $rsm->addFieldResult('s', 'T_ADRESSE1', 'address1');
    $rsm->addFieldResult('s', 'T_ADRESSE2', 'address2');
    $rsm->addFieldResult('s', 'T_ADRESSE3', 'address3');
    $rsm->addFieldResult('s', 'T_CODEPOSTAL', 'zipcode');
    $rsm->addFieldResult('s', 'T_VILLE', 'city');
    $rsm->addFieldResult('s', 'T_SIRET', 'siret');

    $sql = 'SELECT  s.T_TIERS,
            s.T_LIBELLE, 
            s.T_ADRESSE1, 
            s.T_ADRESSE2, 
            s.T_ADRESSE3, 
            s.T_CODEPOSTAL, 
            s.T_VILLE, 
            s.T_SIRET 
            FROM RFFOURNISSEURS s
            WHERE (s.T_NATUREAUXI=?)
            AND
            (s.T_FERME=?)';

    $query = $this->_em->createNativeQuery($sql, $rsm);
    $query->setParameters(array(1 => 'FOU', 2 => '-'));
    
    return $query->getArrayResult();
}
}

This my entity Supplier.php

/**
 * Supplier
 *
 * @ORM\Table(name="supplier")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\SupplierRepository")
 */
class Supplier
{

/**
 * @var int
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var int
 *
 * @ORM\Column(name="code", type="string", unique=true)
 */
private $code;

/**
 * @var string
 *
 * @ORM\Column(name="name", type="string", length=255)
 */
private $name;

/**
 * @var string
 *
 * @ORM\Column(name="address1", type="string", length=255, nullable=true)
 */
private $address1;

/**
 * @var string
 *
 * @ORM\Column(name="address2", type="string", length=255, nullable=true)
 */
private $address2;

/**
 * @var string
 *
 * @ORM\Column(name="address3", type="string", length=255, nullable=true)
 */
private $address3;

/**
 * @var int
 *
 * @ORM\Column(name="zipcode", type="integer", nullable=true)
 */
private $zipcode;

/**
 * @var string
 *
 * @ORM\Column(name="city", type="string", length=255, nullable=true)
 */
private $city;

/**
 * @var int
 *
 * @ORM\Column(name="siret", type="integer", nullable=true)
 */
private $siret;

/**
 * @return int
 */
public function getCode(): int
{
    return $this->code;
}

/**
 * @param int $code
 */
public function setCode(int $code)
{
    $this->code = $code;
}

/**
 * @return string
 */
public function getName(): string
{
    return $this->name;
}

/**
 * @param string $name
 */
public function setName(string $name)
{
    $this->name = $name;
}

/**
 * @return string
 */
public function getAddress1(): string
{
    return $this->address1;
}

/**
 * @param string $address1
 */
public function setAddress1(string $address1)
{
    $this->address1 = $address1;
}

/**
 * @return string
 */
public function getAddress2(): string
{
    return $this->address2;
}

/**
 * @param string $address2
 */
public function setAddress2(string $address2)
{
    $this->address2 = $address2;
}

/**
 * @return string
 */
public function getAddress3(): string
{
    return $this->address3;
}

/**
 * @param string $address3
 */
public function setAddress3(string $address3)
{
    $this->address3 = $address3;
}

/**
 * @return int
 */
public function getZipcode(): int
{
    return $this->zipcode;
}

/**
 * @param int $zipcode
 */
public function setZipcode(int $zipcode)
{
    $this->zipcode = $zipcode;
}

/**
 * @return string
 */
public function getCity(): string
{
    return $this->city;
}

/**
 * @param string $city
 */
public function setCity(string $city)
{
    $this->city = $city;
}

/**
 * @return int
 */
public function getSiret(): int
{
    return $this->siret;
}

/**
 * @param int $siret
 */
public function setSiret(int $siret)
{
    $this->siret = $siret;
}

Error/result

The query is well executed, I have an array of 1025 elements which is correct, but they are NULL, and not Supplier objects:

array(1025) {
    [0]=> NULL
    [1]=> NULL
    [2]=> NULL
    [3]=> NULL
    [4]=> NULL
    ...
}

Any ideas? Is it a type error? During debugging I was able to see that the data is getting pulled from the database:


Solution

The fields of your Supplier entity are all private. Changing them to public should allow the RSM to set them. You should also pull something from the database to fill the id field.

class Supplier
{
    public $id;
    public $code;
    public $name;
    // etc.
}


Answered By - miken32
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing