How do I bind a bigint value in PHP using PDO?

Question

I have a column in a PostgreSQL database which is a bigint.

So far I am not able to find a way formulate a PDO insert in PHP.

PHP Predefined Constants only lists PDO::PARAM_INT. When I use this in my statement, I get the following error. Note, I get the same error even if I don't include the data type in the bindValue statement. From what I understand, by omitting the type PHP is using PDO::PARAM_INT based on the variable type.

Uncaught exception 'PDOException' with message 'SQLSTATE[22003]: Numeric value out of range: 7 ERROR: value "2978878787878787878"; is out of range for type integer' in index.php:163

How do I store a BIGINT in MySQL using PDO? is a question here which talks about the same issue. However, they go right into using string as the database column type. While this is a nice workaround, it doesn't take into account if you are unable to change the database types.

Is there a way of using bigint with PDO's in PHP that doesn't require modifications to the database?

Example Code

$query  =   "INSERT INTO clients ";
$query  .=  "(timestamp, name, value) ";
$query  .=  "VALUES (now(), :name, :value) ";
$query  .=  "RETURNING id";

$name = "Bob Probert";
$value = 2978878787878787878; // note this is less than bigint max of 9223372036854775807

$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->bindValue(':value', $value, PDO::PARAM_INT);

Table

                                                           Table "db.clients"
    Column     |            Type             |                      Modifiers                       | Storage  | Stats target | Description 
---------------+-----------------------------+------------------------------------------------------+----------+--------------+-------------
 id            | integer                     | not null default nextval('tickets_id_seq'::regclass) | plain    |              | 
 timestamp     | timestamp without time zone |                                                      | plain    |              | 
 name          | character varying(40)       |                                                      | extended |              | 
 value         | bigint                      | not null                                             | plain    |              | 
Indexes:
    "tickets_pkey" PRIMARY KEY, btree (id)

Are you sure that it's a BIGINT in your database? Can you show your table schema? — aynber, Aug 16 '17 at 14:44
@aynber +1, the exception mentions integer type, not bigint, for the field — ishegg, Aug 16 '17 at 15:04
`is out of range for type integer' in ` this tells you that your dataType is an `INT` rather than `BigINT` as you claim — Masivuye Cokile, Aug 16 '17 at 15:05
May help to know a few things. Are you on a 64bit hardware? Is the OS 64bit? What version of php are your at? What does echo PHP_INT_MAX; give you? — Jason K, Aug 16 '17 at 15:37
I'm running a 64-bit OS on 64-bit hardware. The PHP version is 5.6.3 and PHP_INT_MAX is 9223372036854775807. — quickblueblur, Aug 17 '17 at 12:05

score -2 · Answer 1 · answered Jun 06 '21 at 13:41

-2

I do not know if this will work in PostgreSQL, but for MySQL binding these values as strings works.

answered Jun 06 '21 at 13:41

Simbiat

339
2
12

How do I bind a bigint value in PHP using PDO?

1 Answers1