Help creating AutoIT tool to query SQL table

[jaguiler]

Hi everyone,

With info from the board - I wrote the following

#include <array.au3>
#include <sql.au3>
Func connectDB()
    $sqlCon = ObjCreate("ADODB.Connection")
    $sqlCon.Open("DRIVER={SQL Server};SERVER=Server1;DATABASE=DB1;UID=sa;PWD=password;")
    Return $sqlCon
EndFunc
    $conn=connectDB()
    $results=$conn.Execute ("SELECT * FROM mytable",@LF)
msgbox(0,"test",$results)

The connection work fine - I tested

I beleive the script is running

But how do I show the results of my script or an given script in a text box or msgbox ? I would like to see the $results printed out in a table much like query analyzer shows for SQL -

Thankyou !!!

Edited September 26, 2008 by jaguiler

[zackrspv]

Hi everyone,

With info from the board - I wrote the following

#include <array.au3>
#include <sql.au3>
Func connectDB()
    $sqlCon = ObjCreate("ADODB.Connection")
    $sqlCon.Open("DRIVER={SQL Server};SERVER=Server1;DATABASE=DB1;UID=sa;PWD=password;")
    Return $sqlCon
EndFunc
    $conn=connectDB()
    $results=$conn.Execute ("SELECT * FROM mytable",@LF)
msgbox(0,"test",$results)

The connection work fine - I tested

I beleive the script is running

But how do I show the results of my script or an given script in a text box or msgbox ? I would like to see the $results printed out in a table much like query analyzer shows for SQL -

Thankyou !!!

If you want to see the results of your query, you have to work with the object. Example:

$query = "SELECT * FROM server WHERE server NOT LIKE ' '"
$ActiveTickets = _Query($sql, $query)
With $ActiveTickets
    While Not .EOF
        Global $server = .Fields['server').value
        Global $port = .Fields('port' ).value
        Global $channel = .Fields('channel' ).value
        .MoveNext
    WEnd
EndWith

Note from above, i'm able to select from the SERVER table and pull the 'server' and the 'port' and the 'channel' as values from what I want.

More details are available if you read up on the MySQL UDF. You may not be using MySQL, but syntax should still be the same. I dunno personally, as i don't use MSSQL or Access

Edited September 26, 2008 by zackrspv

[jaguiler]

What if I do not know the number of fields that I will be outputting - let's say on some queries I want 3 columns and others I want 6 columns. Is there a way to have it show me all the fields in a format without defining each field ?

[Community On Patrol]

Hi jaguiler,

1st Welcome to the AutoIt Forums!

Some of the following tips may not apply to you, but it may make your life a bit easier here on the forum in the future.

CODE

• Did you know that we have an awesome search feature?

  You can find many answers to your current questions, just by typing in the right search patterns.

• A suggestion is to use the Advanced Search mode:

• Type your specific search term in quotes.
• Click the forum you want to search in (the one most likely to have your information would generally be the Example Script forum and or the General Help and Support Forum).
• Click on "Search titles only" radio button.
• Click perform search.

The above will help you narrow down your searches and prevent you from unneccesarily posting a new thread.

[*]Also, you should try to read the Sticky posts that are at the top of each of the AutoIt Forums you enter such as:

• FAQs (Frequently Asked Questions)

• Are my AutoIt EXEs really infected? (About false positives and what to do if you may encounter one)

[*]Keep in mind, the help file will be your best friend, however you may find some of the tutorials written by some of our elite forum members helpful.

• Valuaters Interactive AutoIt 1-2-3 tutorial

• LxPs Learning to script with AutoIt3

[*]Forum Etiquette:

