Announcement

**Carlo Pagani** · 12 Sep 2009, 05:26 AM

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:

Data Access and Storage

http://www.jose.it-berater.org/smfforum/index.php?board=200.0

Data Access and Storage

**Edwin Knoppert** · 12 Sep 2009, 05:52 AM

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.

**Marc van Breemen** · 12 Sep 2009, 06:17 AM

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.

**Chris Holbrook** · 12 Sep 2009, 06:17 AM

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

**Marc van Breemen** · 12 Sep 2009, 06:20 AM

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

.

**Volker Butzlaff** · 12 Sep 2009, 06:25 AM

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:

404 Not Found

http://planetsquires.com/sqlite_client_server.htm

bye,
Volker

**Michael Mattias** · 12 Sep 2009, 08:55 AM

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

**William Burns** · 12 Sep 2009, 11:09 PM

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.

**Fred Harris** · 12 Sep 2009, 11:38 PM

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

**William Burns** · 12 Sep 2009, 11:58 PM

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.

**Marc van Breemen** · 13 Sep 2009, 03:56 AM

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!

**Marc van Breemen** · 13 Sep 2009, 03:59 AM

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

.

**Michael Mattias** · 14 Sep 2009, 08:12 AM

>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

**John Petty** · 14 Sep 2009, 08:26 AM

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?

**Michael Mattias** · 14 Sep 2009, 08:35 AM

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

**Chris Holbrook** · 14 Sep 2009, 08:53 AM

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.

**Michael Mattias** · 14 Sep 2009, 09:17 AM

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

**Chris Holbrook** · 14 Sep 2009, 10:11 AM

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!

**Fred Harris** · 14 Sep 2009, 10:53 AM

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'.

Announcement

Database access

Database access

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment