Re: [h2] How to access the H2 parser and AST?
On Wed, 2020-10-07 at 15:57 +0200, Noel Grandin wrote: > We use a top-down recursive descent parser, and we have separate > documentation files which we pass through a build-time > tool to generate BNF-type diagrams. This one, right? public static Bnf getInstance(Reader csv) throws SQLException, IOException { Bnf bnf = new Bnf(); if (csv == null) { byte[] data = Utils.getResource("/org/h2/res/help.csv"); csv = new InputStreamReader(new ByteArrayInputStream(data)); } bnf.parse(csv); return bnf; } Thanks a lot, I appreciate! Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/ad64c4582371b4c2c5e294d38e9132d61d5ac575.camel%40manticore-projects.com.
Re: [h2] How to access the H2 parser and AST?
On 2020/10/07 3:54 pm, Andreas Reichel wrote: but please let me ask further: How exactly is the SQL syntax and grammar defined/specified for H2? Is there a BNF file somewhere? Or is it all hard-coded in Java only? We use a top-down recursive descent parser, and we have separate documentation files which we pass through a build-time tool to generate BNF-type diagrams. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/264556de-722d-288c-0984-dfc2b3e69f4d%40gmail.com.
Re: [h2] How to access the H2 parser and AST?
Thank you Noel, but please let me ask further: How exactly is the SQL syntax and grammar defined/specified for H2? Is there a BNF file somewhere? Or is it all hard-coded in Java only? I mean, somehow you create your excellent online documentation with the BNF diagrams. Cheers Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/6b662c51deadbc30e353c3630ea099efe149168b.camel%40manticore-projects.com.
Re: [h2] How to access the H2 parser and AST?
On 2020/10/07 2:37 pm, Andreas Reichel wrote: can we access the H2 internal Parse (which should know best) and retrieve the Query AST? I did not find anything in the API documentation. No, the internal parser doesn't generate a real AST, it produces something that roughly half-way between an AST and a query-plan. is there an ANTLR 4 grammar for H2? (I did not find any) No, although the documentation does have a fairly complete set of grammar-like productions. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d272ea92-f302-dfc3-1841-4fb344dc2732%40gmail.com.
[h2] Re: H2 to Oracle migration
Hello Dipesh, It all depends on your H2 version and particular syntax you have used. We run our Database Applications on H2, Oracle and SQL Server and so develop the schemas and queries with maximum compatibility in mind. Especially when you use Oracle-Mode, H2 should not be very different from Oracle, but there are a view exceptions of course: 1) Oracle can't truncate tables in other schemas, while H2 can 2) Oracle uses .NEXTVAL or .CURRVAL on sequences, while H2 (Version 2.0.201) enforces NEXT VALUE FOR when not in Oracle mode (but previous versions of H2 did not) 3) NUMBER vs. DECIMAL 4) before H2 Version 2.0.201, there was no VARBINARY and so you will need to decide about the column type case by case 5) before H2 Version 1.4.200 Git+ there were issues with Scale and Precision of auto-generated columns (which may need corrections) 6) EXTRACT fields from Dates/Times is very different between H2 and Oracle 7) Oracle will refuse FOREIGN KEY constraints, when there are no explicit PRIMARY KEYS on the columns (but H2 prior 2.0.201 created only implicit Unique Indexes) 8) in Oracle and empty String IS NULL, but in H2 and empty string IS NOT NULL 9) TRUNC on timestamps 10) Oracle is very sensitive to Table statistics and Query Plans. It can suddenly drop a Query Plan and chose something else. Also you might want to use query hints, especially PARALLEL, APPEND, DRIVING_SITE) to make your queries fast. 11) Oracle does not use CATALOGS 12) Lots of ALTER TABLE syntax in Oracle is weird (especially the COLUMN modifications) 13) You can not INSERT ... SELECT * FROM CLOBS/BLOBS in Oracle There is possibly a lot more stuff, please feel free to add. My advise is to export the schema ddl and then to run iit on Oracle step by step to see what fails and then to derive the pattern of the incompatibility. Once you have defined such a pattern, you can use JEDIT for REGEXP bases search and bean-shell based replace. Example for concerting .NEXTVAL into NEXT VALUE FOR fragments: Search Regex: (\w*)\.(\w*).NEXTVAL Beanshell Replacement: "NEXT VALUE FOR " + _1.toLowerCase() + "." + _2.toLowerCase() Good luck! Andreas On Tuesday, September 29, 2020 at 6:43:00 PM UTC+7 dipesh...@gmail.com wrote: > Hello Friends, > > We wanted to migrate H2 database to Oracle. > Does anyone performed this in past? any documentation? > > Your inputs will be much appreciated. > > > Thanks > Dipesh Tamore > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/2647de98-8ff4-4960-84a4-b33aae737ad2n%40googlegroups.com.
[h2] Re: age in another column
Siegfried, you will need to calculate the Date Difference in days divided by 365 as shown in the following example: *select Cast({d '2019-05-04'} - {d '2019-03-12'} AS DECIMAL) /365 from dual;* Cheers Andreas On Tuesday, September 29, 2020 at 4:21:33 PM UTC+7 sieg...@skerra.net wrote: > Hello > I export data from an H2 database and save it in a CSV. > That's going well. > I would like to save the age in another column. > Age = current date - Date of birth (GEBURTSDATUM) > > Is that possible. > > CALL > CSVWRITE('C:\Nextcloud-KCH\Vorstand\KC-Adressen\KC-Mitgliederadressen.csv', > 'SELECT ANREDE,TITEL, NAME, VORNAME, STRASSE, PLZ, ORT, TELEFONPRIVAT, > HANDY, EINTRITT, GEBURTSDATUM, EMAIL > FROM PUBLIC.MITGLIED > WHERE BEITRAGSGRUPPE IS NOT 56 ORDER BY NAME, VORNAME asc', > 'charset=windows-1250 escape=" fieldDelimiter=" fieldSeparator=; > writeColumnHeader=true'); > > Siegfried > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/bf523f25-79de-477b-9915-70426ccea36bn%40googlegroups.com.
[h2] How to access the H2 parser and AST?
Dear All, just a quick one: We use JSQLParser as a simple parser in order to get information about a query. Works, but of course it is not perfect. Now I wonder two things: 1. can we access the H2 internal Parse (which should know best) and retrieve the Query AST? I did not find anything in the API documentation. 2. is there an ANTLR 4 grammar for H2? (I did not find any) Cheers Andreas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d2b69aef-a501-4aa2-bdc8-e02ecb7e58ben%40googlegroups.com.