openbiz, postgresql and backticks

[Victor Seva Lopez]

Hi,

does anyone use openbiz with postgresql? I think this is not possible because of the use of backticks in the querys produced by BizDataSql class.

svn/trunk/cubi/openbiz/bin/data/BizDataSql.php

/**

* Add main table in the sql statement T0 alias

*

* @param string $mainTable main table name

* @return void

**/

public function addMainTable($mainTable)

{

$this->_mainTable = "$mainTable";

$this->_tableJoins = " `$mainTable` T0 ";

} This produces sql querys like: "SELECT COUNT(*) FROM `help` T0 INNER JOIN `help_category` T1 ON T1.id = T0.category_id WHERE T0.category_id=''" that do not work with postgresql.

[Jixian Wang]

Hi 

We are majorly use Openbiz to connect Mysql

can u post some error info reported by postgresql

Best regards!

Jixian Wang
jix...@openbiz.me 

--------------------------------
China
北京博智未来科技有限公司
+86 186 1185 0602

USA
Openbiz LLC
+1 (323) 522-5456

[Victor Seva Lopez]

El 16 de abril de 2012 10:49, Jixian Wang <jix...@openbiz.me> escribió:
> Hi
>
> We are majorly use Openbiz to connect Mysql
> can u post some error info reported by postgresql

My errors are localized I am afraid. But the error is about the use of backtics.

Attached patch stopped the errors about this particular issue and I
think querys are ok with Mysql too.

> Best regards!

That was a fast response :)

[Jixian Wang]

I got it your patch file,

Would u like to add me on Skype, 

My skype is :   jixianwang

welcome to openbiz team

Best regards!

Jixian Wang
jix...@openbiz.me 

--------------------------------
China
北京博智未来科技有限公司
+86 186 1185 0602

USA
Openbiz LLC
+1 (323) 522-5456

<backticks.patch>

[Jixian Wang]

For some compatible and security reason , we cannot just ignore the back-quote ,

we need to put some test condition before it .

if current database is type of mysql , then quote it,

for now , we dont have many users using PostgreSQL

I would like to discuss the solution with you on skype. and soon we will fix it in next release

Best regards!

Jixian Wang
jix...@openbiz.me 

--------------------------------
China
北京博智未来科技有限公司
+86 186 1185 0602

USA
Openbiz LLC
+1 (323) 522-5456

On 2012-4-16, at 下午5:11, Victor Seva Lopez wrote:

<backticks.patch>

[Victor Seva Lopez]

El día 16 de abril de 2012 11:41, Jixian Wang <jix...@openbiz.me> escribió:
> For some compatible and security reason , we cannot just ignore the
> back-quote ,
> we need to put some test condition before it .
> if current database is type of mysql , then quote it,

Ok, you know your beast. :)

> for now , we dont have many users using PostgreSQL
>
> I would like to discuss the solution with you on skype. and soon we will fix
> it in next release

I'm afraid I am just a sysadmin trying to get running siremis[0] in
order to evaluate it. I don't think I can help you, I have no
experience in your framework at all.

[0] http://siremis.asipto.com/

[Jixian Wang]

Hey, i wonder that you guys already build applications based on Openbiz Cubi 1.0

http://kb.asipto.com/siremis:install32x:screenshots

Dont be afraid about it , lets just meet each other !

We are happy to meet openbiz users

Best regards!

Jixian Wang
jix...@openbiz.me 

--------------------------------
China
北京博智未来科技有限公司
+86 186 1185 0602

USA
Openbiz LLC
+1 (323) 522-5456

[Agus Suhartono]

Jixian and Victor

