MyDB database

carolight · Post by **carolight** » Tue Aug 24, 2004 11:05 am

The.weavster said that MyDB (from http://www.mghsoft.com) works well with Purebasic.

I've worked out everything (create, open, setfield etc,) but I can't work out the mGetField function, which returns a variant, so can't be used in PB.

I've got around this by using the mGetFieldStr function, which returns a pointer to a string, but I wanted to use a Blob field, which doesn't seem to work with this function.

Weavster or anyone else got any suggestions for me? (If anyone's interested in using this DLL, I can post the code I've got, such as it is...)

Thanks

Manolo · Post by **Manolo** » Wed Aug 25, 2004 12:07 pm

Post you code please.

Thanks,
Manolo

Bonne_den_kule · Post by **Bonne_den_kule** » Wed Aug 25, 2004 2:40 pm

Try to use a memory block instead
*MemoryID = AllocateMemory(5000)
*MemoryID=mGetFieldStr(theparams, 1, 2)

OR THIS

Structure VARIANT
vt.w
wReserved1.w
wReserved2.w
wReserved3.w
value.l
EndStructure

thevariant.variant

carolight · Post by **carolight** » Thu Aug 26, 2004 9:26 am

Thanks, Bonne - I tried, but it's the return from the function that causes a problem.

As requested, this is the code I have now, but it still lacks a proper GetField function, so I guess I shall have to go back to Tsunami, which I find a lot clumsier than MyDB.

Code: Select all

;MyDB.dll is available from http://www.mghsoft.com
;It is shareware, with a nag screen whenever a database is created or opened
;so you can check to see if it will fulfill your purpose before you buy it

; Thanks to aXend for the variant stuff

#VT_I4              = 3  ;Don't know what to use for a float variant
#VT_INT             = 22 ;an integer variant
  
Structure VARIANT       ; 16 bytes
  vt.w 
  wReserved1.w 
  wReserved2.w    
  wReserved3.w 
  StructureUnion
    bVal.b              ; 1 byte, 8-bits
    iVal.w              ; 2 bytes, 16-bits
    lVal.l              ; 4 bytes, 32-bits
    value.l             ; same as lVal, defined for compatibility reasons
    high.l              ; 8 bytes, 64-bits
    low.l
    boolVal.w           ; boolean TRUE ($FFFF) or FALSE ($0000), see below
    bstrVal.l           ; pointer to BSTR (unicode string)
    scode.l             ; special code, e.g. for optional parameters, see below
  EndStructureUnion
EndStructure 

m.VARIANT ; Define a variant structure

; define constants needed for MyDB interface
#FIELD_INTEGER = 1
#FIELD_LONG = 2
#FIELD_SINGLE = 3
#FIELD_DOUBLE = 4
#FIELD_STRING = 5
#FIELD_BOOLEAN = 6
#FIELD_BINARY = 7
#FIELD_AUTOINC = 8 

#KEY_DUPLICATED = 1
#KEY_UNDUPLICATED = 2
#KEY_FIELD_CASE = 1
#KEY_FIELD_NOCASE = 2

#MODE_READ = 0
#MODE_READ_WRITE = 2
#MODE_SHARE = 64
#MODE_READ_SHARE = #MODE_READ | #MODE_SHARE
        
If OpenLibrary(0, "MyDB.DLL") = 0 
  MessageRequester("Error", "Sorry, Can't find the Database Engine", 0) 
  End 
EndIf 

DB.l = CallFunction(0, "mOpen", "test.sdb", #MODE_READ_WRITE)
If DB < 0
  If  CallFunction(0,"mCreateField", "Number", #FIELD_LONG) = 1
    If CallFunction(0, "mCreateField", "Name", #FIELD_STRING,12) = 1
      If CallFunction(0,"mCreateField", "Score", #FIELD_SINGLE) = 1
        If CallFunction(0, "mCreateKey", "key_num", #KEY_UNDUPLICATED, 0) = 1 
          CallFunction(0, "mCreateDB", "test.sdb")
          DB = CallFunction(0, "mOpen", "test.sdb", #MODE_READ_WRITE)
          Debug "Database Created"
        EndIf
      EndIf
    EndIf
  EndIf
EndIf
Debug "No Of Records in Database:"
Debug CallFunction(0, "mGetRecordNum", DB)

;set up a record in the Database
;set up the key field
m\vt = #VT_INT ; this describes type of variant
m\value = 1 
CallFunction(0, "mSetField", DB, m, 0)

;set up the name field using mSetFieldStr, which does not need a variant
b.s = "John Smith"
CallFunction(0, "mSetFieldStr", DB, b,1)

;set up the score field
m\vt = #VT_I4 ; a float variant (?)
m\value = 22.2
CallFunction(0, "mSetField", DB, m, 2)

CallFunction(0, "mInsert", DB)
Debug "Record Inserted"
Debug "No Of Records in Database:"
Debug CallFunction(0, "mGetRecordNum", DB)

Debug "Move to First Record"
CallFunction(0, "mMoveFirst",DB, 0)  

;This is where I have my problem, that I want to use mGetField, but that function
;returns a variant, which PB will not accept back.
;CallFunction() seems to return a 4 byte integer(?).
Debug  Val(PeekS(CallFunction(0, "mGetFieldStr", DB, 0)))
Debug PeekS(CallFunction(0,"mGetFieldStr", DB, 1))
Debug Val(PeekS(CallFunction(0,"mGetFieldStr", DB, 2))) 
; and I just realised my float isn't working either :(

CallFunction(0, "mClose", DB)
End

the.weavster · Post by **the.weavster** » Thu Sep 30, 2004 7:55 pm

Sorry Carolight

I must have missed this posting when you made it.
Another alternative you may want to try is Apollo SDE API.

It's a few DLLs that manage standard xBase files and indexes and it's alot easier to use than Tsunami.

You can get a demo from http://www.VistaSoftware.com

Alternatively you can just cop-out like me and use Firebird and PureBasics
ODBC library.

Weave

carolight · Post by **carolight** » Fri Oct 01, 2004 4:15 am

Thanks, Weave, I'll take a look - still open to any other suggestions about non-ODBC databases.

GedB · Post by **GedB** » Fri Oct 01, 2004 8:49 am

Have you tried sqlite?

blueb · Post by **blueb** » Fri Oct 01, 2004 2:38 pm

I've owned Apollo for at least 5 years and I can vouch that it works well with PureBasic, but it's not as fast as some others (Cheetah comes to mind)

Having said that, the latest version of Firebird has many things that I need, such as the ability to backup all the data while the files are in use. As you know most DBMS require exclusive access to do this. So this is very important to me.

I'm starting to use Firebird with ODBC at the moment but patiently waiting for someone to write a few Pure wrappers for the Firebird Dll (OdbcJdbc.dll)

blueb

the.weavster · Post by **the.weavster** » Mon Oct 04, 2004 7:14 pm

Did you get Cheetah working with PureBasic?

I tried the demo but couldn't get it to work.

blueb · Post by **blueb** » Tue Oct 05, 2004 1:58 am

No, I asked Paul (Cheetah's designer) some questions... but because he didn't know PureBasic, he couldn't say why his dll didn't work with Pure.

Pure worked with some functions, but not with others, so I moved on

It is a very solid, fast DBF tool in PowerBasic and would be very welcome in PureBasic.

--blueb

the.weavster · Post by **the.weavster** » Sun Oct 10, 2004 2:45 pm

Do you know of a nice program (free is best) for creating and editing the structure of Firebird tables?

I have tried a few that are linked to their site but none of them were stable.

Doing it in SQL is a bit of a chore.

Weave

blueb · Post by **blueb** » Mon Oct 11, 2004 2:35 pm

Weavster,

Try IBExpert @ www.ibexpert.com

The personal edition is a free download. It can do everything I need.

Of course, I'd rather find a PB program, but...

blueb

blueb · Post by **blueb** » Mon Oct 11, 2004 5:19 pm

Another good source for Firebird information are the PDF files located on the IBPhoenix web site: http://www.ibphoenix.com/main.nfs?a=ibp ... umentation

Look for the API manual from Interbase 6.0... it will help with your API calls.

blueb

the.weavster · Post by **the.weavster** » Mon Oct 11, 2004 6:57 pm

Thanks blueb

blueb · Post by **blueb** » Tue Oct 12, 2004 2:12 pm

the.weavster wrote:Did you get Cheetah working with PureBasic?

I tried the demo but couldn't get it to work.

Weavster,

I reviewed my Cheetah information from Paul, and he mentioned some developers were having problems because some development languages didn't handle strings properly. His solution was to make 2 different versions (hidden) of each command. All commands (e.g. xdbAppPath) are appended with _Z extension which works around the problem.

So I decided to "re-test" Cheetah and found that things work fine so far.

Here's a sample that I've started on.I will work on all commands this week. Note that I downloaded the PureBasic Demo and the Cheetah Demo and everything seems to work well. It appears that commands either return a long OR a string, therefore the example appear similar.

Code: Select all

; ------------------------------------------------------------
;
;   PureBasic - Cheetah  testing from Cheetah's help file
;
; General Use functions that do not need an open table, etc.
; These are taken directly from the Cheetah help CHM file.
;
; blueb@shaw.ca
; ------------------------------------------------------------

If OpenLibrary(0, "CHEETAH2.DLL") = 0 ; test for success
   End
EndIf

;***************************************************************
;
; Note#1: This version of Cheetah uses the "Z" extensions (Zero delimited strings) 
;         You might have to download the latest version of Cheetah demo
; Note#2 - API expects uppercase commands
;
;***************************************************************

;==============================================
; Return Application's path

  Result = CallFunction(0, UCase("xdbAppPath_z")) 
  Res.s = PeekS(Result)
  ;Debug Result
  Debug Res
  
;==============================================
; CheetahDate$ = CTOD(BASICdate$)
; Convert a date string to the prefered  xbase date of: YYYYMMDD
; These dates must be in the dBase form of mm-dd-yyy

  Result = CallFunction(0, UCase("ctod_z"), "11-01-2003") 
  Res.s = PeekS(Result)
  Debug Res
  
;==============================================
; This function takes a Cheetah (xBase) date (YYYYMMDD) and converts it into a 
; standard BASIC format date (MM-DD-YYYY).
; BASICdate$ = DTOS(CheetahDate$)
  Result = CallFunction(0, UCase("dtos_z"), "20031231") 
  Res.s = PeekS(Result)
  Debug Res
  
;==============================================
; NewDate$ = xdbAddDate$(StartDate$, NumDays&) 
; NewDate$ = xdbAddDate("20021231", 5) 'Add 5 days to the date December 31, 2002

  Result = CallFunction(0, UCase("xdbAddDate_z"), "20021231", 5) 
  Res.s = PeekS(Result)
  ;Debug Result
  Debug Res
  
;==============================================
;JulianDate& = xdbDateToJulian&(DateString$) 
;This function converts a Date (in YYYYMMDD format) To a long integer. 
;The value is referred To as a Julian date And is very useful when performing date arithmetic. 
;The long integers can be added Or subtracted To determine other dates. 

  Result = CallFunction(0, UCase("xdbDateToJulian_z"), "20021231") 
  ;Res.s = PeekS(Result)
  Debug Result
  ;Debug Res

;==============================================
;NormalDate$ = xdbJulianToDate$(JulianNumber&) 
;This function simply converts a long integer in Julian format To a date string in YYYYMMDD format.

  Result = CallFunction(0, UCase("xdbJulianToDate_z"), 2452640) 
  Res.s = PeekS(Result)
  ;Debug Result
  Debug Res
 
;==============================================
;NumDays& = xdbDaysApart&(DateFrom$, DateTo$) 
;Determine how may days separate the two dates. The dates must enter in the YYYYMMDD format.

  Result = CallFunction(0, UCase("xdbDaysApart_z"), "20021231", "20030130") 
  ;Res.s = PeekS(Result)
  Debug Result
  ;Debug Res
  
;==============================================
;NumDays& = xdbDaysInMonth&(Year&, Month&)
;Simply determines how many days are in a given month. This function is leap-year aware, 
;therefore the need To specify the year. The year must be in 4-digit format (e.g. 1995, not 95).

  Result = CallFunction(0, UCase("xdbDaysInMonth_z"), 2004, 2) 
  ;Res.s = PeekS(Result)
  Debug Result
  ;Debug Res
 
;==============================================
;TrueFalse& = xdbValidDate&(DateCheck$) 
;This function returns either %XDBTRUE Or %XDBFALSE depending whether the date passed 
; to it (in YYYYMMDD format) is valid Or not.

  Result = CallFunction(0, UCase("xdbValidDate_z"), "20021201") 
  ;If Result is 0 (Zero) NOT VALID, 1 = VALID Date
  ;Res.s = PeekS(Result)
  Debug Result
  ;Debug Res

;==============================================
;ProgramVersion$ = xdbVersion$
;This function returns the version of Cheetah

  Result = CallFunction(0, UCase("xdbVersion_z")) 
  Res.s = PeekS(Result)
  ;Debug Result
  Debug Res
 
CloseLibrary(0)

Later,
blueb