Where have I gone wrong

General FreeBASIC programming questions.
Post Reply
Gablea
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Where have I gone wrong

Post by Gablea »

@grindstone

Thank you for that example. i will have a look at it once i have worked out how to get the database files to work right. (still struggling a little with that one)
grindstone
Posts: 862
Joined: May 05, 2015 5:35
Location: Germany

Re: Where have I gone wrong

Post by grindstone »

What database do you want to implement? MySQL? Or something proprietary?
Gablea
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Where have I gone wrong

Post by Gablea »

Ideally I would like to use MySQL as I could use a windows back office and a Linux front end.
grindstone
Posts: 862
Joined: May 05, 2015 5:35
Location: Germany

Re: Where have I gone wrong

Post by grindstone »

Gablea wrote:Ideally I would like to use MySQL as I could use a windows back office and a Linux front end.
That's good. There are already some freeBASIC implementations of MySQL access, so it shouldn't be too hard to get it working. I never really pottered at MySQL, and this would be a good opportunity to close that knowledge gap <smile>.

Do you already have a program to translate your data files to MySQL format?
jj2007
Posts: 2326
Joined: Oct 23, 2016 15:28
Location: Roma, Italia
Contact:

Re: Where have I gone wrong

Post by jj2007 »

grindstone wrote:That's good.
Have a look at earlier posts (POS etc), where we talked about using the much simpler tab-delimited format. IMHO it would be better to concentrate on getting other parts running. SQL might be too challenging.
Gablea
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Where have I gone wrong

Post by Gablea »

@grindstone

My windows PoS is already using the MySQL database.

I have tried a few times to get MySQL working but I have hit and miss with it (sometimes it work sometimes it does not)

@jj2007
I agree with your option. a simple data file would be better and it would allow me to still support DOS files.

Just need to have the time to actually sit down and work out how to implement the ideas you have put forward (if you could a example of the cashier table would be most helpful)
grindstone
Posts: 862
Joined: May 05, 2015 5:35
Location: Germany

Re: Where have I gone wrong

Post by grindstone »

jj2007 wrote:...where we talked about using the much simpler tab-delimited format. .... SQL might be too challenging.
MySQL is in the main using this tab-delimited format.

Alas in german, there's an excellent MySQL tutorial, especially related to freeBasic. You should have a look at it anyway, for there's a vivid depiction of the MySQL table's structure (Here you can download a small example of a MySOL database, containing 8 tables).

In addition to this, there's a code example for a freeBASIC MySQL access. If you're interested, I'd translate it for you.
Gablea
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Where have I gone wrong

Post by Gablea »

@gindstone

Yes please if you could translate it I would appreachate it

I know if I went with MySQL I would no long have dos support but Linux in text mode is almost the same.
grindstone
Posts: 862
Joined: May 05, 2015 5:35
Location: Germany

Re: Where have I gone wrong

Post by grindstone »

Here you are. I've added an english description to the code example.
Gablea
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Where have I gone wrong

Post by Gablea »

Thank you. I shall have a read of that tonight. And I will see if I can implement it into my simple KPoS application
Last edited by Gablea on Oct 25, 2018 7:33, edited 1 time in total.
jj2007
Posts: 2326
Joined: Oct 23, 2016 15:28
Location: Roma, Italia
Contact:

Re: Where have I gone wrong

Post by jj2007 »

grindstone wrote:Here you are. I've added an english description to the code example.
Excellent example, Grindstone!
grindstone
Posts: 862
Joined: May 05, 2015 5:35
Location: Germany

Re: Where have I gone wrong

Post by grindstone »

Thank you, I hope my english is not too bad. :-)
jj2007
Posts: 2326
Joined: Oct 23, 2016 15:28
Location: Roma, Italia
Contact:

Re: Where have I gone wrong

Post by jj2007 »

Dein Englisch ist super, aber die deutsche Version ist mindestens genauso gut ;-)
grindstone
Posts: 862
Joined: May 05, 2015 5:35
Location: Germany

Re: Where have I gone wrong

Post by grindstone »

jj2007 wrote:...die deutsche Version ist mindestens genauso gut ;-)
MOD ist ja auch ein hervorragender Autor.
Gablea
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Where have I gone wrong

Post by Gablea »

@grindstone

Thank you so much for the code examples

I just need some advice on the following


Image

as you can see the system does find my User ID on the system no how would i get the following populate from the result of the SQL Search?


KeyPadInput = ""
CashierNamePrint = Trim(CashierDatabase.CashierNameReceipit)
CashierAccess = Trim(CashierDatabase.useraccesslevel) '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 = ""
Salescreen

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; " - ";
                    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 )
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)

Am I following the example correctly?
Post Reply