SQL issue

#1 · January 7, 2020, 8:47 pm

Hi,

I wish to update a database table (Master) Field (Credits) by 1 based on the contents of another Table (stats) and Field (Username).

My SQL query is as follows:

UPDATE Master
SET CREDITS =  CREDITS +1
WHERE EXISTS CUSTOMER = (SELECT Username FROM Stats);

I cannot get this to work - if the Username in the Stats Table exists I wish to increment the Credits field by 1 in the Master Table.

I have tried each and every way but cannot make it work.

With the code above I get the following error "Cannot Perform this operation on a closed dataset"

Any assistance/pointers would be most welcome - TIA

#2 · January 7, 2020, 10:09 pm

@mark-waples

UPDATE Master
SET CREDITS = CREDITS +1
WHERE EXISTS CUSTOMER = (SELECT Username FROM Stats);

With the code above I get the following error "Cannot Perform this operation on a closed dataset"

Isn't it annoying when the the SQL software plays "Guess which Table is closed" with you ? :-((

1) Are both the Master and Stats Tables open at the time the SQL UPDATE command is invoked ?

2) Of course, an incorrect syntax could cause the command interpreter to be seeking a non-existent Table; take a look at the syntax of the WHERE EXISTS clause on this page ... https://www.w3schools.com/sql/sql_exists.asp ... and then try something like ...

WHERE EXISTS (SELECT Username FROM Stats WHERE Master.Username = Stats.Username);

If all else fails, you could consider doing it the long way i.e.

cycle through all the Master records
for each Master record, search the Stats Table for a record with matching Username
if found, increment the Credits field in the Master record

luishp has reacted to this post.

#3 · January 9, 2020, 11:11 am

Hi Gaev,

yes it is annoying when it doesn't inform about what table is not open :)

Both the Master and Stats are open at the time of the SQL Query.

I seem to remember I also tried your suggestion:

WHERE EXISTS (SELECT Username FROM Stats WHERE Master.Username = Stats.Username);

But that threw a "dataset" error

I will revisit your suggestion as I tried so many ways and they all failed and it seems hard to remember which ones I tried out of frustration.

In the end I did cycle through the master records but that is not a good solution as it takes so much time to loop through each record.

Thank you for the response and I will report back once I have had more time to revisit the issue.

Mark

#4 · January 9, 2020, 12:45 pm

Hi Gaev,

I have revisited and came up with this requirement to update the master table when this condition is met:

So if the customer from master = stats.customer AND Stats.Run[Race1Winner] = 1 - then perform the update on the master table

Master Table:

ID	CUSTOMER	CREDITS
1	Big Boris	499
2	Fred Flintstone	500
3	Bob Thomas	500
4	Ted Baker	500

Stats Table:

ID	UserId	CUSTOMER	Run1	Run2	Run3	Run4	Run5	Run6	Run7	Run8
36	1	Big Boris	1	0	0	0	0	0	0	0
37	2	Fred Flintstone	1	0	0	0	0	0	0	0
38	3	Bob Thomas	1	0	0	0	0	0	0	0
39	1	Big Boris	1	0	0	0	0	0	0	0

SQL QUERY:

UPDATE master
SET CREDITS = CREDITS +1
WHERE EXISTS (SELECT CUSTOMER FROM Master WHERE CUSTOMER = Stats.Customer AND Stats.Run[Race1Winner] = 1);

This is how the SQL query appears in the action:

dbpExecSQL "Customers" "UPDATE master|SET CREDITS = CREDITS +1|WHERE EXISTS (SELECT CUSTOMER FROM Master WHERE CUSTOMER = Stats.Customer AND Stats.Run[Race1Winner] = 1);" ""

When I run the sql query it gives me the following error:

Parameter Stats.Customer has no default Value

I don't understand why I get this error?

