tables added to database not visible from other substacks

[dochawk]

This behavior is coming and going, and I've seen it in other contexts, too.

Reducing for the sake of complexity . . .

Substack "Control" sends "Main" stack a message to open the db.

Main opens the db, and uses a routine in its own stack script to setTheDb to the returned access control, storing this as a global variable theDb, declared in Main's script before any routines. As part of the opening routine within Main, it checks to make sure that both tables A and B exist. In this case, it adds table B with a CREATE TABLE query. revDatabaseColumnNames() returns both A & B.

Substack "Manipulate" then adds things to table B. It uses getTheDb() from Main's script to get the id of the script, which matches the number upon opening.

Substack "Output" then uses getTheDb(), and sees the same number. Then things turn weird.

revDatabaseColumnNames() now shows only column A, and revDataFromQuery() returns errors of "no such table" for table B.

If I go back and run the opening routine in Main, it still sees both tables; return to Output, and there is still only A.

I'm using the internal SQLite if this matters.

Is there some step to "set" the CREATE TABLE that I'm missing?

I really don't understand how it can answer differently depending upon which substack I'm in . . .

Thanks

hawk

[Mark]

hawk,

Where and how do you specify the path to the database? This might be the cause of the problem.

Kind regards,

Mark

[dochawk]

I have a

Code: Select all

 put "/Users/hawk/dhbk/bk_clients/"  into tDatabasePath
  

in openStack for the main stack, and then, again in that stack,

Code: Select all

on databaseConnect theDB
   
   ## Open a connection to the database
   ## If the database does not already exist it will be created
   --internal SQLite assumed for the moment; add others later YYY
   
   put revOpenDatabase("sqlite", tDatabasePath &  theDB & ".sqlite"  , , , , ) into rcvdDB


   setDebtorDB rcvdDB
  

(this is actual code; not the snippet above, where theDB is a number rather than text (here, the passed text is "plumber").

My problem isn't that the other substacks can't use the db at all, but that it only sees the older table, and not the added table>

After this snippet, everything uses either the global variable debtorDB (if in the main stack), or getDebtorDb() (if in a substack, and a couple of times in routines of the mainstack.)

thanks, hawk

[shaosean]

Perhaps try forcing a commit?

Code: Select all

revExecuteSQL databaseID, "COMMIT"

[phaworth]

Hi,
'This sounds really weird, I agree!

Where is tdatabasepath declared? Looks like you set it in openCard, but reference it in you connect routine. With SQLite, if the file you name doesn't exist, it will be created as an empty database (I hate that!)

Also be aware that an openStack handler in your main stack will be executed every time a substack is opened unless the substack also has an openStack handler. Best to move the openStack handler to the first card of your main stack to avoid any issues like that.

I can't tell exactly why, but it seems like you are opening the db more than once and the connection on which you create table B is not the same connection that you get in the substacks.

Pete

I have a

Code: Select all

 put "/Users/hawk/dhbk/bk_clients/"  into tDatabasePath
  

in openStack for the main stack, and then, again in that stack,

Code: Select all

on databaseConnect theDB
   
   ## Open a connection to the database
   ## If the database does not already exist it will be created
   --internal SQLite assumed for the moment; add others later YYY
   
   put revOpenDatabase("sqlite", tDatabasePath &  theDB & ".sqlite"  , , , , ) into rcvdDB


   setDebtorDB rcvdDB
  

(this is actual code; not the snippet above, where theDB is a number rather than text (here, the passed text is "plumber").

My problem isn't that the other substacks can't use the db at all, but that it only sees the older table, and not the added table>

After this snippet, everything uses either the global variable debtorDB (if in the main stack), or getDebtorDb() (if in a substack, and a couple of times in routines of the mainstack.)

thanks, hawk

[Mark]

I can't tell exactly why, but it seems like you are opening the db more than once and the connection on which you create table B is not the same connection that you get in the substacks.

That's why I suggested double-checking the path.

Kind regards,

Mark

[phaworth]

Sorry Mark, didn't see your post until after I replied.

I can only think of two scenarios that make logical sense, both involving opening the db more than once.

The first one is that Table B is created as a temporary table in connection 1. Table B would not then be visible to connection 2.

The other scenario is that somehow or other two different databases are being opened, probably, as you say, because of different paths being specified.

Pete

[Mark]

Hi Pete,

I bet it is the second scenario. Maybe hawk forgot to declare a variable as global.

Best,

Mark

[dochawk]

OK, I've whittled the stack down to almost nothing , and can still reproduce the behavior.

After the stack opens, click the "open cook" button in the t_file_control stack, and it begins single stepping. Step into databaseConnect, and step over all the "ck" commands, which toss to the output window (unless you're really bored

After crdna is called, ck5 will report that both fields dinfo and dna exist, while if you type "revDatabaseTableNames(debtordb)" into the message box, only dna is found.

I've attached in both gz and bz2 format.

thankful but baffled,

hawk

[phaworth]

Hi,
I'd love to take a look at this but after decompressing either file you attached, I get a corrupted stack file message from Livecode.

I'm on a Mac - maybe those formats aren't compatible with OS X?

Pete

[phaworth]

Maybe this will be revealed when I can take a look at your stack but I'm curious as to why you are adding a column every time you run the program? Normally, you would create a database with a utility outside your application in the format that you need and then open it in your application without having to worry if it's complete or not. Or perhaps you're using Livecode to create the database?
Pete

[dochawk]

Hi,
I'd love to take a look at this but after decompressing either file you attached, I get a corrupted stack file message from Livecode.

I've put the raw stack at: dochawk.org/test.120604b.rev

I'm on a Mac - maybe those formats aren't compatible with OS X?

I hope not; I'm using lion on an iMac

thanks

hawk

[dochawk]

Or perhaps you're using Livecode to create the database?
Pete

This.

These are the two main tables for a client file. I can't even see one long enough to work with the file!

thanks

hawk

[phaworth]

Figured out the problme - it's a 5.5 stack and I was trying to open it with 5.0. I have it now and will take a look.
Pete

[phaworth]

Hi hawk,
Well, not sure what I'm doing different than you but everything looks fine to me.

I had to change tDatabasePath to be valid on my computer but after that the outwin field lists both tables each time you put something in to it. I looked at both databases in another program and they both had both tables in them.

One problem may be that you are not declaring your globals in all your scripts. I see them in the stack script of your main stack but I don't see them declared anywhere else. That might be OK if you only reference them in handlers in your main script, but if you try to access them from any other scripts, there'll be problems because the script won't know anything about them.

For example, trying to execute revDatabaseTablenames(debtordb) in the message box returned an error because debotordb wasn't available to it as a global, but if I put revDatabaseDebtorDB(1) into the message box, it listed both your tables.

----Edit------
If I type "put revDatabaseTablenames(getdebtorDB())" into the message box, it displays both tables.
--------------

I also looked at both databases you create with an external program and they both contained both tables, so I'm not sure why you think they only have one table in them.

I still think you shouldn't be dealing with the structure of your database in your application. There are plenty of utilities out there that will create an sqlite database for you so all you have to do is open it in your application. I'll declare a vested interest in that statement because I sell such a utility. It's called SQLiteAdmin and you can find it at http://www.lcsql.com. There's a 30-day trial available for download. Some of the functionality is not available in the demo version but if you open your database(s) with it, you'll see that both tables are there and you will be able to add other tables and/or columns if you need to.

Hope that helps,

Pete