New ODBC Database plugin - GameCreators Forum

Author

Message

Duffer

21

Years of Service

User Offline

Joined: 9th Feb 2003

Location: chair

Posted: 29th Jun 2013 17:17 Edited at: 3rd Apr 2014 21:35

Link

[IGNORE THE REST OF THIS THREAD - COULD NEVER GET THIS PLUGIN WORKING LIKE THE SQLITE PLUGIN SO ABANDONED IT]

On the back of the new SQLITE3 database plugin I\'ve created, I have also created a ODBC plugin.

For it to work, you will need to have already installed a suitable ODBC driver. You need to create an ODBC \"User Data Source\".

Anyway, this plugin is very much in need of testing and so I will call the current version v0.2 (25/07/2013).

Command Set

Command Set:-
   
ODBC OPEN DATABASE                             -  a function returning an INTEGER value from (DatabaseID INTEGER, DatabaseFilename STRING, Username STRING, Password STRING)
ODBC CLOSE DATABASE                            -  a function returning an INTEGER value from (DatabaseID INTEGER)
ODBC SAVE DATABASE                             -  a function returning an INTEGER value from (DatabaseID INTEGER, DatabaseFileName STRING, Username STRING, Password STRING)
ODBC DATABASE EXIST                            -  a function returning an INTEGER value from (DatabaseID INTEGER)
ODBC GET LAST ERROR$                           -  a function returning a STRING value from <*no parameters*>
ODBC TABLE EXIST                               -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
ODBC TRUNCATE TABLE                            -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
ODBC EMPTY TABLE                               -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
ODBC DROP TABLE                                -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
ODBC DELETE TABLE                              -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
ODBC CREATE TABLE                              -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnsDefinitionsAndConstraints STRING)
ODBC RENAME TABLE                              -  a function returning an INTEGER value from (DatabaseID INTEGER, OriginalTableName STRING, NewTableName STRING)
ODBC TABLE ROW COUNT                           -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
ODBC TABLE COLUMN NAME$                        -  a function returning a STRING value from (DatabaseID INTEGER, TableName STRING, ColumnNumber INTEGER)
ODBC TABLE COLUMN INDEX                        -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING)
ODBC TABLE COLUMN DEFINITION$                  -  a function returning a STRING value from (DatabaseID INTEGER, TableName STRING, ColumnNumber INTEGER)
ODBC TABLE COLUMN SIZE                         -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnNumber INTEGER)
ODBC TABLE COLUMN COUNT                        -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
ODBC TABLE CELL STRING$                        -  a function returning a STRING value from (DatabaseID INTEGER, TableName STRING, ColumnNumber INTEGER, RowNumber INTEGER)
ODBC TABLE CELL INTEGER                        -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnNumber INTEGER, RowNumber INTEGER)
ODBC TABLE CELL FLOAT                          -  a function returning a FLOAT value from (DatabaseID INTEGER, TableName STRING, ColumnNumber INTEGER, RowNumber INTEGER)
ODBC BEGIN SQL QUERY                           -  a function returning an INTEGER value from (DatabaseID INTEGER, SQLQuery STRING)
ODBC BEGIN SQL QUERY SPECIFIC                  -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName1 STRING, ColumnValue1 STRING,[ ColumnName2 STRING,[ ColumnValue2 STRING,[ ColumnName3 STRING,[ ColumnValue3 STRING])
ODBC FURTHER SQL QUERY                         -  a function returning an INTEGER value from (DatabaseID INTEGER, AdditionalSQLQuery STRING)
ODBC FIRST RECORD ROW                          -  a function returning an INTEGER value from (DatabaseID INTEGER)
ODBC PREVIOUS RECORD ROW                       -  a function returning an INTEGER value from (DatabaseID INTEGER)
ODBC NEXT RECORD ROW                           -  a function returning an INTEGER value from (DatabaseID INTEGER)
ODBC GOTO RECORD ROW                           -  a function returning an INTEGER value from (DatabaseID INTEGER, RowToGoTo INTEGER)
ODBC LAST RECORD ROW                           -  a function returning an INTEGER value from (DatabaseID INTEGER)
ODBC RECORD COLUMN NAME$                       -  a function returning a STRING value from (DatabaseID INTEGER, ColumnNumber INTEGER)
ODBC RECORD COLUMN COUNT                       -  a function returning an INTEGER value from (DatabaseID INTEGER)
ODBC RECORD ROW COUNT                          -  a function returning an INTEGER value from (DatabaseID INTEGER)
ODBC RECORD CURRENT ROW STRING$                -  a function returning a STRING value from (DatabaseID INTEGER, ColumnNumber INTEGER)
ODBC RECORD CURRENT ROW INTEGER                -  a function returning an INTEGER value from (DatabaseID INTEGER, ColumnNumber INTEGER)
ODBC RECORD CURRENT ROW FLOAT                  -  a function returning a FLOAT value from (DatabaseID INTEGER, ColumnNumber INTEGER)
ODBC FINISH SQL QUERY                          -  a function returning an INTEGER value from (DatabaseID INTEGER)
ODBC UPDATING SQL STATEMENT                    -  a function returning an INTEGER value from (DatabaseID INTEGER, UpdatingSQLStatement STRING)
ODBC ADD COLUMN TO TABLE                       -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnDefinitionAndConstraint STRING)
ODBC INSERT ROW INTO TABLE                     -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, SpecifiedNamedColumns STRING, SpecifiedValuesForThoseColumns STRING)
ODBC DELETE ROWS FROM TABLE                    -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, WhereConditions STRING)
ODBC CREATE INDEX                              -  a function returning an INTEGER value from (DatabaseID INTEGER, IndexName STRING, TableName STRING, ColumnName STRING)
ODBC DROP INDEX                                -  a function returning an INTEGER value from (DatabaseID INTEGER, IndexName STRING)
ODBC DELETE INDEX                              -  a function returning an INTEGER value from (DatabaseID INTEGER, IndexName STRING)
    
