Mysql advice

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

Mysql advice

Postby Gablea » Aug 31, 2017 20:21

hi all,

I have been using mysql for a while with my windows application but i am having a major problem with converting the same functions to freebasic

below is the database module from my windows app

Code: Select all

' This Module Handles all the Database Functions

Module DatabaseFunctions
    Dim Exception As String

    Public Sub ConnectToDatabaseServer(ByVal ConnecttionType As String)
        ConnectionString = vbNullString
        ConnectionString += "Server=" & ServerLocation & ";"
        ConnectionString += "Port=" & DatabasePortNumber & ";"
        ConnectionString += "Database=" & DatabaseName & ";" ' DatabaseName
        ConnectionString += "Uid=" & DatabaseUser & ";"
        ConnectionString += "Pwd=" & DatabasePassword & ";"

        Select Case ConnecttionType
            Case "Database"
                dbCon = New MySqlConnection(ConnectionString)

            Case "Thread"
                ThreaddbCon = New MySqlConnection(ConnectionString)

            Case "VAT"
                VATdbCon = New MySqlConnection(ConnectionString)
        End Select
    End Sub

    Public Sub LoadCashierList()
        SQLCommand = vbNullString
        SQLCommand += "SELECT "
        SQLCommand += "userid, "
        SQLCommand += "username "
        SQLCommand += "from usertable "
        SQLCommand += "Where usertype='P' "
        SQLCommand += "order by userid ASC;"

        MySQLCmd = New MySqlCommand(SQLCommand, dbCon)

        'Connect to the Database and find the SQLCommand
        dbCon.Open()

        MySQLDR = MySQLCmd.ExecuteReader
    End Sub

    Public Sub FindCashier(ByVal CashierID As String)
        ConnectToDatabaseServer("Database")

        SQLCommand = vbNullString
        SQLCommand += "select "
        SQLCommand += "userid, "
        SQLCommand += "userpassword, "
        SQLCommand += "username, "
        SQLCommand += "usernameposrecipit, "
        SQLCommand += "changepassword, "
        SQLCommand += "useraccesslevel "
        SQLCommand += "from usertable "
        SQLCommand += "where userid='" & CashierID & "';"

        MySQLCmd = New MySqlCommand(SQLCommand, dbCon)
        'Connect to the Database and find the SQLCommand
        If dbCon.State = ConnectionState.Open Then dbCon.Close()
        dbCon.Open()



        MySQLDR = MySQLCmd.ExecuteReader

        MySQLDR.Read()

        If MySQLDR.HasRows = True Then
            CashierNameSystemWide = MySQLDR.Item("username")
            CashierNumberSystemWide = MySQLDR.Item("userid")
            frmcashierSignOn.labCashierName.Text = CashierNameSystemWide
        Else
            frmcashierSignOn.Enabled = False

            With FrmErrorMessage
                .labCallingForm.Text = "CashierID"
                .labErrorMessage.Text = vbNullString
                .labErrorMessage.Text += "the Cashier ID you just entered can not be found in the system." & vbNewLine
                .labErrorMessage.Text += vbNewLine
                .labErrorMessage.Text += "Please check and try again" & vbNewLine
                .MdiParent = frmBackground
                .Show()
                .Focus()
            End With
            MySQLDR.Close()
        End If

        dbCon.Close()
    End Sub

    Public Sub CheckCashierPassword(ByVal Password As String)
        ConnectToDatabaseServer("Database")

        SQLCommand = vbNullString
        SQLCommand += "select "
        SQLCommand += "userid, "
        SQLCommand += "userpassword, "
        SQLCommand += "username, "
        SQLCommand += "usernameposrecipit, "
        SQLCommand += "changepassword, "
        SQLCommand += "useraccesslevel "
        SQLCommand += "from usertable "
        SQLCommand += "where userid='" & CashierNumberSystemWide & "';"

        MySQLCmd = New MySqlCommand(SQLCommand, dbCon)
        'Connect to the Database and find the SQLCommand
        If dbCon.State = ConnectionState.Open Then dbCon.Close()
        dbCon.Open()
        MySQLDR = MySQLCmd.ExecuteReader


        MySQLDR.Read()

        If MySQLDR.Item("userpassword") = Password Then
            Select Case MySQLDR.Item("changepassword")
                Case 1 ' Change Password
                    CashierAccessSystemWide = MySQLDR.Item("useraccesslevel")
                    frmcashierSignOn.Close()
                    frmCashierChangePassword.MdiParent = frmBackground
                    frmCashierChangePassword.Show()
                    frmCashierChangePassword.Focus()
                    MySQLDR.Close()

                Case 0
                    frmBackground.LabcashierNumber.Text = MySQLDR.Item("userid")
                    LineNumberSelected = 1

                    Select Case frmcashierSignOn.labCashierFunction.Text
                        Case "First"
                            CashierNumberSystemWide = MySQLDR.Item("userid")
                            CashierNameSystemWide = MySQLDR.Item("username")
                            CashierAccessSystemWide = MySQLDR.Item("useraccesslevel")
                            SignedOff = 0
                            frmBackground.LabTransactionNumber.Text = String.Format("{0:000000}", TransNumber)
                            frmBackground.Refresh()

                            MySQLDR.Close()
                            dbCon.Close()


                            frmcashierSignOn.Close()
                            frmCashierChangePassword.Close()
                            With frmSaleScreen
                                .MdiParent = frmBackground
                                .Location = New Point(0, 0)
                                .Show()
                                .Focus()
                            End With
                    End Select

                Case "Second"   ' Override Screen
                    CashierNumberSystemWide1 = CashierNumberSystemWide
                    CashierNameSystemWide1 = CashierNameSystemWide
                    CashierAccessSystemWide1 = CashierAccessSystemWide

                    CashierNumberSystemWide = MySQLDR.Item("userid")
                    CashierNameSystemWide = MySQLDR.Item("username")
                    CashierAccessSystemWide = MySQLDR.Item("useraccesslevel")

                    frmcashierSignOn.Close()
                    frmCashierChangePassword.Close()

                    Select Case functionNameNeeded
                        Case "NoSale"
                            NoSalefunction()

                        Case "Refund"
                            RefundFunction()
                        Case "Exchange"
                            ExchangeFunction()
                        Case "SafeDrop"
                            SafeDropFunction()
                        Case "ClosePoS"
                            ClosePoSFunction()
                        Case "Payout"
                            PayOutFunction()
                        Case "XReport"
                            PoSXReportFunction()
                        Case "FloatIn"
                            FloatInFunction()
                        Case "FloatOut"
                            FloatOutFunction()
                        Case "VoidSingleItem"
                            VoidLastItem()
                        Case "VoidSale"
                            VoidSaleFunction()
                        Case "PriceOverRide"
                            PriceOverRideFunction()
                    End Select

                Case "StoreSale"
                    StoreSaleFunction()
                Case "RecallSale"
                    RecallSaleFunction()

                Case "DiscountOnItem"
                    DiscountOnItemFunction(DiscountType)
                Case "DiscountOnSale"
                    DiscountOnSaleFunction(DiscountType)
            End Select
        Else
            frmcashierSignOn.Enabled = False

            With FrmErrorMessage
                .labCallingForm.Text = "CashierPassword"
                .labErrorMessage.Text = vbNullString
                .labErrorMessage.Text += "Your password does not match the one in the database" & vbNewLine
                .labErrorMessage.Text += vbNewLine
                .labErrorMessage.Text += "Please try again" & vbNewLine
                .MdiParent = frmBackground
                .Show()
                .Focus()
            End With
            MySQLDR.Close()
        End If
    End Sub

    Public Sub CashierPasswordChanged()
        With frmBackground
            .LabcashierNumber.Text = CashierNumberSystemWide
            .LabTransactionNumber.Text = Format(TransNumber, "000000")
            .Refresh()
        End With

        SignedOff = 0

        frmcashierSignOn.Close()
        frmCashierChangePassword.Close()

        With frmSaleScreen
            .Top = 0
            .Left = 0
            .MdiParent = frmBackground
            .Show()
            .Focus()
        End With
    End Sub

    Public Sub UpdateCashierRecord(ByVal CasherNumber As String, ByRef CashierPassword As String)
        SQLCommand = vbNullString
        SQLCommand += "update usertable "
        SQLCommand += "SET "
        SQLCommand += "userpassword='" & CashierPassword & "', "
        SQLCommand += "changepassword='0' "
        SQLCommand += "Where userid='" & CasherNumber & "';"
        SendToDatabase(SQLCommand)
    End Sub

    Public Sub LoadManualDepts()
        SQLCommand = vbNullString
        SQLCommand += "select "
        SQLCommand += "deptnumber, "
        SQLCommand += "deptname, "
        SQLCommand += "deptvatcode "
        SQLCommand += "from manualdepttable "
        SQLCommand += "order by deptnumber ASC;"

        MySQLCmd = New MySqlCommand(SQLCommand, dbCon)

        'Connect to the Database and find the SQLCommand
        If dbCon.State = ConnectionState.Open Then dbCon.Close()
        dbCon.Open()

        MySQLDR = MySQLCmd.ExecuteReader
    End Sub

    Public Sub FindKeyinMenu(ByVal KeyID As String)
        SQLCommand = vbNullString
        SQLCommand += "select "
        SQLCommand += "plulistid, "
        SQLCommand += "Description_1, "
        SQLCommand += "Description_2, "
        SQLCommand += "listtype, "
        SQLCommand += "barcode "
        SQLCommand += "from plumenu "
        SQLCommand += "Where plulistid='" & KeyID & "';"
    End Sub

    Public Sub FindManualDepts(ByVal KeyID As String)
        SQLCommand = vbNullString
        SQLCommand += "select "
        SQLCommand += "deptnumber, "
        SQLCommand += "deptname, "
        SQLCommand += "deptvatcode "
        SQLCommand += "from manualdepttable "
        SQLCommand += "where deptnumber='" & KeyID & "';"
    End Sub

    Public Sub SelectproductData(ByVal ItemCode As String)
        SQLCommand = vbNullString
        SQLCommand += "select "
        SQLCommand += "barcodenumber, "
        SQLCommand += "posdescription, "
        SQLCommand += "shelfedgelabelDescription, "
        SQLCommand += "agerestricted, "
        SQLCommand += "agelimit, "
        SQLCommand += "pricetype, "
        SQLCommand += "retailprice, "
        SQLCommand += "vatcode, "
        SQLCommand += "print_guarantee_message, "
        SQLCommand += "print_guarantee_code, "
        SQLCommand += "requestserial, "
        SQLCommand += "itemnotallowed, "
        SQLCommand += "itemnotallowed_reason "
        SQLCommand += "from producttable "
        SQLCommand += "Where barcodenumber='" & ItemCode & "';"
    End Sub

    Public Sub ProcessManDept(ByVal DeptID As String, ByVal LinePrice As Integer, ByVal VATCode As String)
        ' Find out if the button is a Direct sale key or a Sub key

        ConnectToDatabaseServer("Database")

        Call FindManualDepts(DeptID)

        If dbCon.State <> ConnectionState.Open Then
            dbCon.Open()
        End If

        With MySQLCmd
            .Connection = dbCon
            .CommandText = SQLCommand
        End With

        MySQLDR = MySQLCmd.ExecuteReader()

        If MySQLDR.HasRows Then
            MySQLDR.Read()
            ManDeptSale(DeptID, LinePrice, MySQLDR.Item("deptname"), VATCode)
        End If

        dbCon.Close()
    End Sub

    Public Sub SaveJournalRollToDatabase(ByVal Journaldate As String, ByVal JournalTime As String, ByVal JournalPoSNumber As String, ByVal JournalTransactionNumber As String, ByVal JournalCashierNumber As String, ByVal JournalData As String, ByVal JournalRecDataPrint As String)
        SQLCommand = vbNullString
        SQLCommand += "insert into journalroll "
        SQLCommand += "Values ('"
        SQLCommand += Journaldate & "','"
        SQLCommand += JournalTime & "','"
        SQLCommand += JournalPoSNumber & "','"
        SQLCommand += JournalTransactionNumber & "','"
        SQLCommand += JournalCashierNumber & "','"
        SQLCommand += Replace(JournalData, "'", "''") & "','"
        SQLCommand += Replace(JournalRecDataPrint, "'", "''") & "','"

        If storeEFT = 0 Then
            SQLCommand += "0','" ' No EFT Slip
        Else
            SQLCommand += "1','" ' EFT Slips have been stored in the database
        End If


        If BarcodeRefund = 1 Then
            SQLCommand += RecTransNumber & "'"
        Else
            SQLCommand += "'"
        End If

        SQLCommand += ")"

        SendToDatabase(SQLCommand)
    End Sub

    Public Sub SaveEFTSlip(ByVal eftDate As String, ByVal eftTime As String, ByVal PoSNumber As String, ByVal TransNumber As String, ByVal eftView As String, ByVal eftPrint As String)
        SQLCommand = vbNullString
        SQLCommand += "insert into eftslips "
        SQLCommand += "Values ('"
        SQLCommand += eftDate & "','"
        SQLCommand += eftTime & "','"
        SQLCommand += PoSNumber & "','"
        SQLCommand += TransNumber & "','"
        SQLCommand += eftView & "','"
        SQLCommand += eftPrint & "')"

        SendToDatabase(SQLCommand)
    End Sub

    Public Sub GetRecipitDataSQL()
        SQLCommand = vbNullString
        SQLCommand += "select "
        SQLCommand += "journaldate, "
        SQLCommand += "journaltime, "
        SQLCommand += "posnumber, "
        SQLCommand += "transactionnumber, "
        SQLCommand += "cashiernumber, "
        SQLCommand += "journalentry, "
        SQLCommand += "recdata, "
        SQLCommand += "eftslip, "
        SQLCommand += "receipt_number "
        SQLCommand += "From journalroll "
        SQLCommand += "Where journaldate='" & RecDate & "' "
        SQLCommand += "and posnumber='" & PoSNumber & "' "
        SQLCommand += "Order by transactionnumber DESC;"
    End Sub

    Public Sub GetRecipitTransValueFromDatabase(ByVal Transnumber As String)
        SQLCommand = vbNullString
        SQLCommand += "select "
        SQLCommand += "totalsold "
        SQLCommand += "From timesales "
        SQLCommand += "Where datecol='" & RecDate & "' "
        SQLCommand += "and posnumber='" & PoSNumber & "' "
    End Sub

    Public Sub GetPayOutReasonsSQL()
        SQLCommand = vbNullString
        SQLCommand += "select "
        SQLCommand += "reasonid, "
        SQLCommand += "descriptiondis, "
        SQLCommand += "descriptionpos, "
        SQLCommand += "requestreason, "
        SQLCommand += "requestdelivery, "
        SQLCommand += "requestserialno "
        SQLCommand += "From payoutreasons "
        SQLCommand += "Order by reasonid ASC;"
    End Sub

    Public Sub FindPayOutReasonsSQL(ByVal ReasonID As String)
        SQLCommand = vbNullString
        SQLCommand += "select "
        SQLCommand += "reasonid, "
        SQLCommand += "descriptiondis, "
        SQLCommand += "descriptionpos, "
        SQLCommand += "requestreason, "
        SQLCommand += "requestdelivery, "
        SQLCommand += "requestserialno "
        SQLCommand += "From payoutreasons "
        SQLCommand += "Where reasonid='" & ReasonID & "';"

        MySQLCmd = New MySqlCommand(SQLCommand, dbCon)

        'Connect to the Database and find the SQLCommand
        dbCon.Open()

        MySQLDR = MySQLCmd.ExecuteReader
    End Sub

    Public Sub FindSelectedTrans(ByVal TransNumber As String)
        SQLCommand = vbNullString
        SQLCommand += "select "
        SQLCommand += "journaldate, "
        SQLCommand += "journaltime, "
        SQLCommand += "posnumber, "
        SQLCommand += "transactionnumber, "
        SQLCommand += "cashiernumber, "
        SQLCommand += "JournalEntry, "
        SQLCommand += "RecDataPrint "
        SQLCommand += "From journalroll "
        SQLCommand += "Where journaldate='" & RecDate & "' "
        SQLCommand += "and posnumber='" & PoSNumber & "' "
        SQLCommand += "and transactionnumber='" & TransNumber & "'; "

        MySQLCmd = New MySqlCommand(SQLCommand, dbCon)

        'Connect to the Database and find the SQLCommand
        dbCon.Open()

        MySQLDR = MySQLCmd.ExecuteReader
    End Sub

    Public Sub FindGuaranteeMessage(ByVal ProductName As String, ByVal GuaranteeMessageNumber As String)
        If SaleMode = "Sale" Then
            If GuaranteeMessageNumber <> "00000" Then
                SQLCommand = vbNullString
                SQLCommand += "Select "
                SQLCommand += "messageid, "
                SQLCommand += "messagetitle,"
                SQLCommand += "GuaranteeMessage "
                SQLCommand += "From guaranteemessage "
                SQLCommand += "Where messageid='" & Format(Val(GuaranteeMessageNumber), "00000") & "';"
            End If


            ConnectToDatabaseServer("Database")

            MySQLCmd.Connection = dbCon
            'open connection
            If dbCon.State = ConnectionState.Open Then dbCon.Close()
            dbCon.Open()


            MySQLCmd.CommandText = SQLCommand

            MySQLDR = MySQLCmd.ExecuteReader

            If MySQLDR.HasRows Then
                MySQLDR.Read()
                If GuaranteeMessage <> "" Then
                    GuaranteeMessage += vbNewLine & vbNewLine
                End If

                GuaranteeMessage += Printer_AlignLeft & Printer_Font_Normal & Printer_Font_Bold & "Product : " & ProductName & vbNewLine
                GuaranteeMessage += Printer_AlignLeft & Printer_Font_Normal & MySQLDR.Item("GuaranteeMessage") & vbNewLine
                GuaranteeMessage += Printer_AlignLeft & Printer_Font_Normal : RepeatChrsGuaranteeMessage(RecipitWidth, "-")
                'GuaranteeMessage += vbNewLine
            End If

            MySQLDR.Close()
            MySQLDR.Dispose()
            dbCon.Close()
        End If
    End Sub

    Public Sub RepeatChrsGuaranteeMessage(ByVal Number As Integer, ByVal Characters As String)
        Dim Counter As Integer
        Dim Local_String As String = vbNullString

        For Counter = 1 To Number
            Local_String += Characters
        Next
        GuaranteeMessage += Local_String
    End Sub

    Public Sub FindCouponInDatabase(ByVal CouponBarcode As String)
        Try
            SQLCommand = vbNullString
            SQLCommand += "select "
            SQLCommand += "couponbarcode, "
            SQLCommand += "couponposdescription, "
            SQLCommand += "username, "
            SQLCommand += "usernameposrecipit, "
            SQLCommand += "couponrestriced, "
            SQLCommand += "itembarcode, "
            SQLCommand += "coupnposvalue, "
            SQLCommand += "vatcode "
            SQLCommand += "from scanningcoupontable "
            SQLCommand += "where couponbarcode='" & CouponBarcode & "';"

            MySQLCmd = New MySqlCommand(SQLCommand, dbCon)

            'Connect to the Database and find the SQLCommand
            dbCon.Open()

            MySQLDR = MySQLCmd.ExecuteReader

            While MySQLDR.Read
                CashierNameSystemWide = MySQLDR.Item("username")
                CashierNumberSystemWide = MySQLDR.Item("userid")

            End While

            MySQLDR.Close()
            dbCon.Close()

        Catch ex As Exception
            MySQLDR.Close()
            If dbCon.State = ConnectionState.Open Then
                dbCon.Close()
            End If

            MakeNoise()

            FrmTenderScreen.Enabled = False

            With FrmErrorMessage
                .labCallingForm.Text = "CouponError"
                .labErrorMessage.Text = "Sorry the coupon you just scanned was not found in the system. Please use manual coupon to process it now and then have it added to the system."
                .MdiParent = frmBackground
                .Show()
                .Focus()
            End With

        End Try
    End Sub

    Public Sub ListPLUMainMenu()
        PLUDisplayList.Clear()
        frmPLU.ListPLUItems.Items.Clear()

        Try
            ConnectToDatabaseServer("Database")

            LoadMainPLUMenu()

            MySQLCmd.Connection = dbCon
            'open connection
            If dbCon.State = ConnectionState.Open Then dbCon.Close()
            dbCon.Open()

            MySQLCmd.CommandText = SQLCommand

            MySQLDR = MySQLCmd.ExecuteReader

            While MySQLDR.Read
                AddToPLUList(MySQLDR.Item("plulistid"), MySQLDR.Item("Description_1"))
            End While
            MySQLDR.Close()

            frmSaleScreen.Enabled = False

            With frmPLU
                .Location = New Point(0, 0)
                .labPLUFunction.Text = "PLUMenu"
                .Top = 0
                .Left = 0
                .MdiParent = frmBackground
                .Show()
                .Focus()
            End With


        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, AppName)
        Finally
            With MySQLDR
                .Dispose()
                .Close()
            End With

            With dbCon
                .Dispose()
                .Close()
            End With
        End Try
    End Sub

    Private Sub LoadMainPLUMenu()
        SQLCommand = vbNullString
        SQLCommand += "select "
        SQLCommand += "plulistid, "
        SQLCommand += "Description_1 "
        SQLCommand += "from plumenu "
        SQLCommand += "Where listtype='L' "
        SQLCommand += "order by plulistid ASC;"

        MySQLCmd = New MySqlCommand(SQLCommand, dbCon)

        'Connect to the Database and find the SQLCommand
        With dbCon
            .Dispose()
            .Close()
        End With
    End Sub

    Public Sub ListPLUSubMenus(ByVal PLUID As String)
        PLUDisplayList.Clear()
        frmPLU.ListPLUItems.Items.Clear()

        Try
            ConnectToDatabaseServer("Database")

            FindSubPLUItems(PLUID)

            MySQLCmd.Connection = dbCon
            'open connection
            If dbCon.State = ConnectionState.Open Then dbCon.Close()
            dbCon.Open()
            MySQLCmd.CommandText = SQLCommand

            MySQLDR = MySQLCmd.ExecuteReader

            While MySQLDR.Read
                AddToPLUList(MySQLDR.Item("barcodenumber"), MySQLDR.Item("description"))
            End While
            MySQLDR.Close()

            frmPLU.labPLUFunction.Text = "PLUList"

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, AppName)
        Finally
            With MySQLDR
                .Dispose()
                .Close()
            End With

            With dbCon
                .Dispose()
                .Close()
            End With
        End Try
    End Sub

    Private Sub FindSubPLUItems(ByVal PLUGroupID As String)
        SQLCommand = vbNullString
        SQLCommand += "select "
        SQLCommand += "plulistid, "
        SQLCommand += "description, "
        SQLCommand += "barcodenumber "
        SQLCommand += "from plulist "
        SQLCommand += "Where plulistid='" & Format(Val(PLUGroupID), "0000") & "' "
        SQLCommand += "order by description ASC;"

        MySQLCmd = New MySqlCommand(SQLCommand, dbCon)

        'Connect to the Database and find the SQLCommand
        If dbCon.State = ConnectionState.Open Then dbCon.Close()
        dbCon.Open()
    End Sub

    Public Sub SeeifLottery(ByVal barcodenumber As String)
        SelectLotteryType(barcodenumber)

        Select Case ItemIsLottery
            Case 0 ' Not Lttery so a normal product
                SellProduct(barcodenumber, 0)
                ItemIsLottery = 0

            Case 1 ' Is lottery so sell it
                FindLotterySettings(barcodenumber)
                ItemIsLottery = 0
        End Select
    End Sub

    ' Tacking functions
    Public Sub AddToAgeRefusalTable(ByVal ReasonDate As String, ByVal ReasonTime As String, ByVal TerminalNumber As String, ByVal TransactionNumber As String, ByVal BarcodeNumber As String, ByVal PoSDescription As String, ByVal RefundReason As String)

        SQLCommand = vbNullString
        SQLCommand += "insert into agerefusallog "
        SQLCommand += "values ('"
        SQLCommand += Trim(ReasonDate) & "','"
        SQLCommand += Trim(ReasonTime) & "','"
        SQLCommand += Trim(TerminalNumber) & "','"
        SQLCommand += Trim(CashierNumberSystemWide) & "','"
        SQLCommand += Trim(TransactionNumber) & "','"

        SQLCommand += Trim(BarcodeNumber) & "','"
        SQLCommand += Trim(PoSDescription) & "','"
        SQLCommand += Trim(RefundReason) & "')"

        SendToDatabase(SQLCommand)
    End Sub

    Public Sub AddToReasonDatabase(ByVal ReasonDate As String, ByVal ReasonTime As String, ByVal TerminalNumber As String, ByVal TransactionNumber As String, ByVal RefundReason As String)

        SQLCommand = vbNullString
        SQLCommand += "insert into nosalereasonlog "
        SQLCommand += "values ('"
        SQLCommand += Trim(ReasonDate) & "','"
        SQLCommand += Trim(ReasonTime) & "','"
        SQLCommand += Trim(TerminalNumber) & "','"
        SQLCommand += Trim(CashierNumberSystemWide) & "','"
        SQLCommand += Trim(TransactionNumber) & "','"
        SQLCommand += Trim(RefundReason) & "')"

        SendToDatabase(SQLCommand)
    End Sub

