Gianni Turri wrote:
> Hi all!
>
> In the effort to develop a system capable to connect indifferently to MS
SQL and MySQL I am facing another problem.
>
> Query using concatenated fields are broken using MySQL as database backend.
>
> Note that with MS SQL Server everything works as expected.
>
> Sample code:
>
> select * from MyTable where code1 + code2 = ?vp_code
>
> where code1 and code1 are Char.
>
> in MySql the correct syntax for this query is:
>
> select * from MyTable where concat(code1, code2) = ?vp_code
>
> One solution would be to use this more basic syntax:
>
> select * from MyTable where code1 = ?vp_code1 and code2 = ?vp_code2
>
> but this force me to rewrite too much code.
>
> In conclusion:
> trying to develop a system capable to connect indifferently to MS SQL
and MySQL is becoming more and more complex... may be too much.
>
> My system is as follow:
> Windows XP Pro SP3
> VFP 9 SP1
> MySQL Server 5.1
> MS SQL Server 2008
>
Hi Gianni,
I've developed a solution that works great for my needs. In my n-tier
design, I use a meta-data table that has the SQL defined. I've got a
common SQL column (that works for all backends) but then I've got
"override" columns for that particular flavor of SQL if it's unique.
This approach allows me to switch between VFP and MySQL and other
backends without having to change any of my app code. I just control
all of it from my meta-data. Its structure is as follows:
Field Field Name
Type
Width Dec
Index Collate Nulls
Next Step
1 IID
Integer (AutoInc)
4
No
172 1
2 CVIEW
Character
35
Asc Machine No
3 CSQL
Memo
4
No
4 CSQL_VFP
Memo
4
No
5 CSQL_MYSQL
Memo
4
No
6 CBACKENDTABL..
Character
30
Yes
7 CKEYFIELDLIS..
Character
16
No
8 CCANDIDATEKE..
Character
100
No
9 CUPDATABLEFI..
Memo
4
No
10 CINDEXES
Memo
4
No
11 IUPDATABLE
Integer
4
No
12 TADDED
DateTime
8
No
** Total **
219
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