Total Number of Commands = 43

+ Code Snippet

Command Set:-
   
ODBC OPEN DATABASE                             -  a function returning an INTEGER value from (DatabaseID INTEGER, DatabaseFilename STRING, Username STRING, Password STRING)
ODBC CLOSE DATABASE                            -  a function returning an INTEGER value from (DatabaseID INTEGER)
ODBC SAVE DATABASE                             -  a function returning an INTEGER value from (DatabaseID INTEGER, DatabaseFileName STRING, Username STRING, Password STRING)
ODBC DATABASE EXIST                            -  a function returning an INTEGER value from (DatabaseID INTEGER)
ODBC GET LAST ERROR$                           -  a function returning a STRING value from <*no parameters*>
ODBC TABLE EXIST                               -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
ODBC TRUNCATE TABLE                            -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
ODBC EMPTY TABLE                               -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
ODBC DROP TABLE                                -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
ODBC DELETE TABLE                              -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
ODBC CREATE TABLE                              -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnsDefinitionsAndConstraints STRING)
ODBC RENAME TABLE                              -  a function returning an INTEGER value from (DatabaseID INTEGER, OriginalTableName STRING, NewTableName STRING)
ODBC TABLE ROW COUNT                           -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
ODBC TABLE COLUMN NAME$                        -  a function returning a STRING value from (DatabaseID INTEGER, TableName STRING, ColumnNumber INTEGER)
ODBC TABLE COLUMN INDEX                        -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING)
ODBC TABLE COLUMN DEFINITION$                  -  a function returning a STRING value from (DatabaseID INTEGER, TableName STRING, ColumnNumber INTEGER)
ODBC TABLE COLUMN SIZE                         -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnNumber INTEGER)
ODBC TABLE COLUMN COUNT                        -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
ODBC TABLE CELL STRING$                        -  a function returning a STRING value from (DatabaseID INTEGER, TableName STRING, ColumnNumber INTEGER, RowNumber INTEGER)
ODBC TABLE CELL INTEGER                        -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnNumber INTEGER, RowNumber INTEGER)
ODBC TABLE CELL FLOAT                          -  a function returning a FLOAT value from (DatabaseID INTEGER, TableName STRING, ColumnNumber INTEGER, RowNumber INTEGER)
ODBC BEGIN SQL QUERY                           -  a function returning an INTEGER value from (DatabaseID INTEGER, SQLQuery STRING)
ODBC BEGIN SQL QUERY SPECIFIC                  -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName1 STRING, ColumnValue1 STRING,[ ColumnName2 STRING,[ ColumnValue2 STRING,[ ColumnName3 STRING,[ ColumnValue3 STRING])
ODBC FURTHER SQL QUERY                         -  a function returning an INTEGER value from (DatabaseID INTEGER, AdditionalSQLQuery STRING)
ODBC FIRST RECORD ROW                          -  a function returning an INTEGER value from (DatabaseID INTEGER)
ODBC PREVIOUS RECORD ROW                       -  a function returning an INTEGER value from (DatabaseID INTEGER)
ODBC NEXT RECORD ROW                           -  a function returning an INTEGER value from (DatabaseID INTEGER)
ODBC GOTO RECORD ROW                           -  a function returning an INTEGER value from (DatabaseID INTEGER, RowToGoTo INTEGER)
ODBC LAST RECORD ROW                           -  a function returning an INTEGER value from (DatabaseID INTEGER)
ODBC RECORD COLUMN NAME$                       -  a function returning a STRING value from (DatabaseID INTEGER, ColumnNumber INTEGER)
ODBC RECORD COLUMN COUNT                       -  a function returning an INTEGER value from (DatabaseID INTEGER)
ODBC RECORD ROW COUNT                          -  a function returning an INTEGER value from (DatabaseID INTEGER)
ODBC RECORD CURRENT ROW STRING$                -  a function returning a STRING value from (DatabaseID INTEGER, ColumnNumber INTEGER)
ODBC RECORD CURRENT ROW INTEGER                -  a function returning an INTEGER value from (DatabaseID INTEGER, ColumnNumber INTEGER)
ODBC RECORD CURRENT ROW FLOAT                  -  a function returning a FLOAT value from (DatabaseID INTEGER, ColumnNumber INTEGER)
ODBC FINISH SQL QUERY                          -  a function returning an INTEGER value from (DatabaseID INTEGER)
ODBC UPDATING SQL STATEMENT                    -  a function returning an INTEGER value from (DatabaseID INTEGER, UpdatingSQLStatement STRING)
ODBC ADD COLUMN TO TABLE                       -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnDefinitionAndConstraint STRING)
ODBC INSERT ROW INTO TABLE                     -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, SpecifiedNamedColumns STRING, SpecifiedValuesForThoseColumns STRING)
ODBC DELETE ROWS FROM TABLE                    -  a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, WhereConditions STRING)
ODBC CREATE INDEX                              -  a function returning an INTEGER value from (DatabaseID INTEGER, IndexName STRING, TableName STRING, ColumnName STRING)
ODBC DROP INDEX                                -  a function returning an INTEGER value from (DatabaseID INTEGER, IndexName STRING)
ODBC DELETE INDEX                              -  a function returning an INTEGER value from (DatabaseID INTEGER, IndexName STRING)
    
