Feature #95

Feature #357: Enduro/X 7.0 master task

IDEA - Universal SQL sub-system

Added by Madars about 7 years ago. Updated about 3 years ago.

Status:RejectedStart date:02/04/2017
Priority:Normal (Code 4)Due date:
Assignee:-% Done:

100%

Category:-
Target version:-

Description

It could be possible to send queries to some XATMI server with standard UBF and receive responses from database. For example, we could send:

EX_DB_QUERY ="select data1, data2, data3 from sometable where data4=:EX_DB_COL1"
EX_DB_COL1="abc"

tpcall("SQLSRV"...)

on response we could get:
EX_DB_COL1=data1
EX_DB_COL2=data2
EX_DB_COL3=data3
EX_DB_RESULT=0 (succeed, or DB code/mapped)

The support for XA transactions will come out of the box.

The target driver could use java jdbc system. We could offer using standard dialect, for example https://blog.jooq.org/2013/09/09/crazy-translations-of-simple-sql-expressions-to-various-sql-dialects/ so that developer could use one language for any of the databases.

Some kind of clustering also could be possible with making journal logs and pushing the changes to DBs later... (just idea).

ldbc.jar (273 KB) Madars, 04/27/2018 01:57 PM

ldbc.zip (504 KB) Madars, 04/27/2018 01:57 PM

ldbc-doc.7z (26.4 KB) Madars, 04/27/2018 02:00 PM

History

#1 Updated by Madars over 6 years ago

Then for this case we will need XA driver which will keep the transaction in ATMI context (maybe internal driver/switch symbol). Thus we can run transaction in full multi-thread/go-routine mode and still be in global transactions. And the Java end will roll the transactions with JDBC.

#3 Updated by Madars almost 6 years ago

Would have option for multiple database replication. One database would be primary, and second db would be replicated via journal files.

#4 Updated by Madars almost 6 years ago

#6 Updated by Madars over 5 years ago

  • Parent task set to #328

#7 Updated by Madars over 5 years ago

select 
    fld1 as U_FLD1
    fld2 as U_FLD2
where
    id = :U_ID[1]

Thus when fetching the data, it will try to load the U_FLD1 field if not succeed, then load into column number SQL_COL1_NAME, SQL_COL1_DATA, etc...
if field is NULL, then try to load into U_FLD1_IND, if not found then load into SQL_COL1_IND. Not sure only about how to properly escape the variables, as JDBC by default uses ? for the variable substitutions.

maybe in case if in string ":U_ID1" is needed, then it can be escaped with "\:U_ID1".

Or if compiled statements are used, maybe they needs to be transferred to "?", but the field uploading to java driver shall be done in this order.

#8 Updated by Madars over 5 years ago

https://en.wikipedia.org/wiki/JDBC_driver

That would be Type 3 driver – Network-Protocol driver (middleware driver)

#9 Updated by Madars over 5 years ago

Postgres ECPG could be used as C precompiler for Enduro/X SQL Gateway.

#10 Updated by Madars over 5 years ago

  • Parent task changed from #328 to #357

#11 Updated by Madars about 5 years ago

Needs API for metadata of columns: https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSetMetaData.html

Seems like for data results, we just need to indicate column label (selected one), data type (XATMI typed) and NULL indicator.

All other infos must be request via separate call, otherwise the transport will get very bulky.

#12 Updated by Madars about 3 years ago

  • Status changed from New to Rejected
  • % Done changed from 0 to 100

Also available in: Atom PDF