rdbmsSpecifics is not extendable (custom)

[judsona]

Hi

it is possible at add external drivers that aren't "AutoLoadedPopularDrivers"

L185

        // look for additional driver specified in properties
        subDrivers.addAll(Properties.getAdditionalDrivers());

It should be possible to add your own rdmsSpecifics

// look for additional driver specified in properties
for(DriverRDBMSSpecific : Properties.getAdditonalRDBMSSpecifics()) {
     rdbmsSpecifics.put(DriverRDBMSSpecific.getDriverName(), DriverRDBMSSpecific.getDriverSpecific());
}

I need this because i would like to implement Australian Date formats for the sql generated (default is american MM/dd/yyyy).

[brunorozendo]

Sorry, I don't fallow,

Do you want a format a date that was print with a query? or a date inside a query?

a use this file to change the output:

log4j2.xml

<?xml version="1.0" encoding="UTF-8"?>
<configuration status="OFF">
    <appenders>
        <Console name="Console" target="SYSTEM_OUT">
            <PatternLayout pattern="%d{HH:mm:ss.SSS} %level - %m%ex%n" />
        </Console>
    </appenders>
    <loggers>
        <root level="off">
            <appender-ref ref="Console" />
        </root>
        <logger name="log4jdbc.log4j2" level="info" additivity="true">
            <MarkerFilter marker="LOG4JDBC_NON_STATEMENT" onMatch="DENY" onMismatch="NEUTRAL"/>
        </logger>                
    </loggers>
</configuration>

The output

13:50:34.588 INFO - 5. SELECT @@VERSION; {executed in 86 ms}
13:50:35.058 INFO - 5. SELECT @@VERSION; {executed in 78 ms}
13:50:36.803 INFO - 5. SELECT P.CD_PARAMETRO...; {executed in 43 ms}
13:50:37.745 INFO - 5. SELECT U.CD_FUNCIONAR...; {executed in 69 ms}
13:50:37.961 INFO - 5. SELECT F.CD_CD_FUNCIO...; {executed in 50 ms}
...

If it is not what you want, please describe what you expected

[judsona]

I want to be able to customise how the date format comes inside the query

i.e.

13:50:36.803 INFO - 5. SELECT t_name FROM lr_user usr WHERE usr.d_created >= 'DD/MM/YYYY'

Currently it uses a format MM/dd/YYYY, which cannot be changed. 13:50:36.803 INFO - 5. SELECT t_name FROM lr_user usr WHERE usr.d_created >= 'MM/DD/YYYY'

[brunorozendo]

usr.d_created >= 'DD/MM/YYYY' is wrong. You should use a a CONVERSOR/CAST function to compare dates.

Example:

These two queries are the same, One is in oracle and another in sqlverser

Oracle

SELECT
  *
FROM (SELECT
        U.CD_FUNCIONARIO,
        U.CD_ORGAO,
        MAX(U.DT_BLOQUEIO)         AS DT_BLOQUEIO,
        MAX(U.CD_SISTEMA)          AS CD_SISTEMA,
        MAX(U.DT_BLOQUEIO_SISTEMA) AS DT_BLOQUEIO_SISTEMA,
        MAX(U.CD_PERFIL)           AS CD_PERFIL,
        MAX(U.DT_BLOQUEIO_PERFIL)  AS DT_BLOQUEIO_PERFIL,
        MAX(U.NM_FUNCIONARIO)      AS NM_FUNCIONARIO,
        MAX(U.CD_UG)               AS CD_UG
      FROM SG_V_USUARIO_CONSULTA U
      WHERE (U.DT_BLOQUEIO >= TO_DATE('2017-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
             U.DT_BLOQUEIO <= TO_DATE('2017-04-25 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
      GROUP BY U.CD_FUNCIONARIO, U.CD_ORGAO
      ORDER BY 2 ASC, 8 ASC)
WHERE ROWNUM < 501;

sqlserver

SELECT TOP 500
  U.CD_FUNCIONARIO,
  U.CD_ORGAO,
  MAX(U.DT_BLOQUEIO)         AS DT_BLOQUEIO,
  MAX(U.CD_SISTEMA)          AS CD_SISTEMA,
  MAX(U.DT_BLOQUEIO_SISTEMA) AS DT_BLOQUEIO_SISTEMA,
  MAX(U.CD_PERFIL)           AS CD_PERFIL,
  MAX(U.DT_BLOQUEIO_PERFIL)  AS DT_BLOQUEIO_PERFIL,
  MAX(U.NM_FUNCIONARIO)      AS NM_FUNCIONARIO,
  MAX(U.CD_UG)               AS CD_UG
FROM SG_V_USUARIO_CONSULTA U
WHERE (U.DT_BLOQUEIO >= CONVERT(DATETIME, '2017-04-01 00:00:00', 120) AND
       U.DT_BLOQUEIO <= CONVERT(DATETIME, '2017-04-25 23:59:59', 120))

[judsona]

Brunorozendo,

I was only using that as an example, we're using a less known database called Ingres owned by Actian.

Here is an email of a sql statement produced by the logger

select
distinct overridepe0_.n_key_ovr_pen_int_rate as n_key_ov1_197_,
overridepe0_.n_lock_version as n_lock_v2_197_,
overridepe0_.d_created as d_create3_197_,
overridepe0_.n_key_user_created as n_key_u10_197_,
overridepe0_.d_updated as d_update4_197_,
overridepe0_.n_key_user_updated as n_key_u11_197_,
overridepe0_.f_active as f_active5_197_,
overridepe0_.d_effective as d_effect6_197_,
overridepe0_.n_effective_idx as n_effect7_197_,
overridepe0_.n_interest_rate as n_intere8_197_,
overridepe0_.d_posting as d_postin9_197_,
overridepe0_.n_key_legislative_group as n_key_l12_197_,
overridepe0_.n_key_arrears_exclusion_type as n_key_a13_197_
from lr_ovr_pen_int_rate overridepe0_
where overridepe0_.n_key_legislative_group=-1
and overridepe0_.n_key_arrears_exclusion_type=700644358
and overridepe0_.d_posting<='04/26/2017 07:46:59.129'

Correct format for us would be (Note: Yes we could also use Ingre's DATE() or ANSIDATE() functions. but the following is suitable for the database we are using)

select
distinct overridepe0_.n_key_ovr_pen_int_rate as n_key_ov1_197_,
overridepe0_.n_lock_version as n_lock_v2_197_,
overridepe0_.d_created as d_create3_197_,
overridepe0_.n_key_user_created as n_key_u10_197_,
overridepe0_.d_updated as d_update4_197_,
overridepe0_.n_key_user_updated as n_key_u11_197_,
overridepe0_.f_active as f_active5_197_,
overridepe0_.d_effective as d_effect6_197_,
overridepe0_.n_effective_idx as n_effect7_197_,
overridepe0_.n_interest_rate as n_intere8_197_,
overridepe0_.d_posting as d_postin9_197_,
overridepe0_.n_key_legislative_group as n_key_l12_197_,
overridepe0_.n_key_arrears_exclusion_type as n_key_a13_197_
from lr_ovr_pen_int_rate overridepe0_
where overridepe0_.n_key_legislative_group=-1
and overridepe0_.n_key_arrears_exclusion_type=700644358
and overridepe0_.d_posting<='2017-04-26 07:46:59.129'