Rocket U2 | UniVerse & UniData

 View Only
Expand all | Collapse all

SQL ODBC TO UNIVERSE LIMIT ROWS

  • 1.  SQL ODBC TO UNIVERSE LIMIT ROWS

    Posted 12-17-2021 12:00
    Can someone tell me the proper syntax for SQL statement to Universe. I have a large file that I need to fetch 10000 rows at a time from Universe that will update a remote AZURE DB daily. I don't have control over the AZURE site as EDA seems like a much better solution to keep the remote data current.

    I have tried:

    SELECT TOP 10000 * FROM FILE;
    SELECT * FROM FILE LIMIT 10000;
    SELECT * FROM FILE FETCH FIRST 10000;
    SELECT * FROM FILE WHERE ROWNUM < 10000;

    Thanks JR Moore


    ------------------------------
    JR Moore
    IT Special Projects
    Rocket Forum Shared Account
    Hillsboro US
    ------------------------------


  • 2.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    PARTNER
    Posted 12-19-2021 19:19
    I believe the keyword you are looking for is SAMPLE {n}
    This will select the First {n} rows of the database file.
    e.g
    SELECT * FROM FILE SAMPLE 10000;



    There is also the SAMPLED {n} keyword, which select every {n}th row of the database, but that is not what you seem to be wanting.



    ------------------------------
    Gregor Scott
    Software Architect
    Pentana Solutions Pty Ltd
    Mount Waverley VIC AU
    ------------------------------



  • 3.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    Posted 12-20-2021 09:49
    Gregor:

    Thanks for the reply to my question. I should have mentioned I tried SAMPLE and it works at TCL but not when calling from an outside program. It also works from the U2ODBC tester in Native SQL but not ODBC Compliant SQL. Does anybody know if there is a setting to allow Native SQL from outside Universe?

    Thanks JR Moore

    ------------------------------
    JR Moore
    IT Special Projects
    Rocket Forum Shared Account
    Hillsboro US
    ------------------------------



  • 4.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    PARTNER
    Posted 12-20-2021 18:30

    Thanks for the clarification @JR Moore.

    So you are now looking at the "Programmatic ODBC" stuff.

    Have you tried the SQL "CALL {xx}" syntax?

    You can create a program, or a subroutine if you wish to pass runtime parameters, that can execute an SQL statement, and return an SQL result set back to the caller.
    In the program you can use whatever logic you need to establish a select list, saving it to one of the 1-9 numbers, and then execute your SQL statement that uses the "SLIST" keyword to pick up the select list.


    Here is a snippet from one of my experiments working with the CALL syntax in SQL on UV.
    You can see that I have previously populated select list 9, and the SQL command is using that as the ID filter.
    The rest of the SQL statement is simply column definitions and sort sequencing.

    cmd.SQL := \FROM INVOICE SLIST 9 \
    cmd.SQL := \WHERE INV_GPPctg < \:pctg.LIMIT

    * Run the SQL command
    if @TTY[1,2] = "uv" then
       * UCI connections have @TTY='uvsrv'
       * Build the record set for the UCI connection
       ST = SQLExecDirect(@HSTMT, cmd.SQL)
    end else
       * Run this interactively at TCL
       perform cmd.SQL:";"
    end




    ------------------------------
    Gregor Scott
    Software Architect
    Pentana Solutions Pty Ltd
    Mount Waverley VIC AU
    ------------------------------



  • 5.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    Posted 12-21-2021 09:32
    I'd like to dabble in some sql calls like the one above but I am in Unidata, not universe. Is there a tutorial, class, or well written manual on how to start to use the sql features in unidata? Does the above code work on unidata as well?

    Thanks!

    ------------------------------
    Kathleen Hambrick
    PROGRAMMER
    William C Earhart Co Inc
    Portland OR US
    ------------------------------



  • 6.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    Posted 12-21-2021 11:37
    1. Rocket has _ outstandingly good _ documentation (manuals) on using SQL with U2.
    2. The Rocket BDT (Basic Developer ToolKit) is also _ key.

    ------------------------------
    Bill Brutzman
    IT Manager
    Hk Metalcraft Manufacturing Corporation
    Lodi NJ US
    ------------------------------



  • 7.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    PARTNER
    Posted 12-21-2021 20:00
    Here is a fully working example BASIC program (UniVerse, PICK flavour account syntax) for use in SQL calls:

    program TEST.SQLCALL

    * Get LIMIT option
    get(arg.) OPT.VERB else OPT.VERB = ''
    b.LIMIT = (upcase(OPT.VERB) = "LIMIT")
    if b.LIMIT then get(arg.) qty.VERB else qty.VERB = 0

    * Be verbose if we are an interactive session
    b.VERBOSE = (@TTY[1,2] # "uv")

    SEL.CMD = 'SELECT VOC TO 9 WITH F1 = "F]" AND WITH F2 # "/]" AND WITH F2 # "..]" AND WITH F2 # "./]"'
    if b.LIMIT and len(qty.VERB) then SEL.CMD := " SAMPLE ":qty.VERB

    clearselect 9

    if b.VERBOSE then
    crt "Selecting VOC local F items"
    crt "CMD = ":SEL.CMD
    execute SEL.CMD
    end else
    execute SEL.CMD capturing exop
    end

    b.SL9.ACTIVE = selectinfo(9,1)
    qty.SEL = 0
    if b.SL9.ACTIVE then qty.SEL = selectinfo(9,3)

    if b.VERBOSE then
    crt " Select list 9 Active? ":b.SL9.ACTIVE
    crt " Qty Selected = ":qty.SEL
    end

    if b.SL9.ACTIVE then
    SQL.CMD = 'SELECT @ID FMT "25L" AS "VOCNAME", '
    SQL.CMD := 'EVAL "@RECORD<2>" FMT "25L" AS "DATAFILE", '
    SQL.CMD := 'EVAL "@RECORD<3>" FMT "25L" AS "DICTFILE" '
    SQL.CMD := 'FROM VOC SLIST 9 '
    SQL.CMD := 'ORDER BY VOCNAME ASC'

    if @TTY[1,2] = "uv" then
    * UCI connections have @TTY='uvsrv'
    * Build the record set for the UCI connection
    ST = SQLExecDirect(@HSTMT, SQL.CMD)
    end else
    * Run this interactively
    perform SQL.CMD:";"
    end

    end

    @SYSTEM.RETURN.CODE = qty.SEL

    This can be run at TCL like this:
    TEST.SQLCALL LIMIT 200
    or
    TEST.SQLCALL LIMIT 1000

    It can be used in an SQL call with the following SQL statement:
    CALL TEST.SQLCALL LIMIT 200
    or 
    CALL TEST.SQLCALL LIMIT 200

    I think the BASIC program's syntax is something you will need to fine tune for UniData.
    The SQL syntax should be usable as is.

    ------------------------------
    Gregor Scott
    Software Architect
    Pentana Solutions Pty Ltd
    Mount Waverley VIC AU
    ------------------------------



  • 8.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    Posted 12-22-2021 11:53
    Gregor:

    Thanks for the sample program! I created that on our UV system and ran it but our system complains about the ORDER BY clause in the SQL statement. I get the following error "UniVerse/SQL: syntax error. Unexpected filename. Token was "ORDER". Scanned command was FROM VOC SLIST 9 ORDER".  If I remove the order by clause it works is there a setting somewhere that affects this?

    On another note I was able to get the SAMPLE keyword to work from outside by changing the setting on the DSN in windows to Retry as Universe SQL = yes.

    Thanks again for the help!

    ------------------------------
    JR Moore
    IT Special Projects
    Rocket Forum Shared Account
    Hillsboro US
    ------------------------------



  • 9.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    ROCKETEER
    Posted 12-22-2021 14:41
    The error indicates that the account may have a file named ORDER which overwrote the standard VOC entry keyword for ORDER?

    >CT VOC ORDER

    ORDER
    0001 K
    0002 511

    ------------------------------
    Neil Morris
    Universe Advanced Technical Support
    Rocket Software
    ------------------------------



  • 10.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    Posted 12-22-2021 14:47
    Neil:

    Great catch yes it does.

    Thanks JR Moore

    ------------------------------
    JR Moore
    IT Special Projects
    Rocket Forum Shared Account
    Hillsboro US
    ------------------------------



  • 11.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    Posted 01-29-2024 10:05

    Native Universe SQL can be sent via ODBC using curly braces, NATIVE keyword, with the query in double quotes. This example is for an MSSQL linked server.

    SELECT * FROM OPENQUERY(linkedserver, '{NATIVE "SELECT * FROM table ORDER BY column SAMPLE 10"}')



    ------------------------------
    Greg Clitheroe
    Rocket Forum Shared Account
    ------------------------------



  • 12.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    Posted 12-22-2021 18:38
    After reading through this thread, I'm guessing that you don't always want just the first 10K records, you are looking to 'chunk' the file.

    To achieve that, you'll also need an offset/skip.

    I'm not sure about Universe (couldn't find the reference guide to check), but this isn't directly supported in AQL on D3.  However; if you save the results to a list first, you can do the following:

    execute "sselect file";
    execute "save-list listname";
    
    *first 10K
    execute "get-list listname (1-10000)"
    
    *next 10K
    execute "get-list listname (10001-20000)"​


    The overhead of having to select and save the list beforehand is not optimal, but I'm not aware of any ... SAMPLING {N} SKIP {N} - which would be a really nice addition.

    Hope this helps.

    Jeremy



    ------------------------------
    Jeremy Lockwood
    Awesome
    ASE Supply Inc
    Portland OR US
    ------------------------------



  • 13.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    Posted 12-23-2021 14:31
    Jeremy:

    Thanks for the reply! When I retrieve the records I'm updating a control record and putting a date in the record that I sent the record. Then I select the records without the date from a sorted savedlist. Now I'm getting another error when calling the program.

    Program "SQL_CALL_LIMIT_COMN": Line 26, FATAL: the locks necessary for database operations at the current isolation level (1) are not held by this process.
    line26 = " WRITE DATE() ON F.CONTROL,'RECORD'

    It seems to be a permissions issue with the ODBC user. If I run the same CALL in the ODBC tester with the same credentials used in my external Python program the CALL works?

    ------------------------------
    JR Moore
    IT Special Projects
    Rocket Forum Shared Account
    Hillsboro US
    ------------------------------



  • 14.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    ROCKETEER
    Posted 12-23-2021 16:30
    Does the program perform a READU on F.CONTROL,'RECORD' before doing the WRITE? It would appear the WRITE is happening within a transaction and it expects a lock to be set on the record prior to the WRITE operation. The error indicates a lock is not set.

    ------------------------------
    Neil Morris
    Universe Advanced Technical Support
    Rocket Software
    ------------------------------



  • 15.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    Posted 01-03-2022 18:24
    Neil:

    Thanks for the reply I'm still having lock issues on the control file. My code is below as you can see I added a READU and a RELEASE not sure why it is not working.

    OPEN 'CONTROL' TO F.CONTROL ELSE CONTROL.OPEN = 0
    OPEN 'EPWCOMNINVMSPF' TO F.EPW ELSE STOP
    *
    * pull in the last date
    *

    READU DATEREC FROM F.CONTROL, 'LAST.DATE.REC' THEN
    LAST.DATE = DATEREC<1>
    END ELSE
    LAST.DATE = ""
    END
    THIS.TIME = TIME()
    *
    * if running this for the first time today, create a save list in NI order
    *
    IF LAST.DATE # DATE() THEN
          DATEREC<1> = DATE()
          DATEREC<2> = THIS.TIME
          DATEREC<3> = ""
          DATEREC<4> = ""
          WRITE DATEREC ON F.CONTROL,'LAST.DATE.REC'
          RELEASE F.CONTROL,'LAST.DATE.REC'

    Below is the lock that is set
    Active Record Locks:
    Device.... Inode.... Netnode Userno Lmode Pid Login Id Item-ID........

    64771 139239659 0 -6 37 RU 22294 root LAST.DATE.REC

    Below is the calling python program
    conn = pyodbc.connect('DSN=dsn;UID=user;PWD=pass')
        cursor = conn.cursor()
        stmt = "CALL SQL_CALL_LIMIT_COMN LIMIT 10000"

    Thanks JR Moore


    ------------------------------
    JR Moore
    IT Special Projects
    Rocket Forum Shared Account
    Hillsboro US
    ------------------------------



  • 16.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    Posted 01-03-2022 18:32
    Added detail:
    If I run it on the database it works so it has to be some sort of permission related to the ODBC. I have tried the python as my userid and also as root same result lock set on the CONTROL file. The CONTROl file has rwx-rwx-rwx Linux permissions.

    ------------------------------
    JR Moore
    IT Special Projects
    Rocket Forum Shared Account
    Hillsboro US
    ------------------------------



  • 17.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    ROCKETEER
    Posted 01-04-2022 08:18
    JR,

    How do you release the lock if the LAST.DATE = DATE()? 

    Have you tried putting the RELEASE command outside of the IF...THEN...ELSE statements. 


    ------------------------------
    Mike Rajkowski
    support
    Rocket Internal - All Brands
    DENVER CO US
    ------------------------------



  • 18.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    Posted 01-04-2022 10:03
    Mike:

    Thanks for the reply. I added a release outside of the IF statement and no change. It will run locally so the lock that is being sent seems to be unrelated to the format of the conditional statement. The issue seems to be with the program being run from ODBC and not from TCL.

    Thanks JR Moore

    ------------------------------
    JR Moore
    IT Special Projects
    Auto Parts Warehouse
    Hillsboro US
    ------------------------------



  • 19.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    ROCKETEER
    Posted 01-04-2022 08:29
    Hi JR,
    Based on your last update, I wasn't sure if you were getting the same error or was there now a new error? The suggestion of adding the READU was to eliminate the "locks necessary for database...." error. Is that still happening? Or is there a new error after adding the READU?
    Thanks,
    Neil

    ------------------------------
    Neil Morris
    Universe Advanced Technical Support
    Rocket Software
    ------------------------------



  • 20.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    Posted 01-04-2022 09:51
    Neil:

    No I'm not getting the error now it appears to be hanging at the lock and does not produce any data. 

    Thanks JR Moore

    ------------------------------
    JR Moore
    IT Special Projects
    Auto Parts Warehouse
    Hillsboro US
    ------------------------------



  • 21.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    ROCKETEER
    Posted 02-01-2022 15:02
    Hi JR,
    This thread fell off the radar. Are you still encountering issues?
    If the process is hanging, have you checked the LIST.READU EVERY output on the server to see if you can identify locks which might be blocking?
    At the same time, you could try running PORT.STATUS PID {pid of hung process} LAYER.STACK to see if that identified where the process is currently stuck.
    Thanks,
    Neil

    ------------------------------
    Neil Morris
    Universe Advanced Technical Support
    Rocket Software
    ------------------------------



  • 22.  RE: SQL ODBC TO UNIVERSE LIMIT ROWS

    Posted 02-02-2022 12:48
    Neil:

    Thanks for the inquiry! Not sure why it would hang when I called it VIA a python program. I wrote a PHP program to do the same function and it worked fine??? I was pyodbc to get the data from Universe maybe there was a row limit with the call not sure. If I kept it under 2500 records it would work over that create the locks on Universe.

    ------------------------------
    Jr Moore
    IT Special Projects
    Auto Parts Warehouse
    Hillsboro US
    ------------------------------