ODBC to SQL server 2005 crashes livecode

[Steve Denney]

Hi, I've got an ordering app that fills in an order form (customer & product details) from a database.
Users can specify which columns and tables they want to see & how they're to be sorted and search them. And click on a row to do more.
Anyway, it's been working well with MS access.
Now I need it to work with MS SQL (I've been told there are 3 clients 'ready to go' --meaning I really really need this to work & can't just keep using Access).
I've put the company database into SQL express 2005 for test purposes.
At first everything looked peachy, the app connected, the database tables appeared, but then the trouble started...
It seems that single SQL queries work fine.
A repeat loop with 5 or so iterations usually worked, i.e.
SELECT [column_name] FROM [table] ORDER BY [ID]
(then put the result into a field).

Occasionally it would generate a revDB error "Connection is busy with results for another command"
I tried to fix this by explicitly opening and closing the database for each and every sql query thus (for example):

repeat for each line colName in colsToViewList
dbConnect -- connects to db
put revDataFromQuery(,,dataBaseID,tSQLQuery) into fld fldName
revCloseDatabase dataBaseID
end repeat

This made the appearance of the "Connection is busy with results for another command" error rare but made the app noticeably slower.

But there's another problem.
Livecode simply crashes.
Back to the desktop, all gone.

This happens most commonly when the number of loops is around 10 or more. There are 30+ columns in the customer database. Users can choose to display them all. Sometimes (rarely) the app will survive a thirty iteration loop, almost always it crashes. Sometimes it seems to crash for no discernible reason.

None of this happens with ms access.

I'm using the SQL native client odbc driver.
I've looked into MARS (Multiple Active Result Sets) as this feature (disabled by default) is associated with the "Connection is busy" error. However I can't find any way to enable MARS --it requires a parameter added to the connection string along the lines of:
SQL Native Client ODBC Driver
Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;
Uid=myUsername;Pwd=myPassword;MARS_Connection=yes;

And I haven't been able to reconcile this with liveCode's:
revOpenDatabase("odbc",host[:port], databaseName, [userName],[password],[cursorType])

It's a memory issue? (Even though I open & close the database for each iteration). I haven't tried playing with the SQL Express memory settings (because I really don't know what I'm doing).

Any comments or suggestions very much appreciated...

Steve

[Steve Denney]

Hi, it’s still crashing. I got it to wait 30 ticks between each iteration and list revDatabaseCursors(databaseID) and revOpenDatabases() in the msg box.
revDatabaseCursors returns empty each time (as expected).
revOpenDatabases() jumps by two i.e. the databaseID is 4 then 6 then 8
This I don’t understand since I’m only accessing the database once per loop. It almost always crashes after the 11th loop. If not (and it completes up to 35 loops to display every database column) then it’s guaranteed to crash the next time it goes near the database.

When I say the app crashes I mean the IDE (not a built standalone).
Steve

[bangkok]

When I say the app crashes I mean the IDE (not a built standalone).
Steve

Hi Steve,

I work since 3 years with RunRev/liveCode and MySQL (via LiveCode driver) and MS SQL (via microsoft ODBC driver).

Me too i use loop to insert/update thousands of lines, every day.

I'have an app with MySQL with 50 PC on the local network. On Windows XP, Vista and Seven. I use also apps with remote MySQL servers(linux) (at least 2).

I've never experienced what you say. For me, the solution LiveCode/MySQL or MSSQL was so stable. This is why your message worries me.

Some of my scripts/loops can last 20 minutes... LiveCode just "freezes" the display, but that's it. The job is done, every day, smoothly.

You should tell us more about your OS for the server, the OS for the clients, the version of livecode, the version of mysql server etc.

As for the details, i think it's really, really not a good idea to open/close the DB after each query (!) within a loop.

Me I open/close for each handler or script.

Second question : when you say "SQL native client odbc driver.", what are your talking about exactly ? You're talking about the MySQL driver given with LiveCode ?

Last but not least, i'm wondering why you use loops... for a simple SELECT ?

And then, is it really your script :

Code: Select all

repeat for each line colName in colsToViewList
dbConnect -- connects to db
put revDataFromQuery(,,dataBaseID,tSQLQuery) into fld fldName
revCloseDatabase dataBaseID
end repeat

How and where do you make the tSQLQuery ?

It would be useful if you could post your real script.

[Steve Denney]

