Where have I gone wrong

[grindstone]

Sorry, but I don't understand what exactly you want to know from me.

But at least one question I can answer:

What part of the code can i check to see if the MySQL server has returned any results (so I can show a Sorry user not found Error message)

At this site you'll find a list of all MySQL-API-functions with a description and the error codes they might return.

[Gablea]

@grindstone

Sorry maybe I did not explain myself.

So as you can see I have a record from the server

How would I put the name result from the select cashiername into CashierNamePrint

When I was using the csv file I could use

CashierNamePrint = cashier_name (as that is the field I used to load the data file)


I assume it would be something like *row[0] can that be access like *row[cashiername]

Or does the *row bit only work with numbers?

[grindstone]

Now I got it (I hope :-) ). This should work, assumed the name of the coloumn (aka field name) is "cashiername":

Code: Select all

Public Sub FindCashier (ByVal CashierNumberLocal As String, ByVal CashierPasswordLocal As String)
   Dim SQLQuery       As String
   Dim db             As MYSQL Ptr
   Dim res             As Integer
   Dim restab          As mysql_res Ptr

   SQLQuery = ""
   SQLQuery += "select "
   SQLQuery += "userid, "
   SQLQuery += "userpassword, "
   SQLQuery += "username, "
   SQLQuery += "usernameposrecipit, "
   SQLQuery += "changepassword, "
   SQLQuery += "useraccesslevel "
   SQLQuery += "from usertable "
   SQLQuery += "where userid='" & CashierNumberLocal & "' and userpassword='"  & CashierPasswordLocal & "';"

   'Initialize the API. db points to the MySQL system.
   db = mysql_init(NULL)

   'Connect to the MySQL system.
   If( mysql_real_connect( db, ServerAddress, ServerUserName, ServerUserPassword, ServerDatabaseName, ServerDatabasePort, NULL, 0 ) = 0 ) Then
      Print "Can't connect to the mysql server on port"; MYSQL_PORT
      mysql_close( db )
      Sleep
      End 1
   End If

   'Select a data base.
   If (mysql_select_db( db, ServerDatabaseName)) Then
      Print "Can't select the "; ServerDatabaseName; "Color = teal>"" database !"
      mysql_close(db)
      Sleep
      End 1
   End If

   'Print informations about the RDBMS host and the data base.
   Print "Client info : "; *mysql_get_client_info()
   Print "  Host info : "; *mysql_get_host_info(db)
   Print "Server info : "; *mysql_get_server_info(db)


   'Submit a SQL query.
   res = mysql_query(db, SQLQuery)

   'Declare a pointer to the result table
   If res = 0 Then
      'Get the pointer to the result table
      restab = mysql_store_result( db )
      If restab > 0 Then
         Dim As Integer nrow
         'How many rows are in the result table?
         nrow = mysql_num_rows( restab )
         Print nrow
         If nrow > 0 Then
            Dim As Integer i, j, ncol
            Dim As mysql_row row
            'How many columns are in the result table?
            ncol = mysql_num_fields( restab )
            Print ncol

            'Read the result table.
            For i = 0 To nrow - 1
               'Get the next row of the result table
               row = mysql_fetch_row( restab )
               Print i,
               'Print all columns of the current row to the screen.
               For j = 0 To ncol - 1
                  Dim s As String: s = *row[j]
                  Print  s; " - ";
                  
                  '#########################################
                  Dim As MYSQL_FIELD Ptr fi = mysql_fetch_field_direct(restab, j) 'get the current column name
                  If (*fi->Name = "cashiername") And (s = CashierNumberLocal) Then
                     CashierNamePrint = s
                  EndIf
                  '#########################################
                  
               Next  j
               Print
            Next  i
            Sleep
         End If
      End If
      'Release the memory of the result table.
      mysql_free_result( restab )
   Else
      Print "sql query error"
   End If

   'Close the API access.
   mysql_close( db )
End Sub


[caseih]

Just a small suggestion, grindstone. Even for trivial examples, never append user-supplied variables to a query string (internal program variables are usually fine since you the programmer can guarantee they will always contain valid data). Never ever do it. Even if you think it won't matter. Your query string is a prime example of an SQL injection bug. All someone needs to do is walk up to the POS machine and enter a password like this:

Code: Select all

dummy'; drop table usertable;

