Using mysql with ODBC connection

[jfxwave]

I was using the Lua 5.1 package and it worked good but crashed on app exit.

Now i'm changing to the ODMC plugin and i'm having some problems
here is what i have and if you can help please do.

I do get a good connection using the new ODBC plugin but when i do a query i get a error like "attempt to compare a number with nil".

tbInfo = ODBC.OpenQuery("SELECT * FROM users ");

if tbInfo and nRows > 0 then
for i,v in tbInfo.Data do

checkuser = tbInfo.Data[i]["username"]
checkpassword = tbInfo.Data[i]["password"]

end
end

All i did was change this: (old)

tbInfo = MySQLQueryToTable("SELECT * FROM users ");

With this: (new)

tbInfo = ODBC.OpenQuery("SELECT * FROM users ");

It seems to me that the new line (ODBC) the tbInfo will result in a 0 or 1 and the old line will bring back a table with all the usernames and passwords.

Please help if you can

Thanks
Jean

[Dermot]

It does not return a table. You need to take a look at the sample he provided. You have to use ODBC.NextRow() to walk through the results.

while (not ODBC.IsEOF()) do
    -- process current row of data
    Grid.SetRowCount("Grid1", row+1);
    for column = 0, numCols-1 do
        data = ODBC.GetColumn(column);
        Grid.SetCellText("Grid1", row, column, data, true);
    end
    row = row + 1;
    -- fetch next row of data
    ODBC.NextRow();
end

[Ulrich]

I probably could return a table with the results of a query, in addition to the current functions already available. The way I implemented the ODBC plugin maps the functions provided in the SDK, where GetFieldValue fetches just one field of the recordset.

But I could add a new function which returns a table, if there is need for it.

Ulrich

[jfxwave]

Thanks for the fast reply.

I was hoping that wasn't the case but i knew it. LOL

It went from super ez to WHAT? LOL

all i wanted was a table containing all the usernames and passwords so i could use most of the code i already have but it looks like I'm going to be reading for i while.

The code you posted is using a grid correct?
I just need to check if the username and password is correct.


Thanks for all you help
Jean

[jfxwave]

upeters

I was typing my last message when you posted yours.

That would make my life alot better (i use alot of tables) but check with other people before you add something just for me. I will make mine work someway or other, stuff like this never held me back before.

Thanks
Jean

[Ulrich]

I just need to check if the username and password is correct.

In this case, you could perform a better query then, instead of fetching the whole table to afterwards check if the password can be verified in some line of the whole resultset. Your approach is slower and causes unnecessary load on the database server...

In other words, a more appropriate way would be something like this:

-- adapt column names accordingly
result = ODBC.OpenQuery("SELECT password FROM users WHERE username='" .. somethinghere .. "'");
if (result ~= 0) then
    -- whoops, something is very wrong here
    error = Application.GetLastError();
    Dialog.Message("ODBC Plugin", "Query failed", MB_OK, MB_ICONSTOP);
    -- close ODBC connection to release memory/handles
    ODBC.CloseConnection();
else
    -- query executed, but how many results?
    numCols = ODBC.GetNumCols();
    if (numCols < 1) then
        -- no valid result at all, do something appropriate
    else
        -- fetch the result of the query
        data = ODBC.GetColumn(0);
        -- now compare the data retrieved with what the user typed
        -- and do your thing
    end
    -- don't forget housekeeping
    ODBC.CloseConnection();
end

Ulrich

[jfxwave]

Thanks allot

I needed allot more info from the database but with your code i figured it out.


Thanks again
Jean

[Ulrich]

I need to correct my former example, as I just saw a mistake in the housekeeping part. While typing the former code, I wrote ODBC.CloseConnection instead of ODBC.CloseQuery. This is better:

ODBC.OpenDSNConnection(DSN, name, password);
result = ODBC.OpenQuery("SELECT password FROM users WHERE username='" .. somethinghere .. "'");
if (result ~= 0) then
    -- whoops, something is very wrong here
    error = Application.GetLastError();
    Dialog.Message("ODBC Plugin", "Query failed", MB_OK, MB_ICONSTOP);
else
    -- query executed, but how many results?
    numCols = ODBC.GetNumCols();
    if (numCols < 1) then
        -- no valid result at all, do something appropriate
    else
        -- fetch the result of the query
        data = ODBC.GetColumn(0);
        -- now compare the data retrieved with what the user typed
        -- and do your thing
    end
    -- don't forget housekeeping
    ODBC.CloseQuery();
end
ODBC.CloseConnection();

Ulrich