Blog
SQL
Articles related to Development in SQL Technologies
OpenSSL loading openssl.cnf
Feb 8th
since the openssl.cnf path is hardcoded in binaries. to alter it on windows set environmental variable (as written):
OPENSSLDIR=C:\PATH\TO\OpenSSL\bin OPENSSL_CONF=C:\PATH\TO\OpenSSL\bin\openssl.cnf
OPENSSL_CONF doesn’t work without OPENSSLDIR even if it is set.
settings these options will allow you to use your custom configuration files when creating certificates.
batch file i use to create certificate
GenCERT.bat [save this file to OpenSSL bin directory]
@echo off md %1 openssl genrsa -des3 -out %1/%1.key 1024 openssl req -new -key %1/%1.key -out %1/%1.csr copy %1/%1.key %1/%1.key.org openssl rsa -in %1/%1.key.org -out %1/%1.key openssl x509 -req -days 365 -in %1/%1.csr -signkey %1/%1.key -out %1/%1.crt openssl x509 -outform der -in %1/%1.crt -out %1/%1.der
Sample
:: Start command line and cd to C:\PATH\TO\OpenSSL\bin :: :: usage: GenCERT.bat domain.com :: GenCERT.bat teknober.com
Batch file I use to create SSL certificate for Web etc server
GenPEM.bat [save this file to OpenSSL bin directory]
@echo off mkdir %1 openssl req -x509 -nodes -days 365 -newkey rsa:1024 -keyout %1/%1.pem -out %1/%1.pem -config "%1/%1.cfg"
Sample
:: Start command line and cd to C:\PATH\TO\OpenSSL\bin :: :: usage: GenPEM.bat domain.com :: GenPEM.bat teknober.com
for sample ssl configuration file see the bottom of the page: http://www.openssl.org/docs/apps/ca.html
PHP PDO using Transactions with TModel::GenerateStatement functions
Sep 9th
Only have an example to show using the TModel::GenerateInsertStatement, TModel::GenerateUpdateStatement, TModel::GenerateReplaceStatement
In database driver settings for mysql innodb engine, mariadb aria engine: SET AUTOCOMMIT=0;
sample:
public function panel_set_category_enabled($id_category) {
/* start a transaction */
$this->model->beginTransaction();
/* set: populate related columns */
$columns = array();
$columns[] = new TModelColumn("enabled", 1, PDO::PARAM_INT);
$columns[] = new TModelColumn("date_update", TDate::getDATE_MYSQL(time()), PDO::PARAM_STR);
/* execute and return result */
$result = TModel::generateUpdateSet("tc_category", $columns, new TModelWhere("id", $id_category, PDO::PARAM_INT, null, "="), true);
if ($result) {
$this->model->commit();
return $result;
} else {
$this->model->rollBack();
return $result;
}
}
related articles on this blog:
PHP PDO Generate Insert Statement:
http://blog.teknober.com/2011/03/06/php-pdo-generate-insert-statement/
PHP PDO Generate Update Statement:
http://blog.teknober.com/2011/03/06/php-pdo-generate-update-statement/
PHP PDO Generate Replace Statement:
http://blog.teknober.com/2011/04/13/php-pdo-generate-replace-statement/
PHP PDO Using reserved words as column names
Jun 25th
Using reserved words as columns names causes problems on insert/update/replace while there is no problem with select.
/* this will not work and will throw no exception */
$result = $model->prepare("update mytable set default = 1");
$result->execute();
/* this will work */
$result = $model->prepare("update mytable set `default` = 1");
$result->execute();
PHP PDO Call MySQL Stored Function
Jun 6th
Calling a MySQL Stored Function from PHP PDO Driver. [ this is not for stored procedure ]
$model = new PDO(/* enter your parameters */);
function getID_ByISO($iso = null) {
$result = $model->prepare("select getID_ByISO(?) as id");
$result->bindParam(1, $iso, PDO::PARAM_STR, 2);
$result->setFetchMode(PDO::FETCH_CLASS, 'stdClass');
$result->execute();
return $result->fetch();
}
PHP function will return stdClass Object where the result will be wrapped into.
$obj = getID_ByISO('hu');
result:
print($obj->id); /* 3 is the id for hu in my database */
in sql statement, “select myfunc(?) as id”, result is named as id to have more generic output name
PHP PDO SPLFileObject Import CSV Files to Database
May 21st
Imports COMMA, TAB, SPACE delimited files to mysql, postgresql, mssql, oracle sql, sqlite etc using PDO. Make sure you have all the columns in text file.
if you are going to import large data into database at once, execute this script from command line
for TModelObject Class and PDO GenerateInsertFunction see:
http://blog.teknober.com/2011/03/06/php-pdo-generate-insert-statement/
Works but the problem with this built-in functions it ignores the cells if they are null. see the replacement function below.
public function import($path) {
$file = new SplFileObject($path);
$file->setFlags(SplFileObject::READ_CSV);
/* comma seperated text file */
$file->setCsvControl("\t");
while ($file->valid()) {
$line = $file->fgetcsv();
if (count($line) == 6) {
list($column_a, $column_b, $column_c, $column_d, $column_e, $column_f) = $line;
$this->model_generate_insert("my_table", array(
new TModelOjbect("column_a", $column_a, PDO::PARAM_INT),
new TModelOjbect("column_b", $column_b, PDO::PARAM_INT),
new TModelOjbect("column_c", $column_c, PDO::PARAM_STR, 10),
new TModelOjbect("column_d", $column_d, PDO::PARAM_STR, 200),
new TModelOjbect("column_e", $column_e, PDO::PARAM_INT),
new TModelOjbect("column_f", $column_f, PDO::PARAM_INT)
), true, false);
}
}
}
fixed version:
public function import($path) {
$file = new SplFileObject($path);
/* set file flags */
$file->setFlags(SplFileObject::DROP_NEW_LINE);
while ($file->valid()) {
/* read line from the file */
$line = $file->fgets();
/* split line values */
$line = explode("\t", $line);
/* check column count */
if (count($line) == 6) {
list($column_a, $column_b, $column_c, $column_d, $column_e, $column_f) = $line;
$this->model_generate_insert("my_table", array(
new TModelOjbect("column_a", $column_a, PDO::PARAM_INT),
new TModelOjbect("column_b", $column_b, PDO::PARAM_INT),
new TModelOjbect("column_c", $column_c, PDO::PARAM_STR, 10),
new TModelOjbect("column_d", $column_d, PDO::PARAM_STR, 200),
new TModelOjbect("column_e", $column_e, PDO::PARAM_INT),
new TModelOjbect("column_f", $column_f, PDO::PARAM_INT)
), true, false);
}
}
}
PHP PDO Generate Replace Statement
Apr 13th
MySQL Replace Manual
http://dev.mysql.com/doc/refman/5.0/en/replace.html
PHP PDO Insert Statement
http://blog.teknober.com/2011/03/06/php-pdo-generate-insert-statement/
PHP PDO Update Statement
http://blog.teknober.com/2011/03/06/php-pdo-generate-update-statement/
Sample:
$model = new PDO(/* enter your parameters */);
$statement = generateReplaceSet("mytable", new TModelColumn("name", "Teknober", PDO::PARAM_STR, 100), null, true);
PDO Statement: Replace
function generateReplaceSet($table, $columns = null, $options = null, $execute = false) {
if (isset($columns) == true) {
/* prepare result */
$result = null;
/* prepare sql */
$sql = "
REPLACE `%s` INTO
%s (%s)
VALUES
(%s);";
/* prepare columns_string */
$columns_string = null;
$values_string = null;
if (is_array($columns) == true) {
/* iterate columns to generate columns_string */
foreach ($columns as $value) {
$columns_string .= "`" . $value->getKey() . "`,";
$values_string .= "?,";
}
/* remove trailing comma */
$columns_string = substr($columns_string, 0, strlen($columns_string) - 1);
$values_string = substr($values_string, 0, strlen($values_string) - 1);
} elseif ($columns instanceof TModelColumn) {
$columns_string = "`" . $columns->getKey() . "`";
$values_string = "?";
} else {
trigger_error("No column is defined in set", E_ERROR);
}
/* prepare sql */
$sql = sprintf($sql, $options, $table, $columns_string, $values_string);
/* prepare statement */
$result = $model->prepare($sql);
/* prepare statement parameter index */
$param_index = 0;
/* bind statement parameters */
if (is_array($columns) == true) {
foreach ($columns as $column) {
$result->bindParam(++$param_index, $column->getValue(), $column->getType(), $column->getLength());
}
} elseif ($columns instanceof TModelColumn) {
$result->bindParam(++$param_index, $columns->getValue(), $columns->getType(), $columns->getLength());
} else {
trigger_error("No column is defined in set", E_ERROR);
}
/* clean up */
unset($table, $columns, $options, $sql, $columns_string, $values_string, $param_index);
/* return PDO Statement */
if ($execute) {
return $result->execute();
} else {
return $result;
}
} else {
trigger_error("No column is defined in arguments");
}
}
TModelColumn Class
class TModelColumn {
public $key = null;
public $value = null;
public $type = null;
public $length = null;
public function __construct($key, $value = null, $type = null, $length = null) {
$this->key = $key;
$this->value = $value;
$this->type = $type;
$this->length = $length;
}
public function getKey() {
return $this->key;
}
public function getValue() {
return $this->value;
}
public function getType() {
return $this->type;
}
public function getLength() {
return $this->length;
}
}
$options,
LinkedIn
Twitter
Skype