End Module


what i need to do is convert this so it works in freebasic (first off would this be possible) i can convert things that are not to do with the mysql functions for example When I scan a product code it needs to check the database to see if it a lottery ticket if it is not it should then process it. if it find ity is a Lottery ticket it should then process the sale according

Sorry if I keep repeating myself but why does the idea work fine in Windows but I can not get it to work in FreeBASIC.
Last edited by Gablea on Aug 31, 2017 21:05, edited 1 time in total.
MrSwiss
Posts: 1928
Joined: Jun 02, 2013 9:27
Location: Switzerland

Re: Mysql advice

Postby MrSwiss » Aug 31, 2017 21:03

Gablea wrote:

Code: Select all

Imports MySql.Data
Imports MySql.Data.MySqlClient
Since nobody (here) knows, what those "Import" files contain, the following
question/questions are all for nothing ... (probably some VB.NET stuff).

In FB you'd probably have to start, by "recoding" the imported stuff first ...
Gablea
Posts: 850
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Mysql advice

Postby Gablea » Aug 31, 2017 21:15

I have edited the code to remove the 2 lines that was for VB.net

All I need is help on getting it to work in Freebasic (I have managed once before but i kept remembering data from the previous item so i had to drop it and revert back to the old database format (files on the RAMDrive)

I know i still have a major amount of work to do with the program and getting it to work right with the MySQL database server is on my to do list but I can not understand why one type of code layout would work fine in one language but not in another as they are both based on BASIC.

Example to sell a item My Software would call SeeifLottery(Me.labInputText.Text) (the code would come from the label)

so for FreeBASIC is would do for example

SeeifLottery("5018374320766")

Code: Select all

Public Sub SeeifLottery(ByVal barcodenumber As String)

dim NationalLotteryID As String = "2083801"
dim EuroMillionsID As String = "2083803"
dim ThunderballID As String = "2083804"
dim HotPicksID As String = "2083805"
   
        Select Case Mid$(BarcodeNumber, 1, 7)
            Case NationalLotteryID
                ItemIsLottery = 1
                LotteryIDNumber = NationalLotteryID
            Case EuroMillionsID
                ItemIsLottery = 1
                LotteryIDNumber = EuroMillionsID
            Case ThunderballID
                ItemIsLottery = 1
                LotteryIDNumber = ThunderballID
            Case HotPicksID
                ItemIsLottery = 1
                LotteryIDNumber = HotPicksID
        End Select

        Select Case ItemIsLottery
            Case 0 ' Not Lttery so a normal product
                SellProduct(barcodenumber, 0)
                ItemIsLottery = 0

            Case 1 ' Is lottery so sell it
                FindLotterySettings(barcodenumber)
                ItemIsLottery = 0
        End Select
    End Sub


as you can see by the code it would search for a item if it is not a lottery ticket it will then call the sellproduct sub function
Last edited by Gablea on Aug 31, 2017 21:46, edited 1 time in total.
St_W
Posts: 1057
Joined: Feb 11, 2009 14:24
Location: Austria
Contact:

Re: Mysql advice

Postby St_W » Aug 31, 2017 21:24

MrSwiss wrote:Since nobody (here) knows, what those "Import" files contain, the following
question/questions are all for nothing ... (probably some VB.NET stuff).

It's the ADO.NET driver for MySQL and one basically doesn't need to know its contents to understand the basics of the code and give an estimation whether that can be done in FreeBasic.
And yes, it can definitely be implemented in FreeBasic too. A rewrite wouldn't hurt anyway because the code does not follow a good coding style. For example please use prepared statements instead of piecing together SQL query strings. When implementing the whole thing in .NET or Java please use a ORM (like Entity Framework). That is not available for FB (at least I don't know any).
Unfortunately I fear that the coding style could even get worse when you implement it using FreeBasic, because such frameworks like ADO.NET are simply not available and you have to do everything yourself and therefore need to know what to do and how to do it.
MrSwiss
Posts: 1928
Joined: Jun 02, 2013 9:27
Location: Switzerland

