Sign in to follow this  
Followers 0
Merrik

ADO & MS SQL

13 posts in this topic

Hi,

I'm looking for some help to create an ADO connection to a MS SQL server on a domain from a client computer.

I need to open the connection, update two fields, and close the connection.

I need to do this without functions. This is causing me grief.

Any help is appreciated.

Thanks

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

This uses existing ODBC connection if that's what you're looking for:

$oDb = ObjCreate("ADODB.Connection")

    $oDB.Open ("ODBC_Name")  ;;Be sure to change ODBC name here

    $oSQLRS = $oDB.Execute ("Select * From DBName") ;;Change SQL Statement here

More information on ADOB.Connection Object here

Edit: Spelling

Edited by LurchMan

Dating a girl is just like writing software. Everything's going to work just fine in the testing lab (dating), but as soon as you have contract with a customer (marriage), then your program (life) is going to be facing new situations you never expected. You'll be forced to patch the code (admit you're wrong) and then the code (wife) will just end up all bloated and unmaintainable in the end.

Share this post


Link to post
Share on other sites

I was hoping to use something like this, but without the functions

Func _SQLConnect($sServer, $sDatabase, $fAuthMode = 0, $sUsername = "", $sPassword = "", $sDriver = "{SQL Server}")
    Local $sTemp = StringMid($sDriver, 2, StringLen($sDriver) - 2)
    Local $sKey = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $sVal = RegRead($sKey, $sTemp)
    If @error or $sVal = "" Then Return SetError(2, 0, 0)
    $oConn = ObjCreate("ADODB.Connection")
    If NOT IsObj($oConn) Then Return SetError(3, 0, 0)
    If $fAuthMode Then $oConn.Open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";")
    If NOT $fAuthMode Then $oConn.Open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase)
    If @error Then Return SetError(1, 0, 0)
    Return $oConn
EndFunc   ;==>_SQLConnect

Link:

Share this post


Link to post
Share on other sites

By mere curiosity: why do you need to avoid invoking functions at any rate?


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

By mere curiosity: why do you need to avoid invoking functions at any rate?

:-) I am working within a function. I have been warned about recursions and so I am trying to do this without functions. I used the example included, whuich uses functyions, and it works fine. I have little experience with SQL and could use the help.

Share this post


Link to post
Share on other sites

You can't create a function within a function (Nesting) but there is no harm at all in calling another Function from within a Function and in fact it's a very common practice.


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

Recursion is a function (say Fct) invoking itself, Fct, as part of its body. Recursion is deadly only when the condition to end recursion (return from Fct without invoking Fct anymore) is not well coded. Since stack space is bounded, unbounded recursion cause stack overflow and program crash.

OTOH that a function A invokes a function B, which itself invokes a function C, for instance, is a perfectly normal situation which you can find in almost every decent piece of code. Look at almost any standard UDF (use Alt I from Scite) to convince yourself that it isn't black magic.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Ok Not often do I stop and reply to different things but I do alot with databases and I see a need.

Do all your SQL Functions / Queries etc in the database don't put it in the program..

a. It's faster

b. Cleaner

c. Once you get the just of it, much simplier

_DatabaseOpen()
    $Recordset.Open('exec OfflineDevices', $Connection, 1, 3)
    With $Recordset
        If .Recordcount Then
            .movenext
            Next
            GUICtrlSetData($Edit, .Recordcount & " Offline Devices found." & @CRLF, 1)
        Else
            MsgBox(48, "Associated Device", "Not found.", 5)
        EndIf
    EndWith
    $Connection.close
    _DataBaseclose()

This code is just running the query in the Database and returning the Good Stuff..

The only issue I run into is when I need to pass data to the Query like a variable then I need to put that whole mile long sql statement in my program.

Unless someone else knows how to call a query with a variable attached ... Hope this helps...

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

Hi Merrik,

Somewhere in forum help me got sqlconnect.au3 attached here.

And to use it to update your table, you need to create another file as updatedata.au3 and declare as below:

#include <sqlconnect.au3>

#include <GUIConstantsEx.au3>

;Connect to sqldatabase

$oConnectSQL = _SQLConnect('servername', 'databasename', 1, 'username', 'password') ;1 = sql authentication

;Execute the query to update your table

$oExeResult = _SQLQuery($oConnectSQL, "update yourtable set field1='A', field2 = 'B' where ID=123 "); be careful with your update script

if not IsObj($oExeResult) then

Msgbox(0,"Error","$oExeResult is not an Object.")

else

Msgbox(0,"Error","Update data successfully")

endif

;Disconnect database

_SQLDisconnect($oConnectSQL)

;Your code to create gui here

Edited by mdthanh

Share this post


Link to post
Share on other sites

Somewhere in forum help me got sqlconnect.au3 attached here.

Hi mdthanh,

I guess Merrik already took a look at the _SQL* functions before. See his post from 27 April 2011 - 01:47 PM

:unsure:


Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


Link to post
Share on other sites

Hi mdthanh,

I guess Merrik already took a look at the _SQL* functions before. See his post from 27 April 2011 - 01:47 PM

:unsure:

Oopp, so let hope other could use my post.

Thanks you all,

Share this post


Link to post
Share on other sites

Oopp, so let hope other could use my post.

Thanks you all,

Hi mdthanh,

I think that any help is welcome. :unsure:


Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


Link to post
Share on other sites

Merrik, did you try connecting to the database by using the _SQL functions but as a separate file NOT called or using the services.au3.

You need to know if you can edit/update the database using the _SQL functions before adding the extra layer of running as a service.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0