NewsForumsCodeLogsGallerySpecsSearch

Blitz - MySQL?

Blitz3D Forums/Blitz3D Programming/Blitz - MySQL?

Skel(Posted 2007) [#1]
I have been working on a .NET project for some time that integrates with a MySQL database. Something that I have been attempting is to create rotatable 3D graphs of data (read directly from MySQL) using DirectX, but this has proven to be more difficult than the value of the end result. Back in the day, I was a fairly keen hobby Blitz programmer, and it dawned on me that I might be able to make use of that. So, my question is – does anyone out there have any knowledge re getting Blitz 3D to read data from a MySQL database? Is it simply not possible? Or could one of the existing connectors (http://dev.mysql.com/downloads/connector/) somehow be used?

If anyone has even a slight idea I’d love to hear about it. I’ve turned up absolutely nothing from Googling…


Wayne(Posted 2007) [#2]
I worked closely with Kanati when he developed Blitzdata.

I'm pretty sure I still have the final release of that wonderful code. Blitzdata should allow you to make the odbc connection to mysql.

I'll arrange some links for download unless kanati can still serve them up.

Last year I used it to connect to db400 and display customer cabinet orders in 3d.


Wayne(Posted 2007) [#3]
Let me know if you'd like it emailed ?


Wayne(Posted 2007) [#4]
BlitzData Userlib



The BlitzData userlib is an ADO wrapper for Blitz+ and Blitz3D. Because it wraps COM objects I decided to use Visual Basic and vbAdvance to create the dll. Due to this, you will, in addition to the blitzdata.dll itself need to distribute the VB6sp5 runtimes with your finished app/game. ALSO, you will need to distribute the latest MDAC package which Microsoft has available for download (2.7 at the time of this document’s creation if I am not mistaken). This will increase the size of your distribution, but it will ensure that the end user has all required components.



Features

Connection and Connectionless database access (DSNless)

Quick access to Access/Jet(97/2000), MS SQL(7.0/2000), and Oracle databases

Access to any database that has an MDAC driver installed via a custom connection string

Easy database and table creation via code only

Easily return data on a column by column per-record basis

Easy navigation through records using simple forward and back commands

Return recordsets based on simple or complex T-SQL statements

Use the full T-SQL language (with databases that support it)

Transaction usage via simple begin, commit and rollback commands


Wayne(Posted 2007) [#5]
Function List



BD_OpenDatabase%(Server$,Database$,UserName$,Password$,dbtype%)

BD_CloseDatabase()

BD_GetDatabaseState%()

BD_GetTableState%()

BD_FirstRecord()

BD_NextRecord()

BD_PreviousRecord()

BD_LastRecord()

BD_RecordCount%()

BD_SQLQuery%(query$)

BD_CloseQuery()

BD_GetColumn$(colname$)

BD_AddRecord()

BD_UpdateRecord()

BD_SetStringColumn(col$, val$)

BD_SetIntegerColumn(col$, val%)

BD_SetFloatColumn(col$, val#)

BD_BeginTransaction()

BD_RollbackTransaction()

BD_CommitTransaction()

BD_CreateJetDatabase(fname$,typ%)

BD_CreateJetTable(fname$,table$)

BD_AddJetColumn(fname$,table$,col$,typ%,siz%)

BD_SendSQLCommand(sql$)


Wayne(Posted 2007) [#6]
Function Details

BD_OpenDatabase%(Server$,Database$,UserName$,Password$,dbtype%)

This command opens a database in one of a number of ways.

Returns a 0 if successful and -1 if unable to open the database.


Server$ Filename of database (Access / Jet)

Server ip or name (SQL / Oracle)

Connection string (Custom)

Database$ Database to open (non Access)

Username$ Username to log on as

Password$ Password to log on with

Dbtype% 1 – SQL

2 – Oracle

3 – Access / Jet

4 – Custom connection string


Example: tmpdb = BD_OpenDatabase(“C:\test.mdb”,””,””,””,3)

Access database with no authentication

Tmpdb = BD_OpenDatabase(“SQLServer3”,”Taxes”,”sa”,”sapassword”,1)

SQL server, taxes database, logging in as “sa” with password “sapassword”



BD_CloseDatabase()

Takes no arguments and returns nothing. Closes the currently open database.


BD_GetDataBaseState%()

Returns the current state of the database.


BD_StateClosed = 0

BD_StateOpen = 1

BD_StateConnecting = 2

BD_StateExecuting = 4

BD_StateFetching = 8


BD_GetTableState%()

Returns the current state of the open table/recordset.


BD_StateClosed = 0

BD_StateOpen = 1

BD_StateConnecting = 2

BD_StateExecuting = 4

BD_StateFetching = 8


BD_FirstRecord()

Skip to the first record in the recordset opened with BD_SQLQuery()


BD_NextRecord()

Skip to the next record in the recordset.


BD_PreviousRecord()

Skip back one record in the current recordset.


BD_LastRecord()

Skip to the last record in the current recordset.


BD_RecordCount%()

Returns the number of records in the current recordset.


BD_SQLQuery%(query$)

Opens a recordset using standard T-SQL query language.

Returns 0 if successful and -1 if the recordset could not be opened.


Query$ SQL query to open

Example: response = BD_SQLQuery(“SELECT TOP 10 * FROM AUTHORS”)


BD_CloseQuery()

Closes the currently opened recordset


BD_GetColumn$(Colname$)

Retrieves the contents of the column Colname$ in the current record.

Irregardless of the datatype of the column it is returned as a string.


Colname$ Name of the column to retrieve.

Example: Tmp$ = BD_GetColumn(“FirstName”)

Tmp% = BD_GetColumn(“CustomerNumber”)


(blitz converts the string to an integer in the above)


BD_AddRecord()

Adds a blank record to the currently open recordset and sets it to the current record.


BD_UpdateRecord()

Updates the current record with any changes made to it with the BD_Set… commands.

BD_SetStringColumn(col$, val$)

BD_SetIntegerColumn(col$, val%)

BD_SetFloatColumn(col$, val#)

Sets a column’s value. Use the correct command for the respective datatypes. Some datatypes overlap. For instance a byte, SmallInt, Integer, LongInteger are all the same as far as blitz is concerned. The same goes for currency, and floats.


BD_BeginTransaction()

Begins a “transaction”. A transaction is a set of database commands that are not actually made until “committed”. If an error occurs during the process, at any time, the user can “rollback” the entire transaction.

BD_RollbackTransaction()

Allows you to cancel an entire set of database commands if a transaction was started. If no transaction exists then an error will occur.

BD_CommitTransaction()

Commits the number of database commands that have been called since the last BD_BeginTransaction().


BD_CreateJetDatabase(fname$, typ%)

Creates a new Access/Jet database.


Fname$ the name of the database to create (file name)

Type 95 or 97 creates an access 95/97 database. Anything else creates an access

2000 database

BD_CreateJetTable(fname$, table$)

Creates a new table in an existing jet database.


Fname$ filename of an existing database

Table$ name of the new table to create


BD_AddJetColumn(fname$, table$, colname$, type%, siz%)

Adds a new column to an existing jet database table.


Fname$ filename of existing database

Table$ name of existing table

Colname$ name of the new column to add

Type% column type

Siz% column size (or 0 if not applicable)


BD_BigInt = 20 ;8 byte signed int

BD_Binary = 128 ;binary value

BD_Boolean = 11 ;boolean value

BD_BSTR = 8 ;null terminated character string (unicode)

BD_Chapter = 136 ;4 byte chapter value that identifies rows in

;a child rowset

BD_Char = 129 ;string value

BD_Currency = 6 ;fixed point number with 4 digits past decimal

BD_Date = 7 ;Date value stored as a double (whole # is days

;past 12/30/1899, decimal is fractional part of

;day)

BD_DBDate = 133 ;Date value (yyyymmdd)

BD_DBTime = 134 ;time value (hhmmss)

BD_DBTimeStamp = 135 ;date/timestamp (yyyymmddhhmmss + fraction in

;billionths)

BD_Decimal = 14 ;exact numeric value with fixed precision & scale

BD_Double = 5 ;double precision floating point value

BD_Empty = 0 ;No value

BD_Error = 10 ;Indicates a 32 bit error code

BD_FileTime = 64 ;64 bit value representing the number of 100-

;nanosecond intervals since 1/1/1601

BD_GUID = 72 ;globally unique identifier (GUID)

BD_IDispatch = 9 ;pointer to an idispatch interface on a com object

BD_Integer = 3 ;four byte signed integer

BD_IUnknown = 13 ;pointer to an iunknown interface on a com object

BD_LongVarBinary = 205 ;Long binary value (parameter object only)

BD_LongVarChar = 201 ;Long string value (parameter object only)

BD_LongVarWChar = 203 ;null terminated unicode string

BD_Numeric = 131 ;an exact numeric value with fixed precision

BD_PropVariant = 138 ;automation propvariant

BD_Single = 4 ;single precision floating point value

BD_SmallInt = 2 ;two byte signed integer

BD_TinyInt = 16 ;one byte signed integer

BD_UnsignBigInt = 21 ;8 byte unsigned integer

BD_UnsignInt = 19 ;four byte unsigned integer

BD_UnsignSmallInt = 18 ;two byte unsigned integer

BD_UnsignTinyInt = 17 ;one byte unsigned integer

BD_UserDefined = 132 ;user defined variable

BD_VarBinary = 204 ;binary value (parameter object)

BD_VarChar = 200 ;string value (parameter object)

BD_Variant = 12 ;automation variant (not supported)

BD_VarNumeric = 139 ;numeric value

BD_VarWChar = 202 ;null terminated unicode string (parameter obj)

BD_WChar = 130 ;null terminated unicode string


Wayne(Posted 2007) [#7]
Sample program #1

Graphics 800,600,32,2
SetBuffer BackBuffer()

t% = BD_OpenDatabase("TEST.mdb","","","",3)

If t = -1 Then
  Text 20,20,"Could not open database."
  Flip
  key = WaitKey()
  End
Else
  Text 20,20,"Opened TEST database Successfully."
  Flip
  While Not GetKey():Wend
EndIf

t = BD_SQLQuery("SELECT * FROM TESTTABLE ORDER BY LNAME")
If t = 0 Then
  Text 20,40, "Opened recordset successfully"
  Flip
EndIf

BD_AddRecord()
BD_SetStringColumn("LNAME", "SPRINGSTEEN")
BD_SetStringColumn("FNAME", "BRUCE")
BD_UpdateRecord()

BD_CloseQuery()

t = BD_SQLQuery("SELECT * FROM TESTTABLE ORDER BY LNAME")
If t = 0 Then
  Text 20,60, "Opened recordset successfully"
  Flip
EndIf

For t = 1 To BD_RecordCount()
  Text 20, 60 + (t * 20), BD_GetColumn("LNAME") + ", " + BD_GetColumn("FNAME")
  BD_NextRecord()
  Flip
  WaitKey()
Next

BD_CloseQuery()
BD_CloseDatabase()
While Not GetKey():Wend



Wayne(Posted 2007) [#8]
Sample Program #2 (ODBC Connection to AS400):

Graphics 800,600,32,2
SetBuffer BackBuffer()
BD_SetDebugMode(1)



t% = BD_OpenDatabase("Driver=Client Access ODBC Driver (32-bit);system=PHOENIX;DefaultLibraries=,*usrlibl;ForceTranslation=0;ExtendedDynamic=0;Naming=1;Prompt=2","","","",4)

If t = -1 Then
  Text 20,20,"Could not open database."
  Flip
  key = WaitKey()
  End
Else
  Text 20,20,"Opened TEST database Successfully."
  Flip
  ;While Not GetKey():Wend
EndIf

; Get Record Count
t = BD_SQLQuery("select count(*) count  from o6p o6 Left join cfexp cf on cf.cflot#=o6.odlot# And cf.cfwh=o6.o6wh where o6ord#=20264 ")
If t = 0 Then 
  count=BD_GetColumn("COUNT")
  Text 20,60, "Record Count=" + count 
  Flip
  ;WaitKey()
EndIf

BD_CloseQuery()

t = BD_SQLQuery("select o6ord#, o6sufx, odpn, odorgq,Case when cf.H is Null Then '' Else char(cf.h) End H, Case when cf.W is Null Then '' Else char(cf.w) End W, Case when cf.D is Null Then '' Else char(cf.d) End D, Case when cf.scount is Null Then '' Else char(cf.scount) End SCOUNT from o6p o6 Left join cfexp cf on cf.cflot#=o6.odlot# And cf.cfwh=o6.o6wh where o6ord#=20264 ")
If t = 0 Then 
  Text 20,80, "Opened recordset successfully"
  Text 20,100, "Colunm Count= " + BD_ColumnCount()
For i=1 To  BD_ColumnCount():c$=c$+BD_ColumnName$(i)+", ":Next
	Text 20,110, c$
  Text 20,120, "Bof= " + BD_BOF%()
  Flip
  ;WaitKey()
EndIf

While Not BD_EOF%()
	j=j+1
  	Text 20, 130 + (j * 10), BD_GetColumn("O6ORD#")+", " + BD_GetColumn("ODPN") + ", "+ BD_GetColumn("H") + ", " + BD_GetColumn("W")+", " + BD_GetColumn("D")+", " + BD_GetColumn("SCOUNT")
	Text 90,120, "Bof= " + BD_BOF%()
  	If j=40 Then j=0 
  	BD_NextRecord()
  Flip
Wend

  WaitKey()

BD_CloseQuery()
BD_CloseDatabase()
While Not GetKey():Wend



Wayne(Posted 2007) [#9]
Sample program #3 (MS SQL Server):

Include "bd_constants.bb"

Graphics 800,600,32,2
SetBuffer BackBuffer()

t% = BD_OpenDatabase("sql3","efiling","sqladmin","zamboni",bd_sqlserver)

If t% = 0 Then
  Text 20,20, "Could not open efile database"
  Flip
  WaitKey()
  End
EndIf

t% = BD_SQLQuery("SELECT TOP 5 * FROM DOSDBF")

If t% <> 0 Then
  End
EndIf

Text 20,20,"OPENING EFILE DATABASE ON NTSSQL3"
Flip

For t = 1 To BD_RecordCount()
  Text 20,30+(13 * t), BD_GetColumn("FILENAME") + "     " + BD_GetColumn("FILEDATE")
  Flip
  BD_NextRecord()
Next

BD_CloseQuery()
BD_CloseDatabase()

WaitKey()


Sample Program #4 ( MS Access table creation ):

Include "bd_constants.bb"



Graphics 800,600,32,2
SetBuffer BackBuffer()

db$ = "C:\NTSTEST.MDB"
tbl$ = "TMPDATA"

BD_CreateJetDatabase(db$,0)
BD_CreateJetTable(db$,tbl$)
BD_AddJetColumn(db$,tbl$,"LNAME",BD_VarWChar,150)
BD_AddJetColumn(db$,tbl$,"FNAME",BD_VarWChar,50)
BD_AddJetColumn(db$,tbl$,"AGE",BD_Integer,0)


Sample Program #5 (MS Access):

Graphics 800,600,32,2
SetBuffer BackBuffer()

t% = BD_OpenDatabase("TEST.mdb","","","",3)

If t = -1 Then
  Text 20,20,"Could not open database."
  Flip
  key = WaitKey()
  End
Else
  Text 20,20,"Opened TEST database Successfully."
  Flip
  While Not GetKey():Wend
EndIf

t = BD_SQLQuery("SELECT * FROM TESTTABLE ORDER BY LNAME")
If t = 0 Then
  Text 20,40, "Opened recordset successfully"
  Flip
EndIf

BD_AddRecord()
BD_SetStringColumn("LNAME", "SPRINGSTEEN")
BD_SetStringColumn("FNAME", "BRUCE")
BD_UpdateRecord()

BD_CloseQuery()

t = BD_SQLQuery("SELECT * FROM TESTTABLE ORDER BY LNAME")
If t = 0 Then
  Text 20,60, "Opened recordset successfully"
  Flip
EndIf

For t = 1 To BD_RecordCount()
  Text 20, 60 + (t * 20), BD_GetColumn("LNAME") + ", " + BD_GetColumn("FNAME")
  BD_NextRecord()
  Flip
  WaitKey()
Next

BD_CloseQuery()
BD_CloseDatabase()
While Not GetKey():Wend



Wayne(Posted 2007) [#10]
The include file for constants looks like this:

;DATA TYPE CONSTANTS
Const BD_BigInt = 20 ;8 byte signed int
Const BD_Binary = 128 ;binary value
Const BD_Boolean = 11 ;boolean value
Const BD_BSTR = 8 ;null terminated character string (unicode)
Const BD_Chapter = 136 ;4 byte chapter value that identifies rows in
;a child rowset
Const BD_Char = 129 ;string value
Const BD_Currency = 6 ;fixed point number with 4 digits past decimal
Const BD_Date = 7 ;Date value stored as a double (whole # is days
;past 12/30/1899, decimal is fractional part of
;day)
Const BD_DBDate = 133 ;Date value (yyyymmdd)
Const BD_DBTime = 134 ;time value (hhmmss)
Const BD_DBTimeStamp = 135 ;date/timestamp (yyyymmddhhmmss + fraction in
;billionths)
Const BD_Decimal = 14 ;exact numeric value with fixed precision & scale
Const BD_Double = 5 ;double precision floating point value
Const BD_Empty = 0 ;No value
Const BD_Error = 10 ;Indicates a 32 bit error code
Const BD_FileTime = 64 ;64 bit value representing the number of 100-
;nanosecond intervals since 1/1/1601
Const BD_GUID = 72 ;globally unique identifier (GUID)
Const BD_IDispatch = 9 ;pointer to an idispatch interface on a com object
Const BD_Integer = 3 ;four byte signed integer
Const BD_IUnknown = 13 ;pointer to an iunknown interface on a com object
Const BD_LongVarBinary = 205 ;Long binary value (parameter object only)
Const BD_LongVarChar = 201 ;Long string value (parameter object only)
Const BD_LongVarWChar = 203 ;null terminated unicode string
Const BD_Numeric = 131 ;an exact numeric value with fixed precision
Const BD_PropVariant = 138 ;automation propvariant
Const BD_Single = 4 ;single precision floating point value
Const BD_SmallInt = 2 ;two byte signed integer
Const BD_TinyInt = 16 ;one byte signed integer
Const BD_UnsignBigInt = 21 ;8 byte unsigned integer
Const BD_UnsignInt = 19 ;four byte unsigned integer
Const BD_UnsignSmallInt = 18 ;two byte unsigned integer
Const BD_UnsignTinyInt = 17 ;one byte unsigned integer
Const BD_UserDefined = 132 ;user defined variable
Const BD_VarBinary = 204 ;binary value (parameter object)
Const BD_VarChar = 200 ;string value (parameter object)
Const BD_Variant = 12 ;automation variant (not supported)
Const BD_VarNumeric = 139 ;numeric value
Const BD_VarWChar = 202 ;null terminated unicode string (parameter obj)
Const BD_WChar = 130 ;null terminated unicode string


Const BD_SQLServer = 1
Const BD_OracleServer = 2
Const BD_AccessDB = 3
Const BD_CustomDB = 4

Const BD_StateClosed = 0
Const BD_StateOpen = 1
Const BD_StateConnecting = 2
Const BD_StateExecuting = 4
Const BD_StateFetching = 8

Const BD_NullString$ = "*NULL*"
Const BD_NullInt% = -2147483648
Const BD_NullFloat# = -2147483648.0


Wayne(Posted 2007) [#11]
Kanati did a great job with this package and it works.

All the above and more, let me know if your still interested.

BlitzData 1.41


Blitzplotter(Posted 2007) [#12]
I will be looking at BlitzData 1.41, is there many user docs asasociated with this product ?

regards,


Wayne(Posted 2007) [#13]
I posted most of user documentation.
Summary:

Download vb6 runtime (VBRun60sp5) from Microsoft or here
http://www.cybersoftcentral.com/runtimes/Default.asp

Download latest Mdac2.8 from microsoft.

Place the blitz decls file into your b3d userlibs folder.

Place blitzdata.dll, and BD_Constants include file with the sample programs.

Run the samples.


Blitzplotter(Posted 2007) [#14]
Thanks...


Skel(Posted 2007) [#15]
That sounds perfect! I found a link to the download, but it goes off to some sort of religious search engine! Do you know where I can download a current version? Alternatively, please email me the details. Thanks for all the information posted here - this is EXACTLY what I needed.


Skel(Posted 2007) [#16]
OK - found an active link. Will get to work and see how things go. Thanks again!


Skel(Posted 2007) [#17]
Cancel that. Ran the installer and it's actually BlitzPrinter Userlib. Still looking for a download for BlitzData...


Wayne(Posted 2007) [#18]
mailing


Blitzplotter(Posted 2007) [#19]
I do intend to try this out... just gotta find some time. Have you a good link Wayne ? regards...


Wayne(Posted 2007) [#20]
Blitzplotter,
I don't have a link up at the moment, but can email it latter today or set up a link. Let me know.


Blitzplotter(Posted 2007) [#21]
Wayne, I'd appreciate you emailing me it. I am doing a database analysis/design course at college, it'd be nice to 'practice' via your application. Regards,


Wayne(Posted 2007) [#22]
I'll send it over in about 3 hrs. It's a great tool and we can thank Kanati for creating it.


Blitzplotter(Posted 2007) [#23]
Thanks wayne, received it {;-}, installed it and it is the BlitzPrinter Userlib. Off to check your user docs.... Found download info for Service Pack5 of vb6 but no user docs ?

regards


Blitzplotter(Posted 2007) [#24]
Well, BlitzPrinter userlib installed... tried out the code :


Include "bd_constants.bb"



Graphics 800,600,32,2
SetBuffer BackBuffer()

db$ = "C:\NTSTEST.MDB"
tbl$ = "TMPDATA"

BD_CreateJetDatabase(db$,0)
BD_CreateJetTable(db$,tbl$)
BD_AddJetColumn(db$,tbl$,"LNAME",BD_VarWChar,150)
BD_AddJetColumn(db$,tbl$,"FNAME",BD_VarWChar,50)
BD_AddJetColumn(db$,tbl$,"AGE",BD_Integer,0)


Had the relevant include there... however received the following upon attempting a compile:



function bd_createjetdatabase not found....




Could you mail the BlitzData lib ? thanks....


Wayne(Posted 2007) [#25]
I figured the zip I had of blitzdata was blitzdata not blitzprinter. Since I have all the blitzdata stuff unzipped I'll recreate the zip and send it.

Blitzdata will require vb runtime and mdac, and your choice of database. I installed MS Access when I tested it with mdb's, but you could most likely just install ms jet 3.5 or later.


Blitzplotter(Posted 2007) [#26]
Thanks Wayne, I'm 'playing' with Access DBases at college so this'll tie in nicely.

regards, BPlotter


Wayne(Posted 2007) [#27]
I repackaged the goodies and emailed to all those that requested it. Let me know if I missed anything.

If you have a questions post here and I'll do my best to help.


Banshee(Posted 2007) [#28]
does anyone out there have any knowledge re getting Blitz 3D to read data from a MySQL database?

Another very simple solution that I use is to write an SQL interface in a web page and have my software talk to that, this allows the software to be quickly converted to other database formats just be rewritting the php page. I learned how to do it from GNET.


Wayne(Posted 2007) [#29]
Thats clever solution Banshee, what does the web page code look like ?


Blitzplotter(Posted 2007) [#30]
Thanks Wayne, received the code, will give it a whirl this week.


SoggyP(Posted 2007) [#31]
Hello.

Hmm, interesting. The first access example runs with no errors, but equally doesn't return any values in the recordsets nor updates the tables.

Running the example to create a db proved to be no problem.

Any ideas?

Goodbye.


Wayne(Posted 2007) [#32]
it sure seems like I have and old dll so I'm trying to track down the final release. Most sorry if it turns out I sent out and old one. Arghhh

I was able to create the table and write data to the table but had to change the read loop to use: while not BD_EOF()
that did return data but sadly would not return eof properly and looped forever.

The final release is out there somewhere we must find it.
I also emailed Kanati the original author to see if her has a copy. Anyone else have Blitzdata14rc1 ( I think was final release )?


Wayne(Posted 2007) [#33]
After much searching I think I've found the goodies here
Just get the file: blitzdataupd

http://www.radioactivegamer.com/Blitz/

I'm checking it out too.


Wayne(Posted 2007) [#34]
I have it working!

I'm writing some new SQL examples.


Wayne(Posted 2007) [#35]
..


Wayne(Posted 2007) [#36]
I ran some more tests, was able to create database, create table, and even write data, but could not write consecutive records. I was able to run sql statements and query the access 97 table in this case.

I notice getdatabasestate is not in the dll. I'm not sure this was the final distribution release, but in any event this appears only functional for reading data, unless Kanati has time to help.

IMHO we now need a new solution, perhaps php tip Banshee talked about. I'd wrap the vb stuff myself if I had the software to create the proper DLL.

I'm done with this implementation unless something changes, or I discover I've made a mistake somewhere.

Sorry for those who downloaded. Thanks for the php tip Banshee.

8)


Blitzplotter(Posted 2007) [#37]
thanks for trying wayne {;->


SoggyP(Posted 2007) [#38]
Hello.

Yes, thanks for trying Wayne, much appreciated.

Just to let you know, I've got around this issue in a similar kind of way to Banshee. I've an SQL db that has a number of Stored Procedures and Functions (read as queries) that are called by as asp page. The returns from these queries are output by the asp page in a tagged format. The Blitz app then opens a tcp stream (good old code archives) parsing the values returned.

It's not elegant but it's quick enough and does the job.

One tip if anyone is thinking of doing a similar thing. It seems that when reading the tcp stream, if you reach the end of file there is a significant lag. I got around this by surrounding my data in two other tags <DATA START> and <DATA END> and when reaching <DATA END> close the tcp stream then, without waiting to reach EOF.

Goodbye.


TemisNet(Posted 2007) [#39]
I'm Looking for BlitzData 1.41

The download link ( http://www.radioactivegamer.com/Blitz/) the file isn't right... it comes with the printer lib, not the BlitzData.

I was thinking if you still have the BlitzData.
If yes, can you send it to me (ronaldo.cosmo AT gmail.com), please?

Thx!


Wayne(Posted 2007) [#40]
sure

I'll try contacting Kanati again too.


Wayne(Posted 2007) [#41]
After thinking about it perhaps it would make sense to create a database server process using VB6.

The VB6 ADO_Server process would be started, and listen for connections. Client B3d Programs could connect, have sql commands processed, and results returned.

The B3D program would talk to the remote, or local machine using TCPIP. Communications would be asynchronous no blocking.

Working on the B3d syntax.

Comments welcome.


Blitzplotter(Posted 2007) [#42]
A B3d / VB6 application sounds interesting. I use a VB6 developed GUI to start a choice of BMax apps at the moment. I have never went as far as using VB6 to run an ADO_Server process, it does sound interesting ... I'd be interested to hear how you get on. Regards,


Wayne(Posted 2007) [#43]
This will add multiple rows for msaccess:

;
; ADDING ROWS
; 
Graphics 800,600,32,2
SetBuffer BackBuffer()

t% = BD_OpenDatabase("c:\TEST.mdb","","","",3)

If t = -1 Then

  Text 20,20,"Could not open database."
  Flip
  key = WaitKey()
  End

Else

  Text 20,20,"Opened TEST database Successfully, press any key to add records"
  Flip
  While Not GetKey():Wend


  ; Establish Recordset
  t = BD_SQLQuery("Select * from table1 where 1=2 ")
  ;	BD_CloseQuery()
		
  ; add row
For i=1 To 10
	BD_AddRecord()
	BD_SetStringColumn("LNAME", "SPRINGSTEEN")
	BD_SetStringColumn("FNAME", "BRUCE")
	BD_SetIntegerColumn("AGE", i)
	BD_UpdateRecord()
Next
EndIf

Text 20,40,"Press space to close"
Flip
While Not GetKey():Wend

BD_CloseQuery()
BD_CloseDatabase()



Wayne(Posted 2007) [#44]
Simple query to display records:
Graphics 800,600,32,2
SetBuffer BackBuffer()

t% = BD_OpenDatabase("c:\TEST.mdb","","","",3)

If t = -1 Then
  Print "Could not open database."

  key = WaitKey()
  End
  Print "Opened TEST database Successfully."
  Flip
  While Not GetKey():Wend
EndIf

t = BD_SQLQuery("SELECT * FROM Table1 ORDER BY LNAME")
If t = 0 Then
Print "Opened recordset successfully"

EndIf


While Not BD_EOF()
Print BD_GetColumn("LNAME") + ", " + BD_GetColumn("FNAME")+","+Str(BD_GetColumn("AGE"))
  BD_NextRecord()
Wend

  While Not GetKey():Wend

BD_CloseQuery()
BD_CloseDatabase()



Wayne(Posted 2007) [#45]
I could not get multiple inserts to work as in the following:
ONLY WORKED FOR FIRST INSERT, don't know why.

;
; ADDING ROWS
; 
Graphics 800,600,32,2
SetBuffer BackBuffer()

t% = BD_OpenDatabase("c:\TEST.mdb","","","",3)

If t = -1 Then

  Text 20,20,"Could not open database."
  Flip
  key = WaitKey()
  End

Else

  Text 20,20,"Opened TEST database Successfully, press any key to add records"
  Flip
  While Not GetKey():Wend

	  ; add row
	For i=1 To 10
			t = BD_SQLQuery("insert into table1 (LNAME,FNAME,AGE) values('Doe','John',25);")
	Next
 
End If
While Not GetKey():Wend

Text 40,40,"Press space to close"
Flip

While Not GetKey():Wend
BD_CloseQuery()
BD_CloseDatabase()



Wayne(Posted 2007) [#46]
I researched into using vbadvance to export vb6 functions, and got bogged down in the initializing the vb6 com object. I was considering replicating Kanati work from scratch, but due to vbadvance no longer being sold, and chunks of missing code in the examples I found it seemed torturous to continue on that path.

I looked at using the ODBC API and making something nice, and have lots of info on that, but it's a pretty good size project and my time maybe better spent elsewhere to reach the same goals.

As It stands BlitzData does appear to work but has that sql insert limitation.

I've started laying out the commands for a replacement for Blitzdata. The ado server would be vb6 or vb express, and would communicate via udp, or tcp. The user would just launch the ado server, and have include file that support the command set and the communications.

Example follows, comments welcome.


Wayne(Posted 2007) [#47]
; BlitzData2 - Commands
;------------------------------

BD_OpenDataBase%()

BD_GetDatabaseState%()

BD_CloseDataBase%()



BD_Execute Sql$

BD_GetTableState%()

BD_EOF%()

BD_BOF%()

BD_MoveFirst()

BD_MoveNext()

BD_MovePrev()

BD_MoveLast()

BD_RecordCount%()

BD_GetColumn$(colname$)

BD_Field$(Ordinal%)

BD_FieldCount%()

BD_CloseRecordSet()



BD_BeginTransaction()

BD_RollbackTransaction()

BD_CommitTransaction()

;------------------------------------------------------

Dim SQL$

; ODBC to AS400 connection string example
;BD_ConnectString "Driver=Client Access ODBC Driver 
(32-bit);system=Phoenix;DefaultLibraries=,*usrlibl;Forc
eTranslation=1;ExtendedDynamic=0;Naming=1;Prompt=2;uid=
Wayne;pwd=Secret;" 

; MS ACCESS CONNECTION STRING
BD_ConnectString "Driver= {MicrosoftAccessDriver(*.mdb)};DBQ=c:\TEST.mdb;"

BD_CommandTimeOut 0
BD_ConnectionTimeOut 30


BD_CursorLocation adUseClient
BD_CursorType adOpenStatic

SQL="select * from table1 for read only"

Stat=BD_Execute(SQL)

While Not BD_EOF()
  Print BD_GetColumn("LNAME") + ", " + 
BD_GetColumn("FNAME")+","+Str(BD_GetColumn("AGE"))

  BD_MoveNext()
Wend



Blitzplotter(Posted 2007) [#48]
This looks very interesting, my times a bit limited at the mo, various things on the go - bit of dbase design for college being one. I am keen to try this out...


Gabriele(Posted 2010) [#49]
The link at http://www.radioactivegamer.com/blitz/libs/blitzdata141.zip
download only printer utilities and no Blitzdata
I found Blitzdata on update link http://www.radioactivegamer.com/blitz/libs/blitzdataupd.zip
but is the last version?...seem not work, i can put any name of database and the comand BD_OpenDatabase return 0 all times (so seem they found the database also if not are).
are a new vesion of routines?
Ty all


6(Posted 2010) [#50]
I just tried the blitzdataupd.zip and managed to get a database connection working. It doesn't work perfectly as the above examples show, but I am able to create, update and read data from/to a simple Jet database in Blitz3D which is pretty cool.

I couldn't get the BD_CreateJetTable() & BD_AddJetColumn function to work, so what I did was this:

1) Use BD_CreateJetDatabase("test_db.mdb",3) to create the database

2) I then used the SQLDev tool from Oracle to connect to the database and add tables and columns.

3) I then updated those tables using an SQL INSERT statement like in the code below: 

Graphics 800,600,32,2
SetBuffer BackBuffer()

;Open Database
dbname$ = "test_db.mdb"
BD_OpenDatabase(dbname,"","","",3)

;Insert a record
insertSQL$ = "insert into persons_tbl VALUES('Chuck', 'Morris')"
BD_SQLQuery(insertSQL)
BD_CloseQuery()
BD_CloseDatabase()

;Read records to screen
BD_OpenDatabase(dbname,"","","",3)
BD_SQLQuery("SELECT * FROM Persons")

While Not BD_EOF()
	Print BD_GetColumn("LastName")
	BD_NextRecord()
Wend

WaitKey()

BD_CloseQuery()
BD_CloseDatabase()


If anyone is interested(?), I'll update some more details and examples later. Also, note that after I add data to the database I close the connection. This was necessary as I get a crash otherwise.


Gabriele(Posted 2010) [#51]
I dont have Oracle installed so this example not work...and i get a memory error on line Print BD_GetColumn("LastName")
But seem also Access examples not work or ignore instructions


Axel Wheeler(Posted 2010) [#52]
<whine>
The above history really illustrates the sadness that this website represents.

1. Users can't upload their files here.

Blitz developers, especially new ones, are constantly in search of solutions that don't exist anymore, or may exist on someone's hard drive somewhere, if they read the right post at the right time. It was great, but it's gone. In this case, a great SQL interface, probably the only one for Blitz, would be lost if it weren't for the dedicated users here scrounging around finding the pieces here and there and helping each other out. If it's too big to post in the code archives, it's temporary.

2. Not even screenshots?

Really? The odd thing is, you can post to the gallery, but it requires a wait for approval (which took days when I did it and only one of two images was allowed). Presumably the fear of inappropriate images is behind this, but the rest of the world has instituted "report this image" functionality. Also, it's important to post screenshots in the thread itself, not in some gallery that doesn't even automatically link to a thread about it. Just a few lines of whatever the poster can think to put in. It's just not the right way to do things. Most people end up uploading the image to an image-posting site (or their own website, which are generally also temporary...) and link it. Get it while it's hot, kids.

3. No rankings of any kind for anything.

Lots of things are posted in the code archives and tools sections, but which ones are any good? We are constantly wondering which ones are recommended, tried and true approaches, and which ones are problematic.

4. No discussion of planned features to the products. The user community is completely in the dark as to when updates are coming and what will be in them. New motto: "Blitz: No Promises". A simple sticky called "Feature Requests" in each main product forum would help, if Mark reads it and posts to it occasionally. Another sticky called "Planned Features" would really help.

Blitz has achieved nowhere near the success it could have but for these (relatively) simple things.
</whine>

I'll go now and create a Feature Request thread and email Mark with a request to stikify (?) it.

-Pete (Axel)