Total Number of Commands = 43

Let me know what you think.

[edit]

I can't post anything more here as it is too old but I can edit - I've had a go at a new ODBC plugin here:-

http://forum.thegamecreators.com/?m=forum_view&t=210586&b=8

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...

Back to top

Profile PM Email

GIDustin

16

Years of Service

User Offline

Joined: 30th May 2008

Location:

Posted: 2nd Jul 2013 03:35 Edited at: 2nd Jul 2013 04:48

Link

This wouldn't happen to be a response to my recent DBConn remake request, would it?

It has the commands I was looking for, and I am about to start a very SQL heavy project, so I will be back to this thread alot. The big thing I am concerned with is memory usage, so I will hammer that as well (an array of 15+ different SQL commands, one that will consistently throw errors, executed repeatedly and randomly for 5 minutes straight, that ought to test it)

Edit: Do you have a quick example for this? I am more accustomed to using connection strings, such as the following:

+ Code Snippet

DBConnect("DRIVER={MySQL ODBC 3.51 Driver};SERVER="+sServer$+";PORT="+str$(iPort)+";DATABASE="+sDatabase$+";UID="+sUID$+";PASSWORD="+sPW$+";OPTION=3;sessionVariables=time_zone='+0:00';")

Back to top

Profile PM Email Website

Duffer

21

Years of Service

User Offline

Joined: 9th Feb 2003

Location: chair

Posted: 2nd Jul 2013 10:09

Link

@ GIDustin,

Good grief - did mention it was v0.1 didn't I? The getting column name command seems to throw it a spanner (recent discovery).

DBConnnect()....

The equivalent would be the ODBC Open Database Command.

So success = ODBC Open Database(1,"SQL-ODBC","GIDustinUser","FishIsMyPassword")

Personally, I prefer my SQLite3 plugin... no dependencies.

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...

Back to top

Profile PM Email

GIDustin

16

Years of Service

User Offline

Joined: 30th May 2008

Location:

Posted: 2nd Jul 2013 16:18

