Best practice question .. DB View or SC Query?

Scriptcase 8 Discussion
#1

Using a database view, the grid always works - but I’ve found that views do not transfer from one db to another - the view has to be recreated in the new database and the application has to be recreated to work with it (maybe there is something in mySQL I need to do so that it doesn’t care what database I’m using?).

So then I discovered that i can use a query in scriptcase - instead of selecting a table - to build a grid (see example below). But sometimes when I run the grid, it doesn’t work - I get an error that it cannot access the db. The next time I run it, it is fine (with no changes to the query).

I have also tried building the grid based on the table (sec_users_address) that joins the other tables (sec_users & address); the issue I run into there is that I cannot align columns or display a grid mask for the fields in the sec_users or address tables - only the fields in sec_users_address.

My thought is to build multiple demo projects (modules) and then use those to build a production project.

So my question is - how do others do it? Use a DB View or SC Query?

So my Query looks something like this (combining 3 tables; address, sec_users, and sec_users_address)

SELECT
address.loc_id,
sec_users_address.loc_id,
sec_users_address.login,
sec_users.login,
sec_users_address.user_type,
sec_users.name,
sec_users.entity_type,
sec_users.user_type,
sec_users.email,
sec_users.mobile_phone,
sec_users.active,
sec_users.last_login
FROM
sec_users_address LEFT OUTER JOIN address ON sec_users_address.loc_id = address.loc_id
LEFT OUTER JOIN sec_users ON sec_users_address.login = sec_users.login
ORDER BY
sec_users_address.user_type, sec_users_address.login
WHERE loc_id = ‘[this_loc]’

#2

Well, I usually use a view when the query is too many complex, sometimes complex querys causes execution errors in the app, also view provides some security, as you can hide columns, and the reusabilty, it helps for maintaining code managable.

#3

All of the same reasons that I was building apps using views… but when I copy the database to a new db the view is broken and has to be recreated to work with the new database. In and of itself - not a big deal. But then I copy the application and attempt to run it and get this error:
Fatal error: Cannot use isset() on the result of an expression (you can use “null !== expression” instead) in /(path to my application) on line 1032

The only solution I’ve found is to also recreate the application - which is sometimes a big deal.

How do you do it? (Copy a view to another database and use an application that was created from the original view) I must be missing something.

#4

After numerous hours figuring this out … the issue was that I was COPYING the application from Project A to Project B. The solution (to get rid of the “Fatal Error” message - and not have to recreate the application in Project B is DO NOT COPY AN APPLICATION FROM ONE PROJECT TO ANOTHER!!!

THIS IS THE SOLUTION:
Create the application in Project A
EXPORT the application
Open Project B
IMPORT the application.

THIS IS WHY I LIKE A VIEW BETTER THAN QUERY [LIST=1]

  • Provides some security - users cannot edit a view
  • Resusability
  • Maintain Manageable code
  • The grid always works - A complex query may cause (random) execution error
  • Can sort all fields [/LIST] THE BIGGEST ISSUE I HAVE WITH A VIEW: The view belongs to the database - you have to recreate the view in database B
    • #5

    If it works sometime and not other times, would it be possible set a SESSION variables?

    Just a thought…

    #6

    Thank for sharing Betty.