2

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)
quickblueblur
  • 168
  • 1
  • 12

1 Answers1

-2

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

Simbiat
  • 339
  • 2
  • 12