Accessing a SqlSever database tabel with a UUID/GUID as Key...

[KrugerHenning]

Hi there. I hope this has a quick answer.

I need to access an existing Sqlserver database table using Plex. I used the application Integrator to import the entity into Plex 6.1 and fixed all the types I where I got compile errors. What do I do with the UUID/GUID? If I run the select generated by the view it selects the data.

I have tried types char/numeric/interger/big_int/small_int but the blockfetch don't want to select the data.

In the select the UniqueMsgId is a uniqueidentifier:
{
SELECT UniqueMsgId, MSGID, MsgBuffer, MAILBOX, MsgTime, SndFlag FROM dbo.P63l4V
}
which return the data when running it.

But the blockfetch is using:
{
"SELECT UniqueMsgId, MSGID, MsgBuffer, MAILBOX, MsgTime, SndFlag "
"FROM dbo.P63l4V "
"WHERE "
"( ( UniqueMsgId >= ? ) ) "
"ORDER BY UniqueMsgId "
}
which is not returning anything.
I suspect the data type is wrong. Any help please...

[Legacy User]

Hi Kruger,

Look in the Plex help for FLD DBMS type NME

For example, Microsoft SQL Server supports a GUID data type that can be implemented in a CA Plex-generated table by entering:

MyGUIDfield type Character
MyGUIDfield C format Char
MyGUIDfield DBMS type uniqueidentifier
MyGUIDfield length 36
MyEntity has MyGUIDfield

The specified name ("uniqueidentifier" in this example) must match the name required by the target DBMS.


Regards,
Jeremy Hutchinson
Desynit Limited
:: Plex Wiki :: 2E Wiki ::
:: Plex/2E Feed Aggregator ::

[KrugerHenning]

Thank you Jez,

I found that portion on the help and read it carefully. I implemented it that way but still no joy.
I then create a new table on the external database with only 2 fields to simplify the testing. (Key TstGUID-uniqueidentifier
and Num- int 4)

In SQLsever's table view the size of the uniqueidentifier field is 16....

I then created the entity in Plex and set the types as instructed in the help, but still no joy. I changed the size from length 36 (as was prescribed by help) to 16. Still no joy.

I then changed then TSTGUID field's type to int in the Database and plex ... and bingo it works... Thus my functions and screens are fine but something is not right with these 'uniqueidentifier' types....

What now?

[KrugerHenning]

SqlTeam.com:

Uniqueidentifiers are also referred to as GUIDs. (Globally Unique IDentifier)

That is, the API call that returns a GUID is guaranteed to always return a unique value across space and time. I don't know the full mechanics of creating a GUID, but I seem to remember that it has something to do with the MAC address on your network card and the system time.

To get a GUID in SQL Server (7.0+), you call the NEWID() function.

The uniqueidentifier data type in SQL Server is stored natively as a 16-byte binary value.

This is an example of a formatted GUID: B85E62C3-DC56-40C0-852A-49F759AC68FB.

http://msdn.microsoft.com/en-us/library/system.guid.aspx:

Remarks
--------------------------------------------------------------------------------

A GUID is a 128-bit integer (16 bytes) that can be used across all computers and networks wherever a unique identifier is required. Such an identifier has a very low probability of being duplicated.

SQL Server Books online:
uniqueidentifier
Consists of a 16-byte hexadecimal number indicating a globally unique identifier (GUID). The GUID is useful when a row must be unique among many other rows. For example, use the uniqueidentifier data type for a customer identification number column to compile a master company customer list from multiple countries.

[Legacy User]

Hi Kruger,

Just done a quick test myself using SQL Server 2005, Plex 6.1 and a simple EditDetail entity to test loading of the grid.

Used SQL Profiler to capture what's going on to get the following SQL which Plex executes:

declare @p1 int
set @p1=NULL
declare @p2 int
set @p2=0
declare @p5 int
set @p5=4098
declare @p6 int
set @p6=8200
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 char(36)',N'SELECT T1.id, T1.Data FROM TestGUID T1 WHERE ( ( T1.id >= @P1 ) ) ORDER BY T1.id ',@p5 output,@p6 output,@p7 output,'                                    '
select @p1, @p2, @p5, @p6, @p7

