HY104 Sql server error while binding empty string to pdo request

I have to move the application from mssql functions to PDO. Everything worked smoothly until I found that I had a small error that I could not solve.

Here is my prepared request:

$req_action="INSERT INTO [".DB_SCHEMA."].[dbo].[".ACTION_TABLE."] 
([ID_CONTACT]
,[ID_ADN]
,[TYPE_ACTION]
,[MOTIF_ENTRANT]
,[COMMENTAIRES_APPEL]
,[CODE_CAMPAGNE]
,[EMAIL]
,[SUJET]
,[STATUT_EMAILING]
,[DATE_ENVOI]
,[DELAI_OUVERTURE]
,[MAIL_CLIENT]
,[DATE_OUVERTURE]
,[LIEN_CLIQUE]
,[DELAI_CLIC]
,[DATE_CLIC]
,[DATE_ACTION]
,[USER_ID]
,[LOGIN]
,[DATE_CHARGEMENT]
)
VALUES
(''
,''
,'e-mailing'
,''
,''
,''
,:email
,:sujet
,:statut
,convert(datetime,:date_envoi,103)
,:delai
,:mail
,convert(datetime,:date_ouverture,103)
,:lien_clic
,:delai_clic
,convert(datetime,:date_clic,103)
,convert(datetime,:date_action,103)
,'1'
,'AUTO'
,getdate()
)";

and my bindings:

$prep_req_action->bindValue(":email",$email,PDO::PARAM_STR);
$prep_req_action->bindValue(":sujet",$sujet,PDO::PARAM_STR);
$prep_req_action->bindValue(":statut",$statut_emailing,PDO::PARAM_STR);
$prep_req_action->bindValue(":date_envoi",$sql_date_envoi,PDO::PARAM_STR);
$prep_req_action->bindValue(":delai",$delai_ouverture,PDO::PARAM_STR);
$prep_req_action->bindValue(":mail",$mail_client,PDO::PARAM_STR);
$prep_req_action->bindValue(":date_ouverture",$sql_date_ouverture,PDO::PARAM_STR);
$prep_req_action->bindValue(":lien_clic",$lien_clique,PDO::PARAM_STR);
$prep_req_action->bindValue(":delai_clic",$delai_clic,PDO::PARAM_STR);
$prep_req_action->bindValue(":date_clic",$sql_date_clic,PDO::PARAM_STR);
$prep_req_action->bindValue(":date_action",$sql_date_action,PDO::PARAM_STR);

The problem is that when one of my PHP variables is an empty string (it actually happens a lot from the automatic csv file), the SQL server returns error HY104 (invalid precision).

All my fields are NULL enabled, so I switched the attribute PDO::ATTR_ORACLE_NULLSto PDO::NULL_EMPTY_STRINGto convert empty strings to NULL, but the behavior is exactly the same.

The only workaround I found is to check if my variable is empty, to set it to php null before binding it:

$lien_clique = (empty($lien_clique)) ? null : $lien_clique;

, , PDO NULL_EMPTY_STRING , , tweek.

- , ?

FYI: PHP 5.3.1 Linux, pdo_sqlsrv 3.0 SQL Server 2000.

+5
1

, , , , , , .

...

$stmt->bindValue(":field",$value,PDO::PARAM_STR,$length);

, , $length - , . , , , ODBC.

0

All Articles