Php odbc sql select

odbc_exec

Идентификатор соединения ODBC, за подробностями обращайтесь к odbc_connect() .

Возвращаемые значения

Возвращает идентификатор результата ODBC, если команда SQL была выполнена успешно, или false в случае возникновения ошибки.

Список изменений

Смотрите также

User Contributed Notes 21 notes

hi all, I managed to get this little snippet working, it’s pretty useful if you have long forms to be inserted into a database.

if ( ! empty ( $_POST ) )array_pop($_POST);
foreach($_POST as $key => $val) $columns .= addslashes($key) . «, «;
$values .= «‘» . addslashes($val) . «‘, «;

>
$values = substr_replace($values, «», -2);
$columns = substr_replace($columns, «», -2);

$sql = «INSERT INTO table ($columns) VALUES ($values)»;
echo $sql;
$results = odbc_exec($conn, $sql);
if ($results) echo «Query Executed»;
>else echo «Query failed » .odbc_error();
>
>

Not the most secure in the world but, speeds up collecting data from large forms.

If you’re running NT/IIS with PHP 3.0.11 and want to use MS Access dbs with «stored procedures» you can send an ODBC SQL query like:
$conn_id = odbc_connect ( «odbc_test_db» , «» , «» , SQL_CUR_USE_DRIVER );
$qry_id = odbc_do ( $conn_id , «» );
?>
This way you don’t need to integrate query strings like

SELECT * FROM TblObject WHERE (((TblObject.something) Like «blahblahblah»));

in the php file. You directly call the query «MyQuery» that was generated by MS Access.

If you are having problems with truncated text fields from ODBC queries (pe. at 4096 characters), try some of the following:

in php.ini:
— odbc.defaultlrl = 65536

in your php code, before your queries:
— ini_set ( ‘odbc.defaultlrl’ , ‘65536’ );

I tried this way to see the results of a query and it works!!

$result=odbc_exec($Conn,»select nom from usuaris;»);

The following seems counterintuitive to me and so I am constantly getting burned by it. Just thought I’d add a note for anyone else who might also get burned.

