How can I retrieve the exact value of a BIGINT from my database into MATLAB using Database Toolbox 3.5 (R2008b)?

I have a database with a column (col1) containing BIGINTs. On one of the rows I have the number 12345678901234567 and on another 98765432104567895. If I try to get this number as int64 into MATLAB however I loose precision:

>> setdbprefs('DataReturnFormat','cellarray');
>> conn = database('myDatabase','myUser','myPassword');
>> res = exec(conn,'SELECT col1 FROM myTable');
>> res = fetch(res);
>> data = res.data;
>> int64(res.data{1})
ans =

      12345678901234568
  >> int64(res.data{2})
  ans =

      98765432104567888

Answer 1

[MathWorks Support Team]

To get the BIGINT data as INT64 into MATLAB you can retrieve the number as text data and then use the attached MEX-file below to convert to INT64.

To retrieve the data as text you could consider changing the actual data format in your database or you could simply include a CAST in your query:

>> setdbprefs('DataReturnFormat','cellarray');
>> conn = database('myDatabase','myUser','myPassword');
>> res = exec(conn,'SELECT CAST(col1 AS VARCHAR) FROM myTable');
>> res = fetch(res);
>> data = res.data;
>> longlong = atoi64(data(:,1))
longlong =

      12345678901234567
      98765432104567895

[Martijn]

As the related solution was removed the MEX-file has now been attached to this article itself. Compile it using:

 mex atoi64.c

Before using it.