Advice needed on how to convert this function from VB.net 2008 to FreeBASIC

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

Advice needed on how to convert this function from VB.net 2008 to FreeBASIC

Post by Gablea »

Hi Everyone,

I have this function I am using with in my Windows Application and it makes my code very easy to read and use and I would like to port it to FreeBASIC

Code: Select all

Dim aQuery = QueryDatabase("database", SQLCommand)

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

            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
        End If
This is the Function that is called from the databasefunctions.vb

Code: Select all

Public Function QueryDatabase(ByVal from As String, ByVal aQuery As String) As DatabaseQuery
        Dim aConnection = ConnectToDatabaseServer(from), aCommand = New FbCommand(aQuery, aConnection)

        Dim aReader = aCommand.ExecuteReader()

        Return (New DatabaseQuery(aConnection, aCommand, aReader))
    End Function
and below is the full content of the Current DatabaseFunctions.VB file (this is what makes the aQuery Work each time)

Code: Select all

Option Infer On

Imports FirebirdSql.Data.FirebirdClient
Imports System.IO

' This Module Handles all the Database Functions

Module DatabaseFunctions
    Dim Exception As String

    Public Class DatabaseQuery

        Public Sub New(ByVal aConnection As FbConnection, ByVal aCommand As FbCommand, ByVal aDataReader As FbDataReader)
            _connection = aConnection
            _command = aCommand
            _reader = aDataReader
        End Sub

        Protected Overrides Sub Finalize()
            Finished()
        End Sub

        ''' <summary>
        ''' Retrieves an item by key.
        ''' </summary>
        ''' <param name="key">The key of the item to retrieve.</param>
        Default Public ReadOnly Property Item(ByVal key As String) As Object
            Get
                Return (_reader.Item(key))
            End Get
        End Property

        ''' <summary>
        ''' Retrieves an item by index.
        ''' </summary>
        ''' <param name="index">The index of the item to retrieve.</param>
        Default Public ReadOnly Property Item(ByVal index As Integer) As Object
            Get
                Return (_reader.Item(index))
            End Get
        End Property

        ''' <summary>
        ''' Gets whether or not the query has any rows.
        ''' </summary>
        Public ReadOnly Property HasRows() As Boolean
            Get
                Return (_reader.HasRows)
            End Get
        End Property

        Public Function Read() As Boolean
            Return (_reader.Read())
        End Function

        ''' <summary>
        ''' Finishes the query and releases all resources.
        ''' </summary>
        Public Sub Finished()
            If (_reader IsNot Nothing) Then
                If _reader.IsClosed = False Then
                    '_reader.Close()
                    _reader = Nothing
                End If
            End If

            If (_connection IsNot Nothing) Then
                If (_connection.State = ConnectionState.Open) Then
                    _connection.Close()
                End If
            End If
        End Sub

        Private _connection As FbConnection, _command As FbCommand, _reader As FbDataReader
    End Class

    Private Function ConnectToDatabaseServer(ByVal ConnectionType As String) As FbConnection

        Dim aConnection As FbConnection

        Select Case (ConnectionType.ToLower())
            Case "database"
                dbConn = New FbConnection(FBLocalDatabaseLocation)
                dbConn.Open()
                aConnection = dbConn

            Case "thread"
                ThreaddbConn = New FbConnection(FBLocalDatabaseLocation)
                ThreaddbConn.Open()
                aConnection = ThreaddbConn

            Case "vat"
                VATdbConn = New FbConnection(FBLocalDatabaseLocation)
                VATdbConn.Open()
                aConnection = VATdbConn

            Case "msupdate"
                MSUpdatedbConn = New FbConnection(FBLocalDatabaseLocation)
                MSUpdatedbConn.Open()
                aConnection = MSUpdatedbConn

            Case "sendtodatabase"
                SendToDBConn = New FbConnection(FBLocalDatabaseLocation)
                SendToDBConn.Open()
                aConnection = SendToDBConn

            Case Else
                Throw New ArgumentException("The database you requested is not valid.")
        End Select

        Return (aConnection)
    End Function

    Public Function QueryDatabase(ByVal from As String, ByVal aQuery As String) As DatabaseQuery
        Dim aConnection = ConnectToDatabaseServer(from), aCommand = New FbCommand(aQuery, aConnection)

        Dim aReader = aCommand.ExecuteReader()

        Return (New DatabaseQuery(aConnection, aCommand, aReader))
    End Function
I know for a fact I would have to convert all the fbCommand to work with MySQL (as that is a given) but I am not sure where to start as this would have to work on Linux as well

I know there will be a lot of converting to do but if someone could point me in the correct direction I would be most grateful (I want to use this code as it has powered my Windows app for over 5 years and the database access is stable and smooth EVERY TIME i need to use it)
exagonx
Posts: 314
Joined: Mar 20, 2009 17:03
Location: Italy
Contact:

Re: Advice needed on how to convert this function from VB.net 2008 to FreeBASIC

Post by exagonx »

Gablea wrote:Hi Everyone,

I have this function I am using with in my Windows Application and it makes my code very easy to read and use and I would like to port it to FreeBASIC

I know for a fact I would have to convert all the fbCommand to work with MySQL (as that is a given) but I am not sure where to start as this would have to work on Linux as well

I know there will be a lot of converting to do but if someone could point me in the correct direction I would be most grateful (I want to use this code as it has powered my Windows app for over 5 years and the database access is stable and smooth EVERY TIME i need to use it)
Hello this is my function for mysql under freebasic, you need latest mysql lib for Windows "libmysql.dll" and for linux "libmysqlclient.so.18.3.0" from mysql.com (C driver available on mysql site download area)

Code: Select all

	#INCLUDE Once "mysql\mysql.bi"
	dim shared MySQLOut 