or worse. Any query the user wants to run can be placed in the password. Of course he might not get a good way of reading the data out, but could do all sorts of mischief. You might think I'm being pedantic, but good habits start early! Instead of appending unfiltered user data to a query, you need to use parameterized queries. It adds some steps, but it's really important if you're querying based on *any* user-supplied input. It's a three-step process. First you create the query with placeholders for the data, then you bind your FB variables to the query, then you execute it. I'm not sure why mysql bi file you're using, but I hope it exposes mysql_stmt_prepare() and friends:
https://dev.mysql.com/doc/refman/8.0/en ... rview.html

[grindstone]

I beg your pardon, but you're railing against the wrong one :-) . Everything but the lines framed by "'########" is originally the code Gablea posted here. I only re-posted the whole snippet to make clear where to place the additional lines.

Apart from that I agree with you. The common use of global variables is surely an aftereffect of the program's DOS origin, but it makes the co-developing not easier.

[Gablea]

@grindstone

i think I have broken FreeBASIC he he

Code: Select all

   Dim SQLQuery       As String
   Dim db             As MYSQL Ptr
   Dim res             As Integer

   Dim restab          As mysql_res Ptr
   Dim row             As mysql_row
      
   SQLQuery = ""
   SQLQuery += "select "
   SQLQuery += "userid, "                        '0
   SQLQuery += "userpassword, "                  '1
   SQLQuery += "username, "                     '2
   SQLQuery += "usernameposrecipit, "            '3
   SQLQuery += "changepassword, "               '4
   SQLQuery += "useraccesslevel "               '5
   SQLQuery += "from usertable "
   SQLQuery += "where userid='" & CashierNumberLocal & "' and userpassword='"  & CashierPasswordLocal & "';"

    'Initialize the API. db points to the MySQL system.
    db = mysql_init(NULL)

    'Connect to the MySQL system.
    IF( mysql_real_connect( db, ServerAddress, ServerUserName, ServerUserPassword, ServerDatabaseName, ServerDatabasePort, NULL, 0 ) = 0 ) THEN
            PRINT "Can't connect to the mysql server on port"; MYSQL_PORT
            mysql_close( db )
            Sleep
            End 1
    END IF

    'Select a data base.
    If (mysql_select_db( db, ServerDatabaseName)) Then
      Print "Can't select the "; ServerDatabaseName; "Color = teal>"" database !"
      mysql_close(db)
      Sleep
      End 1
    End If

    'Print informations about the RDBMS host and the data base.
    PRINT "Client info : "; *mysql_get_client_info()
    PRINT "  Host info : "; *mysql_get_host_info(db)
    PRINT "Server info : "; *mysql_get_server_info(db)


    'Submit a SQL query.
    res = mysql_query(db, SQLQuery)


    'Declare a pointer to the result table
    IF res = 0 Then
       If mysql_num_rows(restab) > 0 Then
          restab = mysql_store_result(db)
          row = mysql_fetch_row(restab)
         
         Dim PasswordChange As Integer = *row[4]

          Select Case PasswordChange
             Case 0 'No Change required
                         KeyPadInput = ""
                    CashierNamePrint = Trim(*row[3])
                         CashierAccess = Trim(*row[5])    'Set the system to use the signed on cashier
               CashierOverrideAccess = 0
                  PriceOverridePrice = 0                                 'Clears the Entered Price
                             TotalDue = 0                                  'Resets sale value
                            ItemsSold = 0                                  'Resets item count
                       TotalTendered = 0                                 'Resert the total tendered value
                         RecipitClear = 1                                 'Set the recipit so a new one can be produced for sale
                             SaleMode = "Sale"                           'Sets the salemode back to sale
                   'RecallInProgress = "No"                              'Resets the Recall trigger so the system will run in normal mode
                      ShowTaskBarItems = 1
                     SubTotalPressed = 0
                        DisplayLine1 = ""
                        DisplayLine2 = ""      
                        
                   'Close the API access.
               mysql_close(db)
               Salescreen          
             
             Case 1   ' Password needs to be changed
               mysql_close(db)
                   updateCashierScreen(CashierDisplayComSettings, "PASSWORD EXPIRED", 0, "PRESS CLEAR TO CHANGE" & KeyPadInPutPassword, 1, 0)
                  'PasswordChange1
          End Select
       Else
          updateCashierScreen(CashierDisplayComSettings, "USER OR PASSWORD", 0, "NOT FOUND. PRESS CLEAR" & KeyPadInPutPassword, 1, 0)
            Do : Dim KeyPress As Long = GetKeyNB
               Select Case KeyPress
                  Case Key_Clear
                     KeyPadInput = ""
                     RequestCashierID
                     Exit Sub
               End Select
            Loop
       End If
    End If


