odbc_prepare
I was trying to find the primary keys from an SQLServer database through the ODBC interface. Funnily enough, the odbc_primarykeys function doesn't work with SQLServer (at least not my implementation of it). Fortunately, the sp_keys query is passed through and the answer returned. This code works (providing you know which database you're dealing with, which is a whole 'nother story).
// If this is SQLServer, we need to do a special operation to get the
// primary keys.
//
// Looks like the implementers of the ODBC interface just blew this
// one off, since the database has a query to return the info and the
// info even comes back with the same column names.
if ($DBType == "SQLServer")
$KeySel = odbc_exec($DBConn, "sp_pkeys ".$TableName);
// Otherwise, ask the database through ODBC for the primary key
// names.
else $KeySel = odbc_primarykeys($DBConn, $DatabaseName,
$DatabaseUser, $TableName);
while ($KeySel && ($KeyRec = odbc_fetch_array($KeySel)))
$KeyCol[$KeyRec["KEY_SEQ"]] = $KeyRec["COLUMN_NAME"];
odbc_primarykeys
(PHP 4, PHP 5)
odbc_primarykeys — Restituisce un identificatore di risultato che può essere utilizzato per ricavare il nome della colonna che contiene la chiave primaria della tabella.
Descrizione
resource odbc_primarykeys
( resource $id_connessione
, string $qualifica
, string $proprietario
, string $tabella
)
Restituisce il nome della colonna che contiene la chiave primaria per la tabella. La funzione ritorna un identificatore di risultato ODBC oppure FALSE se si verifica un errore.
Le righe risultanti dall'elaborazione contengono i seguenti campi:
- TABLE_QUALIFIER
- TABLE_OWNER
- TABLE_NAME
- COLUMN_NAME
- KEY_SEQ
- PK_NAME
add a note
User Contributed Notesodbc_primarykeys
ewilde aht bsmdevelopment dawt com
11-Jul-2006 03:39
11-Jul-2006 03:39
dan dot scott at ca dot ibm dot com
20-Dec-2004 07:51
20-Dec-2004 07:51
Responding to devendra_joshi:
In DB2 Universal Database for Linux, UNIX, and Windows the catalog views are accessed through the SYSCAT schema, not the SYSIBM schema -- so you should be issuing "SELECT * FROM SYSCAT.KEYCOLUSE" to list all of the columns that participate in a given key constraint.
A complete list of the catalog views for DB2 can be referenced at http://publib.boulder.ibm.com/infocenter/db2help/ by searching for 'catalog views' and selecting the top hit.
Rio Bautista (RRIT)
20-Feb-2004 08:08
20-Feb-2004 08:08
Sample to get the primary keys of an MSSQL table:
$cn = odbc_connect( "DSN", "sa", "pwd");
$rs = odbc_primarykeys( $cn, "database", "dbo", "table_name");
odbc_result_all($rs);
devendra_joshi at hotmail dot com
11-Jul-2003 02:52
11-Jul-2003 02:52
I want a list of primary keys of a table in db2
by using
'select * from SYSIBM.SYSKEYCOLUSE ' query i am getting the result on CLP
but when i am writing it in PHP as follows it returns 0 ROWS.
$mstmt="select * from SYSIBM.SYSKEYCOLUSE";
$b=odbc_exec($conn,$mstmt);
echo odbc_result_all($b);
where as if we write this code
$mstmt="select * from SYSIBM.SYSFUNCTIONS";
$b=odbc_exec($conn,$mstmt);
echo odbc_result_all($b);
it returns the correct data.