Re: Mysql advice

Postby MrSwiss » Aug 31, 2017 21:49

St_W wrote:It's the ADO.NET driver for MySQL and one basically doesn't need to know its contents ...
Well, not knowing that = I'm not even going to "look" at any following code!
(incomplete information!)
Gablea
Posts: 850
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Mysql advice

Postby Gablea » Aug 31, 2017 21:52

@St_W
I would redo the SQL functions in the VB.net app if I know how to use prepared statements but I have never been show a example of this
and as my system is a closed sourced secure Network I have never had any problem with creating the SQL statement in bits and then sending to
the server for processing (but if it increases speed and makes it more stable than I am willing to look at a example of prepared statement)

I am very willing to redo my coding style in FreeBASIC (I am even happy to start again and use the newest ways of coding in FreeBASIC and I am even willing to Drop my DOS Support (and the ones who know me will know for me to say that is a Big thing as i love DOS)

@MrSwiss
The ADO.net driver is just a interface to the Mysql server the same as what the mysql.bi is for FreeBASIC (ADO.net allows the program to talk directly to the MySQL Server instead of using the ODBC interface (and in Linux I do not believe ODBC is implemented)
Gablea
Posts: 850
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Mysql advice

Postby Gablea » Aug 31, 2017 22:01

@St_W
Using FreeBASIC does it mean I can not use sub functions as much as I have with the VB.net application (as I spit mosty of the fucntions into sepect sub functions)

for example in the VB app i have

SeeifLottery -> calls SellProduct if not a lottery item if it is a lottery it it then call FindLotterySettings

sellproduct -> call PriceProduct -> listproduct

so in FreeBASIC would I have to have all the function for say the Lotteryticket under one Sub or do I need to completely rethink this method?
St_W
Posts: 1057
Joined: Feb 11, 2009 14:24
Location: Austria
Contact:

Re: Mysql advice

Postby St_W » Aug 31, 2017 22:25

Gablea wrote:Using FreeBASIC does it mean I can not use sub functions as much as I have with the VB.net application (as I spit mosty of the fucntions into sepect sub functions)
Of course you can use subs and functions. Your program structure probably won't change that much (if you want it that way). Additionally your code looks a bit old-fashioned, not using newer .NET features/techniques, so the conversion is probably easier. I don't really have enough free time currently to explain things in more detail, but you can learn e.g. how to use prepared statements in your FreeBasic implementation from MySQL C tutorials on the web for sure.

Gablea wrote:[...] my system is a closed sourced secure Network [...]
just a note: keep in mind that the free versions of MySQL including its .NET and C connectors are GPL licensed. That means that your code most likely also has to use the GPL license (or you have to buy commercial MySQL licenses, but those are probably quite expensive). For FreeBasic you can use MariaDB's C connector instead (MariaDB is a fork of MySQL) and for .NET you maybe could use the free version of dotConnect (https://www.devart.com/dotconnect/mysql/) as unfortunately there's no .NET connector for MariaDB. Or you could remove the DLLs from your application and tell the customer to download and install them ;-)
see also https://stackoverflow.com/questions/620 ... ng-and-gpl
Last edited by St_W on Aug 31, 2017 22:28, edited 1 time in total.
BasicCoder2
Posts: 2819
Joined: Jan 01, 2009 7:03

Re: Mysql advice

Postby BasicCoder2 » Aug 31, 2017 22:26

I admire your tenacity on getting this done.
Maybe forget about trying to translate the VB program and just start again from the beginning using FB?
will need google translate if you don't read German.
https://www.freebasic-portal.de/code-be ... n-218.html
And a tutorial also written in German.
http://www.askos.de/tutorial/kap317.htm
https://www.freebasic-portal.de/porticu ... l-817.html
.
caseih
Posts: 1011
Joined: Feb 26, 2007 5:32

Re: Mysql advice

Postby caseih » Sep 01, 2017 0:02

Yes the MySQL client libraries are dual licensed GPL and commerical (license fee). Note that the MariaDB, a fork of MySQL offers the client libraries under the LGPL license, which is compatible with closed-source, proprietary apps. And it's 100% compatible in terms of API. Just change out the DLL. The MariaDB database engine also could be used in place of MySQL. I transparently replaced MySQL with MariaDB some years ago and didn't have to do anything special. Configs and data are the same.

https://mariadb.com/kb/en/the-mariadb-l ... nsing-faq/
https://mariadb.com/kb/en/the-mariadb-l ... libraries/

The MySQL company (well Oracle now) has an interesting interpretation of copyright licensing. They claim just talking to their database over the network port using their described protocol requires your code to be GPL (even your own implementation of the client library), but that is, well, an unusual interpretation not shared by most legal experts.
Gablea
Posts: 850
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Mysql advice

Postby Gablea » Sep 01, 2017 0:07

@caseih
I have been thinking about moving to MariaDB as it is slightly less memory hogging then What MySQL is and it is the prefered Database on Debian 9

Sometimes you can get a headache just from trying to understand the Licensing agreements lol.

@St_W
Thanks for the heads up I shall have a read up on the licences

@BasicCoder2
Thanks for the examples I am going to read them in the morning :) I am determined to get my FreeBASIC app working just like the VB app (with the onscreen list format etc if it kills me or cost me ££££ (or €€€€€ depends on where you read this from) he he he)