• Making a new thread:
  • Use the Search feature first to see if your question has already been answered.
  • Look in the help file as well before even thinking of posting (When what you want could be obtained by simply reading the help file, you don't generally get a good response from your AutoIt community).
  • Titles are very important here. 1 word titles or titles like "help me", "write something for me", "I'm a noob" etc... aren't tolerated.
  • Make sure you are posting in the correct forum:
    • General Help and Support:
      • This forum is for AutoIt related support questions only. If you have a question related to another language, or nothing at all to do with AutoIt then you need to post in the chat forum, or in that languages perspective forum.
    • Example Script:
      • This forum is for AutoIt scripts/executables only.
      • Source code is preferred but not necessary, you do have the right to just post the binary of your project if you wish.
      • Please don't post questions in this forum unless it's directly related to a thread already existing.
  • Use common sense when creating a new thread.

    Ask yourself if the title is descriptive enough to even interest someone (preferably those that know what they are talking about) to even look at your thread, let alone reply in it.

  • Think about how it would show in the search feature if someone were to look for something just like you are looking for (think of the keywords you used yourself and obviously didn't find anything (because we know you used the search feature ) and use those types of keywords in your title as well).
• Thread content:
  • Be descriptive with your query. (Make sure we actually know what you want to do).
  • Show you've made an effort in coding what you want (provide the reproducer code (generally no more than 50 lines as people lose interest in debugging someones script for free)).
  • Don't talk in ebonics. A lot of the forum members are adults, and a lot of them know how to help you, but talk like a child, you'll be treated as such.
  • Don't ask for help making keyloggers, spam (even if it's to do as a prank), or anything that can be thought of as malicious. You'll more than likely have the thread locked by a moderator, and take a bashing from your fellow AutoIt community.
  • When posting code, use code boxes. This can be accomplished by using [code ]<content here>[/code ] (No spaces between the brackets []).

    Using code boxes will keep the indentation and make it easier to read for others to help you.

• Bumping your threads:
  • Use common courtesy here.

    Keep in mind every time you bump your thread to the top of the forum, you knock the other threads down a notch.

    Everyone posting for help has just as much right for their threads to get read as you do.

    Because of that, do not bump your post more than once in a 24 hour period.

    A Bump is simply posting in your thread with nothing that pertains to your query with the sole purpose of moving it up.

    Deleting previous bumps, and posting new ones is not tolerated, and the moderators can find those deletions, so do yourself a favor and don't cross that line >_< .

• Rude or obnoxious content:

  This falls pretty much under the common sense thing. If you use it (common sense) before posting, you won't have issues.

  • Don't use foul language, remember, a lot of the community is at work when they read these threads.
  • Don't provoke or instigate an argument with someone.
• Double Posting:
  • It's understood that sometimes there's a lag in the system, and sometimes people don't see their post go up right aways so they post again.

    If this happens to you, simply notify a moderator with the report feature in the post, and politely ask them to delete it.

  • If you're just creating another topic because your original topic is not being answered the way you want or at all, this is not tolerated. You could lose your posting privileges all together over it.
• Non-English languages
  • If English is not your primary language, please make an attempt to interpret (yourself or online) and post that interpretation.

    We have wonderful users from around the world, so after you've done your post in English, back it up with your question also in your native tongue (You may find your answer much quicker using both).

That's it for now, I hope you have a wonderful learning experience, and hope to see you contribute to the community as your knowledge grows.

[zackrspv]

What if I do not know the number of fields that I will be outputting - let's say on some queries I want 3 columns and others I want 6 columns. Is there a way to have it show me all the fields in a format without defining each field ?

Well, you could use the _GetColNames() function from the MySQL UDF.

Example:

expandcollapse popup

#include <Array.au3>
$dbusername = 
$dbpassword = 
$database = 
$sitedb = 
$tablea = 

$sql = _MySQLConnect($dbusername, $dbpassword, $database, $sitedb)

$data = _GetColNames($sql, $tablea)
_ArrayDisplay($data)

_MySQLEnd($sql)

Func _GetColNames($oConnectionObj, $sTable)
    If IsObj($oConnectionObj) And Not @error Then
        Dim $ret[1], $rs
        $rs = $oConnectionObj.Execute("SHOW COLUMNS FROM " & $sTable & ";")
        With $rs
            While Not .EOF
                ReDim $ret[UBound($ret, 1) + 1]
                $ret[UBound($ret, 1) - 1] = $rs.Fields(0).Value
                .MoveNext
            WEnd
        EndWith
        $ret[0] = UBound($ret, 1) - 1
        Return $ret
    EndIf
    If @error Then
        Return 0
        SetError(1)
    EndIf
EndFunc  ;==>_GetColNames

Func _MySQLConnect($sUsername, $sPassword, $sDatabase, $sServer, $sDriver = "{MySQL ODBC 3.51 Driver}", $iPort = 3306)
    Local $v = StringMid($sDriver, 2, StringLen($sDriver) - 2)
    Local $key = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $val = RegRead($key, $v)
    If @error Or $val = "" Then
        SetError(2)
        Return 0
    EndIf
    $ObjConn = ObjCreate("ADODB.Connection")
    If @error Then MsgBox(0, "Remote ObjCreate Test", "Failed to open remote Object. Error code: " & Hex(@error, 8))
    $ObjConn.open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT=" & $iPort)
    If @error Then
        ToolTip("")
        SetError(1)
        Return 0
    Else
        Return $ObjConn
    EndIf
EndFunc  ;==>_MySQLConnect

Func _MySQLEnd($oConnectionObj)
    If IsObj($oConnectionObj) Then
        $oConnectionObj.close
        SetError(0)
        Return 1
    Else
        SetError(1)
        Return 0
    EndIf
EndFunc  ;==>_MySQLEnd

Of course you'll have to change the $dbnames, $table names, etc, the variables to make it work.

[ChrisL]

Hi everyone,

With info from the board - I wrote the following

#include <array.au3>
#include <sql.au3>
Func connectDB()
    $sqlCon = ObjCreate("ADODB.Connection")
    $sqlCon.Open("DRIVER={SQL Server};SERVER=Server1;DATABASE=DB1;UID=sa;PWD=password;")
    Return $sqlCon
EndFunc
    $conn=connectDB()
    $results=$conn.Execute ("SELECT * FROM mytable",@LF)
msgbox(0,"test",$results)

The connection work fine - I tested

I beleive the script is running

But how do I show the results of my script or an given script in a text box or msgbox ? I would like to see the $results printed out in a table much like query analyzer shows for SQL -

Thankyou !!!

There is a link to some SQL stuff in my signature, see if it helps you.