Announcement

Collapse
No announcement yet.

MySQL and PB/DLL?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL and PB/DLL?

    There is an old post on this in the PBCC forum but I would also like to post
    it here:
    What's the status on "PB with mySQL"?
    Has anybody used mySQL with any succes on PB?
    Kind regards
    Eddy



    ------------------
    [email protected]
    Eddy

  • #2
    Sure. I use MySql on my development machine with PB all the time.
    Use the ODBC API, it's a real boon for us developers - no messing
    around with always new APIs.

    Cheers

    Florent

    ------------------

    Comment


    • #3
      Originally posted by Florent Heyworth:
      Sure. I use MySql on my development machine with PB all the time.
      Do I need include files, especially for PB?
      If yes, where can I get them?



      ------------------
      [email protected]
      Eddy

      Comment


      • #4
        You need to download the latest MyOdbc driver for MySql
        and you'll need the ODBC API header which are available from
        the Downloads\database section of the PowerBASIC site. Since
        ODBC is a standard there's no need for special MySql headers.

        You might want to have a look at Eric Pearson's SQL Tools
        package - the ODBC API can be daunting at first and his tools
        have gotten excellent user reviews.

        Cheers


        Florent



        [This message has been edited by Florent Heyworth (edited April 25, 2001).]

        Comment


        • #5
          Thanks for your advice, Florent!

          Eddy

          ------------------
          [email protected]
          Eddy

          Comment


          • #6
            > the ODBC API can be daunting at first

            ...and second, and third...

            For more information about SQL Tools, click on one of the links at the bottom of this message.

            -- Eric


            ------------------
            Perfect Sync Development Tools
            Perfect Sync Web Site
            Contact Us: mailto:[email protected][email protected]</A>
            "Not my circus, not my monkeys."

            Comment


            • #7
              You can even purchase SQLTools directly from PowerBASIC, Inc. too - the product page is located at http://www.powerbasic.com/products/sqltools/ and you can order on-line at http://www.powerbasic.com/shop/



              ------------------
              Lance
              PowerBASIC Support
              mailto:[email protected][email protected]</A>
              Lance
              mailto:[email protected]

              Comment


              • #8
                Code:
                '###############################################################################
                '###############################################################################
                '
                ' Copyright (C) 2001 Devparadise.com.
                '
                ' This library is free software; you can redistribute it and/or
                ' modify it under the terms of the GNU Library General Public
                ' License as published by the Free Software Foundation; either
                ' version 2 of the License, or (at your option) any later version.
                '
                ' This library is distributed in the hope that it will be useful,
                ' but WITHOUT ANY WARRANTY; without even the implied warranty of
                ' MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
                ' Library General Public License for more details.
                '
                ' You should have received a copy of the GNU Library General Public
                ' License along with this library; if not, write to the Free
                ' Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
                '
                '###############################################################################
                '###############################################################################
                '
                ' Written by Philippe PETIT
                '
                ' Please send questions, comments, and changes to [email protected]
                '
                '###############################################################################
                '###############################################################################
                '
                
                %LONG_SIZE = 4
                %INT_SIZE = 2
                %BYTE_SIZE = 1
                
                '===============================================================================================
                '========= public declarations =================================================================
                '===============================================================================================
                %MYSQL_OPT_CONNECT_TIMEOUT = 0
                %MYSQL_OPT_COMPRESS = 1
                %MYSQL_OPT_NAMED_PIPE = 2
                %MYSQL_INIT_COMMAND = 3
                %MYSQL_READ_DEFAULT_FILE = 4
                %MYSQL_READ_DEFAULT_GROUP = 5
                %MYSQL_SET_CHARSET_DIR = 6
                %MYSQL_SET_CHARSET_NAME = 7
                
                %CLIENT_LONG_PASSWORD = 1                               'new more secure passwords
                %CLIENT_FOUND_ROWS = 2                                  'Found instead of affected rows
                %CLIENT_LONG_FLAG = 4                                   'Get all column flags
                %CLIENT_CONNECT_WITH_DB = 8                             'One can specify db on connect
                %CLIENT_NO_SCHEMA = 16                                  'Don't allow database.table.column
                %CLIENT_COMPRESS = 32                                   'Can use compression protocol
                %CLIENT_ODBC = 64                                       'Odbc client
                %CLIENT_LOCAL_FILES = 128                               'Can use LOAD DATA LOCAL
                %CLIENT_IGNORE_SPACE = 256                              'Ignore spaces before '('
                %CLIENT_CHANGE_USER = 512                               'Support the mysql_change_user()
                %CLIENT_INTERACTIVE = 1024                              'This is an interactive client
                %CLIENT_SSL = 2048                                      'Switch to SSL after handshake
                %CLIENT_IGNORE_SIGPIPE = 4096                           'IGNORE sigpipes
                %CLIENT_TRANSACTIONS = 8196                             'Client knows about transactions
                
                Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (lpDestination As Any, lpSource As Any, ByVal lLength As Long)
                
                'connection management routines
                Declare Sub mysql_close Lib "libmySQL.DLL" ALIAS "mysql_close" (ByVal lMYSQL As Long)
                Declare Function mysql_init Lib "libmySQL.DLL" ALIAS "mysql_init" (ByVal lMYSQL As Long) As Long
                Declare Function mysql_options Lib "libmySQL.DLL" ALIAS "mysql_options" (ByVal lMYSQL As Long, ByVal lOption As Long, sArg As ASCIIZ) As Long
                Declare Function mysql_ping Lib "libmySQL.DLL"  ALIAS "mysql_ping" (ByVal lMYSQL As Long) As Long
                Declare Function mysql_real_connect Lib "libmySQL.DLL"  ALIAS "mysql_real_connect" (ByVal lMYSQL As Long, sHostName As ASCIIZ, sUserName As ASCIIZ, sPassword As ASCIIZ, sDbName As ASCIIZ, byval lPortNum As Long, sSocketName As ASCIIZ, ByVal lFlags As Long) As Long
                
                'status and error-reporting routines
                Declare Function mysql_errno Lib "libmySQL.DLL"  ALIAS "mysql_errno" (ByVal lMYSQL As Long) As Long
                Declare Function mysql_error Lib "libmySQL.DLL"  ALIAS "mysql_error" (ByVal lMYSQL As Long) As Long
                
                'query contruction and execution routines
                Declare Function mysql_query Lib "libmySQL.DLL"  ALIAS "mysql_query" (ByVal lMYSQL As Long, sQueryString As ASCIIZ) As Long
                Declare Function mysql_select_db Lib "libmySQL.DLL"  ALIAS "mysql_select_db" (ByVal lMYSQL As Long, sDbName As ASCIIZ) As Long
                
                'result set processing routines
                Declare Function mysql_affected_rows Lib "libmySQL.DLL"  ALIAS "mysql_affected_rows" (ByVal lMYSQL_RES As Long) As Long
                Declare Sub mysql_data_seek Lib "libmySQL.DLL"  ALIAS "mysql_data_seek" (ByVal lMYSQL_RES As Long, ByVal lOffset As Currency) 
                Declare Function mysql_fetch_field_direct Lib "libmySQL.DLL"  ALIAS "mysql_fetch_field_direct" (ByVal lMYSQL_RES As Long, ByVal lFieldNum As Long) As Long
                Declare Function mysql_fetch_lengths Lib "libmySQL.DLL"  ALIAS "mysql_fetch_lengths" (ByVal lMYSQL_RES As Long) As Long
                Declare Function mysql_fetch_row Lib "libmySQL.DLL"  ALIAS "mysql_fetch_row" (ByVal lMYSQL_RES As Long) As Long
                Declare Function mysql_field_count Lib "libmySQL.DLL"  ALIAS "mysql_field_count" (ByVal lMYSQL As Long) As Long
                Declare Sub mysql_free_result Lib "libmySQL.DLL"  ALIAS "mysql_free_result" (ByVal lMYSQL As Long)
                Declare Function mysql_info Lib "libmySQL.DLL"  ALIAS "mysql_info" (ByVal lMYSQL As Long) As Long
                Declare Function mysql_insert_id Lib "libmySQL.DLL"  ALIAS "mysql_insert_id" (ByVal lMYSQL As Long) As Long
                Declare Function mysql_num_fields Lib "libmySQL.DLL"  ALIAS "mysql_num_fields" (ByVal lMYSQL_RES As Long) As Long
                Declare Function mysql_num_rows Lib "libmySQL.DLL"  ALIAS "mysql_num_rows" (ByVal lMYSQL_RES As Long) As Long
                Declare Function mysql_store_result Lib "libmySQL.DLL"  ALIAS "mysql_store_result" (ByVal lMYSQL As Long) As Long
                Declare Function mysql_use_result Lib "libmySQL.DLL"  ALIAS "mysql_use_result" (ByVal lMYSQL As Long) As Long
                
                
                Function GetFieldName(Byval lField As Long, byval MYSQL_RES as long) As String
                    Dim b As Byte
                    Dim sVal As String
                    Dim lMYSQL_FIELD As Long                               'pointer to mysql field structure
                    Dim lpName As Long                                     'pointer to field name
                
                    lMYSQL_FIELD = mysql_fetch_field_direct(MYSQL_RES, lField)
                    If lMYSQL_FIELD = 0 Then Exit Function
                
                    CopyMemory lpName, ByVal lMYSQL_FIELD, %LONG_SIZE
                
                    If lpName = 0 Then Exit Function
                
                    sVal = ""
                    Do
                        CopyMemory b, ByVal lpName, %BYTE_SIZE
                        If b = 0 Then Exit Do
                        sVal = sVal & Chr$(b)
                        lpName = lpName + %BYTE_SIZE
                    Loop
                
                    GetFieldName = sVal
                End Function
                
                Function GetFieldValue(Byval lField As Long, byval MYSQL_FIELD_LENGTHS as long, byval MYSQL_ROW as long) As string
                    Dim lTmpChar As Long, lCurLen As Long
                    Dim lRowData As Long
                    Dim b As Byte
                    Dim sVal As String
                
                
                    CopyMemory lRowData, ByVal (MYSQL_ROW + (%LONG_SIZE * lField)), %LONG_SIZE
                    CopyMemory lCurLen, ByVal (MYSQL_FIELD_LENGTHS + (%LONG_SIZE * lField)), %LONG_SIZE
                
                    If lRowData = 0 Then
                        'null pointer 
                        GetFieldValue = ""
                    Else
                        sVal = ""
                        For lTmpChar = 1 To lCurLen
                            CopyMemory b, ByVal (lRowData + (%BYTE_SIZE * (lTmpChar - 1))), %BYTE_SIZE
                            sVal = sVal & Chr$(b)
                        Next lTmpChar
                        GetFieldValue = sVal
                    End If
                End Function
                ------------------
                Bien à vous
                Philippe
                www.devparadise.com
                Last edited by Gary Beene; 12 Jul 2014, 07:58 PM. Reason: Code: tag

                Comment


                • #9
                  and create CGI with PB/DLL 5.0 and MySQL :
                  Code:
                  '-------------------------------------------------------------------------------
                  '
                  '   HELLOCGI.BAS for PB/DLL 5.0
                  '   Copyright (c) 2001 by Philippe PETIT.
                  '
                  '-------------------------------------------------------------------------------
                  
                  $COMPILE EXE
                  
                  $INCLUDE "WIN32API.INC"
                  $INCLUDE "PBMYSQL.INC"
                  
                  global hSTDOUT as long
                  global hSTDIN  as long
                  
                  Sub InitCGI()
                  hSTDOUT=GetStdHandle(%STD_OUTPUT_HANDLE)
                  hSTDIN=GetStdHandle(%STD_INPUT_HANDLE)
                  End Sub
                  
                  Sub Send(s As Asciiz)
                  '======================
                  ' Send output to STDOUT
                  '======================
                  Dim rc     As Long
                  Dim mBytes As Long
                      s = s & chr$(13) & chr$(10)
                      WriteFile hSTDOUT, s, Len(s), mBytes, ByVal %NULL
                  End Sub
                  
                  Sub SendBinary(s As Asciiz)
                  '======================
                  ' Send output to STDOUT
                  '======================
                  Dim mBytes As Long
                  
                      WriteFile hSTDOUT, s, Len(s), mBytes, ByVal %NULL
                  End Sub
                  
                  Function Main() As Long   
                      Dim mlMYSQL as long
                      Dim Cursor as long
                      Dim mlMYSQL_RES as long
                      Dim cSeekRow As Currency
                     Dim mlAffectedRecords as long
                     Dim mlRecordCount as long
                     Dim mlFieldCount as long
                      Dim mlCurrentRecord as long
                      Dim mlMYSQL_ROW  as long
                      Dim mlMYSQL_FIELD_LENGTHS  as long
                      
                      
                      Dim i as long
                      InitCGI
                      Send "Status: 200 OK"
                      Send "Content-type: text/html"  & chr$(13) & chr$(10)
                      Send "<HTML><HEAD><TITLE>test</TITLE></HEAD>"
                      Send "<BODY>"
                      mlMYSQL = mysql_init(mlMYSQL)
                      If mysql_real_connect(mlMYSQL, "YourServerNameorIP", "YourLogin", "YourPassWord", "YourDatabaseName", 3306, "", ByVal %NULL)=0 Then
                          Send "hello world connection failed" 
                      Else
                          Send "hello world connection OK !<BR>"
                          Cursor=mysql_query(mlMYSQL,"select * from Tablename")
                          mlMYSQL_RES = mysql_store_result(mlMYSQL)
                        mlAffectedRecords = mysql_affected_rows(mlMYSQL)
                        mlRecordCount = mysql_num_rows(mlMYSQL_RES)
                        mlFieldCount = mysql_num_fields(mlMYSQL_RES)
                        Send "<Table border=1><TR>"
                        For i= 0 to mlFieldCount-1
                            Send "<TD BGCOLOR=#CCCCCC>" & GetFieldName(i,mlMYSQL_RES) & "</TD>"
                        Next i
                        Send "</TR>"
                        For mlCurrentRecord=1 to mlRecordCount
                            Send "<TR>"
                              cSeekRow = (mlCurrentRecord - 1) / (10 ^ 4)
                              mysql_data_seek mlMYSQL_RES, cSeekRow
                              mlMYSQL_ROW = mysql_fetch_row(mlMYSQL_RES)
                              mlMYSQL_FIELD_LENGTHS = mysql_fetch_lengths(mlMYSQL_RES)
                            For i= 0 to mlFieldCount-1
                                Send "<TD>" & GetFieldValue(i,mlMYSQL_FIELD_LENGTHS,mlMYSQL_ROW) & "</TD>"
                             Next i
                             Send "</TR>"
                          Next mlCurrentRecord
                          Send "</TABLE>"
                          mysql_free_result mlMYSQL_RES
                          
                      End If
                     mysql_close mlMYSQL
                     mlMYSQL = 0
                      Send "</BODY>"
                      Send "</HTML>"
                  End Function



                  ------------------
                  Bien à vous
                  Philippe
                  www.devparadise.com
                  Last edited by Gary Beene; 12 Jul 2014, 07:58 PM. Reason: Code: tags

                  Comment


                  • #10
                    This MYSQL thing, seems only to work as client server.
                    Am i wrong?
                    Can i use the DLL directly?

                    If i should make use of ODBC, then i can make use of MDB's even easily.
                    Then why MYSQL?


                    ------------------
                    hellobasic

                    Comment


                    • #11
                      Originally posted by Philippe PETIT:
                      and create CGI with PB/DLL 5.0 and MySQL :

                      cSeekRow = (mlCurrentRecord - 1) / (10 ^ 4)

                      Whoa! Even for PB raising ANYTHING by a power is slow. This seems to be a constant as well.

                      Would it be better to set up a variable one time with 10000 and use it here?
                      Would a defined constant be even faster yet?


                      ------------------
                      ATB

                      Charles Kincaid

                      Comment


                      • #12
                        Hi Edwin

                        As far as I know there's no "embedded" version of MySql (i.e
                        running MySql in the process of your executable or as a
                        dynamic link library - I could be wrong though - I only use
                        it in its "vanilla" state). However, MySql is Open-Source and
                        could "probably" be modified if you were willing to get your hands
                        dirty enough.

                        The advantage of using a database's native API over ODBC is
                        additional speed - this is offset by the fact that your app
                        is then bound to that API.

                        MySql is Open Source but does require you to get a license
                        when used in commercial environments where your product depends
                        on MySql (for more details see their website).

                        While I'm quite prepared to pay licenses (or offload license costs
                        to my clients when applicable) I tend not to use native database
                        APIs since most of my applications are quite generic and should
                        be able to run on as many RDBMs as possible.

                        However, using a native API for an in-house application or some such
                        might result in better performance - it's always a tradeoff.

                        Cheers

                        Florent

                        ------------------

                        Comment


                        • #13
                          I have you got a version the PBmysql.inc for PBcc compiler.
                          I have tried the version in here and it does not work.


                          ------------------

                          Comment


                          • #14
                            have a look at the new posting at http://www.powerbasic.com/support/pb...ad.php?t=15265

                            cheers

                            florent

                            ------------------

                            Comment


                            • #15
                              Is the link above available? It is broken. Thank you.
                              How long is an idea?

                              Comment


                              • #16
                                It's 19 years old, it's a link under the old forum structure.

                                Try https://forum.powerbasic.com/forum/u...erface?t=15265

                                Comment


                                • #17
                                  Thanks, old forum structure didn't come into new structure. So that is where my posts went from long ago.
                                  How long is an idea?

                                  Comment


                                  • #18
                                    Originally posted by Mike Doty View Post
                                    Thanks, old forum structure didn't come into new structure. So that is where my posts went from long ago.
                                    Yep, the thread numbers are all the same, so just copy the URL and edit from

                                    http://www.powerbasic.com/support/pbforums/showthread.php?t=15265
                                    to
                                    https://forum.powerbasic.com/showthread.php?t=15265

                                    Comment

                                    Working...
                                    X