Jump to content
mrtweaver

List Box and MS SQL questions

Recommended Posts

mrtweaver

On my gui i have a list box, when something occurs in the project a message will appear, what i am wondering is if i can have like error messages appear in red text and general messages in black text and good connect messages appear in green text. Will the gui font color do this or is this not able to be done in a list box?

Second part contains question concerning using MS SQL Express and Autoit. I know that MySql does not have the command output inserted. You see i want to run a query that not only updates a column in a table but also returns a value from that row.

The following command if ran in MS SQL will update a column in a table and then return the value from trayID in that row

Update phase1 set success = 1 output inserted.trayID where stored = 12345

So in the row that equals 12345 it will update success to 1 and return the value in trayID. I know from posting on MySQL forum that this is not a feature in that product. I do know this works on MS Sql but am unsure if the UDF will support this command when working with MS SQL

If not then i will have to have two seperate transacts one to update the success column and one to return the value from trayID.

So anyone out there have much success working with MS SQL and Autoit?

Share this post


Link to post
Share on other sites
jchd

Hi,

I find it easier to answer you here rather than in PM and it can also help others.

You can safely pass whatever syntax your target SQL engine accepts, since AutoIt doesn't even look at what is passed around. The only catch is that an update statement is normaly passed to *_Exec (* depending on the UDF you use) and this doesn't expect nor get any result except a return code. For this Microsoftism, you will use instead a query and read resultset as if it was a select statement.

BTW I question the actual need to use this for a couple of reasons: it is non-portable as you've found out, and it looks like premature optimization.

Perform and time the standard:

begin;
Update phase1 set success = 1 where stored = 12345;
select trayID where stored = 12345;
commit;

