Bah.ODBC Questions

I am trying to get the field names for each table.
I have:

Local s:String[] = db.getTables()

s returns my table names.
I know i have to use a TQueryField to get the Name but i don't know how to do the next step.

I have a lot of tables to read data from so i am trying to set up a generic method to read the data into lists.

I can do each table individually already.

I use a base table type with an s_ColumnNames:String[] array that is used to create the table in the first place.

Less efficient, but I imagine you could do this also:

	Method ColumnName:String(_s_table:String, _column:Int)
	
		Local s_name:String 
		
		'	Grab a row from the table
		Local query:TDatabaseQuery = Query("SELECT * from " +_s_table +" LIMIT 1")

		'	If we found a row
		If query And query.nextRow()
		
			'	Grab the row record
			Local record:TQueryRecord = query.rowRecord()
				
			'	Grab the field (column) of this record
			Local query_field:TQueryField = record.getField(_column)
				
			'	Grab the name of this field
			If query_field Then s_name = query_field.name
				
		EndIf
				
		Return s_name
		
	End Method

ODBC has the ability to retrieve column names for specific tables, but I'd have to build something in such a way as to be cross-database friendly.

Unless you are building some kind of database viewer tool where you don't know what tables you'll be using upfront, there are better ways to use ODBC that don't require your app to need to know about field names directly.

Of course, it depends what you want to do :-)

What David has done is what i am after.
I was going to do a "select * from ..." query but i thought there must be a better way.
also i name my fields in each table in proper words (not shortened codes) so i am hoping i can do something like:
field s_name:s_type
where s_type is the INT, STRING etc

not sure if blitz will let me do that but i will try tonight after work. i probably can't since i would be inside a method, but hey, i have to learn somehow...and playing with different ideas is always fun.

i am expecting this approach to fail but since all i really need is the field names for grid/list headers i can do that with Davids code.

many thanks

I personally never trust SELECT * ... and prefer to name the fields directly in the SQL.

But each to their own :-)

I was going to add that SELECT * may well fail above if you are looking at an empty table - I'm not sure.

If you are creating the tables anyway - why not store all the names/types on creation?

For this project i am using a pre-existing database so i don't need to create it, but i should really do the creation anyway in case the database is deleted.

Cheers
Glenn

Creating a NEW ODBC Database:

In my tests I have supplied a blank database with a FileDSN for the LoadDatabase call.
I can add/delete tables/records.

What i can't seem to do is CREATE the blank file from within Blitz. The only function i can see is LoadDatabase.
The below code has me confused:

SuperStrict
Import bah.dbodbc

Global db:TDBConnection
db = LoadDatabase("ODBC", "C2D.mdb", Null, Null, Null, Null, "c:\C2D.mdb")

' Check that the DB connection has been made
If Not db Then
	Notify("Unable to Open the Database c:\C2D.mdb")
	End
Else
	DebugLog ("Opened")
End If
	
' Check if the database has an error on opening
If db.hasError() Then
	errorAndClose(db)
End If
	
Function errorAndClose(db:TDBConnection)
	DebugLog(db.error().toString())
	db.close()
	Notify("Read the DebugLog to see what the error was")
	End
End Function

The debuglog tells me db has opened but i suppose that is because db exists, even if it is null. then the errorandclose function kicks in.
No file is created.

I have read the source files and found references for SQLWriteFileDSN.c but i don't have the faintest clue how to invoke it.

also i was hoping the LoadDatabase call would act like an OpenStream call whereby the file is created if it doesn't exist. obviously it doesn't. I couldn't locate any other command like "CreateDatabase".

How do you guys create a database file and a file DSN for ODBC types?

Cheers
Glenn

What you appear to be trying to do is create an Access database file through ODBC?

As far as I'm aware, you can't do that, since that is a Access specific thing - ODBC being a generic interface for connecting to databases.

I'll have a rummage around google anyways, just in case.

yup that is what i was trying to do.

since i can create tables in the database (container) i figured i should be able to create the container too.

If i can't then i need to supply at a bare minimum a blank database container with the application. So if the specific database i am using is not found then i make a copy of the blank one, rename it to the specific databases name, and then rebuild the tables.
I can do that without any difficulty.

what i don't know how to do is create the filedsn entry for a new installation, other than by doing it manually or supplying the user with instructions, which a lot of users will find quite complicated...

Yep... I was about to suggest that ;-)

The FileDSN is simply a text file with some parameters. Open one up in your favourite text editor and have a look.

You should be able to create them from a standard template into which you can fill in the gaps, like path etc.

As far as I know, you shouldn't need to add anything to the system as such, as all the information you require will be in that file.
You may need to experiment til you get the hang of it.

For interests sake, this : http://teamsybase.net/blogs/?p=44
talks about using OLE to create the database file from scratch, but you're on your own there :-)

many thanks

Thanks Brucey,
Now i understand the whole FileDSN thing. Yet to create a test one though.

That TeamSybase page would be useful if i was using visual basic with a reference to microsoft.ole but as far as i know i can't do that in blitz.
A blank Database with a fileDSN is what i will use for now.

Cheers
Glenn