SQL ODBC TO UNIVERSE LIMIT ROWS

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

Neil:

Great catch yes it does.

Thanks JR Moore

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

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

Sign up

Please log in or register:

Welcome to the Rocket Forum!

Please log in or register:

Scanning file for viruses.

This file cannot be downloaded