INSERT not Working in Database Update Tool

 
1
0
-1

I am currently trying INSERT a row into a table using Database Update Tool and it isn't working for me. Please see my insert statement below......


INSERT INTO app_fd_j_inv_master
(
    c_CategoryId, c_Description, c_LocationId, c_UnitMeasurement, 

    c_QuantityBalance, c_AverageCost, c_TotalValue, c_Enteredby
)
SELECT
    c_CategoryList, c_Item, c_TransLocationTo, null,

    c_QuantityToTrans, 0, 0, #currentUser.firstName# #currentUser.lasstName#
FROM app_fd_j_inv_transfer



Thank for you help in advance


    CommentAdd your comment...

    3 answers

    1.  
      1
      0
      -1

      Hi Briant Fletcher

      Attached is a sample app using the Database Update Tool to insert a record in the process tool but I could not replicate your issue.

      Kindly try this app and you may modify it to be as close to your current app, to try to repeat the error you are experiencing. Then attach your app here for the community to view/check. Thank you.

      APP_68354413_process_database_update_tool.jwa

      1. Briant Fletcher

        Hi Matthew King,

        I have looked at app and imported it. It works fine. I looked at my app and made changes to it to mirror yours and my INSERT still isn't working.

        In my app I'm updating one record while inserting another. I have separated the processes for updating and one for inserting. The Update works, however, the INSERT still is not working.

        I have attached my app here, for you to look at.

        APP_jinventory-3-20191017132107.jwa

        Thank you

      2. Matthew King

        Hi Briant Fletcher

        I checked your INSERT query and there are some errors in your SQL.

        For example:

        • There is no field called "c_TransLocationTo" in your database, I think you meant to type "c_TranLocationTo".
        • In addition, there is no field called "c_modifiedby", it is "modifiedBy" (smile).

        As I was running the Joget v6 on premise edition (Windows), after importing your app, I could use a WIndows database utility SQLyog to test the SQL script's validity. SQLyog will immediately show you the SQL syntax errors when you try to execute the SQL script. BTW the on premise edition of Joget will also present you with a joget.log which is useful for troubleshoot your app. MySQL has a similar Workbench utility for database management.

        Below is the full list of columns in your database for the table app_fd_j_inv_transfer for your reference:

        CREATE TABLE `app_fd_j_inv_transfer` (
`id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`dateCreated` datetime DEFAULT NULL,
`dateModified` datetime DEFAULT NULL,
`createdBy` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`createdByName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`modifiedBy` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`modifiedByName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`c_TranLocationTo` longtext COLLATE utf8_unicode_ci DEFAULT NULL,
`c_Item` longtext COLLATE utf8_unicode_ci DEFAULT NULL,
`c_Unit_ID` longtext COLLATE utf8_unicode_ci DEFAULT NULL,
`c_CategoryList` longtext COLLATE utf8_unicode_ci DEFAULT NULL,
`c_ItemTrans` longtext COLLATE utf8_unicode_ci DEFAULT NULL,
`c_TransferAction` longtext COLLATE utf8_unicode_ci DEFAULT NULL,
`c_TransLocationForm` longtext COLLATE utf8_unicode_ci DEFAULT NULL,
`c_TransferDate` longtext COLLATE utf8_unicode_ci DEFAULT NULL,
`c_QuantityToTrans` longtext COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

        Hope this helps.

        Thank you.

      3. Briant Fletcher

        It does and I understand totally, but I didn't know I can link the cloud version to workbench.

        I made the changes and I now it's working. Thank you

        One other thing how do I the get the id to insert as the format that is in the Table already?


        Thank ou

      4. Briant Fletcher

        How do I get it see the columns in the table in the database?

      CommentAdd your comment...
    2.  
      1
      0
      -1

      I think i figure out why. You insert data to a form data table. The id column of a form data table cannot be NULL value. You may find the error shown in the log file. Try with the following and see.

      INSERT INTO app_fd_j_inv_master
(
  id,
  c_CategoryId,
  c_Description,
  c_LocationId,
  c_QuantityBalance
)
SELECT
  uuid(),
  c_CategoryList,
  c_Item,
  c_TransLocationTo,
  c_QuantityToTrans
FROM app_fd_j_inv_transfer



      1. Briant Fletcher

        Just tried this and No Luck. Still not inserting into the table.

      CommentAdd your comment...
    3.  
      1
      0
      -1

      I think the problem is with the way you use Hash Variable. The 2 hash variable you used will become 2 unknown field to the query. You can try with the following by adding single quote to it.

      INSERT INTO app_fd_j_inv_master
(
    c_CategoryId, c_Description, c_LocationId, c_UnitMeasurement, 

    c_QuantityBalance, c_AverageCost, c_TotalValue, c_Enteredby
)
SELECT
    c_CategoryList, c_Item, c_TransLocationTo, null,

    c_QuantityToTrans, 0, 0, '#currentUser.firstName# #currentUser.lasstName#'
FROM app_fd_j_inv_transfer
      1. Briant Fletcher

        Yes, I had thought about that and actually took it out.

        INSERT INTO app_fd_j_inv_master
        (
          c_CategoryId,
          c_Description,
          c_LocationId,
          c_QuantityBalance
        )
        SELECT
          c_CategoryList,
          c_Item,
          c_TransLocationTo,
          c_QuantityToTrans
        FROM app_fd_j_inv_transfer


        And it still isn't working. I'm currently stumped.

        Question....

        If I have a Database Update Tool plugin that does an update in the same process would that affect it? The update is done before.

        Should break it apart in 2 different processes instead? 

      2. Briant Fletcher

        Ok, just tried separating the 2 and the INSERT still doesn't work...

      CommentAdd your comment...