Totally confusing to me - :(

#5 · January 9, 2020, 3:25 pm

@mark-waples

When I run the sql query it gives me the following error:
Parameter Stats.Customer has no default Value

Could it be that the field in Stats is defined in uppercase (CUSTOMER) but you are referencing it in the dbpExecSQL command with mixed-case (Stats.Customer) ?

#6 · January 9, 2020, 5:23 pm

Hi Gaev,

I changed the query but it made no difference - it's almost like VisualNeowin sees Stats.CUSTOMER as a variable and not as a Field in a Table.

:(

#7 · January 9, 2020, 8:26 pm

@mark-waples

it's almost like VisualNeowin sees Stats.CUSTOMER as a variable and not as a Field in a Table.

Try this ...

UPDATE master
SET CREDITS = CREDITS + 1
WHERE CUSTOMER IN (SELECT CUSTOMER FROM Stats WHERE CUSTOMER = master.CUSTOMER AND Run[Race1Winner] = 1);

... it will (should) ...

- first resolve the SELECT CUSTOMER FROM Stats WHERE CUSTOMER = master.CUSTOMER AND Run[Race1Winner] = 1 condition ... and come up with a list ... either no matches or something like ('Big Boris','Fred Flinstone','Bob Thomas','Big Boris')

- then if CUSTOMER (in the master table) is IN this list, it will add one to CREDITS

Please ...

- make sure case sensitivity is maintained
- try the query with hard-coded value for [Race1Winner]

In the end I did cycle through the master records but that is not a good solution as it takes so much time to loop through each record.

How long does it take ?

When you execute this UPDATE command, typically, how many records in the master and Stats tables ?

Which table's records do you cycle through ?

Vadim has reacted to this post.

#8 · January 10, 2020, 12:48 pm

Hi Gaev,

that worked perfectly - thank you so much

I have no idea as to why the other queries did not work - maybe a bug or limitation within the code that controls the sql.

With the records on the post the loop process had to be slowed down with wait statements. If I did not do this the database would not be updated correctly, so there is a "Timing" issue when looping database records.

With just say 4 records - each loop would take roughly about 2 seconds per loop - it doesn't sound like much but when you have say 100 records to loop through the user would have to wait about 3.5 mins until the process is finished. This was unacceptable.

So thank you so much for your patience and assistance.

Mark

#9 · January 10, 2020, 4:53 pm

@mark-waples

that worked perfectly

Before you close this Topic ... note that while the suggested command syntax got rid of the error messages, I would like to draw your attention to a possible design flaw in your App ...

a) in the earlier post, I noticed that the Stats Table could contain duplicates i.e. "CUSTOMER=Big Boris AND Run1=1"

b) if this is true, then cycling through the Master records would result in the CREDITS field being incremented by JUST 1

... if ...

- duplicates (as defined by me above) are not allowed
or
- CREDITS is to be incremented by one only

... thne everything is AOK ... otherwise, you need a much more complex command/syntax ... perhaps even reverting to the loop/endloop scripting solution

With the records on the post the loop process had to be slowed down with wait statements. If I did not do this the database would not be updated correctly, so there is a "Timing" issue when looping database records.

If you have to revert to the loop/endloop process, post your code here to determine why you need to invoke the Wait command.

And please advise what kind of Database (Access, mySQL etc.) is being deployed ... and where (local/remote) it is located.

#10 · January 10, 2020, 5:11 pm

Thank you GAEV,

Understood unfortunately I cannot post the source code as it is commercially sensitive.

What I can tell you is that the database is MS Access and the database is stored in the same directory as the app.

Mark

#11 · January 11, 2020, 3:25 am

@mark-waples

What I can tell you is that the database is MS Access and the database is stored in the same directory as the app.

In that case, the workaround involving the Wait command seems to be unrelated to the commands associated with cycling through the database table.

Unfortunately I cannot post the source code as it is commercially sensitive.

Understandable ... I was merely looking for the code inside the Loop/EndLoop block ... the logic would be something like ...

dbpOpenAccessDatabase "yourID" "[PubDir]yourFileName.mdb" "password=letMeIn"
dbpOpenTable "yourID" "master" ""
dbpOpenTable "yourID" "Stats" ""


