SQL Fetch Data - Data get not updated...

[ISITEC_RIEPER]

Hello everybody,

i have to read out data from a sql database frequently.

In the first step i delete all table rows with

DBImmediateSQL (db_connectionHandle, "DELETE FROM test_table"

Then i frequently check if there is a row inserted into the table by:

hstmt = DBActivateSQL (db_connectionHandle, "SELECT * FROM test_table");
rows = DBNumberOfRecords(hstmt);
resCode = DBDeactivateSQL (hstmt);

an if rows available i do

resCode = DBFetchRandom(db_mapHandle,rows);
...... use new row values......

and then again delete the row:
DBImmediateSQL (db_connectionHandle, "DELETE FROM test_table"


So far everithing works fine. I addition i watch the database with a
freeware tool to see the contens of the test_table.

I can see that all rows are deleted
I can see the new data row
I can see that the row is deleted
And i can see the next new data.

When i read the new data the first time everything is OK, but the second
time, instead of the new data values i read the first values again, even if
the freeware tool shows that there are different data in the test_table.
Seems the fetched data don't get updated. What am i doing wrong ?

Norbert

[ISITEC_RIEPER]

Hello,

i'm in contact with the service of NI-Germany. I send them parts of my
project and they could reproduce the error, so its a real bug and not a
pogramming error by myself (smilehappy) thats verified by NI.
Now we are expecting to get help from NI (USA).....

Thanks for the assistance of NI-Germany so far.

Norbert Rieper


..
"Norbert Rieper" <nrieper@isitec.de> schrieb im Newsbeitrag
news:485a475a@PYROS.natinst.com...
> Hello everybody,
>
> i have to read out data from a sql database frequently.
>
> In the first step i delete all table rows with
>
> DBImmediateSQL (db_connectionHandle, "DELETE FROM test_table"
>
> Then i frequently check if there is a row inserted into the table by:
>
> hstmt = DBActivateSQL (db_connectionHandle, "SELECT * FROM test_table");
> rows = DBNumberOfRecords(hstmt);
> resCode = DBDeactivateSQL (hstmt);
>
> an if rows available i do
>
> resCode = DBFetchRandom(db_mapHandle,rows);
> ..... use new row values......
>
> and then again delete the row:
> DBImmediateSQL (db_connectionHandle, "DELETE FROM test_table"
>
>
> So far everithing works fine. I addition i watch the database with a
> freeware tool to see the contens of the test_table.
>
> I can see that all rows are deleted
> I can see the new data row
> I can see that the row is deleted
> And i can see the next new data.
>
> When i read the new data the first time everything is OK, but the second
> time, instead of the new data values i read the first values again, even
> if the freeware tool shows that there are different data in the
> test_table. Seems the fetched data don't get updated. What am i doing
> wrong ?
>
> Norbert
>
>
>

[nickb]

Hello Norbert,

I have indeed received your code from NI-Germany today, and spent a good deal of time going through it.  I think the behavior you were seeing comes down to two issues.  The first is that you were calling DBFetchRandom without first calling DBAllowFetchAnyDirection.  It is certainly understandable to have missed this, although if you want, you can find it stated on the function panel for DBFetchRandom.

Where the real issue lies is in the second point, which is how the DBMap calls have been configured.  You call DBBeginMap, DBMapColumnTo* and DBActivateMap all one time when the program is first run.  The small problem with this is that DBActivateMap actually runs an SQL Select statement on whatever recordset is present in the table when the program is first run.  It is possible the table is empty, or that old data is present there.  This maps your variables to the old data, and when you run DBFetchRandom, it simply searches this old recordset for the index you supplied to DBFetchRandom, either erroring if it doesn't exist, or returning old, stale data.  The solution for this is to move those three functions (DBBeginMap, DBMapColumTo*, and DBActivateMap) to your callback function that retrieves the values from the table.  This was you activate the map on the most recent data.  Please let me know if you have any questions!

NickB
National Instruments
Applications Engineering

[ISITEC_RIEPER]

Hello NickB,

after all it was a programming error (blame on me). I was not aware that i
have to map (DBBeginMap......DBActivateMap) every time ibefore i do a fetch,
i thought i have to do it once at the beginning of my program. So i tried it
as you advised me and, smilehappy, it works.
Thanks a lot, you make my day 🐵 Thanks also to Tim from NI Germany.

Norbert



i'm in contact with the service of NI-Germany. I send them parts of my
project and they could reproduce the error, so its a real bug and not a
pogramming error by myself (smilehappy) thats verified by NI.
Now we are expecting to get help from NI (USA).....

Thanks for the assistance of NI-Germany so far.
"nickb" <x@no.email> schrieb im Newsbeitrag
news:1214439010924-731989@exchange.ni.com...
> Hello Norbert, I have indeed received your code from NI-Germany today, and
> spent a good deal of time going through it.&nbsp; I think the behavior you
> were seeing comes down to two issues.&nbsp; The first is that you were
> calling DBFetchRandom without first calling
> DBAllowFetchAnyDirection.&nbsp; It is certainly understandable to have
> missed this, although if you want, you can find it stated on the function
> panel for DBFetchRandom.Where the real issue lies is in the second point,
> which is how the DBMap calls have been configured.&nbsp; You call
> DBBeginMap, DBMapColumnTo* and DBActivateMap all one time when the program
> is first run.&nbsp; The small problem with this is that DBActivateMap
> actually runs an SQL Select statement on whatever recordset is present in
> the table when the program is first run.&nbsp; It is possible the table is
> empty, or that old data is present there.&nbsp; This maps your variables
> to the old data, and when you run DBFetchRandom, it simply searches this
> old recordset for the index you supplied to DBFetchRandom, either erroring
> if it doesn't exist, or returning old, stale data.&nbsp; The solution for
> this is to move those three functions (DBBeginMap, DBMapColumTo*, and
> DBActivateMap) to your callback function that retrieves the values from
> the table.&nbsp; This was you activate the map on the most recent
> data.&nbsp; Please let me know if you have any questions!NickBNational
> InstrumentsApplications Engineering