Where have I gone wrong

General FreeBASIC programming questions.
Post Reply
grindstone
Posts: 862
Joined: May 05, 2015 5:35
Location: Germany

Re: Where have I gone wrong

Post by 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
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Where have I gone wrong

Post by 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
Posts: 862
Joined: May 05, 2015 5:35
Location: Germany

Re: Where have I gone wrong

Post by 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
Posts: 2157
Joined: Feb 26, 2007 5:32

Re: Where have I gone wrong

Post by 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
Posts: 862
Joined: May 05, 2015 5:35
Location: Germany

Re: Where have I gone wrong

Post by 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
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Where have I gone wrong

Post by 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
Posts: 862
Joined: May 05, 2015 5:35
Location: Germany

Re: Where have I gone wrong

Post by grindstone »

Gablea wrote: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
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Where have I gone wrong

Post by 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
Posts: 862
Joined: May 05, 2015 5:35
Location: Germany

Re: Where have I gone wrong

Post by 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
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Where have I gone wrong

Post by 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
Posts: 862
Joined: May 05, 2015 5:35
Location: Germany

Re: Where have I gone wrong

Post by grindstone »

Gablea wrote: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.
Last edited by grindstone on Oct 29, 2018 15:55, edited 1 time in total.
Gablea
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Where have I gone wrong

Post by Gablea »

Yay :)

Is it possible to do in memory table with MySQL? (Local to the machine)
grindstone
Posts: 862
Joined: May 05, 2015 5:35
Location: Germany

Re: Where have I gone wrong

Post by 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
Posts: 2586
Joined: May 24, 2007 22:10
Location: The Netherlands

Re: Where have I gone wrong

Post by badidea »

Gablea wrote: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
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Where have I gone wrong

Post by 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
Post Reply