Connecting to SQL backend with ODBC
I have been experimenting a little with A5v8 and the SQL features. I purchased the Enterprise edition so that I could use the SQL features and, presumably, use A5 to create applications that were based on some back-end SQL database.
I have had some experience with A5v7 connecting to Access databases and I used A5v7 to move my previous Access tables over to A5 to begin using A5 as my application tool instead of Access. It appears that A5v8 handles connections to Access in the same way as was used in v7. However, I am not sure I understand how to use the SQL or ODBC features to connect to some other SQL backend, like MS SQL Server or Firebird.
I've been experimenting with using a Unify/Gupta SQLbase database and an MS SQL Server Express database. Gupta SQLbase is used for a POS system I am experimenting with and MS SQL Server Express comes with MS Office Accounting, which I am also testing. When I begin setting up a new connection using Alpha DAO I have choices for DB2, MySQL, Oracle, MS SQL Server, and ODBC. I'm assuming that if my SQL database is not on the list I should use ODBC.
So, for Gupta SQLbase I choose ODBC and then a list of the DSN connections comes up, one of which is my Gupta data source, previously set up by Gupta and double-checked by me using the ODBC manager. Next, I select Generic since Gupta is not on the list. Although, this is not obvious and I have no idea if this selection is important. There is also a choice for ODBC. I have achieved similar results with both selections.
I then enter the correct username and password and click on the "test connection" button. I am greeted with a box that says "Succeeded" and the SQLBase Server window ends up on my taskbar. I'm thinking this is good. I have established a successful connection.
I then save the connection in A5v8 and then try to attach a table. Here's where I get unexpected results. There is no table list. The only thing that shows is "<Custom SQL Query>". I remember on previous connection to Access seeing all the tables in the Access database being listed here. But now, with an ODBC connection to Gupta I don't get a table list. Is this normal or do I have a problem with the ODBC connection? Do I need help from Gupta or can Alpha Five handle this by itself?
Now, I am assuming that since I have a choice to use a custom SQL query I could simply write an SQL statement and be able to return some data. But I haven't tried it yet. I need to brush up on my SQL before I try it. But I was hoping I could just choose tables and fields from a list and get the data over to A5. Will this be possible with any SQL database or do I need to use only databases on the drop down list provided by A5?
Edit: I went back and tried the ADO tool and I was able to see a list of tables after successfully connecting. However, I could not edit/select the fields. May be a protection issue with the Gupta database. At any rate, it appears A5 needs help to connect to this SQL data. Will I be able to write the code I need to connect to any SQL databse or do I need the help of the SQL database vendor? I don't really understand how connecting by SQL/ODBC works. I thought all I needed was a DSN for the data source in question. Is there more?
Thank you,
Sean
I have been experimenting a little with A5v8 and the SQL features. I purchased the Enterprise edition so that I could use the SQL features and, presumably, use A5 to create applications that were based on some back-end SQL database.
I have had some experience with A5v7 connecting to Access databases and I used A5v7 to move my previous Access tables over to A5 to begin using A5 as my application tool instead of Access. It appears that A5v8 handles connections to Access in the same way as was used in v7. However, I am not sure I understand how to use the SQL or ODBC features to connect to some other SQL backend, like MS SQL Server or Firebird.
I've been experimenting with using a Unify/Gupta SQLbase database and an MS SQL Server Express database. Gupta SQLbase is used for a POS system I am experimenting with and MS SQL Server Express comes with MS Office Accounting, which I am also testing. When I begin setting up a new connection using Alpha DAO I have choices for DB2, MySQL, Oracle, MS SQL Server, and ODBC. I'm assuming that if my SQL database is not on the list I should use ODBC.
So, for Gupta SQLbase I choose ODBC and then a list of the DSN connections comes up, one of which is my Gupta data source, previously set up by Gupta and double-checked by me using the ODBC manager. Next, I select Generic since Gupta is not on the list. Although, this is not obvious and I have no idea if this selection is important. There is also a choice for ODBC. I have achieved similar results with both selections.
I then enter the correct username and password and click on the "test connection" button. I am greeted with a box that says "Succeeded" and the SQLBase Server window ends up on my taskbar. I'm thinking this is good. I have established a successful connection.
I then save the connection in A5v8 and then try to attach a table. Here's where I get unexpected results. There is no table list. The only thing that shows is "<Custom SQL Query>". I remember on previous connection to Access seeing all the tables in the Access database being listed here. But now, with an ODBC connection to Gupta I don't get a table list. Is this normal or do I have a problem with the ODBC connection? Do I need help from Gupta or can Alpha Five handle this by itself?
Now, I am assuming that since I have a choice to use a custom SQL query I could simply write an SQL statement and be able to return some data. But I haven't tried it yet. I need to brush up on my SQL before I try it. But I was hoping I could just choose tables and fields from a list and get the data over to A5. Will this be possible with any SQL database or do I need to use only databases on the drop down list provided by A5?
Edit: I went back and tried the ADO tool and I was able to see a list of tables after successfully connecting. However, I could not edit/select the fields. May be a protection issue with the Gupta database. At any rate, it appears A5 needs help to connect to this SQL data. Will I be able to write the code I need to connect to any SQL databse or do I need the help of the SQL database vendor? I don't really understand how connecting by SQL/ODBC works. I thought all I needed was a DSN for the data source in question. Is there more?
Thank you,
Sean
Comment