There is a lot I still need to read up on and understand (for example doing threads so it can keep the database in sync with the server etc)

Maybe forget about trying to translate the VB program and just start again from the beginning using FB?

Yes I am willing to do that as I can always re use the screen designs etc just implement newer formats etc but I may still need help with that
as on the VB version I use a listbox to display the items and a array to hold all the product info

Sometimes migrating to a new cross platform language is more trouble then its worth. (but the one advantage of FreeBASIC i have found is it
produces such smaller EXE then VB.net and when it goes full screen it goes full screen)
dodicat
Posts: 4340
Joined: Jan 10, 2006 20:30
Location: Scotland

Re: Mysql advice

Postby dodicat » Sep 01, 2017 0:37

Just a little reminder Gablea.
If you intend using Win 10, you will have to use a 32 bit screen to get full screen.
Anything else will crash.
Gablea
Posts: 850
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Mysql advice

Postby Gablea » Sep 01, 2017 0:54

@dodicat
Thanks for the heads up dodicat but the Plan is to eventually drop Microsoft Windows from my support Operating systems List (leaning towards
Debian 9 using Framebuffer for the Tills and maybe even the back office system)

I would not put Windows 10 onto any retail system as I do not trust it (I run Windows 7 at home and I am happy with that)
caseih
Posts: 1011
Joined: Feb 26, 2007 5:32

