I'm actually on a new website project with Symfony 5. As I have to access an historical database where all of our products details are stored, I have to use AdoDb so I can use odbc driver (As doctrine doesn't allow to use it).
When I try selecting a field with accent, the query failed and throw a S0022 error code.
Full error message is :"[Simba][SQLEngine] (31750) Column not found: ar1_h?�ndlerpreis"
See thatar1_h?�ndlerpreis
contain a "?�" character which seems to be the problem.
Here is my code for the query.
Look at thear1_h?�ndlerpreis
which is present into the "Prepare" function first parameter.
$strIn = "";// Adodb odbc doesn't support IN(?) with an array parameter (string are quoted automatically)
foreach ($array_article_number as $number) {
$strIn .= "?,";
}
$strIn = substr($strIn, 0, -1); // Last one doesn't need ',' characters as it's the last one.
$query = $this->_db->Prepare("SELECT ar1_artikelnummer, ar2_mwststeuerschl, ar1_bestand, ar1_klassifizierung, ar1_bezeichnung3, ar1_apothekenpreis, ar1_h?�ndlerpreis AS apreis FROM artikeldaten WHERE ar1_artikelnummer IN(" . $strIn . ")");
$datas = $this->_db->Execute($query, $array_article_number);
I have try to edit the field ar1_h?�ndlerpreis in my query string as :
I have also tried to change encoding character for accessing database :
$this->_db->setConnectionParameter('CharacterSet','UTF-8');
(As explain here)But nothing do...
I provide you more code to see if i did something wrong.
This is my ADOdbConnection Service which i use when i need to connect to the historical database. Most of the time, i use Doctrine for the website specific database.
<?php
namespace App\Services;
use ADOConnection;
class ADOdbConnection
{
private ADOConnection $_db;
/**
* AdodbConnection constructor.
* @param $host
* @param $user
* @param $pwd
* @param $db_name
*/
public function __construct($host, $user, $pwd, $db_name)
{
$this->_db = ADONewConnection('odbc');
$this->_db->setConnectionParameter('CharacterSet','UTF-8');
$this->_db->connect('DRIVER={CONZEPT 16 ODBC-Treiber (64 bit)};SERVER='.$host.';DB='.$db_name.';', $user, $pwd, $db_name);
}
public function getDb() : ADOConnection{
return $this->_db;
}
}
It work as it should, doing a connection to the database.
Then, this is my DAL for the product (Article) table :
<?php
namespace App\Services\DAL_w2;
use ADOConnection;
use App\Services\ADOdbConnection;
class ArticleDal
{
private ADOConnection $_db;
public function __construct(ADOdbConnection $ADOdbConnection)
{
$this->_db = $ADOdbConnection->getDb();
$this->_db->setCharset('UTF-8');
$this->_db->SetFetchMode(ADODB_FETCH_ASSOC);
}
/**
* Join w2 database for every article number given
* @param array $array_article_number An array of article number.
* @return array|null An array of article from w2 article's table or null
*/
public function getDataForListArticleFromArticleNumber(array $array_article_number): ?array
{
$res = null;
if (count($array_article_number)) {
$strIn = "";// Adodb doesn't support IN(?) with an array parameter (string are quoted automatically)
foreach ($array_article_number as $number) {
$strIn .= "?,";
}
$strIn = substr($strIn, 0, -1); // Last one doesn't need ',' characters as it's the last one.
$query = $this->_db->Prepare("SELECT ar1_artikelnummer, ar2_mwststeuerschl, ar1_bestand, ar1_klassifizierung, ar1_bezeichnung3, ar1_apothekenpreis, ar1_h?�ndlerpreis + 0 AS apreis FROM artikeldaten WHERE ar1_artikelnummer IN(" . $strIn . ")");
dump($query);
$datas = $this->_db->Execute($query, $array_article_number);
dump($this->_db->_errorCode);
dump($this->_db->_errorMsg);
dump($datas);
die();
// Assign article number as array key
$res = array();
foreach ($datas as $article_data) {
$res[$article_data['ar1_artikelnummer']] = $article_data;
}
}
$res = $res ?: null; // If we don't have any data
return $res;
}
}
This code display the following result :
ArticleDal.php on line 37: "SELECT ar1_artikelnummer, ar2_mwststeuerschl, ar1_bestand, ar1_klassifizierung, ar1_bezeichnung3, ar1_apothekenpreis, ar1_h?�ndlerpreis + 0 AS apreis FROM artikeldaten WHERE ar1_artikelnummer IN(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) "
ArticleDal.php on line 39: "S0022"
ArticleDal.php on line 40: "[Simba][SQLEngine] (31750) Column not found: ar1_h?�ndlerpreis"
ArticleDal.php on line 41: false
And there is an example of what return aSELECT *
(I truncate it because it have 360 columns...) :
0 => array:360 [?�?
"ar1_artikelnummer" => "0002446"
...
"ar1_preislistenartik" => "0"
"ar1_preislistenpreis" => "0"
b"ar1_preislisten?�nder" => null
"ar1_preislistenprneu" => "0"
"ar1_preislistenneuab" => null
"ar1_collianz" => "1"
"ar1_tiefstpreis_brut" => "0"
"ar1_ean_nummer" => ""
"ar1_faxpreislistenar" => "0"
"ar1_inetrnetartikel" => "0"
"ar1_unterwarengruppe" => "1"
"ar1_verbot_in_stl" => "1"
"ar1_apothekenpreis" => "5.82"
b"ar1_h?�ndlerpreis" => "3.77"
...
It's weird those little 'b' in front of field with accent (For ar1_h?�ndlerpreis and ar1_preislisten?�nder). dump() is the debug function from Symfony framework.
If i use var_dump instead of dump, i got this result :
["ar1_h???ndlerpreis"]=> string(4) "3.77"
Thank you.
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Find the answer in similar questions on our website.
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.
PHP (from the English Hypertext Preprocessor - hypertext preprocessor) is a scripting programming language for developing web applications. Supported by most hosting providers, it is one of the most popular tools for creating dynamic websites.
The PHP scripting language has gained wide popularity due to its processing speed, simplicity, cross-platform, functionality and distribution of source codes under its own license.
https://www.php.net/
Symfony compares favorably with other PHP frameworks in terms of reliability and maturity. This framework appeared a long time ago, in 2005, that is, it has existed much longer than most of the other tools we are considering. It is popular for its web standards compliance and PHP design patterns.
https://symfony.com/
Welcome to the Q&A site for web developers. Here you can ask a question about the problem you are facing and get answers from other experts. We have created a user-friendly interface so that you can quickly and free of charge ask a question about a web programming problem. We also invite other experts to join our community and help other members who ask questions. In addition, you can use our search for questions with a solution.
Ask about the real problem you are facing. Describe in detail what you are doing and what you want to achieve.
Our goal is to create a strong community in which everyone will support each other. If you find a question and know the answer to it, help others with your knowledge.