List Box and MS SQL questions

[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?

[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.

[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?

 

[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;")

 

[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.

 

[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.

[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 July 12, 2015 by mLipok

[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

 

[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.

 

[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?

[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.

[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 ??

 

[mrtweaver]

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

[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