Link

@Duffer,

I know it's 0.1, but that doesn't mean I can't test it until it breaks!

I have gotten it to create a file and use that as the database, but I cannot get it to use ODBC to connect to a remote SQL server. The DBConnect is from the other ODBC DLL I am using, where you enter a connection string, such as:

+ Code Snippet

DBConn$ = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=192.168.1.191;PORT=3307;DATABASE=robyn;UID=Robyn;PASSWORD=SomePassword;OPTION=3;"

I have tried using the above as my "database", and that didn't work. I also tried creating a DSN using the Windows ODBC manager and entering that for the database, but it just made an empty file with the same name.

If you could post just a very small snippet that shows connection to a remote SQL server, I will continue testing your plugin.

Back to top

Profile PM Email Website

GIDustin

16

Years of Service

User Offline

Joined: 30th May 2008

Location:

Posted: 25th Jul 2013 03:46

Link

@Duffer,

I got around to working on this again, and I cannot get it to return anything. It will connect to the database, and run the query, and the column names works, but it will not return any strings or integers. I saw that there might be an problem with indexes as brought up in your SQLLite plugin.

The code below is what I use, and it returns the proper column and row count, but the data retrieved is all empty. Am I doing something wrong?

` Create the connection
print "Press any key to start connection"
wait key
success = ODBC OPEN DATABASE(1,"DDS_DB","un","pw")
if success=0
  print "Connect failed"
  SQL_CheckError2(0)
  sync
  end
endif
print "Connection Created!"
sync
wait key
sync on

t=ODBC BEGIN SQL QUERY(1,"select name,name,deftimeslot from dbo.assets")         : SQL_CheckError2(0)
iNum=ODBC RECORD COLUMN COUNT(1)
for i = 1 to iNum
  print "Column "+str$(i)+":  "+ODBC RECORD COLUMN NAME$(1,i)
next i
sync
` The above works fine, and I get my 3 column names

iNum=ODBC RECORD ROW COUNT(1)                                        : SQL_CheckError2(0)
for x = 0 to iNum
  print str$(x)+".0="+ODBC RECORD CURRENT ROW STRING$(1,0)           : SQL_CheckError2(0)
  print str$(x)+".1="+ODBC RECORD CURRENT ROW STRING$(1,1)           : SQL_CheckError2(0)
  print str$(x)+".2="+ODBC RECORD CURRENT ROW STRING$(1,2)           : SQL_CheckError2(0)
  print str$(x)+".3="+str$(ODBC RECORD CURRENT ROW INTEGER(1,0))     : SQL_CheckError2(0)
  print str$(x)+".4="+str$(ODBC RECORD CURRENT ROW INTEGER(1,1))     : SQL_CheckError2(0)
  print str$(x)+".5="+str$(ODBC RECORD CURRENT ROW INTEGER(1,2))     : SQL_CheckError2(0)
  sync
  nice wait 1000
  if x <> iNum then t=ODBC NEXT RECORD ROW(1)                        : SQL_CheckError2(0)
next x
` In the above, all the STRING commands return blank strings, all the integer commands return 0

print
print "Done,  Press any key to close"
sync
wait key

t=ODBC FINISH SQL QUERY(1)
t=ODBC CLOSE DATABASE(1)

end

function SQL_CheckError2(iKill)
  e$ = ODBC GET LAST ERROR$()
  if e$ <> ""
    print "Error:"
    while e$ <> ""
      print fast left$(e$,64)
      e$ = fast right$(e$,max(0,fast len(e$)-64))
    endwhile
    wait key
    if iKill then end
  endif
  sync
endfunction

+ Code Snippet

` Create the connection
print "Press any key to start connection"
wait key
success = ODBC OPEN DATABASE(1,"DDS_DB","un","pw")
if success=0
  print "Connect failed"
  SQL_CheckError2(0)
  sync
  end
endif
print "Connection Created!"
sync
wait key
sync on

t=ODBC BEGIN SQL QUERY(1,"select name,name,deftimeslot from dbo.assets")         : SQL_CheckError2(0)
iNum=ODBC RECORD COLUMN COUNT(1)
for i = 1 to iNum
  print "Column "+str$(i)+":  "+ODBC RECORD COLUMN NAME$(1,i)