If this is executed directly in Management Studio you get the error:

[color=#FF0612]Msg 8169, Level 16, State 2, Line 1
Conversion failed when converting from a character string to uniqueidentifier.[color]

So SQL cannot convert the postion parameter, which is 36 spaces to a unique identifier.

To get around this you can add a value to your GUID field with the literal value 00000000-0000-0000-0000-00000000000 and pass this as the positioner to the BlockFetch. You may want to set this as the field's emtpy or low value and the appropriate code. Alternatively you may be able to sequence your fetch views using an alternative key which does not include the GUID.

Regards,
Jeremy Hutchinson
Desynit Limited
:: Plex Wiki :: 2E Wiki ::
:: Plex/2E Feed Aggregator ::

[KrugerHenning]

Thank you for your replay.

I have simulated what you did originally to get the error and got the same results.

I have tried option one to declare the '0000-......' value but it does not seem to filter through to the SQL(It stays NULL).
I have tried setting the value manually in the sql by using the profiler out put of my call and adding the value. I get a int-char convertion error.

declare @P1 int
set @P1='00000000-0000-0000-0000-00000000000'
declare @P2 int
set @P2=0
declare @P3 int
set @P3=4098
declare @P4 int
set @P4=8200
declare @P5 int
set @P5=0
exec sp_cursorprepexec @P1 output, @P2 output, N'@P1 char(36)', N'SELECT TSTGUID, NUM FROM dbo.P63llV WHERE ( ( TSTGUID >= @P1 ) ) ORDER BY TSTGUID ', @P3 output, @P4 output, @P5 output, '00000000-0000-0000-0000-00000000000 '
select @P1, @P2, @P3, @P4, @P5

Server: Msg 245, Level 16, State 1, Line 2
Syntax error converting the varchar value '00000000-0000-0000-0000-00000000000' to a column of data type int.

Is it correct that the @P1 gets defined as int should it not be Char? If I change it from int to char it I get the error :
Server: Msg 16902, Level 16, State 29, Procedure sp_cursorprepexec, Line 11
sp_cursorprepexec: The value of parameter 'cursor' is invalid.

I am working on option 2 but the alternate key is not 100% of the time unique and I need to do updates on that selected data too so that will cause problems later on.

[Legacy User]

Hi Kruger,

Sorry, there is a slight mistake in my post above - I'm missing the final 0 from the literal value - it should be 00000000-0000-0000-0000-000000000000. It's 36 characters in length 8 zeros, dash, 4 zeros, dash, 4 zeros, dash , 4 zeros, dash 12 zeroes.

The @P1 you see as the first part of the SQL that Plex executes is an int - it is used to store the handle of the cursor, which Plex then uses to read the results from the cursor.

The third parameter of sp_cursorprepexec defines the parameters of the query, which is the forth paramter. The first parameter of the query is also called @P1 but is defined as char(36) - it is independent of the paramters for preparing the cursor.

If you execute the follwoing SQL in Managment Studio, you will get the Conversion failed message.

[color=#2A14F3]SELECT TSTGUID, NUM FROM dbo.P63llV WHERE ( ( TSTGUID >= '0') ORDER BY TSTGUID[color]

The following query should work for you

[color=#2A14F3]SELECT TSTGUID, NUM FROM dbo.P63llV WHERE ( ( TSTGUID >= '00000000-0000-0000-0000-000000000000') ORDER BY TSTGUID[color]

I've attached an XML export of my test model for you to take a look at. It's Plex 6.1. This all genned and built fine. The table I built outside of Plex - see attached CreateGUIDTable.txt (.sql files are not allowed), and populated with a few values.

Regards,
Jeremy Hutchinson
Desynit Limited
:: Plex Wiki :: 2E Wiki ::
:: Plex/2E Feed Aggregator ::
:: Edge Forum Archive ::

Attachment(s)

CreateGUIDTable.txt   722 B 1 version

Export.xml   19 KB 1 version

[KrugerHenning]

Hi ha!!!! Its working....!!!

Thanx a million. Don't worry about the mistake, I should have checked. :-)