DatabaseQuery => timing problem?

Everything else that doesn't fall into one of the other PB categories.
Post Reply
aXend
Enthusiast
Enthusiast
Posts: 103
Joined: Tue Oct 07, 2003 1:21 pm
Location: Netherlands

DatabaseQuery => timing problem?

Post by aXend »

In the following code I do some UPDATE queries. They work fine when I run this in debugger view. They also work fine when I run the compiled exe and there are more than 2 records in the defined tables. But when there are only 1 or 2 records in the tables it happens that the queries are not executed when I run the compiled exe program. This happens only with the first 3 queries. The last one is always executed.
Is there a timing problem, e.g. the query is executed, but the program doesn't wait for the result and goes on? Should I use Delay() statements? How and where?

Code: Select all

Procedure UpdateDB()
  UseDatabase(#db0)
  sSQL1.s = "UPDATE WERKGEVER SET STRAATNAAM = Ucase(STRAATNAAM), HUISNRTOEV = Ucase(HUISNRTOEV),"
  sSQL2.s = "PLAATSNAAM = UCase(PLAATSNAAM), HANDELSNAAM = UCase(HANDELSNAAM), CONTACTPERSOON = UCase(CONTACTPERSOON),"
  sSQL3.s = "POSTCODE = Left(POSTCODE,4) & Ucase(Right(POSTCODE,2));"
  sSQL = sSQL1+sSQL2+sSQL3
  If DatabaseQuery(sSQL) <> 0
    logText("WERKGEVER update afgerond")
  EndIf

  sSQL1.s = "UPDATE WERKNEMER SET ACHTERNM = UCase(ACHTERNM), VOORL = UCase(VOORL), VOORV = UCase(VOORV),"
  sSQL2.s = "STRAATNAAM = UCase(STRAATNAAM), HUISNRTOEV = UCase(HUISNRTOEV),"
  sSQL3.s = "PLAATSNAAM = UCase(PLAATSNAAM), LANDNAAM = UCase(LANDNAAM), BEROEP = UCase(BEROEP),"
  sSQL4.s = "POSTCODE = Left(POSTCODE,4) & Ucase(Right(POSTCODE,2)),"
  sSQL5.s = "PARTNER_ACHTERNM = Ucase(PARTNER_ACHTERNM), PARTNER_VOORV = Ucase(PARTNER_VOORV),"
  sSQL6.s = "STRAATNAAM_GBA = UCase(STRAATNAAM_GBA), HUISNRTOEV_GBA = UCase(HUISNRTOEV_GBA),"
  sSQL7.s = "PLAATSNAAM_GBA = UCase(PLAATSNAAM_GBA), LANDNAAM_GBA = UCase(LANDNAAM_GBA),"
  sSQL8.s = "POSTCODE_GBA = Left(POSTCODE_GBA,4) & Ucase(Right(POSTCODE_GBA,2));"
  sSQL = sSQL1+sSQL2+sSQL3+sSQL4+sSQL5+sSQL6+sSQL7+sSQL8
  If DatabaseQuery(sSQL) <> 0
    logText("WERKNEMER update deel1 afgerond")
  EndIf

  sSQL = "UPDATE WERKNEMER SET DTM_EIND_ARB_VERH = Null WHERE DTM_EIND_ARB_VERH='00000000';"
  If DatabaseQuery(sSQL)  <> 0
    logText("WERKNEMER update deel2 afgerond")
  EndIf

  If OpenDatabase(#db1,#sDSN,"","")
    sSQL = "SELECT * FROM WERKNEMER"
    If DatabaseQuery(sSQL)  <> 0
      While NextDatabaseRow()
        VOORL.s = RemoveString(GetDatabaseString(dbCol("VOORL")),".")
        WGR_NR.s = GetDatabaseString(dbCol("WGR_NR"))
        WNR_NR.s = GetDatabaseString(dbCol("WNR_NR"))
        UseDatabase(#db0)
        sSQL = "UPDATE WERKNEMER SET VOORL = '"+VOORL+"' WHERE WGR_NR = '"+WGR_NR+"' AND WNR_NR = '"+WNR_NR+"';"
        DatabaseQuery(sSQL)
        UseDatabase(#db1)
      Wend
      logText("WERKNEMER update deel3 afgerond")
    EndIf
    CloseDatabase(#db1)
  EndIf

  UseDatabase(#db0)
  logText("Gegevens in database geconverteerd");
EndProcedure
Top
Karbon
PureBasic Expert
PureBasic Expert
Posts: 2010
Joined: Mon Jun 02, 2003 1:42 am
Location: Ashland, KY
Contact:
Contact Karbon

Post by Karbon »

Code: Select all

sSQL = sSQL1+sSQL2+sSQL3
That needs to be

Code: Select all

sSQL.s = sSQL1+sSQL2+sSQL3
Otherwise sSQL is a long type :-)

Not sure how that would ever work with that statement like it is..

Good luck!
-Mitchell
Check out kBilling for all your billing software needs!
http://www.k-billing.com
Code Signing / Authenticode Certificates (Get rid of those Unknown Publisher warnings!)
http://codesigning.ksoftware.net
Top
aXend
Enthusiast
Enthusiast
Posts: 103
Joined: Tue Oct 07, 2003 1:21 pm
Location: Netherlands

Post by aXend »

Sorry for my incomplete code... :oops:
I have defined sSQL as a Global variable. So it works if I perform this step by step, but the compiled version seems to do thing too fast? :?
Top
Fred
Administrator
Administrator
Posts: 18350
Joined: Fri May 17, 2002 4:39 pm
Location: France
Contact:
Contact Fred

Post by Fred »

That's seems to be a very wierd problem.. All Query are mean to be synchrone which means that's it's granted to be finished when the function exits. Sounds like an ODBC drivers issue here. What database do you use ? I use mysql all the time and never get such errors.
Top
aXend
Enthusiast
Enthusiast
Posts: 103
Joined: Tue Oct 07, 2003 1:21 pm
Location: Netherlands

Post by aXend »

I use MS-Access 2000 on Windows 98 SE, with a pentium III.
Database driver: ODBCJT32.dll, version 4.00.6019.00, date 9-11-02 (dd-mm-yy).

I was very suprised too. See some prove. :?

This is the listing of my logfile, when using the compiled version
  • 22-10-2003 11:57:28:780 ===== Start =====
    22-10-2003 11:57:28:890 ===== Ready =====
    22-10-2003 11:57:28:890 Start met files inlezen
    22-10-2003 11:57:29:990 Verwerking bericht: AV1001002_90001_00021.xml
    22-10-2003 11:57:31:200 Bericht opgeslagen
    22-10-2003 11:57:32:460 Gegevens ingelezen
    22-10-2003 11:57:33:120 WERKGEVER update afgerond
    22-10-2003 11:57:33:450 WERKNEMER update deel1 afgerond
    22-10-2003 11:57:33:450 WERKNEMER update deel2 afgerond
    22-10-2003 11:57:33:620 WERKNEMER update deel3 afgerond
    22-10-2003 11:57:33:620 Gegevens in database geconverteerd
    22-10-2003 11:57:34:390 Gegevens klaar om te verzenden
    22-10-2003 11:57:38:620 Aantal werknemers verwerkt:2
    22-10-2003 11:57:38:780 Start met files inlezen
    22-10-2003 11:57:38:890 Verwerking bericht: AV1001001_90001_00135.xml
    22-10-2003 11:57:39:220 Bericht opgeslagen
    22-10-2003 11:57:40:260 Gegevens ingelezen
    22-10-2003 11:57:40:260 WERKGEVER update afgerond
    22-10-2003 11:57:40:320 WERKNEMER update deel1 afgerond
    22-10-2003 11:57:40:920 WERKNEMER update deel3 afgerond
    22-10-2003 11:57:40:920 Gegevens in database geconverteerd
    22-10-2003 11:57:41:420 Gegevens klaar om te verzenden
    22-10-2003 11:57:41:580 Aantal werknemers verwerkt:9
    22-10-2003 11:57:41:690 Start met files inlezen
    22-10-2003 11:57:41:750 Verwerking bericht: AV1001002_10001_00023.xml
    22-10-2003 11:57:41:910 Bericht opgeslagen
    22-10-2003 11:57:42:190 Gegevens ingelezen
    22-10-2003 11:57:42:300 WERKNEMER update deel3 afgerond
    22-10-2003 11:57:42:300 Gegevens in database geconverteerd
    22-10-2003 11:57:42:740 Gegevens klaar om te verzenden
    22-10-2003 11:57:42:790 Aantal werknemers verwerkt:1
    22-10-2003 11:57:42:900 Start met files inlezen
    22-10-2003 11:57:42:950 Verwerking bericht: AV1001002_10001_00024.xml
    22-10-2003 11:57:43:120 Bericht opgeslagen
    22-10-2003 11:57:43:500 Gegevens ingelezen
    22-10-2003 11:57:43:610 WERKNEMER update deel3 afgerond
    22-10-2003 11:57:43:610 Gegevens in database geconverteerd
    22-10-2003 11:57:43:940 Gegevens klaar om te verzenden
    22-10-2003 11:57:44:000 Aantal werknemers verwerkt:2
    22-10-2003 11:57:44:110 Start met files inlezen
    22-10-2003 11:57:44:160 Verwerking bericht: AV1001001_90001_00017.xml
    22-10-2003 11:57:44:550 Bericht opgeslagen
    22-10-2003 11:57:44:880 Gegevens ingelezen
    22-10-2003 11:57:44:880 WERKGEVER update afgerond
    22-10-2003 11:57:44:880 WERKNEMER update deel1 afgerond
    22-10-2003 11:57:44:930 WERKNEMER update deel2 afgerond
    22-10-2003 11:57:44:990 WERKNEMER update deel3 afgerond
    22-10-2003 11:57:44:990 Gegevens in database geconverteerd
    22-10-2003 11:57:45:480 Gegevens klaar om te verzenden
    22-10-2003 11:57:45:540 Aantal werknemers verwerkt:1
    22-10-2003 11:57:45:650 Geen files meer aanwezig
    22-10-2003 11:57:45:810 ==== Finished ====
This is the listing of my logfile when running with sql-tracing on (from ODBC panel).
  • 22-10-2003 12:05:56:410 ===== Start =====
    22-10-2003 12:06:03:550 ===== Ready =====
    22-10-2003 12:06:03:550 Start met files inlezen
    22-10-2003 12:06:04:750 Verwerking bericht: AV1001002_90001_00021.xml
    22-10-2003 12:06:06:350 Bericht opgeslagen
    22-10-2003 12:06:19:420 Gegevens ingelezen
    22-10-2003 12:06:20:350 WERKGEVER update afgerond
    22-10-2003 12:06:20:680 WERKNEMER update deel1 afgerond
    22-10-2003 12:06:20:900 WERKNEMER update deel2 afgerond
    22-10-2003 12:06:23:540 WERKNEMER update deel3 afgerond
    22-10-2003 12:06:23:590 Gegevens in database geconverteerd
    22-10-2003 12:06:35:400 Gegevens klaar om te verzenden
    22-10-2003 12:06:39:140 Aantal werknemers verwerkt:2
    22-10-2003 12:06:39:690 Start met files inlezen
    22-10-2003 12:06:39:850 Verwerking bericht: AV1001001_90001_00135.xml
    22-10-2003 12:06:40:400 Bericht opgeslagen
    22-10-2003 12:07:26:810 Gegevens ingelezen
    22-10-2003 12:07:27:030 WERKGEVER update afgerond
    22-10-2003 12:07:27:140 WERKNEMER update deel1 afgerond
    22-10-2003 12:07:36:210 WERKNEMER update deel3 afgerond
    22-10-2003 12:07:36:260 Gegevens in database geconverteerd
    22-10-2003 12:07:47:250 Gegevens klaar om te verzenden
    22-10-2003 12:07:47:460 Aantal werknemers verwerkt:9
    22-10-2003 12:07:47:960 Start met files inlezen
    22-10-2003 12:07:47:960 Verwerking bericht: AV1001002_10001_00023.xml
    22-10-2003 12:07:48:510 Bericht opgeslagen
    22-10-2003 12:07:55:430 Gegevens ingelezen
    22-10-2003 12:07:55:650 WERKGEVER update afgerond
    22-10-2003 12:07:55:700 WERKNEMER update deel1 afgerond
    22-10-2003 12:07:55:920 WERKNEMER update deel2 afgerond
    22-10-2003 12:07:57:190 WERKNEMER update deel3 afgerond
    22-10-2003 12:07:57:240 Gegevens in database geconverteerd
    22-10-2003 12:08:09:330 Gegevens klaar om te verzenden
    22-10-2003 12:08:09:430 Aantal werknemers verwerkt:1
    22-10-2003 12:08:09:820 Start met files inlezen
    22-10-2003 12:08:09:930 Verwerking bericht: AV1001002_10001_00024.xml
    22-10-2003 12:08:10:370 Bericht opgeslagen
    22-10-2003 12:08:22:670 Gegevens ingelezen
    22-10-2003 12:08:23:110 WERKGEVER update afgerond
    22-10-2003 12:08:23:110 WERKNEMER update deel1 afgerond
    22-10-2003 12:08:23:280 WERKNEMER update deel2 afgerond
    22-10-2003 12:08:25:750 WERKNEMER update deel3 afgerond
    22-10-2003 12:08:25:800 Gegevens in database geconverteerd
    22-10-2003 12:08:41:020 Gegevens klaar om te verzenden
    22-10-2003 12:08:41:130 Aantal werknemers verwerkt:2
    22-10-2003 12:08:41:510 Start met files inlezen
    22-10-2003 12:08:41:680 Verwerking bericht: AV1001001_90001_00017.xml
    22-10-2003 12:08:42:340 Bericht opgeslagen
    22-10-2003 12:08:49:310 Gegevens ingelezen
    22-10-2003 12:08:49:530 WERKGEVER update afgerond
    22-10-2003 12:08:50:020 WERKNEMER update deel1 afgerond
    22-10-2003 12:08:50:130 WERKNEMER update deel2 afgerond
    22-10-2003 12:08:51:510 WERKNEMER update deel3 afgerond
    22-10-2003 12:08:51:620 Gegevens in database geconverteerd
    22-10-2003 12:09:02:270 Gegevens klaar om te verzenden
    22-10-2003 12:09:02:380 Aantal werknemers verwerkt:1
    22-10-2003 12:09:02:770 Geen files meer aanwezig
    22-10-2003 12:09:03:150 ==== Finished ====
You can see that the same files produce different outcomes. Skipping "deel2" is regular, because of the query. But all the others should work.
It looks like it that when the process is slowed down it works allright. But that can't be the intention...
Top
Tranquil
Addict
Addict
Posts: 952
Joined: Mon Apr 28, 2003 2:22 pm
Location: Europe

Post by Tranquil »

I'm very intrested in that topic couse I'm coding software for a big corporation in germany and they want to use it from january on. Thats the release date. I use the ODBC driver too and I get realy in trouble if there are bugs in PB.

Could anyone confirm this behavior? I never noticed that till yet.

Mike
Tranquil
Top
Post Reply

Return to “General Discussion”