php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #50707 Sqlite3Result->columnType() always returns SQLITE3_NULL
Submitted: 2010-01-09 12:48 UTC Modified: 2010-01-11 10:37 UTC
From: cagret at gmail dot com Assigned:
Status: Not a bug Package: SQLite related
PHP Version: 5.3.1 OS: Win xp pro
Private report: No CVE-ID: None
 [2010-01-09 12:48 UTC] cagret at gmail dot com
Description:
------------
Sqlite3Result->columnType() always returns SQLITE3_NULL.

Table structure:
CREATE TABLE IF NOT EXISTS Test (Id int primary key, Name varchar(50));

Reproduce code:
---------------
$query = sprintf('SELECT * FROM %s', $table);
$result = $db->query($query);
$columns = array();
$numcols = $result->numColumns();
for ($i = 0; $i < $numcols; $i++) {
	$colname = $result->columnName($i);
	$coltype = $result->columnType($i);
}

$coltype == 5 (SQLITE3_NULL) for each column.

Expected result:
----------------
SQLITE3_INTEGER or SQLITE3_TEXT

Actual result:
--------------
SQLITE3_NULL

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2010-01-11 02:47 UTC] iliaa@php.net
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.php.net/manual/ and the instructions on how to report
a bug at http://bugs.php.net/how-to-report.php

 The value returned by sqlite3_column_type() is only meaningful if no 
type conversions have occurred as described below. After a type 
conversion, the value returned by sqlite3_column_type() is undefined.
 [2010-01-11 05:15 UTC] cagret at gmail dot com
What do you mean by "no type conversions have occured", "as described below" > am I missing something? Where in my code am I making any type conversions?

I don't quite understand, what is this function for, if it always returns SQLITE3_NULL ?

I was creating a web based browser for sqlite3 database files, in column headers I want to display column type:

Id (int) | Name (varchar)

That's all, what is the way of doing that?

I've found a solution, but it looks like a way around, using columnType() would be easier. I am parsing the "CREATE TABLE..." sql that i fetch by querying sqlite_master table, using a simple regexp. Here is the function:

function sqlite3_columns($table)
{
	global $db;
	// $result->columnType(0) - bug, always returns SQLITE3_NULL
	$query = sprintf("SELECT * FROM sqlite_master WHERE type='table' and name='%s'", $table);
	$result = $db->query($query);
	$row = $result->fetchArray(SQLITE3_ASSOC);
	$result->finalize();
	$sql = $row['sql'];
	preg_match_all('#[\(,]\s*(\w+)\s+(\w+)#', $sql, $pmatch);
	$columns = array();
	foreach ($pmatch[1] as $k => $colname) {
		$columns[] = array('name'=>$colname, 'type'=>$pmatch[2][$k]);
	}
	return $columns;
}
 [2010-01-11 10:37 UTC] scottmac@php.net
I believe you need to fetch a result for columnType to work since it can be different for each row.

Perhaps querying sqlite_master would be easiest?
 
PHP Copyright © 2001-2022 The PHP Group
All rights reserved.
Last updated: Sun Dec 04 16:05:54 2022 UTC