dbpQuery "yourID" "Stats" "Run[Race1Winner] = 1"

If "[yourID.Stats.$RecCount]" ">" "0"
   dbpFirst "yourID" "Stats"
   Loop "1" "[yourID.Stats.$RecCount]" "[thisLoopCycle]"
      ... increment CREDITS field in master
      GoSub "incrementCredits"
      dbpNext "yourID" "Stats"
   EndLoop
EndIf


:incrementCredits
... subroutine to increment CREDITS field in master record with matching CUSTOMER field
dbpQuery "yourID" "master" "CUSTOMER = [yourID.Stats.CUSTOMER]"
If "[yourID.master.$RecCount]" "=" "1"
   ... found a match; increment CREDITS
   SetVar "[yourID.master.CREDITS]" "1 + [yourID.master.CREDITS]"
EndIf
Return

... try this out and post details of number of query'ed Stats records, time to execute ... and any unexpected results.

Vadim has reacted to this post.

#12 · January 12, 2020, 1:05 pm

Hi Gaev,

in that case the loop code was:

Loop "1" "[Race1TotalNumberofBets]" "[Race1Loop]"
:EXECUTE SQL TO FIND WINNERS
dbpExecSQL "Customers" "UPDATE master|SET CREDITS = CREDITS +1|WHERE CUSTOMER = [#34][Customers.Stats.Username][#34] AND [#34][Customers.Stats.Run[Race1Winner]][#34] = [#34]1[#34];|" ""
dbpOpenTable "customers" "Master" ""
Delay "1500"
StickyNote "776" "700" "** Credits Loading - Please Wait! **" "2000"
dbpNext "Customers" "Stats"
EndLoop

I had to introduce the "Delay "1500" otherwise the results were not correctly written back to the database

Mark

#13 · January 12, 2020, 8:28 pm

@mark-waples

I am somewhat unsure.

a) I am assuming that [Race1TotalNumberofBets] is the count of Stats records WHERE Run1 = 1; hence the need to do the Loop/EndLoop code [Race1TotalNumberofBets] times ... please advise if my assumption is incorrect

b) What is [Race1Winner] in the condition [Customers.Stats.Run[Race1Winner]] = 1 ?

c) what is the purpose of ...

dbpOpenTable "customers" "Master" ""

... you are asking to open the master Table (again and again with each iteration of the loop) ... if the master Table was opened before the Loop/Endloop, it would stay open at the end of the loop.

1) Try ...

dbpOpenTable "Stats" ""

... filter records in Stats where Run1 field is 1
dbpQuery "Customer" "Stats" "Run1 = 1"
... [Customer.Stats.$RecCount] will contain number of filtered records
SetVar "[Race1TotalNumberofBets]" "[Customer.Stats.$RecCount]"

dbpOpenTable "customers" "Master" ""

Loop "1" "[Race1TotalNumberofBets]" "[Race1Loop]"

   .EXECUTE SQL TO FIND WINNERS
   dbpExecSQL
      "Customers"
      "UPDATE master
         SET CREDITS = CREDITS + 1
         WHERE CUSTOMER = [#34][Customers.Stats.Username][#34] AND [#34][Customers.Stats.Run[Race1Winner]][#34] = [#34]1[#34];|
      "
      ""
   dbpNext "Customers" "Stats"
EndLoop

If the above code does NOT produce the results you expected, try and replace [Customers.Stats.Run[Race1Winner]] with [Customers.Stats.Run1] ... I faintly recall (about 5 to 10 years ago) that Dave mentioned that "nested variables do not get resolved in raw SQL commands sent to the database.

If that works, there is still a way (Loops inside Loops) to repeat the logic for fields Run1 to Run8

If that still does not work work, please post details of what does NOT work.

BTW, if you can try my previously posted code, let us know the accuracy and timing of the process.

SQL issue - Forum

SQL issue