ODBC Prepared Statements

Databases
1

Hi, I’m new to Xojo and I’m struggling with ODBC Prepared Statements.

As a learning exercise, I’m trying to convert the Eddies Electronics example from SQLite to MS Access 2007 using ODBC accdb.

For some reason this code no longer works and I can’t fathom the reason why.

Can anyone help me over this hurdle?

Many thanks in advance.

This is the code

2

Self.prepare should refer to your database connection, i.e. db.prepare. Likewise with stmt.SQLSelect, is should be db.SQLSelect.

3

Thank you for the response, but self has a value of OrdersDatabase and stmt has a value of ODBCPreparedStatement, so I’m guessing that these are the correct values.

There must be some crucial difference between the SQLitePreparedStatement and the ODBCPreparedStatement that Xojo does not like.

4

But what class is OrdersDatabase? A class you defined yourself I guess. Tanner is right with his comment.

5

What do you mean exactly by “no longer woks”? At what point, and how, is it failing?

6

The Super of OrdersDatabase has been changed from SQLiteDatabase to ODBCDatabase.

It does not return a recordset.

This modification does work.

8

Check the error condition of the database after you attempt to get the RecordSet. I’ll bet it’s not what you think.

9

Many thanks for your help, but is this what you mean?

It shows that rs is Nil, but with the modified code rs = RecordSet.

10

You have to connect before you send any other command to the database.

11

If the super was originally an SQLiteDatabase then why are you running ODBC… statements against it?

Also, I’d suggest you clearly define the database as a global property with an unambiguous name. I suspect references to self may come back to haunt you.

12

Eddies Electronics is an example of a database program that comes with Xojo. All I am trying to do is use an Access 2007 database that has been converted from the supplied SQLite database.

I am altering the minimum amount of code that’s necessary to achieve this.

I am doing this because eventually, I want to be able to use Xojo as the frontend for my existing Access 2007 databases.

I thought that by doing it this way, it would prove to be the easiest way to learn, but I seem to have discovered some anomalies.

13

This is the connection code

14

A database object has three error properties: Error, ErrorCode, and ErrorMessage. Right after you attempt to get the RecordSet, check to see if Error is true. If so, see what the ErrorMessage says.

15

Thanks again for your help, but when I tried what you suggest it confirmed that Error is false.

16

Thanks, but is this just bad code in general or ODBC specific?

17

Self is a reserved work in Xojo, normally used in referring to control properties. Don’t name your objects ‘self’.

What ODBC connector are you using on the Mac? (There’s no built-in connector)

18

The Method FindCustomersByName is defined as a RecordSet, so the use of self in this case seems legitimate, although I thought it was odd at first.

Using Self isn’t causing the problem I am having with ODBC Prepared Statements, because I get the same problem if I modify the code in the LoadCustomers Method to avoid calling FindCustomersByName. LoadCustomers Method uses App.Orders instead of Self and causes the same problem.

You will see what I mean if you have a look at the example program Eddies Electronics.

I’m using Windows 7 Professional 64 bit with the Xojo ODBC Plug-in.

I expected that others would have encountered this problem, but I can’t find any mention on the internet.

19

I can’t speak to ODBC with Access but I have no issues with the Like condition in my ODBC queries.

Did some research and it appears Access 2007 uses “*” as the wildcard character not “%”.

Try that.

See here

20

Do you have a working ODBC connection on Windows? Was it created using the 32bit version of ODBC (C:\Windows\SysWOW64\odbcad32.exe)? Xojo can’t read 64bit connections.

Here’s what I use to establish connections to SQL Server:

  BAP4 = new ODBCDatabase_TT  
  #if TargetWin32
    BAP4.DataSource = "Driver={SQL Server};Server=192.168.242.118;Database=BAP4;UID=user;Pwd=password"
  #endif
  #if TargetCocoa
    BAP4.DataSource = "Driver={Actual SQL Server};Server=192.168.242.118;Database=BAP4;UID=user;Pwd=password"
  #endif
  if BAP4.Connect then
    MsgBox "connected"
  else
    MsgBox "error: " + BAP4.ErrorMessage
    break
  end if

On Windows, this code connects using a 32bit User DSN named BAP4.

You should be able to modify this for Access. You can find more info at http://www.connectionstrings.com/access/

21

It was worth a try, but it’s made no difference.

Thanks anyway!