Re: Mysql advice

Postby caseih » Sep 01, 2017 2:03

Gablea wrote:I have been thinking about moving to MariaDB as it is slightly less memory hogging then What MySQL is and it is the prefered Database on Debian 9
Yes most distros ship MariaDB by default now. However I don't think there's any difference in memory use between it and MySQL, since at this point in time, MariaDB pretty much *is* MySQL.
jj2007
Posts: 50
Joined: Oct 23, 2016 15:28
Location: Roma, Italia
Contact:

Re: Mysql advice

Postby jj2007 » Sep 01, 2017 8:57

BasicCoder2 wrote:And a tutorial also written in German.
http://www.askos.de/tutorial/kap317.htm


Very nice! I checked Von Freebasic aus auf MySQL zugreifen and wonder if this is correct:

Code: Select all

    IF res=0 THEN
      'Hole den Zeiger auf die Ergebnistabelle
      restab=mysql_store_result(db)
      IF restab>0 THEN
        'Wieviel Zeilen hat die Ergebnistabelle?
        nrow=mysql_num_fields(restab)
        ? nrow
        IF nrow>0 THEN

          DIM AS INTEGER i,j,ncol
          DIM AS mysql_row row
          'Wieviel Spalten hat die Ergebnistabelle?
          ncol = mysql_num_fields(restab)


mysql_num_fields for both rows and columns?

Return to “General”

Who is online

Users browsing this forum: No registered users and 3 guests