function db_columnType($oStmt, $iPos) { $rval = odbc_field_type($oStmt, $iPos); if ($rval == "") { $rval = "UNDEFINED"; } return $rval; }
function GetFields() { $_fields = array(); $_result = odbc_exec($this->_Link, $this->SelectCommand); for ($i = 1; $i <= odbc_num_fields($_result); $i++) { $_field = array("Name" => odbc_field_name($_result, $i), "Type" => odbc_field_type($_result, $i), "Not_Null" => 0); array_push($_fields, $_field); } return $_fields; }
/** * @param String strSQL * @return Array */ public function db_getfieldslist($strSQL) { $res = array(); $qResult = $this->connectionObj->query($strSQL); $fieldsNumber = $qResult->numFields(); for ($i = 0; $i < $fieldsNumber; $i++) { $stype = odbc_field_type($qResult->getQueryHandle(), $i + 1); $ntype = $this->getFieldTypeNumber($stype); $res[$i] = array("fieldname" => $qResult->fieldName($i), "type" => $ntype, "is_nullable" => 0); } return $res; }
function db_getfieldslist($strSQL) { global $conn; $res=array(); $rs=db_query($strSQL,$conn); for($i=0;$i<db_numfields($rs);$i++) { $stype=odbc_field_type($rs,$i+1); $ntype=db_fieldtypenum($stype); $res[$i]=array("fieldname"=>db_fieldname($rs,$i),"type"=>$ntype,"is_nullable"=>0); } return $res; }
/** * Field data * * Generates an array of objects containing field meta-data * * @access public * @return array */ function field_data() { $retval = array(); for ($i = 0; $i < $this->num_fields(); $i++) { $F = new stdClass(); $F->name = odbc_field_name($this->result_id, $i); $F->type = odbc_field_type($this->result_id, $i); $F->max_length = odbc_field_len($this->result_id, $i); $F->primary_key = 0; $F->default = ''; $retval[] = $F; } return $retval; }
function &FetchField($fieldOffset = -1) { $off = $fieldOffset + 1; // offsets begin at 1 $o = new ADOFieldObject(); $o->name = @odbc_field_name($this->_queryID, $off); $o->type = @odbc_field_type($this->_queryID, $off); $o->max_length = @odbc_field_len($this->_queryID, $off); if (ADODB_ASSOC_CASE == 0) { $o->name = strtolower($o->name); } else { if (ADODB_ASSOC_CASE == 1) { $o->name = strtoupper($o->name); } } return $o; }
/** * Returns information about a table or a result set * * @param object|string $result DB_result object from a query or a * string containing the name of a table. * While this also accepts a query result * resource identifier, this behavior is * deprecated. * @param int $mode a valid tableInfo mode * * @return array an associative array with the information requested. * A DB_Error object on failure. * * @see DB_common::tableInfo() * @since Method available since Release 1.7.0 */ function tableInfo($result, $mode = null) { if (is_string($result)) { /* * Probably received a table name. * Create a result resource identifier. */ $id = @odbc_exec($this->connection, "SELECT * FROM {$result}"); if (!$id) { return $this->odbcRaiseError(); } $got_string = true; } elseif (isset($result->result)) { /* * Probably received a result object. * Extract the result resource identifier. */ $id = $result->result; $got_string = false; } else { /* * Probably received a result resource identifier. * Copy it. * Deprecated. Here for compatibility only. */ $id = $result; $got_string = false; } if (!is_resource($id)) { return $this->odbcRaiseError(DB_ERROR_NEED_MORE_DATA); } if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) { $case_func = 'strtolower'; } else { $case_func = 'strval'; } $count = @odbc_num_fields($id); $res = array(); if ($mode) { $res['num_fields'] = $count; } for ($i = 0; $i < $count; $i++) { $col = $i + 1; $res[$i] = array('table' => $got_string ? $case_func($result) : '', 'name' => $case_func(@odbc_field_name($id, $col)), 'type' => @odbc_field_type($id, $col), 'len' => @odbc_field_len($id, $col), 'flags' => ''); if ($mode & DB_TABLEINFO_ORDER) { $res['order'][$res[$i]['name']] = $i; } if ($mode & DB_TABLEINFO_ORDERTABLE) { $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i; } } // free the result only if we were called on a table if ($got_string) { @odbc_free_result($id); } return $res; }
function write_data_odbc($table_name) { global $db; $ary_type = $ary_name = array(); $ident_set = false; $sql_data = ''; // Grab all of the data from current table. $sql = "SELECT *\n\t\t\tFROM {$table_name}"; $result = $db->sql_query($sql); $retrieved_data = odbc_num_rows($result); if ($retrieved_data) { $sql = "SELECT 1 as has_identity\n\t\t\t\tFROM INFORMATION_SCHEMA.COLUMNS\n\t\t\t\tWHERE COLUMNPROPERTY(object_id('{$table_name}'), COLUMN_NAME, 'IsIdentity') = 1"; $result2 = $db->sql_query($sql); $row2 = $db->sql_fetchrow($result2); if (!empty($row2['has_identity'])) { $sql_data .= "\nSET IDENTITY_INSERT {$table_name} ON\nGO\n"; $ident_set = true; } $db->sql_freeresult($result2); } $i_num_fields = odbc_num_fields($result); for ($i = 0; $i < $i_num_fields; $i++) { $ary_type[$i] = odbc_field_type($result, $i + 1); $ary_name[$i] = odbc_field_name($result, $i + 1); } while ($row = $db->sql_fetchrow($result)) { $schema_vals = $schema_fields = array(); // Build the SQL statement to recreate the data. for ($i = 0; $i < $i_num_fields; $i++) { $str_val = $row[$ary_name[$i]]; if (preg_match('#char|text|bool|varbinary#i', $ary_type[$i])) { $str_quote = ''; $str_empty = "''"; $str_val = sanitize_data_mssql(str_replace("'", "''", $str_val)); } else { if (preg_match('#date|timestamp#i', $ary_type[$i])) { if (empty($str_val)) { $str_quote = ''; } else { $str_quote = "'"; } } else { $str_quote = ''; $str_empty = 'NULL'; } } if (empty($str_val) && $str_val !== '0' && !(is_int($str_val) || is_float($str_val))) { $str_val = $str_empty; } $schema_vals[$i] = $str_quote . $str_val . $str_quote; $schema_fields[$i] = $ary_name[$i]; } // Take the ordered fields and their associated data and build it // into a valid sql statement to recreate that field in the data. $sql_data .= "INSERT INTO {$table_name} (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\nGO\n"; $this->flush($sql_data); $sql_data = ''; } $db->sql_freeresult($result); if ($retrieved_data && $ident_set) { $sql_data .= "\nSET IDENTITY_INSERT {$table_name} OFF\nGO\n"; } $this->flush($sql_data); }
function sql_fieldtype($offset, $query_id = 0) { if (!$query_id) { $query_id = $this->query_result; } if ($query_id) { $result = @odbc_field_type($query_id, $offset); return $result; } else { return false; } }
function FieldType($rsMain, $i) { return odbc_field_type($rsMain, $i + 1); }
free(){odbc_free_result($this->resultSet);$this->resultSet=NULL;}function getResultColumns(){$count=odbc_num_fields($this->resultSet);$columns=array();for($i=1;$i<=$count;$i++){$columns[]=array('name'=>odbc_field_name($this->resultSet,$i),'table'=>NULL,'fullname'=>odbc_field_name($this->resultSet,$i),'nativetype'=>odbc_field_type($this->resultSet,$i));}return$columns;}function
function afficheForm($entite, $titre, $id, $event = null, $source = null) { include_once 'classes/materiel.class.php'; include_once 'classes/fournisseur.class.php'; //initialise disabled global $disabled; $disabled = ''; $cnx = ouvresylob(1); //recup table pr chq entité switch ($entite) { case "contrat": $latable = "informix.zz_contratmat"; $lajointure = ""; $leschamps = "*"; $lechamp = "id_contratmat"; $obligatoires = array("num", "debut", "fin", "preavis", "ent"); break; case "intervention": $latable = "informix.zz_inter"; $lajointure = ""; $leschamps = "id_inter as id,debut_inter as debut, fin_inter as fin, \r\n presta_inter as prestataire, intervenant_inter as intervenant,\r\n etat_inter, type_inter, \r\n periodicite_inter as periodicite, priorite_inter as priorite, \r\n domaine_inter as domaine, cout_inter as cout,\r\n detail_inter as detail, supp_inter as supp"; $lechamp = "id_inter"; $obligatoires = array("debut", "fin", "intervenant", "type_inter", "domaine"); break; case "materiel": $latable = "informix.zz_materiel"; $lajointure = " left join informix.zz_misservice on informix.zz_misservice.id_materiel = informix.zz_materiel.id_materiel\r\n left join informix.zz_destruction on informix.zz_destruction.id_materiel = informix.zz_materiel.id_materiel\r\n left join informix.bas_postrav on informix.bas_postrav.no_poste= informix.zz_materiel.id_posttrav"; $leschamps = "informix.zz_materiel.id_materiel as id, sn_materiel as sn, desig_materiel as designation,\r\n zone_materiel as zonemateriel, famille_materiel as fammat, id_posttrav as poste,\r\n type_materiel as typemateriel, marq_materiel as marque_materiel, frn_materiel as fournisseur,\r\n dteha_materiel as achat, etat_materiel as etatmateriel, contrat_materiel as contratmat, \r\n informix.zz_materiel.usage_materiel as usagemateriel, dte_misservice, dte_destruction"; $lechamp = "informix.zz_materiel.id_materiel"; $obligatoires = array("designation", "zonemateriel", "fammat", "typemateriel", "etatmateriel", "dte_misservice"); break; default: $latable = "informix.zz_" . $entite; $lechamp = "id_" . $entite; $obligatoires = array(""); break; } //on recupere les champs de l'entite $sql = "select " . $leschamps . " from " . $latable; if ($lajointure != '') { $sql .= $lajointure; } if ($id != null) { //on a un id => recup entite pr modif $sql = $sql . " where " . $lechamp . " = " . $id . ""; } else { $sql = $sql . " where 1=2"; //recup dernier id renseigné $sql0 = "SELECT max(" . $lechamp . ") FROM " . $latable; //echo $sql0; $res = odbc_exec($cnx, $sql0) or die('Erreur : ' . $sql); if (odbc_result($res, 1) > 0) { $no = odbc_result($res, 1) + 1; } else { $no = 1; } } $result = odbc_exec($cnx, $sql) or die('Erreur : ' . $sql); $ncols = odbc_num_fields($result); for ($i = 0; $i < odbc_num_fields($result); $i++) { $champs[] = odbc_field_name($result, $i + 1); $typeChamp[] = odbc_field_type($result, $i + 1); } $nb_champs = odbc_num_fields($result); //initialise tableau de valeurs a vide $valeurs = array(); for ($i = 0; $i < $nb_champs; $i++) { $valeurs[$i] = ''; } //si id renseigné => on est en modif : on recup donnees table if ($id) { while (odbc_fetch_row($result)) { for ($i = 0; $i < $nb_champs; $i++) { $valeurs[$i] = odbc_result($result, $i + 1); } //si etat = 3 on disabled if ($entite == 'intervention' and $valeurs[5] == 3 or $entite == 'materiel' and $valeurs[10] == 3) { $disabled = 'disabled'; } } $no = $id; } elseif (isset($_POST['id'])) { for ($i = 0; $i < $nb_champs; $i++) { if (isset($_POST[$champs[$i]]) and $_POST[$champs[$i]] != '') { $valeurs[$i] = $_POST[$champs[$i]]; } } $no = $_POST['id']; //on recup n° entite pr affichage } elseif (isset($_GET['materiel']) and $_GET['materiel'] != '') { for ($i = 0; $i < $nb_champs; $i++) { if (isset($_GET[$champs[$i]]) and $_GET[$champs[$i]] != '') { $valeurs[$i] = utf8_decode($_GET[$champs[$i]]); //on a encode pr passer en param ds calendar => on decode } } } //odbc_close($cnx); echo "<br>"; echo "<div id='edit'>"; echo "<TABLE align=center style=background:#fdf2db;color:#135356>\r\n <TR>\r\n <TD colspan=6 style='background:#519085' width=1090 align=right>\r\n <a href='" . $entite . ".php' ><img border=0 src='images/list.png' title='Affichage liste'></a>\r\n </td>\r\n </tr>\r\n </table>"; //affichage different pour materiel_infos if ($entite != 'materiel_infos') { //si contrat ou inter on doit recup liste materiels concernés if ($entite == "contrat" or $entite == "intervention") { $java = "onSubmit='javascript: soumettre_1liste(document.forms[0].choisi);'"; } else { $java = ""; } //affichage du formulaire echo " <form name='" . $entite . "' " . $java . " action = 'edit_" . $entite . ".php' \r\n method='post' onfocus='trie(this," . $entite . ",2);');'>\r\n <TABLE align=center style=background:#fdf2db;color:#135356>\r\n <TR>\r\n <TD colspan = 6 style='background:#519085' width=1090>\r\n <span>" . strtoupper(utf8_decode($titre)) . "</span>\r\n </TD>\r\n </TR>\r\n <tr><td colspan=6> </td></tr>\r\n <TR>\r\n <TD colspan = 6 align=center>\r\n <font color=#135356 size=5><b>" . str_replace("MATERIEL", "ENTITE", strtoupper(utf8_decode(str_replace("informix.zz_", "", $entite)))) . " N°" . $no . "</b></font>\r\n </TD>\r\n </TR>\r\n <tr><td colspan=6> </td></tr>\r\n <TR>\r\n <TD></TD><TD></TD>\r\n </TR>"; } //si form contrat ou inter on choisi le(s) materiel(s) concerné(s) if ($entite == 'contrat' or $entite == 'intervention') { afficheFiltre($entite); //liste materiel selectionné if ($id != '') { switch ($entite) { case 'contrat': $listemateriels = getMatContrat($id); break; case 'intervention': $listemateriels = getMatInter($id); break; default: break; } } else { $listemateriels = array(); } echo "<tr><td colspan=6><table border=0 align=center><tr> \r\n <th>Entités disponibles </th><th></th><th></th><th>Entité(s) concernée(s) </th></tr><tr>\r\n <td><select size = 10 style='width:300' name=dispo[] id=dispo " . $disabled . " multiple onDblClick='javascript: deplacer(this.form.dispo, this.form.choisi);'>"; //contenu liste materiel dispo renseigné par script echo " </select></td>"; echo " <td></td><td>\r\n <input type=button " . $disabled . " value=Ajouter onclick='javascript: deplacer(this.form.dispo, this.form.choisi);'><br><br>\r\n <input type=button " . $disabled . " value=Retirer onclick='javascript: deplacer(this.form.choisi, this.form.dispo);'>\r\n </td>"; echo " <td><select size = 10 style='width:300' class='obligatoire' name=choisi[] id=choisi " . $disabled . " multiple onDblClick='javascript: deplacer(this.form.choisi, this.form.dispo);'>"; foreach ($listemateriels as $materiel) { echo " <option value ='" . $materiel . "' >" . $materiel . " - " . utf8_encode(getNomMat($materiel)) . "</option>"; } echo " </select></td></tr></table>"; } echo " <TR><tr><td colspan=6> </td></tr>"; //on boucle sur ts les champs de l'entite pour faire le formulaire for ($i = 0; $i < $nb_champs; $i++) { $champs[$i] = str_replace("_contratmat", "", $champs[$i]); if (in_array($champs[$i], $obligatoires)) { $classe = "class=obligatoire"; } else { $classe = ""; } $label = formatLabel($champs[$i]); //on cree les champs de formulaire selon leur type switch ($typeChamp[$i]) { case "INTEGER": //int sylob formInteger($champs[$i], $valeurs[$i], $disabled, $classe); break; case "SERIAL": //id $id = $valeurs[$i]; //recupere l'id echo "<input type=hidden name=id value='" . $id . "'>"; break; case "FLOAT": //duree ou prix if ($champs[$i] == "duree") { echo "<td>" . $label . " </td><td><input type=text " . $disabled . " " . $classe . " name=" . $champs[$i] . " value = '" . $valeurs[$i] . "' onKeyPress='return chiffres(event);'> heure(s)</td>"; } else { echo "<td>" . $label . " </td><td><input type=text " . $disabled . " " . $classe . " name=" . $champs[$i] . " value = '" . $valeurs[$i] . "' onKeyPress='return chiffres(event);'> € HT</td>"; } break; case "DATE": //date sylob formDate($champs[$i], $valeurs[$i], $disabled); break; case "DATETIME YEAR TO MINUTE": //date + heure sylob if (isset($_GET['debut'])) { //echo $_GET['debut']; formHeure($champs[$i], $_GET['debut'], $disabled); } else { //echo $valeurs[$i]; formHeure($champs[$i], $valeurs[$i], $disabled); } break; case "CHAR": //char sylob formChar($champs[$i], $valeurs[$i], $disabled, $classe); break; case "VARCHAR": //textarea echo "</TR><TR><td>" . $label . " </td><td colspan=5><textarea " . $disabled . " name=" . $champs[$i] . " cols=50 rows=5>" . utf8_encode($valeurs[$i]) . "</textarea></td></TR><TR>"; break; default: echo "pas traité :" . $champs[$i] . " - " . $typeChamp[$i]; break; } if ($i % 3 == 0) { echo "<tr><td colspan=6> </td></tr>"; } } //si on vient du calendrier on recuperre source if (isset($source) and $source == "calendrier") { echo "<input type=hidden " . $disabled . " name=source value=" . $source . ">"; } if ($entite != "materiel_infos") { echo " </TR> \r\n <TR>\r\n <TD COLSPAN=6 align = center>\r\n <input type=button " . $disabled . " id=monform name=monform onclick=verifForm(this.form); value=Valider />\r\n </TD>\r\n </TR>\r\n </TABLE> \r\n </form>\r\n </div>"; } }
function db_field_type($res, $field_offset) { switch (DATABASE) { case 'mysql': return mysql_field_type($res, $field_offset); case 'mysqli': $fo = mysqli_fetch_field_direct($res, $field_offset); if ($fo === false) { return false; } return $fo->type; case 'sqlserver': return odbc_field_type($res, $field_offset); } }
echo ", fields=" . $maxfields . ", rows=" . odbc_num_rows($r) . "\n"; if (odbc_num_rows($r) == 0) { // if no rows, dont go looking for data _or_ fields continue; } // Get all the field names and store in an array $fields if ($maxfields > 3) { $maxfields = $maxfields - 3; // TODO: why this is needed, php crashes otherise } $ignores = array('DATE_ACCOUNT_OPENED'); // TODO: if there are fields not to show for ($i = 1; $i <= $maxfields; $i++) { //echo "\nfield " . $i . odbc_field_name($r, $i) . " type=" . odbc_field_type($r, $i); if (!in_array(odbc_field_name($r, $i), $ignores)) { $fields[odbc_field_name($r, $i)] = odbc_field_type($r, $i); } //else // echo "Ignore: " . odbc_field_name($r, $i); } //print_r($fields); // to show the field structure // for each row store data in array $results indexed by fieldname $x = 0; while ($row = odbc_fetch_row($r)) { for ($i = 1; $i <= odbc_num_fields($r); $i++) { if (!in_array(odbc_field_name($r, $i), $ignores)) { $results[$x][odbc_field_name($r, $i)] = odbc_result($r, $i); } } $x++; }
<th>Name</th> <th>Type</th> <th>Length</th> </tr> <?php $info = odbc_exec($conn, "select * from php_test"); $numfields = odbc_num_fields($info); for ($i = 1; $i <= $numfields; $i++) { ?> <tr> <td><?php echo odbc_field_name($info, $i); ?> </td> <td><?php echo odbc_field_type($info, $i); ?> </td> <td><?php echo odbc_field_len($info, $i); ?> </td> </tr> <?php } odbc_free_result($info); ?> </table> Inserting data: <?php
/** * Returns an array of the fields in given table name. * * @param Model $model Model object to describe * @return array Fields in table. Keys are name and type */ function &describe(&$model) { $cache = parent::describe($model); if ($cache != null) { return $cache; } $fields = array(); $sql = 'SELECT * FROM ' . $this->fullTableName($model); $result = odbc_exec($this->connection, $sql); $count = odbc_num_fields($result); for ($i = 1; $i <= $count; $i++) { $cols[$i - 1] = odbc_field_name($result, $i); } foreach ($cols as $column) { $type = odbc_field_type(odbc_exec($this->connection, "SELECT " . $column . " FROM " . $this->fullTableName($model)), 1); $fields[$column] = array('type' => $type); } $this->__cacheDescription($model->tablePrefix . $model->table, $fields); return $fields; }
} } mysql_free_result($result); mysql_close($connection); break; case 'ORACLE': $connect = odbc_connect($servername, $username, $password); $result = odbc_exec($connect, $sql); # return error if query failed if (!$result) { print json_encode(array('error' => array('message' => odbc_errormsg($connect), 'code' => odbc_error($connect)))); exit; } else { #odbc returns results as strings, so get datatype fields here for ($x = 1; $x <= odbc_num_fields($result); $x++) { $types[] = odbc_field_type($result, $x); } while ($row = odbc_fetch_array($result)) { $x = 0; #convert each string to its actual datatype, so easily usable in javascript foreach ($row as $key => $value) { switch ($types[$x]) { case 'NUMBER': $row[$key] = $value + 0; break; default: break; } $x++; } $data[] = $row;
<?php if (isset($_POST['table'])) { $sql = Grammar::select_all($_POST['table']); $result = odbc_exec($connection, $sql); if ($result) { $nCols = odbc_num_fields($result); odbc_fetch_row($result, 0); } else { $nCols = 0; $status = odbc_errormsg($connection); } echo "\"metadata\":["; for ($i = 1; $i <= $nCols; $i++) { echo "{\"type\":"; echo json_encode(odbc_field_type($result, $i)); echo ",\"name\":"; echo json_encode(odbc_field_name($result, $i)); echo ",\"len\":"; echo json_encode(odbc_field_len($result, $i)); echo ",\"precision\":"; echo json_encode(odbc_field_precision($result, $i)); echo ",\"scale\":"; echo json_encode(odbc_field_scale($result, $i)); if ($i < $nCols) { echo "},"; } else { echo "}"; } } echo "],";
function &FetchField($fieldOffset = -1) { $off = $fieldOffset + 1; // offsets begin at 1 $o = new ADOFieldObject(); $o->name = @odbc_field_name($this->_queryID, $off); $o->type = @odbc_field_type($this->_queryID, $off); $o->max_length = @odbc_field_len($this->_queryID, $off); return $o; }
function ecritFormulaire($pTable, $pNomForm, $pFichAction, $pMethode, $pFichier) { //écrit dans un fichier le code HTML produisant un formulaire pour les champs d'une table d'une base $result = odbc_do($this->connexion, "select * from " . $pTable); //explore les champs de la table //écriture des propriétés du formulaire fputs($pFichier, '<form name="' . $pNomForm . '" method="' . $pMethode . '" action="' . $pFichAction . '">'); //écrit dans le fichier fputs($pFichier, "\n"); //retour à la ligne //parcours des champs de la table for ($i = 1; $i < odbc_num_fields($result) + 1; $i++) { $this->traiteUnChampForm(odbc_field_name($result, $i), odbc_field_type($result, $i), odbc_field_len($result, $i), $pFichier); } //écriture du pied de formulaire avec les boutons correspondants fputs($pFichier, '<label class="titre"></label><div class="zone"><input type="reset" value="annuler"></input><input type="submit"></input></form>'); }
public function columnData() { if (empty($this->query)) { return false; } $columns = array(); for ($i = 0, $index = 1, $c = $this->num_fields(); $i < $c; $i++, $index++) { $columns[$i] = new stdClass(); $columns[$i]->name = odbc_field_name($this->query, $index); $columns[$i]->type = odbc_field_type($this->query, $index); $columns[$i]->max_length = odbc_field_len($this->query, $index); $columns[$i]->primary_key = 0; $columns[$i]->default = ''; } return $columns; }
/** * Returns metadata for all columns in a result set. * * @return array */ public function getColumnsMeta() { $count = odbc_num_fields($this->resultSet); $meta = array(); for ($i = 1; $i <= $count; $i++) { // items 'name' and 'table' are required $meta[] = array('name' => odbc_field_name($this->resultSet, $i), 'table' => NULL, 'type' => odbc_field_type($this->resultSet, $i), 'length' => odbc_field_len($this->resultSet, $i), 'scale' => odbc_field_scale($this->resultSet, $i), 'precision' => odbc_field_precision($this->resultSet, $i)); } return $meta; }
/** * Get the rows returned from a SELECT query. * * @param resource The query result pointer * @param ?integer Whether to start reading from (NULL: irrelevant for this forum driver) * @return array A list of row maps */ function db_get_query_rows($results, $start = NULL) { $out = array(); $i = 0; $num_fields = odbc_num_fields($results); $types = array(); $names = array(); for ($x = 1; $x <= $num_fields; $x++) { $types[$x] = odbc_field_type($results, $x); $names[$x] = odbc_field_name($results, $x); } while (odbc_fetch_row($results)) { if (is_null($start) || $i >= $start) { $newrow = array(); for ($j = 1; $j <= $num_fields; $j++) { $v = odbc_result($results, $j); $type = $types[$j]; $name = strtolower($names[$j]); if ($type == 'INTEGER' || $type == 'SMALLINT' || $type == 'UINTEGER') { if (!is_null($v)) { $newrow[$name] = intval($v); } else { $newrow[$name] = NULL; } } else { $newrow[$name] = $v; } } $out[] = $newrow; } $i++; } odbc_free_result($results); // echo '<p>End '.microtime(false); return $out; }
\tti TINYINT, \tc CHAR(123), \tvc VARCHAR(125) ) EOSQL; odbc_exec($conn, "IF OBJECT_ID('php_types') IS NOT NULL DROP TABLE php_types") or die(odbc_errormsg()); odbc_exec($conn, $sql) or die(odbc_errormsg()); $sql = "select * from php_types"; echo "Query: {$sql}\n"; $result = odbc_exec($conn, $sql) or die(odbc_errormsg()); $all = array('ui' => 'smallint-5', 'i' => 'int-10', 'ti' => 'tinyint-3', 'c' => 'char-123', 'vc' => 'varchar-125'); $err = ''; $ok = 0; for ($i = 1; $i <= odbc_num_fields($result); $i++) { $name = odbc_field_name($result, $i); $type = odbc_field_type($result, $i); $len = odbc_field_len($result, $i); echo "column {$name} type {$type} len {$len}\n"; $type = strtolower($type); if ($all[$name] != "{$type}-{$len}") { $err .= "Invalid column {$name}\n"; } else { ++$ok; } } if ($ok != 5) { $err .= "Expected 5 columns\n"; } if ($err) { echo "{$err}"; exit(1);
function sql_query($query = "", $transaction = FALSE) { if ($query != "") { $this->num_queries++; if ($transaction == BEGIN_TRANSACTION && !$this->in_transaction) { if (!odbc_autocommit($this->db_connect_id, false)) { return false; } $this->in_transaction = TRUE; } if (preg_match("/^SELECT(.*?)(LIMIT ([0-9]+)[, ]*([0-9]+)*)?\$/s", $query, $limits)) { $query = $limits[1]; if (!empty($limits[2])) { $row_offset = $limits[4] ? $limits[3] : ""; $num_rows = $limits[4] ? $limits[4] : $limits[3]; $query = "TOP " . ($row_offset + $num_rows) . $query; } $this->result = odbc_exec($this->db_connect_id, "SELECT {$query}"); if ($this->result) { if (empty($this->field_names[$this->result])) { for ($i = 1; $i < odbc_num_fields($this->result) + 1; $i++) { $this->field_names[$this->result][] = odbc_field_name($this->result, $i); $this->field_types[$this->result][] = odbc_field_type($this->result, $i); } } $this->current_row[$this->result] = 0; $this->result_rowset[$this->result] = array(); $row_outer = isset($row_offset) ? $row_offset + 1 : 1; $row_outer_max = isset($num_rows) ? $row_offset + $num_rows + 1 : 1000000000.0; $row_inner = 0; while (odbc_fetch_row($this->result, $row_outer) && $row_outer < $row_outer_max) { for ($j = 0; $j < count($this->field_names[$this->result]); $j++) { $this->result_rowset[$this->result][$row_inner][$this->field_names[$this->result][$j]] = stripslashes(odbc_result($this->result, $j + 1)); } $row_outer++; $row_inner++; } $this->num_rows[$this->result] = count($this->result_rowset[$this->result]); } } else { if (eregi("^INSERT ", $query)) { $this->result = odbc_exec($this->db_connect_id, $query); if ($this->result) { $result_id = odbc_exec($this->db_connect_id, "SELECT @@IDENTITY"); if ($result_id) { if (odbc_fetch_row($result_id)) { $this->next_id[$this->db_connect_id] = odbc_result($result_id, 1); $this->affected_rows[$this->db_connect_id] = odbc_num_rows($this->result); } } } } else { $this->result = odbc_exec($this->db_connect_id, $query); if ($this->result) { $this->affected_rows[$this->db_connect_id] = odbc_num_rows($this->result); } } } if (!$this->result) { if ($this->in_transaction) { odbc_rollback($this->db_connect_id); odbc_autocommit($this->db_connect_id, true); $this->in_transaction = FALSE; } return false; } if ($transaction == END_TRANSACTION && $this->in_transaction) { $this->in_transaction = FALSE; if (!odbc_commit($this->db_connect_id)) { odbc_rollback($this->db_connect_id); odbc_autocommit($this->db_connect_id, true); return false; } odbc_autocommit($this->db_connect_id, true); } odbc_free_result($this->result); return $this->result; } else { if ($transaction == END_TRANSACTION && $this->in_transaction) { $this->in_transaction = FALSE; if (!@odbc_commit($this->db_connect_id)) { odbc_rollback($this->db_connect_id); odbc_autocommit($this->db_connect_id, true); return false; } odbc_autocommit($this->db_connect_id, true); } return true; } }
function metadata($table) { $count = 0; $id = 0; $res = array(); $this->connect(); $id = odbc_do($this->Link_ID, "select * from {$table}"); if (!$id) { $this->Errno = 1; $this->Error = "General Error (The ODBC interface cannot return detailed error messages)."; $this->halt("Metadata query failed."); } $count = odbc_num_fields($id); for ($i = 1; $i <= $count; $i++) { $res[$i]["table"] = $table; $name = odbc_field_name($id, $i); $res[$i]["name"] = $name; $res[$i]["type"] = odbc_field_type($id, $name); $res[$i]["len"] = 0; // can we determine the width of this column? $res[$i]["flags"] = ""; // any optional flags to report? } odbc_free_result($id); return $res; }
/** * Field data * * Generates an array of objects containing field meta-data * * @return array */ public function field_data() { $retval = array(); for ($i = 0, $odbc_index = 1, $c = $this->num_fields(); $i < $c; $i++, $odbc_index++) { $retval[$i] = new stdClass(); $retval[$i]->name = odbc_field_name($this->result_id, $odbc_index); $retval[$i]->type = odbc_field_type($this->result_id, $odbc_index); $retval[$i]->max_length = odbc_field_len($this->result_id, $odbc_index); $retval[$i]->primary_key = 0; $retval[$i]->default = ''; } return $retval; }
function _odbc_execute_query($query) { $result = false; if (eregi("^SELECT ", $query)) { $result = @odbc_exec($this->db_connect_id, $query); if ($result) { if (empty($this->field_names[$result])) { for ($i = 1, $j = @odbc_num_fields($result) + 1; $i < $j; $i++) { $this->field_names[$result][] = @odbc_field_name($result, $i); $this->field_types[$result][] = @odbc_field_type($result, $i); } } $this->current_row[$result] = 0; $this->result_rowset[$result] = array(); $row_outer = isset($row_offset) ? $row_offset + 1 : 1; $row_outer_max = isset($num_rows) ? $row_offset + $num_rows + 1 : 1000000000.0; $row_inner = 0; while (@odbc_fetch_row($result, $row_outer) && $row_outer < $row_outer_max) { for ($i = 0, $j = sizeof($this->field_names[$result]); $i < $j; $i++) { $this->result_rowset[$result][$row_inner][$this->field_names[$result][$i]] = stripslashes(@odbc_result($result, $i + 1)); } $row_outer++; $row_inner++; } $this->num_rows[$result] = sizeof($this->result_rowset[$result]); } } else { if (eregi("^INSERT ", $query)) { $result = @odbc_exec($this->db_connect_id, $query); if ($result) { $result_id = @odbc_exec($this->db_connect_id, 'SELECT @@IDENTITY'); if ($result_id) { if (@odbc_fetch_row($result_id)) { $this->next_id[$this->db_connect_id] = @odbc_result($result_id, 1); $this->affected_rows[$this->db_connect_id] = @odbc_num_rows($result); } } } } else { $result = @odbc_exec($this->db_connect_id, $query); if ($result) { $this->affected_rows[$this->db_connect_id] = @odbc_num_rows($result); } } } return $result; }
/** * Returns metadata for all columns in a result set. * @return array */ public function getColumnsMeta() { $count = odbc_num_fields($this->resultSet); $res = array(); for ($i = 1; $i <= $count; $i++) { $res[] = array('name' => odbc_field_name($this->resultSet, $i), 'table' => NULL, 'fullname' => odbc_field_name($this->resultSet, $i), 'nativetype' => odbc_field_type($this->resultSet, $i)); } return $res; }
function main($id, $mode) { global $db, $user, $auth, $template, $table_prefix; global $config, $phpbb_root_path, $phpbb_admin_path, $phpEx; $user->add_lang('acp/database'); $this->tpl_name = 'acp_database'; $this->page_title = 'ACP_DATABASE'; $action = request_var('action', ''); $submit = isset($_POST['submit']) ? true : false; $template->assign_vars(array('MODE' => $mode)); switch ($mode) { case 'backup': switch ($action) { case 'download': $type = request_var('type', ''); $table = request_var('table', array('')); $format = request_var('method', ''); $where = request_var('where', ''); $store = $download = $structure = $schema_data = false; if ($where == 'store_and_download' || $where == 'store') { $store = true; } if ($where == 'store_and_download' || $where == 'download') { $download = true; } if ($type == 'full' || $type == 'structure') { $structure = true; } if ($type == 'full' || $type == 'data') { $schema_data = true; } @set_time_limit(1200); $filename = time(); // We set up the info needed for our on-the-fly creation :D switch ($format) { case 'text': $ext = '.sql'; $open = 'fopen'; $write = 'fwrite'; $close = 'fclose'; $oper = ''; $mimetype = 'text/x-sql'; break; case 'bzip2': $ext = '.sql.bz2'; $open = 'bzopen'; $write = 'bzwrite'; $close = 'bzclose'; $oper = 'bzcompress'; $mimetype = 'application/x-bzip2'; break; case 'gzip': $ext = '.sql.gz'; $open = 'gzopen'; $write = 'gzwrite'; $close = 'gzclose'; $oper = 'gzencode'; $mimetype = 'application/x-gzip'; break; } // We write the file to "store" first (and then compress the file) to not use too much // memory. The server process can be easily killed by storing too much data at once. if ($store == true) { $file = $phpbb_root_path . 'store/' . $filename . $ext; $fp = $open($file, 'w'); if (!$fp) { trigger_error('Unable to write temporary file to storage folder'); } } if ($download == true) { $name = $filename . $ext; header('Pragma: no-cache'); header("Content-Type: {$mimetype}; name=\"{$name}\""); header("Content-disposition: attachment; filename={$name}"); } // All of the generated queries go here $sql_data = ''; $sql_data .= "#\n"; $sql_data .= "# phpBB Backup Script\n"; $sql_data .= "# Dump of tables for {$table_prefix}\n"; $sql_data .= "# DATE : " . gmdate("d-m-Y H:i:s", $filename) . " GMT\n"; $sql_data .= "#\n"; switch (SQL_LAYER) { case 'sqlite': $sql_data .= "BEGIN TRANSACTION;\n"; break; case 'postgres': $sql_data .= "BEGIN;\n"; break; case 'mssql': case 'mssql_odbc': $sql_data .= "BEGIN TRANSACTION\nGO\n"; break; } foreach ($table as $table_name) { // Get the table structure if ($structure) { switch (SQL_LAYER) { case 'mysqli': case 'mysql4': case 'mysql': case 'sqlite': $sql_data .= '# Table: ' . $table_name . "\n"; $sql_data .= "DROP TABLE IF EXISTS {$table_name};\n"; break; case 'oracle': $sql_data .= '# Table: ' . $table_name . "\n"; $sql_data .= "DROP TABLE {$table_name};\n\\\n"; break; case 'postgres': case 'firebird': $sql_data .= '# Table: ' . $table_name . "\n"; $sql_data .= "DROP TABLE {$table_name};\n"; break; case 'mssql': case 'mssql_odbc': $sql_data .= '# Table: ' . $table_name . "\n"; $sql_data .= "IF OBJECT_ID(N'{$table_name}', N'U') IS NOT NULL\n"; $sql_data .= "DROP TABLE {$table_name};\nGO\n"; break; } $sql_data .= $this->get_table_structure($table_name); } // Now write the data for the first time. :) if ($store == true) { $write($fp, $sql_data); } if ($download == true) { if (!empty($oper)) { echo $oper($sql_data); } else { echo $sql_data; } } $sql_data = ''; // Data if ($schema_data) { $sql_data .= "\n"; switch (SQL_LAYER) { case 'mysqli': $sql = "SELECT * FROM {$table_name}"; $result = mysqli_query($db->db_connect_id, $sql, MYSQLI_USE_RESULT); if ($result != false) { $fields_cnt = mysqli_num_fields($result); // Get field information $field = mysqli_fetch_fields($result); $field_set = array(); for ($j = 0; $j < $fields_cnt; $j++) { $field_set[$j] = $field[$j]->name; } $search = array('\\', "'", "", "\n", "\r", ""); $replace = array('\\\\\\\\', "''", '\\0', '\\n', '\\r', '\\Z'); $fields = implode(', ', $field_set); $values = array(); $schema_insert = 'INSERT INTO ' . $table_name . ' (' . $fields . ') VALUES ('; while ($row = mysqli_fetch_row($result)) { for ($j = 0; $j < $fields_cnt; $j++) { if (!isset($row[$j]) || is_null($row[$j])) { $values[$j] = 'NULL'; } else { if ($field[$j]->flags & 32768 && !($field[$j]->flags & 1024)) { $values[$j] = $row[$j]; } else { $values[$j] = "'" . str_replace($search, $replace, $row[$j]) . "'"; } } } $sql_data .= $schema_insert . implode(', ', $values) . ");\n"; if ($store == true) { $write($fp, $sql_data); } if ($download == true) { if (!empty($oper)) { echo $oper($sql_data); } else { echo $sql_data; } } $sql_data = ''; $values = array(); } mysqli_free_result($result); } break; case 'mysql4': case 'mysql': $sql = "SELECT * FROM {$table_name}"; $result = mysql_unbuffered_query($sql, $db->db_connect_id); if ($result != false) { $fields_cnt = mysql_num_fields($result); // Get field information $field = array(); for ($i = 0; $i < $fields_cnt; $i++) { $field[$i] = mysql_fetch_field($result, $i); } $field_set = array(); for ($j = 0; $j < $fields_cnt; $j++) { $field_set[$j] = $field[$j]->name; } $search = array('\\', "'", "", "\n", "\r", ""); $replace = array('\\\\\\\\', "''", '\\0', '\\n', '\\r', '\\Z'); $fields = implode(', ', $field_set); $schema_insert = 'INSERT INTO ' . $table_name . ' (' . $fields . ') VALUES ('; while ($row = mysql_fetch_row($result)) { $values = array(); for ($j = 0; $j < $fields_cnt; $j++) { if (!isset($row[$j]) || is_null($row[$j])) { $values[$j] = 'NULL'; } else { if ($field[$j]->numeric && $field[$j]->type !== 'timestamp') { $values[$j] = $row[$j]; } else { $values[$j] = "'" . str_replace($search, $replace, $row[$j]) . "'"; } } } $sql_data .= $schema_insert . implode(', ', $values) . ");\n"; if ($store == true) { $write($fp, $sql_data); } if ($download == true) { if (!empty($oper)) { echo $oper($sql_data); } else { echo $sql_data; } } $sql_data = ''; } mysql_free_result($result); } break; case 'sqlite': $col_types = sqlite_fetch_column_types($table_name, $db->db_connect_id); $sql = "SELECT * FROM {$table_name}"; $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) { $names = $data = array(); foreach ($row as $row_name => $row_data) { $names[] = $row_name; // Figure out what this data is, escape it properly if (is_null($row_data)) { $row_data = 'NULL'; } else { if ($row_data == '') { $row_data = "''"; } else { if (strpos($col_types[$row_name], 'text') !== false || strpos($col_types[$row_name], 'char') !== false) { $row_data = "'" . $row_data . "'"; } } } $data[] = $row_data; } $sql_data .= 'INSERT INTO ' . $table_name . ' (' . implode(', ', $names) . ') VALUES (' . implode(', ', $data) . ");\n"; if ($store == true) { $write($fp, $sql_data); } if ($download == true) { if (!empty($oper)) { echo $oper($sql_data); } else { echo $sql_data; } } $sql_data = ''; } $db->sql_freeresult($result); break; case 'postgres': $ary_type = $ary_name = array(); // Grab all of the data from current table. $sql = "SELECT *\n\t\t\t\t\t\t\t\t\t\t\tFROM {$table_name}"; $result = $db->sql_query($sql); $i_num_fields = pg_num_fields($result); $seq = ''; for ($i = 0; $i < $i_num_fields; $i++) { $ary_type[$i] = pg_field_type($result, $i); $ary_name[$i] = pg_field_name($result, $i); $sql = "SELECT pg_get_expr(d.adbin, d.adrelid) as rowdefault\n\t\t\t\t\t\t\t\t\t\t\t\tFROM pg_attrdef d, pg_class c\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE (c.relname = '{$table_name}')\n\t\t\t\t\t\t\t\t\t\t\t\t\tAND (c.oid = d.adrelid)\n\t\t\t\t\t\t\t\t\t\t\t\t\tAND d.adnum = " . strval($i + 1); $result2 = $db->sql_query($sql); if ($row = $db->sql_fetchrow($result2)) { // Determine if we must reset the sequences if (strpos($row['rowdefault'], 'nextval(\'') === 0) { $seq .= "SELECT SETVAL('{$table_name}_seq',(select case when max({$ary_name[$i]})>0 then max({$ary_name[$i]})+1 else 1 end from {$table_name}));\n"; } } } while ($row = $db->sql_fetchrow($result)) { $schema_vals = $schema_fields = array(); // Build the SQL statement to recreate the data. for ($i = 0; $i < $i_num_fields; $i++) { $str_val = $row[$ary_name[$i]]; if (preg_match('#char|text|bool#i', $ary_type[$i])) { $str_quote = "'"; $str_empty = ''; $str_val = addslashes($str_val); } else { if (preg_match('#date|timestamp#i', $ary_type[$i])) { if (empty($str_val)) { $str_quote = ''; } else { $str_quote = "'"; } } else { $str_quote = ''; $str_empty = 'NULL'; } } if (empty($str_val) && $str_val !== '0') { $str_val = $str_empty; } $schema_vals[$i] = $str_quote . $str_val . $str_quote; $schema_fields[$i] = $ary_name[$i]; } // Take the ordered fields and their associated data and build it // into a valid sql statement to recreate that field in the data. $sql_data .= "INSERT INTO {$table_name} (" . implode(', ', $schema_fields) . ') VALUES(' . implode(', ', $schema_vals) . ");\n"; if ($store == true) { $write($fp, $sql_data); } if ($download == true) { if (!empty($oper)) { echo $oper($sql_data); } else { echo $sql_data; } } $sql_data = ''; } $db->sql_freeresult($result); // Write out the sequence statements if ($store == true) { $write($fp, $seq); } if ($download == true) { if (!empty($oper)) { echo $oper($seq); } else { echo $seq; } } $seq = ''; break; case 'mssql_odbc': $ary_type = $ary_name = array(); $ident_set = false; // Grab all of the data from current table. $sql = "SELECT *\n\t\t\t\t\t\t\t\t\t\t\tFROM {$table_name}"; $result = $db->sql_query($sql); $retrieved_data = odbc_num_rows($result); if ($retrieved_data) { $sql = "SELECT 1 as has_identity\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM INFORMATION_SCHEMA.COLUMNS\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE COLUMNPROPERTY(object_id('{$table_name}'), COLUMN_NAME, 'IsIdentity') = 1"; $result2 = $db->sql_query($sql); $row2 = $db->sql_fetchrow($result2); if (!empty($row2['has_identity'])) { $sql_data .= "\nSET IDENTITY_INSERT {$table_name} ON\nGO\n"; $ident_set = true; } $db->sql_freeresult($result2); } $i_num_fields = odbc_num_fields($result); for ($i = 0; $i < $i_num_fields; $i++) { $ary_type[$i] = odbc_field_type($result, $i); $ary_name[$i] = odbc_field_name($result, $i); } while ($row = $db->sql_fetchrow($result)) { $schema_vals = $schema_fields = array(); // Build the SQL statement to recreate the data. for ($i = 0; $i < $i_num_fields; $i++) { $str_val = $row[$ary_name[$i]]; if (preg_match('#char|text|bool#i', $ary_type[$i])) { $str_quote = "'"; $str_empty = ''; $str_val = addslashes($str_val); } else { if (preg_match('#date|timestamp#i', $ary_type[$i])) { if (empty($str_val)) { $str_quote = ''; } else { $str_quote = "'"; } } else { $str_quote = ''; $str_empty = 'NULL'; } } if (empty($str_val) && $str_val !== '0' && !(is_int($str_val) || is_float($str_val))) { $str_val = $str_empty; } $schema_vals[$i] = $str_quote . $str_val . $str_quote; $schema_fields[$i] = $ary_name[$i]; } // Take the ordered fields and their associated data and build it // into a valid sql statement to recreate that field in the data. $sql_data .= "INSERT INTO {$table_name} (" . implode(', ', $schema_fields) . ') VALUES(' . implode(', ', $schema_vals) . ");\n"; if ($store == true) { $write($fp, $sql_data); } if ($download == true) { if (!empty($oper)) { echo $oper($sql_data); } else { echo $sql_data; } } $sql_data = ''; } $db->sql_freeresult($result); if ($retrieved_data) { $sql_data = "\nGO\n"; if ($ident_set) { $sql_data .= "\nSET IDENTITY_INSERT {$table_name} OFF\nGO\n"; } } break; case 'mssql': $ary_type = $ary_name = array(); $ident_set = false; // Grab all of the data from current table. $sql = "SELECT *\n\t\t\t\t\t\t\t\t\t\t\tFROM {$table_name}"; $result = $db->sql_query($sql); $retrieved_data = mssql_num_rows($result); $i_num_fields = mssql_num_fields($result); for ($i = 0; $i < $i_num_fields; $i++) { $ary_type[$i] = mssql_field_type($result, $i); $ary_name[$i] = mssql_field_name($result, $i); } if ($retrieved_data) { $sql = "SELECT 1 as has_identity\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM INFORMATION_SCHEMA.COLUMNS\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE COLUMNPROPERTY(object_id('{$table_name}'), COLUMN_NAME, 'IsIdentity') = 1"; $result2 = $db->sql_query($sql); $row2 = $db->sql_fetchrow($result2); if (!empty($row2['has_identity'])) { $sql_data .= "\nSET IDENTITY_INSERT {$table_name} ON\nGO\n"; $ident_set = true; } $db->sql_freeresult($result2); } while ($row = $db->sql_fetchrow($result)) { $schema_vals = $schema_fields = array(); // Build the SQL statement to recreate the data. for ($i = 0; $i < $i_num_fields; $i++) { $str_val = $row[$ary_name[$i]]; if (preg_match('#char|text|bool#i', $ary_type[$i])) { $str_quote = "'"; $str_empty = ''; $str_val = addslashes($str_val); } else { if (preg_match('#date|timestamp#i', $ary_type[$i])) { if (empty($str_val)) { $str_quote = ''; } else { $str_quote = "'"; } } else { $str_quote = ''; $str_empty = 'NULL'; } } if (empty($str_val) && $str_val !== '0' && !(is_int($str_val) || is_float($str_val))) { $str_val = $str_empty; } $schema_vals[$i] = $str_quote . $str_val . $str_quote; $schema_fields[$i] = $ary_name[$i]; } // Take the ordered fields and their associated data and build it // into a valid sql statement to recreate that field in the data. $sql_data .= "INSERT INTO {$table_name} (" . implode(', ', $schema_fields) . ') VALUES(' . implode(', ', $schema_vals) . ");\n"; if ($store == true) { $write($fp, $sql_data); } if ($download == true) { if (!empty($oper)) { echo $oper($sql_data); } else { echo $sql_data; } } $sql_data = ''; } $db->sql_freeresult($result); if ($retrieved_data) { $sql_data = "\nGO\n"; if ($ident_set) { $sql_data .= "\nSET IDENTITY_INSERT {$table_name} OFF\nGO\n"; } } break; case 'firebird': $ary_type = $ary_name = array(); // Grab all of the data from current table. $sql = "SELECT *\n\t\t\t\t\t\t\t\t\t\t\tFROM {$table_name}"; $result = $db->sql_query($sql); $i_num_fields = ibase_num_fields($result); for ($i = 0; $i < $i_num_fields; $i++) { $info = ibase_field_info($result, $i); $ary_type[$i] = $info['type']; $ary_name[$i] = $info['name']; } while ($row = $db->sql_fetchrow($result)) { $schema_vals = $schema_fields = array(); // Build the SQL statement to recreate the data. for ($i = 0; $i < $i_num_fields; $i++) { $str_val = $row[strtolower($ary_name[$i])]; if (preg_match('#char|text|bool#i', $ary_type[$i])) { $str_quote = "'"; $str_empty = ''; $str_val = addslashes($str_val); } else { if (preg_match('#date|timestamp#i', $ary_type[$i])) { if (empty($str_val)) { $str_quote = ''; } else { $str_quote = "'"; } } else { $str_quote = ''; $str_empty = 'NULL'; } } if (empty($str_val) && $str_val !== '0') { $str_val = $str_empty; } $schema_vals[$i] = $str_quote . $str_val . $str_quote; $schema_fields[$i] = "'" . $ary_name[$i] . "'"; } // Take the ordered fields and their associated data and build it // into a valid sql statement to recreate that field in the data. $sql_data .= "INSERT INTO {$table_name} (" . implode(', ', $schema_fields) . ') VALUES(' . implode(', ', $schema_vals) . ");\n"; if ($store == true) { $write($fp, $sql_data); } if ($download == true) { if (!empty($oper)) { echo $oper($sql_data); } else { echo $sql_data; } } $sql_data = ''; } $db->sql_freeresult($result); break; case 'oracle': $ary_type = $ary_name = array(); // Grab all of the data from current table. $sql = "SELECT *\n\t\t\t\t\t\t\t\t\t\t\tFROM {$table_name}"; $result = $db->sql_query($sql); $i_num_fields = ocinumcols($result); for ($i = 0; $i < $i_num_fields; $i++) { $ary_type[$i] = ocicolumntype($result, $i); $ary_name[$i] = ocicolumnname($result, $i); } while ($row = $db->sql_fetchrow($result)) { $schema_vals = $schema_fields = array(); // Build the SQL statement to recreate the data. for ($i = 0; $i < $i_num_fields; $i++) { $str_val = $row[$ary_name[$i]]; if (preg_match('#char|text|bool#i', $ary_type[$i])) { $str_quote = "'"; $str_empty = ''; $str_val = addslashes($str_val); } else { if (preg_match('#date|timestamp#i', $ary_type[$i])) { if (empty($str_val)) { $str_quote = ''; } else { $str_quote = "'"; } } else { $str_quote = ''; $str_empty = 'NULL'; } } if (empty($str_val) && $str_val !== '0') { $str_val = $str_empty; } $schema_vals[$i] = $str_quote . $str_val . $str_quote; $schema_fields[$i] = '"' . $ary_name[$i] . "'"; } // Take the ordered fields and their associated data and build it // into a valid sql statement to recreate that field in the data. $sql_data .= "INSERT INTO {$table_name} (" . implode(', ', $schema_fields) . ') VALUES(' . implode(', ', $schema_vals) . ");\n"; if ($store == true) { $write($fp, $sql_data); } if ($download == true) { if (!empty($oper)) { echo $oper($sql_data); } else { echo $sql_data; } } $sql_data = ''; } $db->sql_freeresult($result); break; } } } switch (SQL_LAYER) { case 'sqlite': case 'postgres': $sql_data .= "COMMIT;"; break; case 'mssql': case 'mssql_odbc': $sql_data .= "COMMIT\nGO"; break; } if ($store == true) { $write($fp, $sql_data); $close($fp); } if ($download == true) { if (!empty($oper)) { echo $oper($sql_data); } else { echo $sql_data; } exit; } unset($sql_data); add_log('admin', 'LOG_DB_BACKUP'); trigger_error($user->lang['BACKUP_SUCCESS']); break; default: $tables = array(); switch (SQL_LAYER) { case 'sqlite': $sql = "SELECT name\n\t\t\t\t\t\t\t\t\tFROM sqlite_master\n\t\t\t\t\t\t\t\t\tWHERE type='table'\n\t\t\t\t\t\t\t\t\tORDER BY name"; $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) { if (strpos($row['name'], $table_prefix) === 0) { $tables[] = $row['name']; } } $db->sql_freeresult($result); break; case 'mysqli': case 'mysql4': case 'mysql': $sql = "SHOW TABLES\n\t\t\t\t\t\t\t\t\tLIKE '{$table_prefix}%'"; $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) { $tables[] = current($row); } $db->sql_freeresult($result); break; case 'postgres': $sql = "SELECT relname\n\t\t\t\t\t\t\t\t\tFROM pg_stat_user_tables\n\t\t\t\t\t\t\t\t\tORDER BY relname;"; $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) { if (strpos($row['relname'], $table_prefix) === 0) { $tables[] = $row['relname']; } } $db->sql_freeresult($result); break; case 'mssql': case 'mssql_odbc': $sql = "SELECT TABLE_NAME\n\t\t\t\t\t\t\t\t\tFROM INFORMATION_SCHEMA.TABLES\n\t\t\t\t\t\t\t\t\tWHERE TABLE_TYPE = 'BASE TABLE'\n\t\t\t\t\t\t\t\t\tORDER BY TABLE_NAME"; $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) { if (strpos($row['TABLE_NAME'], $table_prefix) === 0) { $tables[] = $row['TABLE_NAME']; } } $db->sql_freeresult($result); break; case 'firebird': $sql = 'SELECT RDB$RELATION_NAME as TABLE_NAME FROM RDB$RELATIONS WHERE RDB$SYSTEM_FLAG=0 AND RDB$VIEW_BLR IS NULL'; $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) { if (stripos($row['table_name'], $table_prefix) === 0) { $tables[] = $row['table_name']; } } $db->sql_freeresult($result); break; case 'oracle': $sql = 'SELECT TNAME as table_name FROM TAB'; $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) { if (stripos($row['table_name'], $table_prefix) === 0) { $tables[] = $row['table_name']; } } $db->sql_freeresult($result); break; } foreach ($tables as $table) { $template->assign_block_vars('tables', array('TABLE' => $table)); } $template->assign_vars(array('U_ACTION' => $this->u_action . '&action=download')); $available_methods = array('gzip' => 'zlib', 'bzip2' => 'bz2'); foreach ($available_methods as $type => $module) { if (!@extension_loaded($module)) { continue; } $template->assign_block_vars('methods', array('TYPE' => $type)); } $template->assign_block_vars('methods', array('TYPE' => 'text')); break; } break; case 'restore': switch ($action) { case 'submit': $delete = request_var('delete', ''); $file = request_var('file', ''); preg_match('#^(\\d{10})\\.(sql(?:\\.(?:gz|bz2))?)$#', $file, $matches); $file_name = $phpbb_root_path . 'store/' . $matches[0]; if (!(file_exists($file_name) && is_readable($file_name))) { trigger_error($user->lang['BACKUP_INVALID']); } if ($delete) { unlink($file_name); trigger_error($user->lang['BACKUP_DELETE']); } $data = file_get_contents($file_name); switch ($matches[2]) { case 'sql.bz2': $data = bzdecompress($data); break; case 'sql.gz': $data = gzinflate(substr($data, 10)); break; } $download = request_var('download', ''); if ($download) { $name = $matches[0]; switch ($matches[2]) { case 'sql': $mimetype = 'text/x-sql'; break; case 'sql.bz2': $mimetype = 'application/x-bzip2'; break; case 'sql.gz': $mimetype = 'application/x-gzip'; break; } header('Pragma: no-cache'); header("Content-Type: {$mimetype}; name=\"{$name}\""); header("Content-disposition: attachment; filename={$name}"); echo $data; die; } if (!empty($data)) { // Strip out sql comments... remove_remarks($data); switch (SQL_LAYER) { case 'firebird': $delim = ';;'; break; case 'mysql': case 'mysql4': case 'mysqli': case 'sqlite': case 'postgres': $delim = ';'; break; case 'oracle': $delim = '/'; break; case 'mssql': case 'mssql-odbc': $delim = 'GO'; break; } $pieces = split_sql_file($data, $delim); $sql_count = count($pieces); for ($i = 0; $i < $sql_count; $i++) { $sql = trim($pieces[$i]); if (!empty($sql) && $sql[0] != '#') { $db->sql_query($sql); } } } add_log('admin', 'LOG_DB_RESTORE'); trigger_error($user->lang['RESTORE_SUCCESS']); break; default: $selected = false; $methods = array('sql'); $available_methods = array('sql.gz' => 'zlib', 'sql.bz2' => 'bz2'); foreach ($available_methods as $type => $module) { if (!@extension_loaded($module)) { continue; } $methods[] = $type; } $dir = $phpbb_root_path . 'store/'; $dh = opendir($dir); while (($file = readdir($dh)) !== false) { if (preg_match('#^(\\d{10})\\.(sql(?:\\.(?:gz|bz2))?)$#', $file, $matches)) { $supported = in_array($matches[2], $methods); if ($supported == 'true') { $template->assign_block_vars('files', array('FILE' => $file, 'NAME' => gmdate("d-m-Y H:i:s", $matches[1]), 'SUPPORTED' => $supported)); $selected = true; } } } closedir($dh); if ($selected === true) { $template->assign_var('EXISTS', true); } $template->assign_vars(array('U_ACTION' => $this->u_action . '&action=submit')); break; } break; } }