Obtaining EOF of a partitioned SQL table
Manik Ray
Have any of you used FILE_GETINFOLISTBYNAME or FILE_GETINFOLIST to
obtain the EOF of a partitioned SQL table?.
HP documentation says, using these calls, one can obtain E.O.F of any
Enscribe or SQL files except SQL short-hand view. However, when I
wrote a small program to experiment this, I find this working for
Enscribe file only. It returns guardian error (ERR 2 - Operation not
supported for the file used) for SQL table.
BTW, can user open a SQL table using FILE_OPEN_?
Thanks
RM
JShepherd
rjma...@yahoo.com says...
Sample C source code to do that
http://www.asyste.com/utilities/fi.c
-------
$DATA01.JASSQL.PKEYSEQ 20 Jun 2009, 15:26
SQL BASE TABLE
CATALOG $DATA01.JASCAT
VERSION 350
TYPE K
FORMAT 2
EXT ( 50 PAGES, 50 PAGES, MAXEXTENTS 16 )
REC 198
BLOCK 4096
KEY ( COLUMN 0, OFFSET 0, LENGTH 8, ASC,
COLUMN 1, OFFSET 8, LENGTH 4, ASC )
PART ( 0, $DATA01, 50 PAGES, 50 PAGES, MAXEXTENTS 16, FORMAT 2,
( -9223372036854775808, -2147483648 ) )
PART ( 1, $DATA02, 64 PAGES, 64 PAGES, MAXEXTENTS 24, FORMAT 2, ( 1, 0 ) )
PART ( 2, $DATA03, 64 PAGES, 64 PAGES, MAXEXTENTS 24, FORMAT 2, ( 2, 0 ) )
PART ( 3, $DATA04, 64 PAGES, 64 PAGES, MAXEXTENTS 24, FORMAT 2, ( 3, 0 ) )
PART ( 4, $DATA05, 64 PAGES, 64 PAGES, MAXEXTENTS 24, FORMAT 2, ( 4, 0 ) )
PART ( 5, $DATA06, 64 PAGES, 64 PAGES, MAXEXTENTS 24, FORMAT 2, ( 5, 0 ) )
PART ( 6, $DATA07, 64 PAGES, 64 PAGES, MAXEXTENTS 24, FORMAT 2, ( 6, 0 ) )
PART ( 7, $DATA09, 64 PAGES, 64 PAGES, MAXEXTENTS 24, FORMAT 2, ( 7, 0 ) )
PART ( 8, $DATA10, 64 PAGES, 64 PAGES, MAXEXTENTS 24, FORMAT 2, ( 8, 0 ) )
PART ( 9, $DATA12, 64 PAGES, 64 PAGES, MAXEXTENTS 24, FORMAT 2, ( 9, 0 ) )
PART ( 10, $DEV01, 64 PAGES, 64 PAGES, MAXEXTENTS 24, FORMAT 2, ( 10, 0 ) )
PART ( 11, $DEV02, 64 PAGES, 64 PAGES, MAXEXTENTS 24, FORMAT 2, ( 11, 0 ) )
PART ( 12, $DEV03, 64 PAGES, 64 PAGES, MAXEXTENTS 24, FORMAT 2, ( 12, 0 ) )
PART ( 13, $DEV04, 64 PAGES, 64 PAGES, MAXEXTENTS 24, FORMAT 2, ( 13, 0 ) )
PART ( 14, $DEV05, 64 PAGES, 64 PAGES, MAXEXTENTS 24, FORMAT 2, ( 14, 0 ) )
35> fic $data01.jassql.pkeyseq
\NSK3.$DATA01.JASSQL.PKEYSEQ 3145728
Keith Dick
I know FILE_GETINFOLISTBYNAME_ works for SQL/MP tables, and JShepherd's example demonstrates that, too.
I don't know whether it works for SQL/MX tables. If it does, I suppose you would have to give it the Guardian name, not the Ansi name, of the table, but I'm just guessing, since I've never tried it.
When you experimented, did you get the error 2 on the FILE_OPEN_ or on the FILE_GETINFOLIST_ call?
Utility programs, such as DUP, BACKUP, LOAD, do open SQL/MP tables using FILE_OPEN_, but they open for unstructured access. You can even do a octal or hex dump of the blocks of a SQL/MP table using a command like:
FUP COPY <table>,,H,UNSTR,BLOCKIN 4096,RECIN 4096,COUNT 5
So it might be possible for an ordinary user program to use FILE_OPEN_ on a SQL/MP table if the call specifies unstructured access. Perhaps you could do that and then use FILE_GETINFOLIST_ on the file number.
There is a chance that FILE_OPEN_ permits unstructured open only from a licensed program. I know there are some SQL-related things that only licensed programs can do, but I don't remember whether that is one of them. I know that SQLUTIL and BACKUP are licensed, but I don't remember whether FUP is licensed.
As far as using FILE_OPEN_ to open a SQL/MP table for the purposes of doing row-level access, I don't know whether an ordinary program can do that. Certainly any user program that contains embedded SQL statements does those things via code in the SQL Executor. I don't know whether there is anything in the code that makes sure those calls come only from trusted places, such as the executor. Maybe not, but even if you can do an ordinary open, you cannot then call READ or WRITE -- there are SQL Filesystem procedures that are used instead of READ or WRITE, and those aren't described in customer manuals. And, if I remember correctly, there is an extra parameter to the open that is needed when opening a SQL/MP table for row-level access, and it isn't documented in the customer manuals, either.
I know that SQLUTIL's COPY command does open a SQL/MP table and does row-level reads when you use the SHARE option in a COPY command. But SQLUTIL is licensed, so the permission to do that might be linked to the licensing.
I don't know how the corresponding stuff works in SQL/MX, but I expect it is restricted in about the same way, plus the additional complication that the Guardian filename in which the data actually resides might have additional limitations on how it may be opened.
Keith Dick
I wanted to write to you directly, but I see you are using a fake email address, to avoid spam, I assume.
In your example program, could you tell me why you used a union between the int64 and int32 array? I don't see anything in the example that requires it. In fact, I think you wouldn't need the local variable at all -- I think you could have passed (short *)rba64 and sizeof(int64_t) to FILE_GETINFOLISTBYNAME_, couldn't you?
This isn't a big deal. The code you have works and isn't terribly complicated. I'm just wondering whether there is something I'm not seeing about it that requires it be done that way.
JShepherd
Keit...@earthlink.net says...
I snipped it out of existing code that uses a #ifdef to function
on older Dxx systems that pre-date 64-bit file sizes.
Since Dxx support is likely not needed by the OP I removed that
section of code (except for the union).