locked
When using Oracle, a table with PK, Lightswitch still asks for the ID in add edit screen. Why ? RRS feed

  • Question

  • I have an oracle table with an ID as PK. And the version I use for oracle doesn't have auto generated columns as in SQL server.so Id is generated via a trigger.  I have attached this table to Lightswitch successfully , and it recognizes my ID as Primary Key.So far so good. However when I generate an Add Edit Screen for this table, despite of ID being primary key, the form asks it explicitly. If I do the same setup for sql server, this field is not generated for screen. What could be wrong ?

    Friday, August 29, 2014 3:32 PM

Answers

  • Hi,

    I was able to solve the problem. The issue is since oracle doesn't have auto generated columns with versions less than 12, a common practice is to use trigger/sequence combo. However Lightswitch doesn't understand this when you import the table and within the lmsl file generated for the table, it simply ignores this fact.

    What I did is, I edit the lmsl file for the table and added ReadOnly and hidden attributes manually.

    Now everything works smoothly with oracle. I suggest if lightswitch team reads this to add this feature. Since I believe it is a low hanging fruit to fix.

    • Marked as answer by onurg Monday, September 1, 2014 9:13 AM
    Monday, September 1, 2014 9:02 AM

All replies

  • Hi,

    I think you did nothing wrong,as the LightSwitch technology was designed with Microsoft SQL Server in mind, support for Visual Studio LightSwitch in Oracle (and other) data providers has some certain limitations.

    Link below also talked about connecting Visual Studio LightSwitch to Oracle and also need to enter pk,please have a see:

    http://www.devart.com/dotconnect/oracle/articles/lightswitch-oracle.html

    Besides, I would like to recommend you to use Microsoft SQL Server for better compatibility if you don't mind.

    Best Regards,
    Qimin Ding


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.




    Monday, September 1, 2014 6:43 AM
  • Hi,

    I was able to solve the problem. The issue is since oracle doesn't have auto generated columns with versions less than 12, a common practice is to use trigger/sequence combo. However Lightswitch doesn't understand this when you import the table and within the lmsl file generated for the table, it simply ignores this fact.

    What I did is, I edit the lmsl file for the table and added ReadOnly and hidden attributes manually.

    Now everything works smoothly with oracle. I suggest if lightswitch team reads this to add this feature. Since I believe it is a low hanging fruit to fix.

    • Marked as answer by onurg Monday, September 1, 2014 9:13 AM
    Monday, September 1, 2014 9:02 AM
  • Thanks for sharing your workaround.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.




    Monday, September 1, 2014 9:08 AM
  • hi onurg

    I have the same problem, and would like to know exactly how I try to solve using your workaround.  Any chance of a step-by-step guide?  I have no idea how to set the readonly attribute of a column for a table in the lmsl.

    many thanks

    Tuesday, October 27, 2015 4:43 AM
  • Hello davoroy,

    Unfortunately I don't have time to prepare a step by step guide. Since it has been quite a while I worked on this. But I can tell you this. Open the lmsl file with a text editor like notepad and edit the attributes I mentioned above. If you are not sure  you can follow the path that I do:

    Create a test project for SQL server with a simple table with PK. Open that lmsl with a text editor, if you compare it with oracle generated lmsl file, you will see for the SQL server there are extra attributes. you can easily add the same ReadOnly and hidden attributes in XML to Oracle generated lmsl file.

    I hope it helps.

    Tuesday, October 27, 2015 9:13 PM
  • great stuff!  thanks so much for this, very much appreciated.
    Tuesday, October 27, 2015 10:16 PM
  • Wish I found this post earlier as I did another work around which is more involved.

    When I setup the trigger in Oracle, in addition to the 'when is null' statement I added a 'when is "0"' case.

    FOR EACH ROW   WHEN (new.MY_PK is NULL OR new.MY_PK = '0')


    In LS, I set the _postrender event to check if the value is null and replace with zero if it is.

    It works, but like I said, onurg's method seems cleaner. Either method requires some initial setup.

    Thursday, May 5, 2016 9:28 PM