Announcement

Collapse
No announcement yet.

Database access

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

  • Database access

    Hi,

    While reading through the docs and several forum threads I learned, to my big surprise, that PB doesn't have any built-in database support. I saw some posts in which SQLTools is mentioned as the best solution right now. But as I'm not yet willing to spend another $100 I wonder if there isn't an other way to have simple straight forward database access (via ODBC and/or ADO/OLEDB)?

    I know a lot of database programming, so I will write all the database logic myself. I don't need 'fancy stuff' like bounding controls to datasets, disconnected status, etc. I just need to be able to do the following two things:
    • Executing SQL commands (f.e. inserts) and get the result of that ('error' or 'no error').
    • Get a dataset (via SELECT) which I can loop through and with that fill controls by code.


    Spending $100 for those two 'simple' tasks is a bit of overkill for me. I'm not ready to dive in the Windows API, so I need a relatively simple solution.

    Thx.

  • #2
    Hi Marc

    I personally use SQL Tools and can recommend it if you are needing to perform serious DB work.

    However, here is an alternate solution:
    http://www.jose.it-berater.org/smffo...hp?board=200.0

    Comment


    • #3
      I guess ordinary ADO examples may be on the PB site.
      Though.. PB does activex servers and thus ADO works fine.
      I do that all the time.
      hellobasic

      Comment


      • #4
        Originally posted by Carlo Pagani View Post
        However, here is an alternate solution:
        http://www.jose.it-berater.org/smffo...hp?board=200.0
        Thanks Carlo, this is what I need. Didn't know that this great resource was available, but now I do . I just registered on the JRS forum and awaiting for approval to download the necessary INC files.

        Comment


        • #5
          There is also quite a bit about SQLite which is brilliant and free...

          Comment


          • #6
            Originally posted by Chris Holbrook View Post
            There is also quite a bit about SQLite which is brilliant and free...
            I already read about SQLite. But I need to be able to access databases like MSSQL, MySQL and SQLBase.

            Thx anyway for letting me know .

            Comment


            • #7
              DB Connect...

              Marc,

              SQL Tools is a professional general solution to connect nearly all important DBs via ODBC.

              But you have many other "free" alternatives, which perhaps serve for your requirements. One is the one Chris has mentioned: PB + (free) SQLitening + (free) SQLite

              SQLitening you find here:
              http://planetsquires.com/sqlite_client_server.htm

              bye,
              Volker
              www.zentrader.de (Trading system development and simulation tools)

              Comment


              • #8
                Generic 'ADO' Connection and Query Tester (CC 5+/Win 9+) 11-02-08

                Please note the "Search" facility will locate multiple examples of database access using both the ODBC interface (with and without SQL Tools) and the OLE interface (using ADO).

                (SQL Tools is a helper library for use with the ODBC interface; ADO is a helper library for use with the OLE interface).

                MCM
                Michael Mattias
                Tal Systems (retired)
                Port Washington WI USA
                [email protected]
                http://www.talsystems.com

                Comment


                • #9
                  Marc, nothing else is needed to use PowerBasic 9 to access databases. I use ODBC which is very powerful and flexible. ODBC can look scary at first, but its really not that bad. In fact I have created some simple wrapper functions to make it almost too easy. For example this is all that is needed to connect to a database and run a "insert" SQL statment using my wrappers:
                  Code:
                  sConnectString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=MyDB.mdb;" 
                  If DBconnect(sConnectString, hEnv, hConn, sError) then
                     sSQL = "INSERT INTO table1 (RecID, PCName, OSVer) VALUES (1234, 'MyWorkPC1', 'XP');"
                     If DBSQLExec(hConn, sSQL, iUpdated, sError) then
                        Msgbox "INSERT " & str$(iUpdated) & " records.",,"INSERT Worked"
                     Else
                        Msgbox sError,,"Error on INSERT statement"
                     End if
                     DBdisconnect hEnv, hConn   'disconnect from DB
                  Else
                    Msgbox sError,,"Error connecting"
                  End if
                  That doesn't seem too bad does it? (and it includes error checking)

                  FYI, that connection string can point to a SQL server, Access DB, ODBC DNS, or just about any other DB.

                  I will see if I can whip up a simple example of both a INSERT and SELECT SQL statements to post.
                  "I haven't lost my mind... its backed up on tape... I think??" :D

                  Comment


                  • #10
                    Hi Marc!

                    PowerBASIC is a great language for database access. I have Sql Tools, have used Ado and Dao a fair amount, but I especially like direct ODBC. I've put quite a few examples of that in the source code Forum. Just as William Burns just said, the way to go is wrap as much of the low level grunginess up and out of sight with wrappers. Just within the past week or so I've been having great fun with a demo I've put together which connects to Microsoft Excel, Microsoft Access, and Sql Server Express. It creates a database in all those systems, adds a few records, then dumps them to an output screen. I'm just getting it together this weekend and I'll probably post it in Jose's Forum shortly. Anyway, I just threw together a quick example for CC. Here it is...

                    'tested with PB Console Compiler 5
                    Code:
                    #Compile  Exe
                    #Dim      All
                    #Register None
                    #Include  "Win32Api.inc"
                    #include  "Sqltypes.inc"
                    #include  "Sql32.inc"
                    #include  "Sqlext32.inc"
                    #Include  "Win32Api.inc"
                    
                    
                    Class CSql
                      Instance m_strConnectionString  As String
                      Instance m_strDatabase          As String
                      Instance m_strDriver            As String
                      Instance m_strServer            As String
                      Instance m_strDBQ               As String
                      Instance m_szCnStrOut           As Asciiz*512
                      Instance iBytes                 As Integer
                      Instance swStrLen               As Integer
                      Instance m_hEnvr                As Dword
                      Instance m_hConn                As Dword
                      Instance m_iNativeErrPtr        As Long
                      Instance m_iTextLenPtr          As Integer
                      Instance m_szErrCode            As Asciiz*8
                      Instance m_szErrMsg             As Asciiz*512
                      Instance m_blnConnected         As Long
                    
                      Interface ISql : Inherit IUnknown
                        Property Get strDatabase() As String
                          Property=m_strDatabase
                        End Property
                        Property Set strDatabase(Byval strName As String)
                          m_strDatabase=strName
                        End Property
                    
                        Property Get strDriver() As String
                          Property=m_strDriver
                        End Property
                        Property Set strDriver(Byval strName As String)
                          m_strDriver=strName
                        End Property
                    
                        Property Get strServer() As String
                          Property=m_strServer
                        End Property
                        Property Set strServer(Byval strName As String)
                          m_strServer=strName
                        End Property
                    
                        Property Get hConn() As Dword
                          Property=m_hConn
                        End Property
                    
                        Property Get strDBQ() As String
                          Property=m_strDBQ
                        End Property
                        Property Set strDBQ(Byval strName As String)
                          m_strDBQ=strName
                        End Property
                    
                        Property Get strConnectionString() As String
                          Property=m_strConnectionString
                        End Property
                        Property Set strConnectionString(Byval strName As String)
                          m_strConnectionString=strName
                        End Property
                    
                        Property Get blnConnected() As Long
                          Property=m_blnConnected
                        End Property
                        Property Set blnConnected(Byval iConnected As Long)
                          m_blnConnected=iConnected
                        End Property
                    
                        Property Get strErrCode() As String
                          Property=m_szErrCode
                        End Property
                    
                        Property Get strErrMsg() As String
                          Property=m_szErrMsg
                        End Property
                    
                        Property Get iNativeErrCode() As Long
                          Property=m_iNativeErrPtr
                        End Property
                    
                        Method MakeConnectionString()
                          If m_strConnectionString="" Then
                             Select Case m_strDriver
                               Case "SQL Server"
                                 If m_strDBQ="" Then
                                    m_strConnectionString= _
                                    "DRIVER="              & m_strDriver        & ";" & _
                                    "SERVER="              & m_strServer        & ";"
                                 Else
                                    m_strConnectionString= _
                                    "DRIVER="              & m_strDriver        & ";" & _
                                    "SERVER="              & m_strServer        & ";" & _
                                    "DATABASE="            & m_strDatabase      & ";" & _
                                    "DBQ="                 & m_strDBQ           & ";"
                                 End If
                               Case "Microsoft Access Driver (*.mdb)"
                                 m_strConnectionString= _
                                 "DRIVER="                 & m_strDriver        & ";" & _
                                 "DBQ="                    & m_strDBQ           & ";"
                               Case "Microsoft Excel Driver (*.xls)"
                                 m_strConnectionString= _
                                 "DRIVER="                 & m_strDriver        & ";" & _
                                 "DBQ="                    & m_strDBQ           & ";"
                             End Select
                          End If
                        End Method
                    
                        Method ODBCConnect()
                          Local szCnIn As Asciiz*512, szCnOut As Asciiz*512
                          Local iRet As Long
                    
                          Me.MakeConnectionString()
                          Call SQLAllocHandle(%SQL_HANDLE_ENV,%SQL_NULL_HANDLE,m_hEnvr)
                          Call SQLSetEnvAttr(m_hEnvr,%SQL_ATTR_ODBC_VERSION,ByVal %SQL_OV_ODBC3,%SQL_IS_INTEGER)
                          Call SQLAllocHandle(%SQL_HANDLE_DBC,m_hEnvr,m_hConn)
                          szCnIn=m_strConnectionString
                          iRet=SQLDriverConnect(m_hConn,0,szCnIn,Len(szCnIn),szCnOut,512,iBytes,%SQL_DRIVER_NOPROMPT)
                          If iRet=0 Or iRet=1 Then
                             m_blnConnected=%TRUE
                          Else
                             m_blnConnected=%FALSE
                             Call SQLGetDiagRec(%SQL_HANDLE_DBC,m_hConn,1,m_szErrCode,m_iNativeErrPtr,m_szErrMsg,512,m_iTextLenPtr)
                          End If
                        End Method
                    
                        Method ODBCGetDiagRec(Byval hStmt As Dword)
                          Call SQLGetDiagRec(%SQL_HANDLE_STMT,hStmt,1,m_szErrCode,m_iNativeErrPtr,m_szErrMsg,512,m_iTextLenPtr)
                        End Method
                    
                        Method ODBCDisconnect()
                          If Me.blnConnected=%TRUE Then
                             Call SQLDisconnect(m_hConn)                  'Disconnect From Data Source
                             Call SQLFreeHandle(%SQL_HANDLE_DBC,m_hConn)  'Free Connection Handle
                             Call SQLFreeHandle(%SQL_HANDLE_ENV,m_hEnvr)  'Free Environment Handle
                          End If
                        End Method
                      End Interface
                    End Class
                    
                    
                    Function PBMain() As Long
                      Local lpBuffer As Asciiz*512
                      Local nSize As Dword
                      Local Sql As ISql
                    
                      Let Sql=Class "CSql"
                      Sql.strDriver="SQL Server"    'For SQL Server Express
                      nSize=512
                      Call GetComputerName(lpBuffer,nSize)
                      Sql.strServer=lpBuffer+"\SQLEXPRESS"
                      Sql.ODBCConnect()
                      If Sql.blnConnected Then
                         Print "Connected To Sql Server Express OK"
                         Sql.ODBCDisconnect()
                      Else
                         Print "Couldn't Connect To Sql Server Express"
                         Print Sql.strErrMsg
                         Print Sql.iNativeErrCode
                      End If
                      WaitKey$
                    
                      PBMain=0
                    End Function
                    Fred
                    "fharris"+Chr$(64)+"evenlink"+Chr$(46)+"com"

                    Comment


                    • #11
                      I posted the full example in the source code section at:
                      http://www.powerbasic.com/support/pb...ad.php?t=41475

                      Let me know if you have any questions.
                      "I haven't lost my mind... its backed up on tape... I think??" :D

                      Comment


                      • #12
                        Originally posted by William Burns View Post
                        I posted the full example in the source code section at:
                        http://www.powerbasic.com/support/pb...ad.php?t=41475

                        Let me know if you have any questions.
                        Many thanks for these great code examples! At first glance it looks very straightforward, which is what I need. Good to know that I don't have to buy yet another tool .

                        I will try them out in the coming week and will let you know how it worked out! Thx!

                        Comment


                        • #13
                          Originally posted by Fred Harris View Post
                          Hi Marc!

                          PowerBASIC is a great language for database access. I have Sql Tools, have used Ado and Dao a fair amount, but I especially like direct ODBC. I've put quite a few examples of that in the source code Forum. Just as William Burns just said, the way to go is wrap as much of the low level grunginess up and out of sight with wrappers. Just within the past week or so I've been having great fun with a demo I've put together which connects to Microsoft Excel, Microsoft Access, and Sql Server Express. It creates a database in all those systems, adds a few records, then dumps them to an output screen. I'm just getting it together this weekend and I'll probably post it in Jose's Forum shortly. Anyway, I just threw together a quick example for CC. Here it is... <snip>
                          Thanks Fred! I will try this out asap and will let you know my results .

                          Comment


                          • #14
                            >my big surprise, that PB doesn't have any built-in database support

                            DISCLAIMER: in context "database support" is interpreted to mean "support for database access using SQL statements."

                            Just musing here, but I am trying to think of any compiler which has intrinsic ("built in") database support, and the only one I can come up with is the SQR compiler, which offers intrinsic syntax for Oracle DBMS access.

                            Microsoft Visual anything doesn't have it.

                            Neither the IBM Mainframe COBOL nor the Microfocus COBOL compilers have it, although there are third-party libraries available for both to add ESQL support via pre-compilation.


                            MCM
                            Michael Mattias
                            Tal Systems (retired)
                            Port Washington WI USA
                            [email protected]
                            http://www.talsystems.com

                            Comment


                            • #15
                              Originally posted by Michael Mattias View Post
                              Just musing here, but I am trying to think of any compiler which has intrinsic ("built in") database support, and the only one I can come up with is the SQR compiler, which offers intrinsic syntax for Oracle DBMS access.
                              MCM
                              FoxPro?

                              Comment


                              • #16
                                Foxpro, hmm... and I'll bet there are some other "database tools" available which compile.

                                Maybe I should have qualified "compiler" with "general-purpose applications"

                                All that said, I'd pay real money for embedded SQL support in PB via precompilation.
                                Code:
                                  EXEC SQL 
                                         Select X, Y, Z from A  where T=5  into    var1, var2, var3 
                                  END-EXEC
                                Michael Mattias
                                Tal Systems (retired)
                                Port Washington WI USA
                                [email protected]
                                http://www.talsystems.com

                                Comment


                                • #17
                                  Originally posted by Michael Mattias View Post
                                  All that said, I'd pay real money for embedded SQL support in PB via precompilation.
                                  Code:
                                    EXEC SQL 
                                           Select X, Y, Z from A  where T=5  into    var1, var2, var3 
                                    END-EXEC
                                  In the example quoted that would not be difficult to do using SQLite, perhaps changing the syntax just a bit.

                                  Comment


                                  • #18
                                    In the example quoted that would not be difficult to do using SQLite, perhaps changing the syntax just a bit.
                                    Code:
                                    EXEC SQL            ===> function not defined
                                    Select x, y, z ... .===> statement expected 
                                    END-EXEC           ==> function not defined
                                    There are all kinds of 'wrapper' functions floating around here which simplify one-line statements. Heck, I wrote a couple myself.

                                    But what about more complex stuff?

                                    Code:
                                     EXEC SQL   
                                        DECLARE CURSOR.....
                                    
                                     EXEC SQL   
                                        FETCH NEXT ....
                                    
                                    
                                      EXEC SQL 
                                           WHENEVER SQLError .....
                                    A 'real' ESQL precompiler could make DBMS programming a whole lot easier.

                                    However, the problem with ESQL precompilers/libraries is, they are often limited to a specific brand-name DBMS.... which is something I could probably live with, but golly it would be nice to have same which is entirely DBMS-brand-name agostic.

                                    MCM
                                    Michael Mattias
                                    Tal Systems (retired)
                                    Port Washington WI USA
                                    [email protected]
                                    http://www.talsystems.com

                                    Comment


                                    • #19
                                      Originally posted by Michael Mattias View Post
                                      ... but golly it would be nice to have same which is entirely DBMS-brand-name agostic.
                                      Why not "free beer for all" - just as likely and much more fun!

                                      Comment


                                      • #20
                                        database support

                                        I have a feeling the context in which 'database support' was used relates to Microsoft's Visual Studio products; with the emphasis on 'Visual'. With those things there are all sorts of icons and such depicting 'data containers'. You right click on one of the little thingy barrel looking container icons and dialogs present themselves where you select from all kinds of graphical controls, i.e., list boxes, tree views depicting tables and fields in a DB, etc., and end up with some kind of database connection to your present project. Like I said, heavy emphasis on 'visual'. No such 'dark ages' stuff like actually writting Sql Statements.

                                        Here a few weeks ago I installed Visual Studio 2008 Pro. I told the thing to go ahead and install everything. Before I did the install I carefully wrote down all the services running in Task Manager, and the amount of disk space I had used up. After the install I had about 10 gigabytes less. That's about three times the size of the hard drive I had on my first Windows computer which ran Win95. With the amount of RAM and hard disk space stuff like that uses, you can do stuff like mentioned in terms of 'database support'.
                                        Fred
                                        "fharris"+Chr$(64)+"evenlink"+Chr$(46)+"com"

                                        Comment

                                        Working...
                                        X