Since you're writing then reading the same row back to back, everything will be in cache and will run very fast. Until you're doing a large number of such updates per second (and I already know it isn't the case), this isn't a good reason to switch SQL engines.


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
mrtweaver

Ok here is where i am currently at in development of my application.

First we have the camera which takes the pic and decodes the IMB barcode, it then passes this barcode out via tcp. Average time is 3 to 4 pieces per second, which translates to 250mS top speed as things are. The software will then receive this and will compare it to a database, this database can range in size from 25000 up to 2000000 records, each record will contain the IMB barcode as well as the success column and trayid column.

So the camera takes the pic sends over the data the software will validate that that code is in the database which of course means that piece belongs, then it will show what tray that piece belongs in on the gui. eventually it will control a usb relay but that is farther away at this time. So that would mean if i do seperate write then read that would be 8 transacts per second. will the database be able to keep up? Unsure but just to make sure if i can i would rather do it in 4 transacts.

So if i am understanding what you are saying then using that begin with the two commands such as you listed it will run both almost the same as 1?

If so i might do it that way.

So i would take what you wrote above and just put that in and _Exec? Or how would i code that in autoit?

 

Share this post


Link to post
Share on other sites
jchd

You're confusing operations (processing a single SQL statement) and performing a transaction (which typically comprises several SQL statements).
You just have to perform 4 transactions per second, not 8.

I can't guide you as I don't know which interface you're using. Here's corresponding SQLite example (w/o error checking):

_SQLite_Exec($hDb, "begin;Update phase1 set success = 1 where stored = 12345;")
_SQLite_QuerySingleRow($hDb, "select trayID where stored = 12345;", $aRow)
$TrayID = $aRow[0]
_SQLite_Exec($hDb, "commit;")

 


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
mrtweaver

Ok so what am i doing wrong with my select statement here is what i have:

$sVal = '...........'

$nSql = ('SELECT num from phase1 where stored = '" & $sVal &"' ;")

$nData= _EzMySql_Exec($nSql)

msgbox (0, "data", $nData)

It constantly shows 1

Have also tried changing the $nSql to this:

$nSql = _EzMySql_Query(.....)

msgbox (0,"data", $nSql)

Figured i would get the select query working before adding in the START TRANSACTION and COMMIT functions:

I can easily get the update to work but the select so far has not been working well.

 

Share this post


Link to post
Share on other sites
jchd

Once again, *_Exec is not expecting any result. I don't have any experience with EzMySQL so I don't know which function you should use to obtain a resultset from a select, but I bet there are examples going along with this UDF.


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
mLipok

On my gui i have a list box, when something occurs in the project a message will appear, what i am wondering is if i can have like error messages appear in red text and general messages in black text and good connect messages appear in green text. Will the gui font color do this or is this not able to be done in a list box?

Second part contains question concerning using MS SQL Express and Autoit. I know that MySql does not have the command output inserted. You see i want to run a query that not only updates a column in a table but also returns a value from that row.

The following command if ran in MS SQL will update a column in a table and then return the value from trayID in that row

Update phase1 set success = 1 output inserted.trayID where stored = 12345

So in the row that equals 12345 it will update success to 1 and return the value in trayID. I know from posting on MySQL forum that this is not a feature in that product. I do know this works on MS Sql but am unsure if the UDF will support this command when working with MS SQL

If not then i will have to have two seperate transacts one to update the success column and one to return the value from trayID.

So anyone out there have much success working with MS SQL and Autoit?

Hi.

I'm using _sql.au3 (long time, almost every day, on multiple machines) , and have no problem with this UDF.

Please post here any repro script (based on this above UDF) which show what you want to achieve, because actualy I do not know what exactly the problem is .

 

Edited by mLipok

Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API Forum Rules *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library
 

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskScheduler

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2018-10-15

Share this post


Link to post
Share on other sites
mrtweaver

mLipok is the udf you are using written for MySql, Microsoft Sql, SQLite, or generic and will do any?

Right now i just have a demo code where i am attempting both a read and a write to a MySQL database. I know in MS SQL I can do the read and write in one command using the output inserted command in SQL. I am not sure if this command is avail for use in AutoIt. The reason being was i am not a database guru so i am not sure if doing a read and write seperately will take more time than if done at same time. The application i am writing has to be very fast fastest through put right now would be around 4 a second so each time the camera read it will send the data via tcp over to the application the application will compare it with the database if it is in the database it will check off the success value by changing it to a 1 and it will retrieve the value in trayID.

Now I am sure each command in autoit takes time but in my demo program all i am doing is opening up the database, logging in, setting the instance, then reading and writing from same table, same row. According to autoit when it is done executing since i have it write things to the console it take 1.32 seconds approx

If i just do a read or a write it takes 1.1 to 1.2 so the difference seems to be negligable

So for now till i have a chance to run the appication tomorrow i wont know if it will be fast enough.

So now on with the other part of the question and that would be having colored text inthe list box

 

Share this post


Link to post
Share on other sites
mLipok

This UDF is for MS SQL.

I know in MS SQL I can do the read and write in one command using the output inserted command in SQL. I am not sure if this command is avail for use in AutoIt.

Output inserted command is part of MS SQL as you correctly state, so this is not related to AutoIt (you can use it with this above UDF)
The speed is relate to many things, not only to development environment == AutoIt.

 


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API Forum Rules *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library
 

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskScheduler

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2018-10-15

Share this post


Link to post
Share on other sites
mrtweaver

mLipok can you possibly supply an example of how to use the UDF?

I mean i know how to write the command when using something like the GUI front end for MS SQL and i could probably put that command into a Autoit line but what parameter is returned when using it?

Would you use something like

$aVal = _SQL_Exec(Update phase1 set success =1 output insertered.trayID where stored = $recv)

granted i dont have all the quotes and such on it but would this return the value to $aVal or would i put the returned value in the line such as:

_SQL_Exec(Update phase1 set success =1 output insertered.trayID where stored = $recv, $aVal)

Just not sure how it would function so hoping you can answer that.

And since it has not been addressed i am assuming that doing colored text in the list box is not an option?

Share this post


Link to post
Share on other sites
jchd

Which UDF are you using to interface with your DB engine?

For the last time, Exec doesn't fetch any result, so it won't work with an update using output inserted. Use the function in this UDF used for fetching results issued by select.


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
mLipok

 

mLipok can you possibly supply an example of how to use the UDF?

Local $sQuery = ''
Local $oADODB_Connection= _SQL_Startup(....
_SQL_Connect($oADODB_Connection, ......
Lcoal $vReturn = _SQL_Execute($oADODB_Connection, $sQuer)
MsgBox(0,'',VarGetType($vReturn))

 

And since it has not been addressed i am assuming that doing colored text in the list box is not an option?

ListBox ?? or ListView ??

 


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API Forum Rules *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library
 

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskScheduler

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2018-10-15

Share this post


Link to post
Share on other sites
mrtweaver

Using GuiCtrlCreateList to create the box then populating data inside the box as events occur

Share this post


Link to post
Share on other sites
mrtweaver

I would like to refresh this because i have a new question concerning Autoit and SQL. In this case Microsoft SQL

Ok lets take the above example given by mLipok Lets say i do the following:

$query = " update phase1 set stored = 1 ....."

If _SQL_EXEC(-1,$query)= $SQL_ERROR then ....

My question is what compromises an SQL Error? I mean lets say you send the command to a closed SQL link, that most likely would cause this error to be true. But lets say you send a select or an update or a insert, and the insert turns to be false, would that set the error to a true? For example if the end of the query was WHERE STORED = 12345 and no where in the database does stored = 12345, would this throw the SQL Error or is there a better way to test for a query that was not able to be executed?

Hope this is clear, thanks for the feedback and have a great day

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

×