if (!odbc_exec(«select MyValue from MyTable where Key1=’x’ and Key2=’y'»))

is not a good way to search for the existence of a record with Key1 = x and Key2 = y. The odbc_exec always returns a result handle, even though there aren’t any records.

Rather, you must use one of the fetch functions to find out that the record really doesn’t exist. This should work:

if (!($Selhand = odbc_exec(«select MyValue from MyTable where Key1=’x’ and Key2=’y'»))
|| !odbc_result($Selhand, 1))

If a single quote exists within the field specified by your WHERE statement, ODBC fails because of a parsing error. Although it seems intuitive, using \» around the field does not work (\»$var\»). The only solution I found was to replace all single quotes in my field with two single quotes. ODBC interprets the first single quote as an escape character and interprets the second single quote as a literal. Thanks to http://www.devguru.com/features/knowledge_base/A100206.html for this tip.

I tried the following line of code

$odbc = odbc_connect ( «pbk» , «root» , «» ) or die( odbc_errormsg ());
$q = «insert into pbk values(\» $name \», \» $phone \»)» ;
print $q ;
odbc_exec ( $odbc , $q ) or die( «

» . odbc_errormsg ());
?>

it does not work. However if I use single quotes instead of \» the thing runs smoothly

thus the following would work

$odbc = odbc_connect ( «pbk» , «yourworstnightmare» , «abracadabra» ) or die( odbc_errormsg ());
$q = «insert into pbk values(‘ $name ‘, ‘ $phone ‘)» ;
print $q ;
odbc_exec ( $odbc , $q ) or die( «

» . odbc_errormsg ());
?>

Also having a user dsn is no good on win2k. Always have a System DSN. I don’t know yet what are the implications of the same.

In a previous contribution it was told that if you’re running NT/IIS with PHP 3.0.11 you can use MS Access dbs «stored procedures».

That was right, but if those stores procedures have parameters you have to supply them in the command line like this:

$conn_id = odbc_connect( «odbc_test_db», «»,»», SQL_CUR_USE_DRIVER );
$qry_id = odbc_do( $conn_id, «» );

As an addition to the note about square brackets earlier:

Enclosing sql field names in ‘[‘ and ‘]’ also allows you to use MS Access reserved words like ‘date’ and ‘field’ and ‘time’ in your SQL query. it seems that the square brackets simply tell Access to ignore any other meaning whatever is inside them has and take them simply as field names.

Problem: Fieldnames in SQL-Statement have blanks and [] don’t work!

SELECT table2.first, table1.[last name] FROM tabel1, table2 -> don’t work

SELECT table2.first, table1.\»last name\» FROM tabel1, table2 -> Try this

PS: Don’t forget the espace characters .

ODBC/MS Access Date Fields:

Matching dates in SELECT statements for MS Access requires the following format:
#Y-m-d H:i:s#

SELECT * FROM TableName WHERE Birthdate = #2001-01-07 00:00:00#

SELECT * FROM TableName WHERE Birthdate BETWEEN #2000-01-07 00:00:00# AND #2001-01-07 00:00:00#

This took me forever to figure out.

Additional links to ODBC_exec:
How to actually write the SQL commands:
http://www.roth.net/perl/odbc/faq/
http://www.netaxs.com/~joc/perl/article/SQL.html
Demystifying SQL
BIG REF MANUAL:
http://w3.one.net/~jhoffman/sqltut.htm
Introduction to Structured Query Language
Covers read, add, modify & delete of data.

for Win32(NT) and MSAcess 2000, whenever you retrieve a date column/field, php will automatically convert it to ‘yyyy/mm/dd hh:mm:ss’ format regardless of the style of date you’ve denoted in Access.
This seems to pose a problem when you exec SELECT, UPDATE, or DELETE queries, but strangley INSERT works fine. I’ve tried parsing the date into the desired format, but php still yells criteria mismatch.

It is easy to inject evil code into SQL statements. This wraps parameters in quotes so they are not executable. In your own stored procedures you can convert the string to numeric as needed.

function sql_make_string($sin) return «‘».str_replace(«‘»,»»»,$sin).»‘»;
>

// this may delete all data from MYTABLE
$evil = «734′; DELETE FROM MYTABLE; print ‘ha ha»;
$sql = «SELECT * FROM MYTABLE WHERE mykey = ‘$evil'»;
$rst = odbc_exec($connection,$sql);

// this probably will not delete the data.
$good = sql_make_string($evil);
$sql = «SELECT * FROM MYTABLE WHERE mykey note» >

This opens select statements ‘for update’ by default in db2. If you’re using db2, you have to tack on ‘for read only’ at the end to select from SYSCAT.TABLES, for example, without firing an error like

Warning: SQL error: [IBM][CLI Driver][DB2/LINUX] SQL0151N The column «MAXFREESPACESEARCH» cannot be updated. SQLSTATE=42808 , SQL state 42808 in SQLExecDirect

$query = odbc_exec($conn, «select * from syscat.tables for read only»);
odbc_result_all($query);

will work (only for db2). I don’t know about other databases.

The select statement will work in the ‘db2’ command line, but not in php, because of this side effect.

An SQL statement that contains quotes (i.e. «) instead of apostrophes (i.e. ‘) to delimit strings works fine in Access, however, in odbc_exec, it fails with

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 6.

$q = «INSERT INTO TableA (Fld1, Fld2, Fld3) VALUES(‘A’, ‘B’, ‘C’);»

works fine in both Access and ODBC, but

$q = ‘INSERT INTO TableA (Fld1, Fld2, Fld3) VALUES(«A», «B», «C»);’

fails with the above error.

// — This is a complete working dynamic example of using:
// odbc_connect, odbc_exec, getting col Names,
// odbc_fetch_row and no of rows. hope it helps
// — your driver should point to your MS access file

$conn = odbc_connect ( ‘MSAccessDriver’ , » , » );

if ( $conn )
$sql = «select * from $month » ;
//this function will execute the sql satament
$result = odbc_exec ( $conn , $sql );

>
else echo «odbc not connected
» ;
?>

If Openlink -> MS Access Database fails and gives «Driver Not Capable» error or «No tuples available» warning, use the SQL_CUR_USE_ODBC cursor when using odbc_connect().

«[Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 1.»

this not so clear to understand error comes when using access-odbc and a field name can’t be found. check for correct spelling of fields.

I wanted to access an MSAccess database via ODBC. The connection functioned without problems, but when I placed a SQL statement into my odbc_exec() i always got an error:
Warning: SQL error: [Microsoft][ODBC Driver Manager] Driver does not support that function, SQL state IM001 in SQLSetStmtOption in \\Server\directory/test.php3 on line 19.

Resolved my problem by myself: i simply had to install a new odbc-driver from the microsoft homepage.

• Функции ODBC
  • odbc_​autocommit
  • odbc_​binmode
  • odbc_​close_​all
  • odbc_​close
  • odbc_​columnprivileges
  • odbc_​columns
  • odbc_​commit
  • odbc_​connect
  • odbc_​connection_​string_​is_​quoted
  • odbc_​connection_​string_​quote
  • odbc_​connection_​string_​should_​quote
  • odbc_​cursor
  • odbc_​data_​source
  • odbc_​do
  • odbc_​error
  • odbc_​errormsg
  • odbc_​exec
  • odbc_​execute
  • odbc_​fetch_​array
  • odbc_​fetch_​into
  • odbc_​fetch_​object
  • odbc_​fetch_​row
  • odbc_​field_​len
  • odbc_​field_​name
  • odbc_​field_​num
  • odbc_​field_​precision
  • odbc_​field_​scale
  • odbc_​field_​type
  • odbc_​foreignkeys
  • odbc_​free_​result
  • odbc_​gettypeinfo
  • odbc_​longreadlen
  • odbc_​next_​result
  • odbc_​num_​fields
  • odbc_​num_​rows
  • odbc_​pconnect
  • odbc_​prepare
  • odbc_​primarykeys
  • odbc_​procedurecolumns
  • odbc_​procedures
  • odbc_​result_​all
  • odbc_​result
  • odbc_​rollback
  • odbc_​setoption
  • odbc_​specialcolumns
  • odbc_​statistics
  • odbc_​tableprivileges
  • odbc_​tables

  Источник

  odbc_execute

  Executes a statement prepared with odbc_prepare() .

  Parameters

  The result id resource , from odbc_prepare() .

  Parameters in params will be substituted for placeholders in the prepared statement in order. Elements of this array will be converted to strings by calling this function.

  Any parameters in params which start and end with single quotes will be taken as the name of a file to read and send to the database server as the data for the appropriate placeholder.

  If you wish to store a string which actually begins and ends with single quotes, you must add a space or other non-single-quote character to the beginning or end of the parameter, which will prevent the parameter from being taken as a file name. If this is not an option, then you must use another mechanism to store the string, such as executing the query directly with odbc_exec() ).

  Return Values

  Returns true on success or false on failure.

  Examples

  Example #1 odbc_execute() and odbc_prepare() example

  In the following code, $success will only be true if all three parameters to myproc are IN parameters:

  $a = 1 ;
  $b = 2 ;
  $c = 3 ;
  $stmt = odbc_prepare ( $conn , ‘CALL myproc(. )’ );
  $success = odbc_execute ( $stmt , array( $a , $b , $c ));
  ?>

  If you need to call a stored procedure using INOUT or OUT parameters, the recommended workaround is to use a native extension for your database (for example, oci8 for Oracle).

  See Also

  Источник