API_UUIDCREATE() and Sql Server

[harrison4411]

I have a grid that is not saving the GUID into SQL Server 2008 table. The field is called PrimaryID with Data Type uniqueidentifier.

The Column Properties has Default Value is set with (newid()) and property RowGuid is set to Yes.

My grid is updateable with one row to insert new record. The PrimaryID field has no Initial value set. I am setting this in the Server-side Events: CanInsertRecord with this code: DataSubmitted.PrimaryID = API_UUIDCREATE()

It works fine on my local machine. Adding several records with no errors.

I publish app on the WAS server okay. When I add new data and click Submit I receive Error message stating: "Record was not inserted ... Function requested was not supported".

Please help with saving GUID record ID.

Thanks.

[Steve Wood]

Re: API_UUIDCREATE() and Sql Server

The field type in the database would be a CHAR size 40 with NO formula of any kind. It can and should be the PK but just a normal CHAR.

If you use remspecial(api_uuidcreate()) it is a little cleaner, size 32 with no dashes.

[harrison4411]

Re: API_UUIDCREATE() and Sql Server

So you saying to only change the table structure ?

[Steve Wood]

Re: API_UUIDCREATE() and Sql Server

Yes. From what you are saying, you are generating a GUID in Alpha and populating the table with that GUID. But you also seem to be setting the table to also generate a GUID. I don't know what newid() and RowGuid are, but they sound like GUID generation formula. An Alpha-generated GUID is just a character value.

[harrison4411]

Re: API_UUIDCREATE() and Sql Server

See attached Column Properties

GUID_1.JPG

[Steve Wood]

Re: API_UUIDCREATE() and Sql Server

OK, that is fine but you still have to pick which method you are going to use to create a GUID. You are currently setting a GUID using newid() and then trying to shove a text value into the same filed using DataSubmitted.PrimaryID = API_UUIDCREATE().

[GGandhi]

Re: API_UUIDCREATE() and Sql Server

See attached Column Properties

[ATTACH=CONFIG]39752[/ATTACH]

take a look at your attached image, the size of the column is 16 not compatible with uuid from alpha, api_uuidcreate() function creates 40 char field when you remove the hyphens it becomes 32.
you should follow what steve said: create char field in sqlserver and let alpha create the id to populate it. you can keep the hyphens or remove them, when you remove it looks better, not sure they work better.

[harrison4411]

Re: API_UUIDCREATE() and Sql Server

My need understanding why the app works running off my desktop that uses same SQL server table. Note attached pictures and records created. Picture GUID_2 is using localhost:8011/LivePreview show record can be added.
Attachments GUID_3 and GUID_MSG are from browser running deployed grid.
I don't understand why it doesn't work from production.

GUID_msg.JPG GUID_3.JPG GUID_2.JPG

[Steve Wood]

Re: API_UUIDCREATE() and Sql Server

I don't use MSSQL, but in my few seconds of research, your values in your screenshots are all from using RowGrid + uniqueidentifier + newid().

If that works for you, then omit your code in CanInsertRecord.

Here is an article on the subject (which both supports and denounces using RowGrid: http://sqlservernewbie.blogspot.com/...6/rowguid.html

I don't use MSSQL because frankly it confuses the heck out of me compared to MYSQL/MariaDB.

[JerryBrightbill]

Re: API_UUIDCREATE() and Sql Server

Your error may have nothing to do with the data type or the function used to create the primary key value. If it did, the error would be in the xbasic code and would never attempt to INSERT the record.

If the error is coming from SQL Server (which is likely from the message), that indicates the machine with the Alpha Anywhere app server may not be using the correct SQL Server Native Client. SQL Server 2008 would used Native Client 10, while SQL Server 2012 and 2014 use Native Client 11. If you have the wrong driver installed on the machine, you may get the function not supported error.

The documentation gives some info on this under the topic "How to Connect to SQL Server from 64-bit Windows". You can also search the Alpha Anywhere release notes for "Native Client" for information. Microsoft has information on the latest drivers at

http://www.microsoft.com/en-us/downl....aspx?id=36434

If using a uniqueidentifer in SQL Server, you may need to pass in a guid type in some contexts, not character.

The function *guid_create() will create a variable of the type needed, which is identified as a "k" type in Alpha Anywhere. You can useAPI_UUIDCREATE() when passing in as character. Since you are using "DataSubmitted", that is always character so DataSubmitted.PrimaryID = API_UUIDCREATE() is appropriate. You can als0 use convert_type to change types, and the function isNonNullGuid() to see if the value is a value uniqueidentifier. That function accepts "K" or "C" type values.

?*guid_create()
= "{9d75f4b8-a776-4ba7-823b-c53ed2be22f6}"

?typeof(*guid_create())
= "K"

?API_UUIDCREATE()
= "144affaf-dc07-4e06-83ae-5e78289dcc62"

?typeof(API_UUIDCREATE())
= "C"

newGuid = convert_type(API_UUIDCREATE(),"K")
?typeof(newGuid)
= "K"
?NewGuid
= "{c11fb3b9-86c9-472d-af59-6811fb34ef3e}"

NewGuidC = convert_type(*guid_create(),"C")
?typeof(newGuidc)
= "C"

?newGuidC
= "{add9e5ef-0919-486b-89fa-a648bd8bad67}"

?isnonnullguid(*guid_create())
= .T.

?isnonnullguid(API_UUIDCREATE())
= .T.

[harrison4411]

Re: API_UUIDCREATE() and Sql Server

Jerry,
Right now I am testing and deploying on same server. So Native SQL would be same driver since both WAS and Desktop versions are same: Build 3381 Addins: 4096 and both hit the same SQL 2008 Server. Yes ?

Also I can make updates and have them saved without errors from both Local and productions browser.

Is there a way you want me to check the server version and the desktop SQL version ?

This is connection string:
{A5API='SQLServer',A5DBVersion=2008,Server='Eggroll',A5ANSINullPadWarn=Default,UserName='Harrison',Password='��������',Database='BloodTracker'}

[harrison4411]

Re: API_UUIDCREATE() and Sql Server

Jerry, Thanks for the instructions. Yes the drive was 6.0 and not Native SQL 10 or above. I download ODBC and Native drive. Tested and back in the game.

Thanks VERY MUCH. :)

Attached Files: Server ODBC and Workstation ODBC driver tab.


odbc_Server.JPG odbc_workstation.jpg