Apache Derby database jdbc sql generic adapter question

[Guest]

Hello,

We have moved our repository onto an Apache Derby Database and connect using a generic sqljdbc connection. We previously had our database on SQL Server. I had some passthru code that produced information on our repository tables, BOT_SCHED, Resource Analyzer tables etc but some of the code is not understood by the sqljdbc connection now (which is fair enough). My question is does anyone know of a good resource that I can use to understand what sql passthru I can use that will be understood by the sqljdbc connection. I suppose not really a WebFOCUS quesiton but I thought I'd put it here as I have been looking for a while and am feeling a bit stuck to find an answer so thought i would try here.

[Guest]

Morning James

My understanding is that Derby uses the same syntax as DB2 but I have no personal knowledge of that.

Can you give us an example of what used to work, and what errors you get when you try to use it now that you're in Derby land? Maybe we can find a pattern - like suppose datetime fields are a common thing to have to recode.

You probably already found this but I'll put it in here in case its helpful in the future:

https://db.apache.org/derby/faq.html#users_sql

Derby SQL Reference

https://db.apache.org/derby/docs/10.16/ref/index.html

Also I found several online sites that claim to be able to convert your TSQL (that's MS SQL code) to DB2 or even specifically for Derby.

I make no claims about any of these, just passing along some site names:

https://www.sqlines.com/overview

This one looks like it's more for Java but they have a specific SQL translator tool that lets you choose MS SQL Server and Derby:

https://www.jooq.org/translate/

If you have an example(s), maybe we can find the root of the problem and you'll be able to apply that logic to any other queries.

[Guest]

Hello, This is the code (your hunch was right, it involves date / times):

SQL SQLJDBC  

SELECT

T2.JOBDESC AS Title,

T2.SCHEDULEID AS "Schedule ID",

T1.PRT_PATH AS Path,

T1.CREATEDBY,

T2.CASTER_USER,

CASE WHEN T2.LASTEXTIME IS NULL THEN 'None' ELSE

FORMAT(T2.LASTEXTIME,'dddd, MMMM dd, yyyy hh:mm:ss tt zz')

END AS "Last Time Executed",

CASE T2.LASTEXSTATUS WHEN 'NOERROR' THEN 'Success'

           WHEN 'WARNING' THEN 'Warning'

           WHEN 'FAILURE' THEN 'Error'

           ELSE T2.LASTEXSTATUS END AS "Last Job Status",

CASE WHEN T3.NEXTRUNTIME = '00000000000009223372036854775807' THEN 'None' ELSE

FORMAT(convert(datetime, dateadd(s, convert(bigint, round(convert (numeric(38,12), T3.NEXTRUNTIME) / 1000,0)), '1970-01-01')), 'dddd, MMMM dd, yyyy hh:mm:ss tt zz', 'en-US') 

END AS "Next Run Time",

T3.NEXTRUNTIME AS "Next Run Time To Order by",

T1.*,

T2.*,

T3.*,

T4.*,

T5.*

FROM WebFocusdev.dbo.WF_REPOSOBJ T1 

INNER JOIN WebFocusdev.dbo.BOTSCHED T2 ON T1.EXT_ID = T2.SCHEDULEID

INNER JOIN  WebFocusdev.dbo.BOTSIT T3 ON T2.SCHEDULEID = T3.SCHEDULEID 

INNER JOIN WebFocusdev.dbo.BOTPACK T4 ON T2.PACKETID = T4.PACKETID

INNER JOIN WebFocusdev.dbo.BOTDIST T5 ON T2.SCHEDULEID = T5.SCHEDULEID

WHERE T1.OBJTYPE = 113

ORDER BY 6 DESC

END

and the error shows as :

(FOC1400) SQLCODE IS 20000 (HEX: 00004E20) XOPEN: 42X01 </b> (FOC1500) : (20000) [42X01] Syntax error: Encountered "convert" at line 1, column (FOC1500) : 492.[derby][sqlException@4c372610] java.sql.SQLException (FOC1500) : [derby][sqlException@4c372610][sqlca@29beb1b7] DERBY SQLCA from server (FOC1500) : [derby][sqlException@4c372610][sqlca@29beb1b7] SqlCode = -20001 (FOC1500) : [derby][sqlException@4c372610][sqlca@29beb1b7] SqlErrd = { 0, 0, (FOC1500) : 0, 0, 0, 0 } (FOC1500) : [derby][sqlException@4c372610][sqlca@29beb1b7] SqlErrmc = (FOC1500) : Encountered "convert" at line 1, column 49242X01 (FOC1500) : [derby][sqlException@4c372610][sqlca@29beb1b7] SqlErrp = (FOC1500) : CSS10140 (FOC1500) : [derby][sqlException@4c372610][sqlca@29beb1b7] SqlState = 42X01 (FOC1500) : [derby][sqlException@4c372610][sqlca@29beb1b7] SqlWarn = (FOC1500) : [derby][s = L (FOC1405) SQL PREPARE ERROR.

So I'm thinking when it hits the convert the SQLJDBC doesn't like it, which is understandable, but it made me think, is there a JDBC SQL book that I need to look at to see what functions I can use with the Derby Database.

(if I take the 2 fields with the functions in them, "Last Time Executed" and "Next Run Time" out the code works ok)

Thanks for your help.

[Guest]

Hey James

Just for grins - see if this code works for you from the jooq.org website. I doctored your original query only slightly to make sure it worked for me in sql server management studio first. Then when I got it to return an answer in the native SQL Server tools, I plugged it into the query converter and chose 'from' of 'sql server' and the 'to' side, I chose 'DB2 LUW latest version':

select

 T2.JOBDESC "Title",

 T2.SCHEDULEID "Schedule ID",

 T1.PRT_PATH "Path",

 T1.CREATEDBY,

 T2.CASTER_USER,

 case

  when T2.LASTEXTIME is null then 'None'

  else FORMAT(T2.LASTEXTIME, 'dddd, MMMM dd, yyyy hh:mm:ss tt zz')

 end "Last Time Executed",

 case T2.LASTEXSTATUS

  when 'NOERROR' then 'Success'

  when 'WARNING' then 'Warning'

  when 'FAILURE' then 'Error'

  else T2.LASTEXSTATUS

 end "Last Job Status",

 case

  when T3.NEXTRUNTIME = '00000000000009223372036854775807' then 'None'

  else FORMAT(

   cast(('1970-01-01' + cast(round(

    (cast(T3.NEXTRUNTIME as decimal(38, 12)) / 1000),

    0

   ) as bigint) second) as timestamp),

   'dddd, MMMM dd, yyyy hh:mm:ss tt zz',

   'en-US'

  )

 end "Next Run Time",

 T3.NEXTRUNTIME "Next Run Time To Order by",

 T1.*,

 T2.*,

 T3.*,

 T4.*,

 T5.*

from WF_REPOSOBJ T1

 join BOTSCHED T2

  on T1.EXT_ID = T2.SCHEDULEID

 join BOTSIT T3

  on T2.SCHEDULEID = T3.SCHEDULEID

 join BOTPACK T4

  on T2.PACKETID = T4.PACKETID

 join BOTDIST T5

  on T2.SCHEDULEID = T5.SCHEDULEID

where T1.OBJTYPE = 113

order by 6 desc

I see they changed something around the word convert.

[Guest]

Toby, I tried your code, I did get an error message still which was :

(FOC1400) SQLCODE IS 20000 (HEX: 00004E20) XOPEN: 42X48 </b> (FOC1500) : (20000) [42X48] Value '38' is not a valid precision for DECIMAL. (FOC1500) : [derby][sqlException@59652afa] java.sql.SQLException (FOC1500) : [derby][sqlException@59652afa][sqlca@26bc3c32] DERBY SQLCA from server (FOC1500) : [derby][sqlException@59652afa][sqlca@26bc3c32] SqlCode = -20001 (FOC1500) : [derby][sqlException@59652afa][sqlca@26bc3c32] SqlErrd = { 0, 0, (FOC1500) : 0, 0, 0, 0 } (FOC1500) : [derby][sqlException@59652afa][sqlca@26bc3c32] SqlErrmc = (FOC1500) : DECIMAL3842X48 (FOC1500) : [derby][sqlException@59652afa][sqlca@26bc3c32] SqlErrp = (FOC1500) : CSS10140 (FOC1500) : [derby][sqlException@59652afa][sqlca@26bc3c32] SqlState = 42X48 (FOC1500) : [derby][sqlException@59652afa][sqlca@26bc3c32] SqlWarn = (FOC1500) : [derby][sqlException@59652afa] SQL state = 42X48 [59652afa] SQL L (FOC1405) SQL PREPARE ERROR.

I've just put the code in and I think maybe after following process you did got closer to being able to be used. I will see if can get to work with small changes.

Many thanks