Sign in to follow this  
Followers 0
rajsiyer

Using SQL statements In a loop, with 2 tables, Citect

3 posts in this topic

Hi Friends, I am working on a small project that needs to log data from energy-meters (about 200) onto a SQL database. I have written a function for logging in Cicode that is connecting to a DSN with multiple tables. It gets the customer's Id, one by one in a loop from one table assigns the readings, and logs it to another table. Problem observed with my method is that the loop does not move beyond the first entry in the Cust_ID table. I suspect that there may be a need to use different handles for different tables. OR - The TagRead() function is blocking further execution of the loop. PS:- The use of SqlNext(hSql) has worked well in a similar function to load combo-boxes with All Cust_IDs. Something causes the While loop to end Greatly will appreciate any help in resolving the error. PS:- The use of SqlNext(hSql) has worked well in a similar function to load combo-boxes with All Cust_IDs. Something causes the While loop to end Greatly will appreciate any help in resolving the error. Edited by rajsiyer

Share this post


Link to post
Share on other sites
There are 2 tables in the DSN Named EM_AccessData 1. Table Cust_IDs is the set of Cust_Id. 2. Table Live_log which is where the data is logged every few minutes. Handles.. Handle hSQL is used to connect to the database. The SQLExec() for reading is done form the Cust_Ids table using StsSQL1 The SQLExec() for inserting record into the Live_log table uses StsSQL2 FUNCTION Log_EnergyMeters() STRING sCurId; STRING sCurRdg; STRING sCurPwr; STRING sDt; INT hSQL; // Handle for connect INT StsSQL1// Status showing exec- error Cons_Ids INT StsSQL2// status for Live_log execution hSQL = SQLConnect("DSN=EM_AccessData;UID=Admin;PWD=Iyer"); IF hSQL <> -1 THEN StsSQL1 = SQLExec(hSQL, "SELECT Cons_ID FROM Cons_IDs order by Cons_Id"); IF StsSQL1 = 0 THEN WHILE SQLNext(hSQL) = 0 DO sCurId = SQLGetField(hSQL, "Cons_ID");//Look up the Cons_IDs table and get each Cons_ID sDt = Date()+" "+ Time(); sCurRdg = TagRead(sCurID+"_CurRdg"); sCurPwr = TagRead(sCurID+"_kW"); StsSQL2 = SQLExec(hSQL, "Insert into Live_log(Dt_Time_, Consumer_ID, CurMtr_Rdg, Power_kW)VALUES('"+sDt+"','"+sCurId+"','"+sCurRdg+"','"+sCurPwr+"')"); IF StsSQL2 = 307 THEN Message("Error", SQLErrMsg(), 48); END SQLEnd(hSQL); SleepMS(20); END// Do... WHILE SQLEnd(hSQL); SQLDisconnect(hSQL); END//for StsSQL2 =0 or otherwise SQLDisconnect(hSQL); !Message("Error", SQLErrMsg(), 48); END// Outer If for StsSQL1 = or <> 0 END//Function

Share this post


Link to post
Share on other sites
Looking over your code you aren't iterating over the list of Cons_ID values, you are only selecting the entire list from the table. You would either need to copy the result from Cons_ID into a temp table, grab the first one, do what you need with it, delete it, making the 2nd record the top one in the list, select it, repeat. Or you could write some code to include the row number, or if you have a primary key using an integer value use that, along with a counter to select different rows in each run through the while loop. Here is some information on the ROW_NNUMBER function in SQL Server, similar ideas should be available in other database systems: https://msdn.microsoft.com/en-us/library/ms186734.aspx SQL is notoriously stubborn when working with for/while loops, I have felt your pain many times over the years.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0