2. PHPRunner General questions
  3. Table not found?
This topic is locked

Table not found?

 		5/2/2023 6:46:00 PM
PHPRunner General questions
author

Hello All,
With regards to the SaaS article: https://xlinesoft.com/blog/2019/10/03/saas-application-design/
After changing those two lines of code, updating the user records with the appropriate database names (saas001 and saas002).
In both cases this returned the following error:
`Technical information
Error type 256
Error description ERROR: relation "public.campaign" does not exist LINE 3: FROM "public"."campaign" ) a ^
URL localhost/campaign_list.php?
Error file C:\Users\craig\Projects\PHPrunner\dnd_saasdb\output\connections\Connection.php
Error line 668
SQL query select count(*) from (SELECT "campaign_id", "campaign_nm", "campaign_descr_txt", "active_ind", "create_ts", "update_ts", "eff_start_dt", "eff_end_dt", "show_to_players_ind" FROM "public"."campaign" ) a

Call stack
File: line Function Arguments

0 connections\Connection.php:668 Connection->triggerError 0. ERROR: relation "public.campaign" does not exist LINE 3: FROM "public"."campaign" ) a ^

1 connections\PostgreConnection.php:104 PostgreConnection->query 0. select count(*) from (SELECT "campaign_id", "campaign_nm", "campaign_descr_txt", "active_ind", "create_ts", "update_ts", "eff_start_dt", "eff_end_dt", "show_to_players_ind" FROM "public"."campaign" ) a

2 connections\Connection.php:635 Connection->getFetchedRowsNumber 0. SELECT "campaign_id", "campaign_nm", "campaign_descr_txt", "active_ind", "create_ts", "update_ts", "eff_start_dt", "eff_end_dt", "show_to_players_ind" FROM "public"."campaign"

3 classes\datasource\table.php:621 DataSourceTable->getCount 0. DsCommand

4 classes\listpage.php:2799 ListPage->calculateRecordCount N/A

5 classes\listpage.php:2204 ListPage->prepareForBuildPage N/A

6 campaign_list.php:158 Global scope N/A`Based on this I believe that the code cannot find the table.

How do I make sure the code finds the table(s) that do exist?
Thanks,
Craigbert
W
 		wedi 5/3/2023

Hi Craigbert,
does your select works if you run it directly in the database?
Another question: why do you determine count() not with
` select count() from public.campaign?`And the error descripts: relation "public.campaign" instead of "public"."campaing". Is the select only a part of a greater select?
Regards,
wedi
502108 5/3/2023

Hi wedi,
Thanks for the response.
That is all 100% generated code. I have not modified any of it.
So it is possible that the double quotes could be what is wrong. I have not tested running that <em>exact</em> SQL locally.
As for running SQL directly I have no issues. For the record it is a PostgreSQL database.
Is there someplace I could go in and adjust something like that?
Thanks,
Craigbert
Admin 5/3/2023

I recommend to run the following query manually against your database to see what might be wrong with it.
select count(*) from (SELECT "campaign_id", "campaign_nm", "campaign_descr_txt", "active_ind", "create_ts", "update_ts", "eff_start_dt", "eff_end_dt", "show_to_players_ind" FROM "public"."campaign" ) a
502108 5/3/2023

Hi admin,
I will do that later today or early this evening as I am away from my dev workstation today.
As a side note that is an interesting way to get a record count though...sub-selects have their place and use, but why would the generator do that just to get a record count? Why not just: SELECT COUNT(*) FROM TABLE_NAME?
On smaller tables the performance impact would not be noticable, but as the table(s) grow in size it would absolutely start making a difference.
And why would everything be quoted? That is not a normal Postgres "thing". Generally you only need to quote if there is something special about the name. For example if it is, heaven forbid, because the object is using a reserved keyword or if a distinction has to be made for a case sensitive object. For example, Postgres treats customer and CUSTOMER as the same object (i.e. it is case insensitive without the quotes. But it considers "customer" a different object from "CUSTOMER".
It feels like this approach is carry over from some other DBMS. Not judging, more of just an observation. {shrug} :-)
502108 5/3/2023

Hi admin,
I was able to grab a quick Postgresql instance (online) and I created the table, inserted three records and ran the query above. It works without any issues.
This makes me think that the code / app "thinks" the primary database (the one with only the user table) instead of switching to a different database to run this query.
Any other thoughts about what I should try to get this working?
Thanks,
Craigbert
502108 5/3/2023

...and I can confirm now that the SQL works on the instance that I am <em>actually</em> using and working with.