Code: Select all
;===========================================================================================================================================
; Original code and idea (Could not have done database without him) : Rings.
; Reworked and made automatic and easy : Paul.
; Bashed over the head and extensively redone to suit my needs : Fangles/FangBeast/BeastFang etc
;
; Note: I prefer to use subroutines wherever possible now as I have learned from a great many people that it is slightly faster than a
; Procedure but that is my choice and no-one has to be locked into that. Also, I don't have to explicitly declare my variables global this
; was as they will remain so.
;
; The raw database header can be renamed to whatever you like that makes sense to you, as can any variable I/we have used as once again,
; nothing is set in concrete. The raw file can be created by using Microsoft Access to create a blank database with no table in it.
; Actually, you can probably use any database program.
;
; Also, I have headr that you can create this header on the fly by using the API. Unfortunately, I do not know how to do it or I would
; tell you.
;
; Note1: Also remember that if you have a subroutine that calls a procedural function, either have the procedure 'on top' of the calling
; routine or remember to put a procedural declaration for it at the top of your code as PureBasic doesn't maintain a searchable procedure
; stack, it is a single pass compiler (as I understand it).
;
; Note2: The "MessageRequester" parts can be replaced with debug statements for yourself as you develop your program or as LarsG recently
; Suggested to me, make all the relevant sections into procedures with their own error return codes to check each stage of the database and
; table creation routines for total control over the process or custom error routines etc.
;===========================================================================================================================================
#ODBCLibrary = 1
;============================================================================================================================
; This part originally reworked by paul to autocreate a database and autofill it with a table. I re-did the entire thing so
; that you can create any new databases in your program whenever you wanted to.
;============================================================================================================================
Declare.l MakeConnection(Driver.s, strAttributes.s)
makeanewdatabase:
databasename.s = SaveFileRequester("Create Database", "icat.mdb", "Database File | *.mdb" , 0) ; Get database name from the user
name.s = GetFilePart(databasename.s) : name.s = Left(name.s, Len(name.s) - 4) ; Strip the name from the path
user.s = "" ; Not using a user name
pass.s = "" ; not using a password
If OpenDatabase(0, name.s, user.s, pass.s) = 0 ; If database can't be opened, now we create it
If CreateFile(0, databasename.s) ; Database doesn't exist so we create a blank one with the 'databasename.s' variable
WriteData(0, ?database, 40960) ; Write the raw database header to the newly created file
CloseFile(0) ; Close the file now if we successfully created it
builddb = 1 ; Create a flag saying it was done okay
MessageRequester("Create Blank Database", "Blank Database " + databasename.s + " has been created And opened For use", #PB_MessageRequester_Ok)
Else ; Otherwise, give an error message and exit the subroutine
MessageRequester("Make a New Database", "Could not create the blank database, raw header not found or damaged", #PB_MessageRequester_Ok)
fileopen = 0 ; Make sure other routines know a file was never opened to prevent errors
Return ; get right out of this routine now
EndIf
DataSection ; Raw database data
database: : IncludeBinary "C:\Development\My Code\iCat\Resource\_db.raw" ; My path to my raw header data (Can make with Access)
EndDataSection
result = Makeconnection("Microsoft Access Driver (*.mdb)", "Server=APServer;Description=" + name + ";DSN=" + name + ";DBQ=" + databasename + ";UID=" + "" + ";PWD=" + "" + ";")
EndIf ; End of the routine
;------------------------------------------------------------------------------------------------
If OpenDatabase(0, name.s, user.s, pass.s) ; If we can now open the new database, do the following
rt = 1 ; Tell other routines we opened the database okay to now make a table
MessageRequester("Open New Database", "New Database " + databasename.s + " has been opened successfully", #PB_MessageRequester_Ok)
fileopen = 1 ; Tell other routines the file was opened okay
Else ; Otherwise do the below
MessageRequester("Open New Database", "Could not open the database that was just created", #PB_MessageRequester_Ok)
fileopen = 0 ; Tell other routines the database cannot be opened, so don't use it
Return ; Now get the heck out of here
EndIf ; End of the routine
If builddb And rt ; If database was both created and opened successfully, now make the table
query.s = "create table Style(id autoincrement, deleted long, marked long, dvol text(3), dlabel text(15), dfile text(254), "
query.s + "fver text(6), fcrc text(15) unique, fid0 text(20), fname text(50), ftype text(15), fsize text(15), fcat text(30), "
query.s + "fcoll text(30), fdisp text(30), tmark text(30), tlink text(50), fid1 text(20), fid2 text(20), fid3 text(20), "
query.s + "fid4 text(20), fid5 text(20), comment memo);"
If DatabaseQuery(0, query.s) <> 0 ; Send the created sql query string to the SQL ODBC driver
Else ; Otherwise do the below
MessageRequester("Create Table", "Failed To create the table in the open database", #PB_MessageRequester_Ok)
fileopen = 0 ; Can't create the table so tell other routines not to use it
Return ; Get the heck out of here
EndIf ; End of the routine
EndIf ; End of the routine
Return
;============================================================================================================================
; Make an ODBC connection to a database on disk (This part created by Rings, not sure if Paul did anything to it)
;============================================================================================================================
Procedure.l MakeConnection(Driver.s, strAttributes.s)
Result = OpenLibrary(#ODBCLibrary, "ODBCCP32.DLL") ; Open the ODBC library as Library number 1
If Result ; If the library was able to be opened, do the following
MyMemory = AllocateMemory(Len(strAttributes)) ; Allocate the memory you need here
CopyMemory(@strAttributes, MyMemory, Len(strAttributes)) ; Copy the database information string into the memory space
For L = 1 To Len(strAttributes) ; Check the string in the memory space now
If PeekB(MyMemory + L - 1) = Asc(";") ; If you find a semicolon anywhere in the string
PokeB(MyMemory + L - 1, 0) ; Replace it with an empty character as the driver doesn't use it
EndIf ; End the current check
Next L ; Check the next byte
Result = CallFunction(1, "SQLConfigDataSource", 0, 1, @Driver, MyMemory) ; Call the function you need from the ODBC library with the right details
FreeMemory(MyMemory) ; Free the memory now
CloseLibrary(#ODBCLibrary) ; Close the library
If Result ; If the fucntion call worked, tell the rest of the code
ProcedureReturn 1 ; Return the result
EndIf ; End the check
EndIf ; End the routine
EndProcedure
;============================================================================================================================
; Notes
;============================================================================================================================
;
; "icat.mdb" is my default database name
; "C:\Development\My Code\iCat\Resource\_db.raw" is the path to the raw database header that can be created by Access itself
; It's only around 40k or so.
;
; create table Style SQL command to create a table called "Style" in the current opened database
; id autoincrement Automatically increment the record number as you add records
;
;============================================================================================================================
; Value types
;============================================================================================================================
; deleted long a field called "deleted" which is a purebasic long value
; dvol text(3) a field called "dvol" which is a text field of 3 characters long
; comment memo a field called "comment" which is a memo field in SQL. This length of a
; string you can pass here is unfortunately subject to purebasic's string
; length limitation which will be addressed in future releases
;
;============================================================================================================================
; Simpler example for table creation
;============================================================================================================================
; QueryString.s = "Create Table MyTable(ID AutoIncrement, DeadRecord Long, RecordName Text(128), Description memo);"
; NOTE ** Don't forget the semicolon at the end of the SQL string or you will get an error