BeX5与PostgreSQL兼容性问题

liuhui

系统版本：BeX5 3.8；PostgreSQL 10.4

目前我的情况是系统库基于PostgreSQL，BeX5能运行起来，少量查询不正常，但所有的保存都会失败。

1、查询失败问题，打开系统的“组织权限—》组织管理”
会执行下面这个SQL， LEFT JOIN SA_Code SA_OrgLevel ON ( SA_OPOrg.SLEVEL = SA_OrgLevel.SCODE )这一句报错，因为SA_Code.SCODE是varchar而SA_OPOrg.SLEVEL是int
PG不支持跨类型的判断，需要先转换。

1. SELECT
   
2.     SA_OPOrg.sID AS SA_OPOrg,
   
3.     '' AS dptName,
   
4.     SA_OrgLevel.SNAME AS levelName,
   
5.     '' AS ognName,
   
6.     SA_OPPerson.SCODE AS personCode,
   
7.     SA_OPPerson.sID AS personID,
   
8.     SA_OPPerson.SIDCARD AS personIDCard,
   
9.     SA_OPPerson.SLOGINNAME AS personLoginName,
   
10.     SA_OPPerson.SMAINORGID AS personMainOrgID,
    
11.     SA_OPPerson.SNAME AS personName,
    
12.     SA_OPPerson.SNUMB AS personNumb,
    
13.     SA_OPPerson.SPASSWORD AS personPassword,
    
14.     SA_OPPerson.SSEX AS personSex,
    
15.     SA_OPPerson.SVALIDSTATE AS personValidState,
    
16.     SA_OPPerson.VERSION AS personVersion,
    
17.     '' AS posName,
    
18.     SA_OPOrg.SADDRESS AS sAddress,
    
19.     SA_OPOrg.SCODE AS sCode,
    
20.     SA_OPOrg.SDESCRIPTION AS sDescription,
    
21.     SA_OPOrg.SFCODE AS sFCode,
    
22.     SA_OPOrg.sFID AS sFID,
    
23.     SA_OPOrg.sFName AS sFName,
    
24.     SA_OPOrg.SFAX AS sFax,
    
25.     SA_OPOrg.SLEVEL AS sLevel,
    
26.     SA_OPOrg.SLONGNAME AS sLongName,
    
27.     SA_OPOrg.SNAME AS sName,
    
28.     SA_OPOrg.SNODEKIND AS sNodeKind,
    
29.     SA_OPOrg.SORGKINDID AS sOrgKindID,
    
30.     SA_OPOrg.sParent AS sParent,
    
31.     SA_OPOrg.SPERSONID AS sPersonID,
    
32.     SA_OPOrg.SPHONE AS sPhone,
    
33.     SA_OPOrg.SSEQUENCE AS sSequence,
    
34.     SA_OPOrg.SVALIDSTATE AS sValidState,
    
35.     SA_OPOrg.SWXDEPTID AS sWxDeptID,
    
36.     SA_OPOrg.SZIP AS sZip,
    
37.     SA_OPOrg.VERSION AS version
    
38. FROM
    
39.     SA_OPOrg SA_OPOrg
    
40.     LEFT JOIN SA_OPPerson SA_OPPerson ON SA_OPOrg.SPERSONID = SA_OPPerson.sID
    
41.     LEFT JOIN SA_Code SA_OrgLevel ON ( SA_OPOrg.SLEVEL = SA_OrgLevel.SCODE )
    
42.     AND ( SA_OrgLevel.sType = 'orgLevel' )
    
43. WHERE
    
44.     ( SA_OPOrg.sParent IS NULL )
    
45.     AND ( SA_OPOrg.SVALIDSTATE = 1 )
    
46. ORDER BY
    
47.     SA_OPOrg.SSEQUENCE ASC

复制代码

2、insert保存，全部都会失败
我分析了下，是执行的insert语句X5会在字段前加上了表名，而pgsql不支持这个语法。
例如下面是新增一个角色执行的SQL，去掉其中所有的字段前缀“SA_OPRole.”，则会成功，否则失败。

1. INSERT INTO SA_OPRole (
   
2. SA_OPRole.sID,
   
3. SA_OPRole.VERSION,
   
4. SA_OPRole.SNAME,
   
5. SA_OPRole.SCODE,
   
6. SA_OPRole.SCATALOG,
   
7. SA_OPRole.SROLEKIND,
   
8. SA_OPRole.SDESCRIPTION,
   
9. SA_OPRole.SSEQUENCE,
   
10. SA_OPRole.SVALIDSTATE,
    
11. SA_OPRole.sParentRolesNames
    
12. )
    
13. VALUES
    
14.     (
    
15.     '7B4D772596BF4587A A8E6C56D5171A3B',
    
16.     0,
    
17.     'test1',
    
18.     'test1',
    
19.     NULL,
    
20.     'fun',
    
21.     NULL,
    
22.     NULL,
    
23.     1,
    
24.     ''
    
25.     )

复制代码

3、重构查询、保存核心方法：
由于BizData.save()等方法是X5的内置的jar中，未找到源码，我改不了

综上，请问是不是目前BeX5还不支持PostgreSQL，或者是平台有什么参数需要修改一下，让它适配PostgreSQL？

我参照如下文档，集成postgreSQL
http://bbs.wex5.com/forum.php?mo ... hlight=product-name
http://bbs.wex5.com/forum.php?mo ... ighlight=postgresql

jishuang

没有对PostgreSQL支持过，如果要支持可以到商务人员协商