it is put in ANY USER number and password i get a error box show up saying "FBtemp has stopped working" after it shows the following data

Client info : 6.1.11
Host info : 192.168.1.199 via TCP/IP
Server info : 5.5.60-0+deb8u1

have i miss used some of the code?

[grindstone]

i think I have broken FreeBASIC he he

To me it looks rather as something like a segmentation fault (usually caused by trying to dereference a NULL pointer). And I suppose it's your IDE that opens that message box.

In your snippet only the first row of the result set is checked. Is this the desired behaviour?

[Gablea]

What I am trying to do is to check to see if any results have come from the server if they have then process them otherwise show a error message

So say the user entered the correct user ID and password it would then run this code

Code: Select all

Dim PasswordChange As Integer = *row[4]

          Select Case PasswordChange
             Case 0 'No Change required
                         KeyPadInput = ""
                    CashierNamePrint = Trim(*row[3])
                         CashierAccess = Trim(*row[5])    'Set the system to use the signed on cashier
               CashierOverrideAccess = 0
                  PriceOverridePrice = 0                                 'Clears the Entered Price
                             TotalDue = 0                                  'Resets sale value
                            ItemsSold = 0                                  'Resets item count
                       TotalTendered = 0                                 'Resert the total tendered value
                         RecipitClear = 1                                 'Set the recipit so a new one can be produced for sale
                             SaleMode = "Sale"                           'Sets the salemode back to sale
                   'RecallInProgress = "No"                              'Resets the Recall trigger so the system will run in normal mode
                      ShowTaskBarItems = 1
                     SubTotalPressed = 0
                        DisplayLine1 = ""
                        DisplayLine2 = ""     
                       
                   'Close the API access.
               mysql_close(db)
               Salescreen         
             
             Case 1   ' Password needs to be changed
               mysql_close(db)
                   updateCashierScreen(CashierDisplayComSettings, "PASSWORD EXPIRED", 0, "PRESS CLEAR TO CHANGE" & KeyPadInPutPassword, 1, 0)
                  'PasswordChange1
          End Select


otherwise if they enter either the wrong user ID or password or both the system would then process this bit of code

Code: Select all

updateCashierScreen(CashierDisplayComSettings, "USER OR PASSWORD", 0, "NOT FOUND. PRESS CLEAR" & KeyPadInPutPassword, 1, 0)
            Do : Dim KeyPress As Long = GetKeyNB
               Select Case KeyPress
                  Case Key_Clear
                     KeyPadInput = ""
                     RequestCashierID
                     Exit Sub
               End Select
            Loop


[grindstone]

So it's purport that the query delivers either one row (if user ID and password match) or none (otherwise).

The only mistake that I can find in your snippet is that you close the API access (mysql_close(db)) without first releasing the memory (mysql_free_result(restab)). If the snippet is running in a loop this probably causes a memory leak that ends in a crash.

If that's not the reason, maybe insert a SLEEP:END behind

Code: Select all

'Print informations about the RDBMS host and the data base.
Print "Client info : "; *mysql_get_client_info()
Print "  Host info : "; *mysql_get_host_info(db)
Print "Server info : "; *mysql_get_server_info(db)

and run the program. If it stops without crashing, move the SLEEP:END one line down, run the program again and so on, until you find the line that causes the crash.

[Gablea]

@grindstone

I shall update the code with that bit added and see what happens.

So from what you can see am i on the right path?

[grindstone]

So from what you can see am i on the right path?

Apart from the common teething troubles the conception seems to work. So I would say: "yes".

Maybe you should try to rather use local variables than global ones if possible (and reasonable). From my experience this makes the maintenance a lot easier.

[Gablea]

Yay :)

Is it possible to do in memory table with MySQL? (Local to the machine)

[grindstone]

Sorry, I don't understamd what you mean. What do you want to load into memory? The result table or the database? Or are you thinking of loading a copy of the database to each till computer?

[badidea]

Is it possible to do in memory table with MySQL? (Local to the machine)

The idea behind a database server is that you have all the data on 1 machine only and the clients with very limited data, only the data needed a a specific time. Every request or change is done via the database server. If the client catches fire, no problem. If two clients want to update the same product amount, (nearly) no problem.

[Gablea]

I understand that but my VB.net version it uses a in memory table to control the receipt data as well as any promotions and i have almost all the code created for all the offers etc so I would like if possible to replicate the function with FreeBASIC