Function MySQL_DB(FURL as String, FDBName as String, FSVRUserName as String, FSVRPassword as String, FSVRPort as integer, FQuery as String) as Integer 'DIM Shared MySQLOut As MYSQL_RES PTR'
    DIM Conn As MYSQL PTR
    DIM Row As MYSQL_ROW
    Dim Errore as Integer

   
    Conn = mysql_init(NULL)
	
    if (Conn = 0) then mysql_errno(Conn)
    
    if (mysql_real_connect(Conn, FURL, FSVRUserName, FSVRPassword, FDBName, FSVRPort, NULL, 0) = 0) then 
        if (mysql_real_connect(Conn, FURL, FSVRUserName, FSVRPassword, FDBName, FSVRPort, NULL, 0) = 0) then 
			print "Error server MySQL"
			print *mysql_error(Conn)
			print "Number error: " & str(mysql_errno(Conn))
			Errore = mysql_errno(Conn)
			mysql_close(Conn)
        end if
    Else
    
		if (mysql_query(Conn, FQuery) <> 0 ) then 
			print "Error server MySQL"
			print *mysql_error(Conn)
			print "Numero error: " & str(mysql_errno(Conn))
			Errore = mysql_errno(Conn)
			mysql_close(Conn)
		else
			MySQLOut = mysql_store_result(Conn)
			mysql_close(Conn)
		end if
		
    end if
    
  
    if mysql_errno(Conn) then print "Errore MySQL : " & str(mysql_errno(Conn))

    Return Errore
    
End Function

Gablea
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Advice needed on how to convert this function from VB.net 2008 to FreeBASIC

Post by Gablea »

exagonx wrote:

Code: Select all

	#INCLUDE Once "mysql\mysql.bi"
	dim shared MySQLOut 
Function MySQL_DB(FURL as String, FDBName as String, FSVRUserName as String, FSVRPassword as String, FSVRPort as integer, FQuery as String) as Integer 'DIM Shared MySQLOut As MYSQL_RES PTR'
    DIM Conn As MYSQL PTR
    DIM Row As MYSQL_ROW
    Dim Errore as Integer

   
    Conn = mysql_init(NULL)
	
    if (Conn = 0) then mysql_errno(Conn)
    
    if (mysql_real_connect(Conn, FURL, FSVRUserName, FSVRPassword, FDBName, FSVRPort, NULL, 0) = 0) then 
        if (mysql_real_connect(Conn, FURL, FSVRUserName, FSVRPassword, FDBName, FSVRPort, NULL, 0) = 0) then 
			print "Error server MySQL"
			print *mysql_error(Conn)
			print "Number error: " & str(mysql_errno(Conn))
			Errore = mysql_errno(Conn)
			mysql_close(Conn)
        end if
    Else
    
		if (mysql_query(Conn, FQuery) <> 0 ) then 
			print "Error server MySQL"
			print *mysql_error(Conn)
			print "Numero error: " & str(mysql_errno(Conn))
			Errore = mysql_errno(Conn)
			mysql_close(Conn)
		else
			MySQLOut = mysql_store_result(Conn)
			mysql_close(Conn)
		end if
		
    end if
    
  
    if mysql_errno(Conn) then print "Errore MySQL : " & str(mysql_errno(Conn))

    Return Errore
    
End Function

Thanks for this exagonx do you have a example of how I can use your function? I assume I call some how the MySQL_DB(FURL as String, FDBName as String, FSVRUserName as String, FSVRPassword as String, FSVRPort as integer, FQuery as String) function

but how do I get the results?
exagonx
Posts: 314
Joined: Mar 20, 2009 17:03
Location: Italy
Contact:

Re: Advice needed on how to convert this function from VB.net 2008 to FreeBASIC

Post by exagonx »

Gablea wrote:
exagonx wrote:

Thanks for this exagonx do you have a example of how I can use your function? I assume I call some how the MySQL_DB(FURL as String, FDBName as String, FSVRUserName as String, FSVRPassword as String, FSVRPort as integer, FQuery as String) function

but how do I get the results?
Hello in the second row you can see the shared var the result are stored there.
when I time I will write something better to understand

the function cant return a pointer then you have to made it like shared or inside a class

Code: Select all


'this var are the shared where the database reading output are stored 
DIM Shared MySQLOut As MYSQL_RES PTR

'Function MySQL_DB(FURL as String, FDBName as String, FSVRUserName as String, FSVRPassword as String, FSVRPort as integer, FQuery as String)

' Var where you store the data for connect to the dabase
dim as string ServerURL ="localhost"
dim as integer ServerPort = 3306
dim as string DataBaseName = "test"
dim as string DataBaseUser = "username"
dim as string DataBasePassword = "password"

' Var for send and retrive answer
dim as string Error, SQLQuery


'Vars for read retrived data
Dim Row As MYSQL_ROW
dim TotalRow as integer
dim MyList as integer

dim as integer id
dim as string CodProd, CodBar, Description

SQLQuery = "SELECT * FROM `testtable`;"  ' Query SQL if you have a table named testtable

Error = MySQL_DB(ServerURL, DataBaseName, DataBaseUser, DataBasePassword, ServerPort , SQLQuery)
if len(Error) >0 then 
	print "Something goes wrong: " & Error  
else
	TotalRow = mysql_num_rows(MySQLOut) ' read the ammount of rows in the database

For MyList = 0 To TotalRow -1 Step 1

	Row = mysql_fetch_row(MySQLOut)
	id = CInt(*Row[0])
	CodProd = *Row[1]
	CodBar = *Row[2]
	Description = *Row[3]
	
	print int(id) & "-" & CodProd  & "-" &  CodBar  & "-" &  Description
next MyList
This is how you use it
Post Reply