I once had a project using openbiz and Postgre
Yes, DataObject can't work correctly on Postgre,
because DataObject use backticks character (`) to generate SQL

I ever fix this problem and commit to repository,
but then returned again to use backticks.
maybe coused error on other system :D


Agus Suhartono
Binexa Internet, Internet Business Solution
co-Creating Better Life in The World Through Technology
www.binexa.com

[rhossis]

Hi, How about to extend BizDataObj (or add to the core) with a class that has methods that return
the format of query if needed e.g. ANSI class. But for default user can decide to just load BizDataObj
if on MySQL?

[Agus Suhartono]

there are tree file you must change

BizDataObj.php
BizDataObj_SQLHelper.php
BizDataSql.php

maybe you can create BACKTICKS constant,
and use it to substitute [ ` ] character

next time you can change value of BACKTICKS constant


Agus Suhartono
Binexa Internet
co-Creating Better Life Through Technology
www.binexa.com

[Jixian Wang]

Hi Augs , why dont we made a support patch on our source code level,
we can put it in next release

before generate sql , lets do a condition test. if Bizsystem::getDBConnection() type is PostgreSQL then without the [`]
here i dont have a test environment, if u can fix it would be great!

btw , your can found ur name on this download resource now
http://www.codefans.net/soft/17757.shtml

Jixian

[Victor Seva Lopez]

Hi,

El sábado, 28 de abril de 2012 03:40:49 UTC+2, Jixian Wang escribió:

Hi Augs , why dont we made a support patch on our source code level,
we can put it in next release

before generate sql , lets do a condition test. if Bizsystem::getDBConnection() type is PostgreSQL then without the [`]
here i dont have a test environment, if u can fix it would be great!

may be this is a start.

[0] http://code.google.com/p/openbiz-cubi/issues/detail?id=25#c1 

[Agus Suhartono]

hi victor, thank for your posting
yes this method can work, you only need to test it
i recomended for temporary only, because hard code

i plan to use variable for flexibility

$useBackticks = yes or true depend by DB type but can change by user
$backticksChar = "`" or "" (empty string)

and every backticks character replaced by $backticksChar

i will be commit this code ASAP

agus suhartono

[Agus Suhartono]

Hi Victor,
i read your file "backtick_fix.patch",
and this is good solution
why you not test it and commit to repo?

thank you

agus suhartono

[Victor Seva Lopez]

Hi,
I'm afraid nor I can test or commit this. I'm using an older version of openbiz. AFAIK this works on my system but I only used postgresql.

[Arnold Chitwa]

Zend natively allows for this backticks according to the PDO adapter in use so, i would advice Agus and the  team to allow abstraction earlied inthe code say when we calling  Zend_Db::factory($configs), and then from there the object created can be furnished with the decorations that allow other users to survive the shortcomings. for now i will advice getting the Zend Reference manual

regards

Chitwa

[Openbiz.me]

Agree. This sounds the right solution.

Sent from my iPhone

[Agus Suhartono]

thank you Arnold for your advice,
use adapter is better sollution instead use if statement

can you give sample, how Zend_Db handle backtick?
maybe use SQL generator?

$select = $db->select()
->from()
->joinLeft()
->where()
->where()
->where(')


agus suhartono

[Agus Suhartono]

ok
we can use **Zend_Db_Adapter->quoteIdentifier()**

example :

// we might have a table name that is an SQL reserved word
$tableName = $db->quoteIdentifier("order");

$sql = "SELECT * FROM $tableName";

echo $sql
// SELECT * FROM "order"

reference :
http://files.zend.com/help/Zend-Framework/zend.db.html#zend.db.adapter.quoting.quote-identifier

or using *Zend_Db::AUTO_QUOTE_IDENTIFIERS*

http://files.zend.com/help/Zend-Framework/zend.db.html#zend.db.adapter.connecting.parameters.example2

agus suhartono


On Fri, Jul 27, 2012 at 9:42 AM, Agus Suhartono

[Rocky Swen]

Nice solution :)

[Arnold Chitwa]

sharing some zend manual with all you on Google Docs you can download it and read more. I may not be able to write a patch immediately say upto 3rd week of august. for now this pdf manual has been a saviour to me and am sure it will help all of us here at OpenBiz