Raised This Month: $2 Target: $400
 0% 

NEW DBI API (MySQL /SQL)


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
BAILOPAN
Join Date: Jan 2004
Old 06-11-2004 , 18:22   NEW DBI API (MySQL /SQL)
Reply With Quote #1

This is very important for scripters! For AMXx 0.20, I have significantly changed the SQL interface. All plugins will be rendered broken, so you will need to upgrade.

If you need any help with this, just ask. I can help you convert your plugin or answer technical questions. Remember though, this is for 0.20 and you should be prepared to swap in the new code close to the release date (we will announce this).

This new DBI is important because it gets rid of the shoddy interface from AMX 0.9.x. Plugin writers now get resource handles for both result sets and connections, as well as a better guarantee that the driver will not crash.

To best explain this, here is a sample program:

Code:
//Create a connection     new Sql:mysql = dbi_connect("localhost", "dvander", "pass", "dbase") //If the connection is less than 1, it is bad        if (mysql < SQL_OK) {         new err[255]         new errNum = dbi_error(mysql, err, 254)         server_print("error1: %s|%d", err, errNum)         return 1     }         server_print("Connection handle: %d", mysql) //Run a query     new Result:ret = dbi_query(mysql, "INSERT INTO config (keyname, val) VALUES ('amx', 'yes')") //If the query is less than 0, it failed        if (ret < RESULT_NONE) {         new err[255]         new errNum = dbi_error(mysql, err, 254)         server_print("error2: %s|%d", err, errNum)         return 1     } //Do a select query      new Result:res = dbi_query(mysql, "SELECT * FROM config") //If the query is greater than 0, you got a handle to the result set        if (res <= RESULT_NONE) {         new err[255]         new errNum = dbi_error(mysql, err, 254)         server_print("error3: %s|%d", err, errNum)         return 1     }         server_print("Result handle: %d", res) //Loop through the result set        while (res && dbi_nextrow(res)>0) {         new qry[32] //Get the column/field called "keyname" from the result set         dbi_result(res, "keyname", qry, 32)         server_print("result: %s", qry)     } //Free the result set        dbi_free_result(res) //Close the connection      ret = dbi_close(mysql)     if (ret <= RESULT_NONE) {         new err[255]         new errNum = dbi_error(mysql, err, 254)         server_print("error4: %s|%d", err, errNum)         return 1     }

These differences are subtle and important. It means you can create nested or recursive SQL queries and not have the previous ones erased. It also gives you much better errorchecking/debugging control. You can also reference fields by name instead of number.

To see more, look in the dbi.inc file from CVS.
__________________
egg
BAILOPAN is offline
Sonic
Junior Member
Join Date: Feb 2004
Location: Germany
Old 06-11-2004 , 19:45  
Reply With Quote #2

nice

Support for lastinsertid ?
__________________
Sonic is offline
Downtown1
Veteran Member
Join Date: Mar 2004
Old 06-11-2004 , 22:54  
Reply With Quote #3

Code:
//If the query is greater than 0, you got a handle to the result set        if (res <= 0) {         new err[255]         new errNum = dbi_error(mysql, err, 254)         server_print("error3: %s|%d", err, errNum)         return 1     }         server_print("Result handle: %d", res)

What about if the result is an "Empty set"? What is it supposed to return then?
Downtown1 is offline
BAILOPAN
Join Date: Jan 2004
Old 06-11-2004 , 23:19  
Reply With Quote #4

If the result is an empty set, the return value for the result handle (in this program, it is res), will be 0.

That's why I did <0 for INSERT, to check if the insert succeeded, and <=0 for SELECT, to make sure there were items in the table.

Last insert id is a MySQL only feature so I won't add it to the DBI I think. You can do this:
SELECT MAX(field) FROM table
and get the first (only) result.
__________________
egg
BAILOPAN is offline
Sonic
Junior Member
Join Date: Feb 2004
Location: Germany
Old 06-12-2004 , 11:53  
Reply With Quote #5

Quote:
Originally Posted by BAILOPAN
If the result is an empty set, the return value for the result handle (in this program, it is res), will be 0.

That's why I did <0 for INSERT, to check if the insert succeeded, and <=0 for SELECT, to make sure there were items in the table.

Last insert id is a MySQL only feature so I won't add it to the DBI I think. You can do this:
SELECT MAX(field) FROM table
and get the first (only) result.
yes, but one query more ...
__________________
Sonic is offline
Downtown1
Veteran Member
Join Date: Mar 2004
Old 06-13-2004 , 16:13  
Reply With Quote #6

Quote:
Originally Posted by BAILOPAN
If the result is an empty set, the return value for the result handle (in this program, it is res), will be 0.

That's why I did <0 for INSERT, to check if the insert succeeded, and <=0 for SELECT, to make sure there were items in the table.

Last insert id is a MySQL only feature so I won't add it to the DBI I think. You can do this:
SELECT MAX(field) FROM table
and get the first (only) result.
If it's 0 for an "Empty set," why would you be printing an error if res == 0? An "Empty set" is a perfectly legitimate response from the DB, is it not?
Downtown1 is offline
rasvan
Member
Join Date: Mar 2004
Location: Bucharest
Old 07-05-2004 , 04:13   Function not found (name "dbi_close")
Reply With Quote #7

CS16STEAM, Linux Server.

I downloaded from CVS all new plugins and include files (dbi.inc also).
All plugins are version 2.0 now.

I get these errors.

L 07/05/2004 - 09:45:15: AMX Mod X log file started (file "addons/amxx/logs/L0705021.log") (version "0.16")
L 07/05/2004 - 09:45:15: [AMXX] Function not found (name "dbi_close") (plugin "admin.amx")

I suppose this is because I do not know how to make the modules.

If my supposition is right,
Can you help getting the modules made for us !

Thank you for any advice.
Rasvan
rasvan is offline
Send a message via Yahoo to rasvan
BAILOPAN
Join Date: Jan 2004
Old 07-06-2004 , 12:49  
Reply With Quote #8

Don't use 0.20 stuff unless you know what you are doing ;]
We put it in CVS for collaboration, not for public use.
__________________
egg
BAILOPAN is offline
rasvan
Member
Join Date: Mar 2004
Location: Bucharest
Old 07-06-2004 , 13:29  
Reply With Quote #9

Quote:
Originally Posted by BAILOPAN
Don't use 0.20 stuff unless you know what you are doing ;]
We put it in CVS for collaboration, not for public use.
BAILOPAN,

The CVS 0.20 version works great for me.

As for the error:
L 07/05/2004 - 11:20:42: [AMXX] Function not found (name "dbi_close") (plugin "admin.amx")
I just commented out this line in admin.sma:
//#define USING_SQL 1

And no more problems.

NOTE: I suppose it is not illegal to use CVS, is it ?

Thank you for everything,
Rasvan
rasvan is offline
Send a message via Yahoo to rasvan
BAILOPAN
Join Date: Jan 2004
Old 07-06-2004 , 13:33  
Reply With Quote #10

Of course not, but if you're using 0.20 plugins that use 0.20 natives, it won't work.

I guess the bottom line is: Don't ask for support for stuff that isn't released
__________________
egg
BAILOPAN is offline
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 09:27.


Powered by vBulletin®
Copyright ©2000 - 2020, vBulletin Solutions, Inc.
Theme made by Freecode