next i
sync
` The above works fine, and I get my 3 column names

iNum=ODBC RECORD ROW COUNT(1)                                        : SQL_CheckError2(0)
for x = 0 to iNum
  print str$(x)+".0="+ODBC RECORD CURRENT ROW STRING$(1,0)           : SQL_CheckError2(0)
  print str$(x)+".1="+ODBC RECORD CURRENT ROW STRING$(1,1)           : SQL_CheckError2(0)
  print str$(x)+".2="+ODBC RECORD CURRENT ROW STRING$(1,2)           : SQL_CheckError2(0)
  print str$(x)+".3="+str$(ODBC RECORD CURRENT ROW INTEGER(1,0))     : SQL_CheckError2(0)
  print str$(x)+".4="+str$(ODBC RECORD CURRENT ROW INTEGER(1,1))     : SQL_CheckError2(0)
  print str$(x)+".5="+str$(ODBC RECORD CURRENT ROW INTEGER(1,2))     : SQL_CheckError2(0)
  sync
  nice wait 1000
  if x <> iNum then t=ODBC NEXT RECORD ROW(1)                        : SQL_CheckError2(0)
next x
` In the above, all the STRING commands return blank strings, all the integer commands return 0

print
print "Done,  Press any key to close"
sync
wait key

t=ODBC FINISH SQL QUERY(1)
t=ODBC CLOSE DATABASE(1)

end

function SQL_CheckError2(iKill)
  e$ = ODBC GET LAST ERROR$()
  if e$ <> ""
    print "Error:"
    while e$ <> ""
      print fast left$(e$,64)
      e$ = fast right$(e$,max(0,fast len(e$)-64))
    endwhile
    wait key
    if iKill then end
  endif
  sync
endfunction

Back to top

Profile PM Email Website

Duffer

21

Years of Service

User Offline

Joined: 9th Feb 2003

Location: chair

Posted: 25th Jul 2013 16:35 Edited at: 25th Jul 2013 16:35

Link

@ GIDustin,

Nil desperandum. It was a bit of a rush job.

I aim to spruce it up over the weekend and hopefully the code will then work for you.

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...

Back to top

Profile PM Email

GIDustin

16

Years of Service

User Offline

Joined: 30th May 2008

Location:

Posted: 25th Jul 2013 17:28

Link

No problem man.

My next project requires an active ODBC connection to two different servers at the same time, and currently this plugin is the only one that supports that. Otherwise, I have to run two different plugins simultaneously, each plugin handling one server. It will be much easier to just convert to yours once it is looked over.

Back to top

Profile PM Email Website

Duffer

21

Years of Service

User Offline

Joined: 9th Feb 2003

Location: chair

Posted: 25th Jul 2013 17:30

Link

No worries, leave it with me.

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...

Back to top

Profile PM Email

Duffer

21

Years of Service

User Offline

Joined: 9th Feb 2003

Location: chair

Posted: 25th Jul 2013 18:11 Edited at: 20th Oct 2013 18:06

Link

@ GIDustin,

Can you give v0.2 a try. I\'ve attached it here and also pegged it to the first post.

Let me know if it works at all. Fingers crossed.

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...

Back to top

Profile PM Email

GIDustin

16

Years of Service

User Offline

Joined: 30th May 2008

Location:

Posted: 26th Jul 2013 03:28

Link

@Duffer,

Not sure that helped at all. I still cannot get any data returned, but now I cannot get the names of the columns apart from the first either. The rest return blank strings.

Back to top

Profile PM Email Website

GIDustin

16

Years of Service

User Offline

Joined: 30th May 2008

Location:

Posted: 6th Aug 2013 22:50

Link

@Duffer,

Any news on this?

Back to top

Profile PM Email Website

Duffer

21

Years of Service

User Offline

Joined: 9th Feb 2003

Location: chair

Posted: 11th Oct 2013 23:16

Link

@ GIDustin,

Sorry for the delay on this.

Please can you try the attached and see if it makes a difference?

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...

Back to top

Profile PM Email

Duffer

21

Years of Service

User Offline

Joined: 9th Feb 2003

Location: chair

Posted: 20th Oct 2013 18:05 Edited at: 3rd Apr 2014 21:34

Link

@ Moderators,

I give up - try as I might, I cannot get the ODBC Plugin to work - so please can you close this thread (I will concentrate on the SQLITE plugin now).

Sorry to GIDustin...

[edit]

hopefully this new version works better:-

http://forum.thegamecreators.com/?m=forum_view&t=210586&b=8

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...

Back to top

Profile PM Email

Sorry your browser is not supported!

DLL Talk / New ODBC Database plugin