Thanks bangkok, I've been feeling physically sick over this.
The ordering app is both on the Boss' pc and the rep's tablets/laptops.

The main database is on the company server, which runs server 2008, the Boss' app uses this database.

However, each rep version of the app has its own local database. The reps need this because they're not always in contact with the home base server. The rep databases sync with the main database when they can (and rep changes are sent to home base).

The rep pcs (and the Boss') run windows 7 or xp.

We have a Unix website and a Mysql database too but that's not involved with this.

I don't know what the other client's setups are, but presumably they'll be similar.

So that's the overview.
Right now I need the app to work with a local database (both are on my pc). This is like the rep in the field. I’m not updating over the network (well the app does do that, but I can’t even show all the database in fields on a card without a crash).

I have 3.5 GB ram (2 x 2GB sticks)
Intell Pentium III xeon 2833.8 mhz
total physical memory 6144 MB
avail physical memory 2.47 GB
total virtual memory 2 GB
avail virtual memory 196 GB
page file space 5.34 GB
I’m running xp. I’ve installed sql server 2005 (the free express version). I have the configuration tools and SS management studio express.

Livecode (the app) is using the runrev odbc driver. I'm using liveCode 5.02
The crashes also occurred with the 5.5 dp1 (the latest). We've got a package with upgrades for the next few months.

At the database odbc sources (control panel), I chose SQL native client (instead of SQL server). Maybe I should give SQL server a try?

After the app starts I connect to the database ok. I can list the tables ok. I can click on a table and show all the columns in that table as checkboxes.

I can tick checkboxes. Those columns will show on an order page or a customer page (card). This can be saved as a default view. (Or a view that can be loaded at any time)
I can tick all 6 product columns. All appear on the order form page. I didn’t use data grid I made my own. You can scroll, search, change column widths. It looks a little like excel with an order form beside it. Works.
BUT when I choose the customer table and tick show all columns (there are 35) it crashes.

I’ve set it to see how many columns it gets through (this is the loop I talk about). After 11 it almost always crashes to the desktop. If it makes it all the way then it’s ok… Until I do something which talks to the database (like, show the tables). Then it crashes.

It acts like there’s a memory leak. It builds up and doesn’t go away. And when it gets to a certain size something breaks.

I tried closing the database after every query to fix this. It slowed down the app (bad). It did appear to help with the "Connection is busy with results for another command" error. It did not fix the crashing.
Honestly, I don’t think the problem is with my code. Well, maybe it is  Works fine with ms access though.
It’s a little complicated, here's another try at explaining:

Here is one place I can almost always make it crash...

0n addColView theStak, theTable, colsToViewList, applySort, primaryKey
--theStak is the stack where the view will be shown, it’s either customer view (full page) or order form (has order on page too).
--theTable, the chosen table name

--Users provide a list of columns to show, and a list of sorts to apply.

--colsToViewList, simply a list of column names (the ones to show)
--applySort, you can sort by as many columns as you want (don’t have to be shown), default is first column i.e. in this case [ID]

removeColView theStak -- gets rid of previous view, down to templates
then I create all the required column, search and header fields from templates, add scripts, group them as required.
There’s 200 lines of code there.
Then finally…

Code: Select all

repeat for each line colName in colsToViewList 
        
        put ("col-" & i) into fldName
        put merge("SELECT [" & colName & "] FROM [" & theTable & "] ORDER BY " & applySort) into tSQLQuery
        --At its simplest applySort is [ID]
  
        dbConnect --simply connects to the database
        put revDataFromQuery(,,dataBaseID,tSQLQuery) into fld fldName – HERE CRASHES USUALLY AFTER 11 TIMES
        revCloseDatabase dataBaseID
        put empty into databaseID
        set the width of fld fldName to the formattedWidth of fld fldName
        set the width of fld ("srch-" & i) to the formattedWidth of fld fldName
        set the width of btn ("Header-" & i) to the formattedWidth of fld fldName
        set the label of btn ("Header-" & i) to colName 
        add 1 to i
     end repeat

Then the addColView procedure puts the primary key column into a hidden field, formats the page some more (another 50 or so lines of code)
end addColView

I want to change the open/close database behavior back to what it was i.e. not every time

There are still 2 big problems here.
"Connection is busy with results for another command" errors
Fatal crashes.

Maybe it’s the settings in sql 2005.
I've got what are suposed to be the latest updates, drivers, but maybe I should change?
Maybe it’s my set up.
Please, I need to get this fixed.
Steve

[bangkok]

Very interesting.

1-you crash with the local dbs, right ?
Those local DB are with SQL Express, right ?

2-your users, the rep on field, crash also ?

3-and the boss ?

4-what don't you try to use SQL lite for the local part ? Much easier to manage for your users.
And no need ODBC driver, to setup, etc. It's only a file that livecode can create on the fly.

5-I'm really convinced that the problem... is coming from your code.

6-I still don't understand why you make several SELECT, within a loop, only to get one column each time.

7-you say "

Code: Select all

   put revDataFromQuery(,,dataBaseID,tSQLQuery) into fld fldName – HERE CRASHES USUALLY AFTER 11 TIMES

Are you sure that the variable fldName contains what it should contains ? And you have all the objects with the proper name in your stack ?
Let's imagine... you have all the fields with the proper name... but not the 35th. LC could crash because it can't find the field.

8-this is why try to dissociate the 2 :
put the data into a normal var
and then put the var into the field with dynamic name
With Script Debug Mode, you'll be able to see if livecode crashes at the first step, or the second.

Code: Select all

put revDataFromQuery(,,dataBaseID,tSQLQuery) into myVar
put myVar into fld fldName

9-you say "when I choose the customer table and tick show all columns (there are 35) it crashes. "

Let's be rationnal : why LiveCode/SQL would be okay with 6 columns but crashes with 35 ?

There lies your problem : create another script, where you do it the simple way with a simple select with your 35 columns... I think it will work.

At that point, back to your script (200 lines !) : "then I create all the required column, search and header fields from templates, add scripts, group them as required. "

You obviously do many things in the script.... perhaps too many. Try to make it easier : use datagrids.

No need to create each time columns, fields, script and group.

Datagrids are great :

Code: Select all

put revDatabaseColumnNames(dbID, "myDataBase") into listColumns
set the dgProp["columns"] of grp "myDG"  to listColumns

The DG will get all your DB columns, and create all the columns with the same header name within the DG. Once and for all (at the opening of the stack for instance).

Then, hide some columns, in order to keep only the ones choosen by the user. Advantage for SQL : no need to create complicated queries. Just do a SELECT * FROM MYTABLE WHERE... everything will go smoothly inside the DG, at the proper location.

Anyway. I think you should review completly your interfacce and the way you deal with it by script.

It seems unlikely that you have a SQL problem, a driver problem.

Good luck !

[Steve Denney]

Thanks for looking at my problem, bangkok. The company's using access, the app works fine.
This is the test on my computer only.
I could address each and all of your points, but feel I'm making my answers too complicated to take in as it is.

The crash can happen at almost any time the database is accessed (not just one place in my code -- any place). And I mean crash.
It tends to happen when multiple queries are processed. It becomes more likely over time.

If anyone has an insight into the ms sql error "Connection is busy with results for another command" then that is probably at the root of the problem.

How do I enable MARS, or ensure MS SQL leaves memory available, or handles LC odbc cleanly? I suspect something like that is the answer.
How does one set up SQL express to work with LC?

Is anyone else actually using SQL express?

I've contacted runrev. Hopefully there's an answer.

Steve

[Steve Denney]

Hi, not meaning to bump this, rather for those who might have similar difficulties.
First, re the revDB error (see below): LC does work well-enough with MS SQL/SQL express but you need to open & close the database for each query (NB some looped queries will work with the db opened at the start & closed at the end, there’s probably a reason, I don’t know why, I simply use trial & error now to see which will work).
I am slightly peeved (this took over a week to fix) ‘cause I hit on this solution in my first post, quote:
“revDB error "Connection is busy with results for another command" I tried to fix this by explicitly opening and closing the database for each and every sql query...”
Many thanks to runrev’s Karthik Sukumaran who pored through my code & found the place where I hadn’t done the above.
Second, re the crashes: these relate to attempting to query a column that was of the data type ‘text’. In MS SQL this is for large record entries (up to 2GB), ours were very much smaller (only a thousand chars or so) but no matter, there may also be an issue with the data type ‘date/time’. I really didn’t get any answers on this or even acknowledgement that it was a problem. Our solution has been to stick with int & varchar. The big row issue (why we used ‘text’ in the 1st place), I handled by only storing links to text docs rather than the contents of the docs.
So... close after every query & watch out for column type
Thanks again to Bangkok and Karthik Sukumaran.
Steve