Hi Doug,

it's

sql>\fraw
sql>select sys.uuid();

i.e., \f is a separate command rather than a prefix.

See also the welcome message of mclient, "\q" in mclient, `man mclient`
and/or https://www.monetdb.org/Documentation/mclient-man-page

Best,
Stefan

----- On Oct 1, 2015, at 5:00 PM, Doug Service n848dg@gmail.com wrote:

> I was able to get that to work using -fraw
>
> sql>select sys.uuid();
> % .L # table_name
> % uuid # name
> % uuid # type
> % 0 # length
> [ 6c0e6904-c3a7-44c4-ba73-4d798cc0357d ]
> sql>
>
> but not with \fraw (no -fraw on the command line)
>
> sql>\fraw select sys.uuid();
> unsupported formatter
> sql>\f raw select sys.uuid();
> unsupported formatter
> sql>
>
> fraw looks like a good debugging tool.
>
> I am in the process of changing our schema to use UUIDs for keys in places that
> we need unique keys both inside and outside of the database. However, I am
> having problems inserting tuples with uuid columns that refer to existing uuid
> primary keys. In the following transcript from mclient you will see that the
> insert into the institution table succeeded using sys.uuid() to generate the
> primary key but the insert into the account table failed using the same uuid as
> the foreign key. I suspect I am using the wrong syntax for UUID constant, but I
> cannot find an example on the monetdb website.
>
> sql>CREATE TABLE institution(
> more> pk_uuid UUID NOT NULL,
> more> name VARCHAR(128) NOT NULL,
> more> CONSTRAINT institution_pk PRIMARY KEY (pk_uuid)
> more>);
> operation successful (35.688ms)
> sql>INSERT INTO institution (pk_uuid, name) VALUES (sys.uuid(),
> 'TheInstitution');
> 1 affected row (30.064ms)
> sql>SELECT * FROM institution;
> +--------------------------------------------------------------------------------------+----------------+
>| pk_uuid | name |
> +========================================+================+
>| fdf1b0a7-9cd2-4bb6-868a-049a199ef278 | TheInstitution |
> +--------------------------------------------------------------------------------------+----------------+
> 1 tuple (2.240ms)
> sql>CREATE TABLE account(
> more> pk_uuid UUID NOT NULL,
> more> name VARCHAR(128) NOT NULL,
> more> fk_institution_uuid UUID NOT NULL,
> more> number VARCHAR(64) NOT NULL,
> more> CONSTRAINT account_pk PRIMARY KEY (pk_uuid),
> more> CONSTRAINT account_institution_fk FOREIGN KEY (fk_institution_uuid)
> REFERENCES institution (pk_uuid)
> more>);
> operation successful (23.009ms)
> sql>INSERT INTO account (pk_uuid, name, fk_institution_uuid, number) VALUES
> more> (sys.uuid(), 'Master Account', 'fdf1b0a7-9cd2-4bb6-868a-049a199ef278',
> 'FXXXXXXXXXX');
> TypeException:user.s12_1[22]:'calc.uuid' undefined in: calc.uuid(nil:uuid);
> program contains errors
> sql>
>
>
>
> On Thu, Oct 1, 2015 at 7:16 AM, Sjoerd Mullender < sjoerd@acm.org > wrote:
>
>
> On 30/09/15 19:26, Niels Nes wrote:
>> On Wed, Sep 30, 2015 at 03:01:31PM +0000, Doug Service wrote:
>>> Yes, that works fine. How is the type supported in mapi? Is it a
>>> VarChar(36)?
>> Yes over the wire it should be varchar(36).
>
> Actually, as you can see in the mclient output if you use the -fraw
> option or the \fraw command, the type is specified as "uuid". But
> internally in the library there is no special treatment.
>
>> Niels
>>>
>>> Installed version information is at the end.
>>>
>>> Doug
>>>
>>> sql>select sys.uuid();
>>> +----------------------------------------------------------------------------------------------------------------------+
>>>
>>>
>|
>>> uuid
>>> |
>>> +======================================================================
>>>
>>>
> ================================================+
>>> | 772fd36b-f7d2-48cf-aaac-31261fd8ef46
>>> |
>>> +----------------------------------------------------------------------------------------------------------------------+
>>>
>>>
> 1 tuple (0.726ms)
>>> sql>select sys.uuid();
>>> +----------------------------------------------------------------------------------------------------------------------+
>>>
>>>
>|
>>> uuid
>>> |
>>> +======================================================================
>>>
>>>
> ================================================+
>>> | 5e2ae37b-8bd8-48ac-b35b-fc96fe760931
>>> |
>>> +----------------------------------------------------------------------------------------------------------------------+
>>>
>>>
> 1 tuple (0.774ms)
>>> sql>
>>>
>>> Versions Installed
>>>
>>> ii libmonetdb-client-dev 11.21.5 amd64
>>> MonetDB client/server interface library development files ii
>>> libmonetdb-client7 11.21.5 amd64
>>> MonetDB client/server interface library ii libmonetdb-stream6
>>> 11.19.15-20150603 amd64 MonetDB stream
>>> library ii libmonetdb-stream7 11.21.5
>>> amd64 MonetDB stream library ii libmonetdb11
>>> 11.19.15-20150603 amd64 MonetDB core
>>> library ii libmonetdb12 11.21.5 amd64
>>> MonetDB core library ii monetdb-client 11.21.5
>>> amd64 MonetDB database client ii monetdb-client-tools
>>> 11.21.5 amd64 MonetDB database
>>> client ii monetdb5-server 11.21.5
>>> amd64 MonetDB database server version 5 ii
>>> monetdb5-server-hugeint 11.21.5 amd64
>>> MonetDB - 128-bit integer support for MonetDB5-server ii
>>> monetdb5-sql 11.21.5 amd64
>>> MonetDB SQL support for monetdb5
>>>
>>>
>>>
>>> On Wed, Sep 30, 2015 at 2:38 PM, Niels Nes < Niels.Nes@cwi.nl >
>>> wrote:
>>>
>>> On Wed, Sep 30, 2015 at 02:26:40PM +0000, Doug Service wrote:
>>>> The documentation at https://www.monetdb.org/Documentation/
>>> Manuals/
>>>> SQLreference/UUItype indicates that a UUID type is supported
>>>> in
>>> MonetDB
>>>> and gives the example
>>>>
>>>> select uuid(); +--------------------------------------+ | uuid
>>>> | +======================================+ |
>>>> 65950c76-a2f6-4543-660a-b849cf5f2453 |
>>>> +--------------------------------------+
>>>>
>>>> which appears to output from mclient. When I try the same with
>>> mclient
>>>> I receive
>>>>
>>>> auto commit mode: on sql>select uuid(); SELECT: no such
>>>> operator 'uuid'
>>> This works fine in the default and stable versions. Maybe your
>>> in the wrong schema, ie does select sys.uuid(); work?
>>>
>>> Niels
>>>>
>>>> mapi.h only shows support for the following types
>>>>
>>>>
>>>> #define MAPI_AUTO 0 /* automatic type detection */
>>>> #define MAPI_TINY 1 #define MAPI_UTINY 2 #define
>>>> MAPI_SHORT 3 #define MAPI_USHORT 4 #define MAPI_INT
>>>> 5 #define MAPI_UINT 6 #define MAPI_LONG 7 #define
>>>> MAPI_ULONG 8 #define MAPI_LONGLONG 9 #define
>>>> MAPI_ULONGLONG 10 #define MAPI_CHAR 11 #define
>>>> MAPI_VARCHAR 12 #define MAPI_FLOAT 13 #define
>>>> MAPI_DOUBLE 14 #define MAPI_DATE 15 #define MAPI_TIME
>>>> 16 #define MAPI_DATETIME 17 #define MAPI_NUMERIC 18
>>>>
>>>>
>>>> Is it possible to get MonetDB to generate UUIDs and how is the
>>> type
>>>> supported in mapi.
>>>>
>>>> Thanks
>>>>
>>>> Doug Service
>>>
>>>> _______________________________________________ users-list
>>>> mailing list users-list@monetdb.org
>>>> https://www.monetdb.org/mailman/listinfo/users-list
>>>
>>>
>>> -- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI)
>>> Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14,
>>> phone ++31 20 592-4098 sip:4098@sip.cwi.nl url:
>>> https://www.cwi.nl/people/niels e-mail: Niels.Nes@cwi.nl
>>> _______________________________________________ users-list
>>> mailing list users-list@monetdb.org
>>> https://www.monetdb.org/mailman/listinfo/users-list
>>>
>>>
>>
>>> _______________________________________________ users-list
>>> mailing list users-list@monetdb.org
>>> https://www.monetdb.org/mailman/listinfo/users-list
>>
>>
>
>
> --
> Sjoerd Mullender
>
>
> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list
>
>
>
> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list

--

| Stefan.Manegold@CWI.nl | DB Architectures   (DA) |
| www.CWI.nl/~manegold/  | Science Park 123 (L321) |
| +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |

_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list