postgres table query not getting all names

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

postgres table query not getting all names

Mark Wieder via use-livecode
Sometime  ago, I wrote my own getTableNames() because
revDatabaseTableNames() would consistently fail to find them all.

Now, it to is filing.

It is, in essence,

*put* "SELECT table_name FROM information_schema.tables ;" \

& cr into dcmd

*put* revDataFromQuery(,cr,db,dcmd) into tabList

*return* tabList

I have another chunk of code which is now acting up:

*put* getTableNames(dhbkSrvrDb) into dhSrvTbls

*put* "dhStatTbl" into dhtbl_stat

*if* dhtbl_stat is not among the words of dhSrvTbls *then*

*put* "CREATE TABLE " & dhtbl_stat  . . . blah blah blah


table dhStatTbl already exists, yet the conditional is reached.  It
executes, and throws an error because this table is already there.


This is, to be mild, a brutal bug.

Might there be something I'm missing?

--
Dr. Richard E. Hawkins, Esq.
(702) 508-8462
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgres table query not getting all names

Mark Wieder via use-livecode
On 07/14/2017 03:48 PM, Dr. Hawkins via use-livecode wrote:

> Sometime  ago, I wrote my own getTableNames() because
> revDatabaseTableNames() would consistently fail to find them all.
>
> Now, it to is filing.
>
> It is, in essence,
>
> *put* "SELECT table_name FROM information_schema.tables ;" \
>
> & cr into dcmd
>
> *put* revDataFromQuery(,cr,db,dcmd) into tabList
>
> *return* tabList
>
> I have another chunk of code which is now acting up:
>
> *put* getTableNames(dhbkSrvrDb) into dhSrvTbls
>
> *put* "dhStatTbl" into dhtbl_stat
>
> *if* dhtbl_stat is not among the words of dhSrvTbls *then*
>
> *put* "CREATE TABLE " & dhtbl_stat  . . . blah blah blah
>
>
> table dhStatTbl already exists, yet the conditional is reached.  It
> executes, and throws an error because this table is already there.
>
>
> This is, to be mild, a brutal bug.
>
> Might there be something I'm missing?
>

Just a guess, but the concept of 'word' is pretty slippery.
You might try 'true words' or make a comma-separated list and look among
the items or...

--
  Mark Wieder
  [hidden email]

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgres table query not getting all names

Mark Wieder via use-livecode
If there is a built in function for getting the schema of the particular database, I would use that. For instance you can query sqLite master database for a list of tables using:

SELECT * FROM my_db.sqlite_master

mySQL can be queried using SHOW TABLES

Googling this indicates that for postgres the syntax is:

SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE';

Bob S

> On Jul 14, 2017, at 16:13 , Mark Wieder via use-livecode <[hidden email]> wrote:
>
> On 07/14/2017 03:48 PM, Dr. Hawkins via use-livecode wrote:
>> Sometime  ago, I wrote my own getTableNames() because
>> revDatabaseTableNames() would consistently fail to find them all.
>> Now, it to is filing.
>> It is, in essence,
>> *put* "SELECT table_name FROM information_schema.tables ;" \
>> & cr into dcmd
>> *put* revDataFromQuery(,cr,db,dcmd) into tabList
>> *return* tabList
>> I have another chunk of code which is now acting up:
>> *put* getTableNames(dhbkSrvrDb) into dhSrvTbls
>> *put* "dhStatTbl" into dhtbl_stat
>> *if* dhtbl_stat is not among the words of dhSrvTbls *then*
>> *put* "CREATE TABLE " & dhtbl_stat  . . . blah blah blah
>> table dhStatTbl already exists, yet the conditional is reached.  It
>> executes, and throws an error because this table is already there.
>> This is, to be mild, a brutal bug.
>> Might there be something I'm missing?
>
> Just a guess, but the concept of 'word' is pretty slippery.
> You might try 'true words' or make a comma-separated list and look among the items or...
>
> --
> Mark Wieder
> [hidden email]
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgres table query not getting all names

Mark Wieder via use-livecode
On Sun, Jul 16, 2017 at 12:25 PM, Bob Sneidar via use-livecode <
[hidden email]> wrote:

> If there is a built in function for getting the schema of the particular
> database, I would use that. For instance you can query sqLite master
> database for a list of tables using:
>

That's where I started, but getTableNames() frequently missed tables.
Adding my own query ws a self-defense.

I don't remember whether I reported this and the mothership couldn't
reproduce, or whether it clearly couldn't be reproduced.   Iirc, it only
affected postgres, and that my own function uses getTableNames() for mySQL

There is no possibility of allowing access to my database; confidential
information from far too many people.
--
Dr. Richard E. Hawkins, Esq.
(702) 508-8462
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Loading...