oracle querying with methods find, does not work

[gustperz]

I'm creating a REST API with slim3 framework and I using Doctrine2 as orm
I have the entity:

/**
 * @ORM\Entity
 * @ORM\Table(name="TERCEROS")
 */
class Empleado
{
    /**
     * @ORM\Id
     * @ORM\Column(type="string", name="CODIGO", length=15)
     * @ORM\GeneratedValue(strategy="NONE")
     * @var string
     */
    protected $cedula;

    /**
     * @ORM\Column(type="string", name="APELLIDO1")
     * @var string
     */
    protected $apellido1;
}

and so get the manager:

$path = array(__DIR__.'/../../Entities');
$devMode = getenv('DEV_MODE');
$config = Setup::createAnnotationMetadataConfiguration($path, $devMode, null, null, false);

$config->setProxyDir($path[0] . '/Proxy');
$config->setProxyNamespace('Proxy');

$empleadosManager = \Doctrine\ORM\EntityManager::create(arrayparams, $config);

when using any method find which should return one single Empleado this return null

$empleadosRepository = $empleadosManager->getRepository(Empleado::class);
$empleados = $empleadosRespository->findAll(); //this returns the data correctly
$empleado = $empleadosRespository->find('12345678'); //=null
$empleado = $empleadosRespository->findOneBy(['apellido1' =>'fulano']); //=null

None are found, even though I have an Empleado with apellido1 = 'fulano' and cedula = '1234567' inserted in the database.

All table and column names are upercase, the names on the entity changed to make it a more consistent with the objective of the api, since in the TERCEROS table are stored employees, I do not know because it was called that way when it was created.

PS: Sorry for my english

[Ocramius]

@gustperz do you by chance use PHP7 whith this example?

I suggest logging the executed queries and checking that parameters are passed in correctly, as this might be affected by a bug that pretty much breaks oci8 support on PHP7, see doctrine/dbal#2262

[gustperz]

@Ocramius I'm using php 5.5.19

[billschaller]

@gustperz Can you show us the generated SQL?

[gustperz]

@zeroedin-bill @Ocramius
$empleadosRespository->find('1120567829') generates

SELECT t0.CODIGO AS CODIGO_1, t0.APELLIDO1 AS APELLIDO1_2, t0.APELLIDO2 AS APELLIDO2_3, t0.NOMBRES AS NOMBRES_4, t0.IND_EMPL AS IND_EMPL_5, t0.ESTADO AS ESTADO_6 FROM TERCEROS t0 WHERE t0.CODIGO = ?
array(1) {
  [0]=>
  string(10) "1120567829"
}
array(1) {
  [0]=>
  string(6) "string"
}

this output is EchoSQLLogger

[billschaller]

Can you also dump the table DDL?

[gustperz]

@zeroedin-bill this is the DDL of table TERCEROS

--------------------------------------------------------
--  DDL for Table TERCEROS
--------------------------------------------------------

  CREATE TABLE "ASD"."TERCEROS" 
   (    "IND_EMPL" CHAR(1 BYTE), 
    "ESTADO" CHAR(1 BYTE), 
    "APELLIDO1" CHAR(15 BYTE), 
    "APELLIDO2" CHAR(15 BYTE), 
    "NOMBRES" CHAR(20 BYTE), 
    "CODIGO" CHAR(15 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Index TERCEROS_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "ASD"."TERCEROS_PK" ON "ASD"."TERCEROS" ("CODIGO") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  Constraints for Table TERCEROS
--------------------------------------------------------

  ALTER TABLE "ASD"."TERCEROS" ADD CONSTRAINT "TERCEROS_PK" PRIMARY KEY ("CODIGO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE;

[billschaller]

@gustperz The only thing I can think here is that OCI8Statement is binding the parameter wrong somehow.

Can you attach a debugger and trace the query into OCI8Statement and see how it's passing the parameters to bindParam and oci_bind_by_name?

I do not have an oracle environment so I'm unable to do this myself at the moment.

[josecarlosbcn]

Hi!!!

I've got the same problem that @gustperz Can anybody help us? Thanks in advance!!!

[cyframepaul]

You are using CHAR fields so you will need to pad your 10 character string "1120567829" to 15 characters "1120567829     "

      

Same thing for "fulano" which should be 15 characters long "fulano         " since your table uses CHAR's 😢:

"APELLIDO1" CHAR(15 BYTE) 
"CODIGO" CHAR(15 BYTE)

Try This:

$empleado = $empleadosRespository->find(str_pad('1120567829', 15));
$empleado = $empleadosRespository->findOneBy(['apellido1' => str_pad('fulano', 15)]);

Your mapping information is also wrong, you need to add the options={"fixed":true} for both columns in you Entity

I'm usually using yml mappings, so please double check this before using but I believe the annotation should look like this:

@ORM\Column(type="string", name="CODIGO", length=15, options={"fixed":true} )

@ORM\Column(type="string", name="APELLIDO1", options={"fixed":true} )

@gustperz Some advise:
Avoid using CHAR fields like the plague. Pretend like they don't even exist. If any of your peers,clients or bosses is insistent on using them, tell them that Oracle Licensing now requires the 500,000$ Enterprise Edition Only "Advanced CHAR Lover Add-on" if you want to use CHAR's in your database and that Oracle announced CHAR's will be removed in the upcoming "Oracle Database 13 Quantum IoT Edition".

Even Die Hard Oracle Evangelists recommend against using them. If you plan on using doctrine extensively (or any ORM for that matter), move to VARCHAR2 even if it means spending time and $$ to update other applications or PL\SQL that you are trying to maintain compatibility with.

[Ocramius]

@cyframepaul nice one! Closing this one as invalid as per your comment.