Sign in to follow this  
Followers 0
Jochem

_sqlite_exec trimming cells

11 posts in this topic

For $i = 9 To UBound($aSoftwarecheck[0][0]) Step -1
$APP = $aSoftwarecheck[$i][0]
_SQlite_Query (-1, "SELECT " & $APP & " FROM " & $table & " WHERE computer LIKE 'w%';", $hQuery)
While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK ;1 row at a time
$sMsg = $aRow[0]if StringInStr($sMsg," ")then
$aupdatedversion = stringsplit($sMsg," ")_SQLite_Exec(-1, "update " & $table & " set " & $APP & "='" & $aupdatedversion[2];")
endif
WEnd
next

I have a problem with removind a part of some collumns. I have a collumn with the software name + version and now I want to remove the software name and keep the version in the table. (The collumns allready contains the names).

so I made the above code but the the sqlite exec needs to run the command on the row the selected by the sqllte_fetchdata command. And i don`t know how

by the way is there a good tutorial somewhere about sql commands?? My knowledge is 0....

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

You're not giving enough information for us to help you. We need to know:

What is your schema, which means: show us the SQL CREATE statement used to create your table and everything related to this table (index, ...).

I understand that your "name" column carries the name of the software along with its version. I get that you want to remove the name part, which you store elsewhere in the row.

An easy way to do that would be to select both the "name" column and the column where the name (only) is stored. Then it would be very easy to remove the name from "name". That is, provided the name column is identical to the name part of "name". If we can rely on that, the whole operation can be carried out with a single SQL statement. If names part may differ, then you'll have to expose us how you determine what is the name part and consequently what is the version part. In other words we need a programmatic way to split the string.

Please try to understand that your naming of columns is unfortunate and prone to gross errors.

Showing a few complete rows with column names would definitely help to help you.

There are too many tutorials to list about generic SQL on the web, so: http://lmgtfy.com/?q=sql+tutorial and some SQLite-specific tutorials http://lmgtfy.com/?q=sqlite+tutorial

Edited by jchd

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

#3 ·  Posted (edited)

#include <SQLite.au3>
#include <networklist.au3>
 
global $aCompListtotaal = _netwerklist(0x1), $sCompList = _ArrayToString($aCompListtotaal, @LF)
global $aCompList = StringRegExp($sCompList, "(?i)(ws\-[^l].*)(?:\v*|\z)", 3)
global $hQuery, $aNames, $aRow
global $table = "INVENTORY"
global $aSoftwarecheck[10][3] = [ _
["profit", "profit", ""], _
["adobe_CS", "CS", ""], _
["adobe_CS", "CS", ""], _
["acrobat_reader ", "reader", ""], _
["flash", "flash", ""], _
["java", "Java(TM)", ""], _
["norton", "symantec", ""], _
["sketchup", "sketchup", ""], _
["autocad", "autocad", ""], _
["profit_web_wizard", "profit web wizard", ""]]
 
_SQLite_Startup()
_SQLite_Open(".\sql\KCAP_SYSTEEMBEHEER")
For $i = 9 To UBound($aSoftwarecheck[0][0]) Step -1
$APP = $aSoftwarecheck[$i][0]
_SQlite_Query (-1, "SELECT " & $APP & " FROM " & $table & " WHERE computer LIKE 'w%';", $hQuery)
While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK ;1 row at a time
$sMsg = $aRow[0]
ConsoleWrite($sMsg & @CRLF)
if StringInStr($sMsg," ")then
$aupdatedversion = stringsplit($sMsg," ")
ConsoleWrite($aupdatedversion[2] & @CRLF)
;~  _SQLite_Exec(-1, "update " & $table & " set " & $APP & "='" & $aupdatedversion[2] & "' where computer LIKE 'w%';")
endif
WEnd
next
_SQLite_Close()
_SQLite_Shutdown()

this is the complete code, but not working offcoarse because my last _SQLite_Exec command is running on all cell instead of running on the cell selected by the loop &quot;While _SQLite_FetchData&quot;

the schema is like

computer autocad adobe

-----------------------------------------------------------

ws-c99 autocad 2011 adobe CS3

ws-c100 autocad 2012 adobe CS5

etc for the rest of the used software and I want a table like

computer autocad adobe

-----------------------------------------------------------

ws-c99 2011 CS3

ws-c100 2012 CS5

Edited by Jochem

Share this post


Link to post
Share on other sites

I start to get what you have.

OK, let me put it straight: what you have is SQL used 100% backwards.

Let's gone one step at a time. I asked for the schema: this is the exact SQL statement used for creation of the database. That doesn't seem to carry any personal information nor the recipe of any MacDonald sauce so please do that.

I wished to see something like: create table inventory (...) ... but the real thing!

I suspect you also have a table of computers in the same DB. Show me its schema as well.

You should never have several software columns in your inventory: your DB is awfully denormalized. Normalization means that a single information is only present _once_ in the DB. If you and your company are serious about maintaining a DB I urge you to consider this as a primary truth.

Anyway can you please FIRST make a backup of your DB then only try this:

#include <SQLite.au3>
;~ #include <networklist.au3>
;~ global $aCompListtotaal = _netwerklist(0x1), $sCompList = _ArrayToString($aCompListtotaal, @LF)
;~ global $aCompList = StringRegExp($sCompList, "(?i)(ws\-[^l].*)(?:\v*|\z)", 3)
;~ global $table = "INVENTORY"
;~ Global $hQuery, $aNames, $aRow
Global $aSoftwarecheck[9][3] = [ _
  ["profit", "profit", ""], _
  ["adobe_CS", "CS", ""], _
  ["acrobat_reader ", "reader", ""], _
  ["flash", "flash", ""], _
  ["java", "Java™", ""], _
  ["norton", "symantec", ""], _
  ["sketchup", "sketchup", ""], _
  ["autocad", "autocad", ""], _
  ["profit_web_wizard", "profit web wizard", ""] _
]
;~   ["adobe_CS", "CS", ""], _  ; duplicate entry removed
_SQLite_Startup()
Local $hDB = _SQLite_Open(".\sql\KCAP_SYSTEEMBEHEER")
Local $aRows, $nrows, $ncols, $aupdatedversion
_SQLite_Exec($hDB, "begin immediate;")
For $i = 0 To UBound($aSoftwarecheck) - 1
_SQLite_GetTable2d($hDB, "SELECT rowid, [" & $aSoftwarecheck[$i][0] & "] FROM inventory WHERE computer LIKE 'w%';", $aRows, $nrows, $ncols)
For $j = 0 To UBound($aRow) - 1
  ConsoleWrite($aRows[$j][1] & @CRLF)
  If StringInStr($aRows[$j][1], " ") Then
   $aupdatedversion = StringSplit($aRows[$j][1], " ")
   ConsoleWrite($aupdatedversion[2] & @CRLF)
   _SQLite_Exec($hDB, "update inventory set [" & $aSoftwarecheck[$i][0] & "] = " & $aupdatedversion[2] & " where rowid = " & $aRows[$j][0])
  EndIf
Next
Next
_SQLite_Exec($hDB, "commit;")
_SQLite_Close()
_SQLite_Shutdown()

Realize that what you're going to do is the following:

For each software:

For each 'w%' computer:

trim the app if needed

next

next

With a normalized DB, you would have a table of computers, a table of softwares (editor, name, version) and a table of cross-links between those two tables (and even a separate list of editors!). SQL is not an Excel spreadsheet and even if a normalized design is much harder to follow for human eyes, SQL has been precisely designed to manipulate a bunch of thusly linked tables _very_ efficiently and produce highly flexible reports.

With the current design, should you have a very detailed software inventory on a large list of computers, you'd end up with an unmanageable inventory table having hundreds or even thousands of columns. This doesn't make sense. Also this design precludes situations where a computer has more than one version of a given software installed.

Don't hesitate to ask if something isn't clear (or not working as expected, since I have no way to test the code I guessed).


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

#7 ·  Posted (edited)

Hi, thanks for help,

I used a script to convert my excel spreadsheet to a database. I had a (not to big) spreadsheet with 10 software packages and some other data. And a script to update / check en run some commandos remote on all the computer in the network. All based on the information in the spreadsheet.

The problem with excel was that I need to have excel installed from the computer that I am running the script. So I decided to change it to a DB.

And every works now.

I will have to overlook the schema.

Is there a way to get the schema, because I never created the DB; I converted my spreadsheet?

It can be very usefull to generate some nice reports etc.

I allready printed some tutorials to check.

Edited by Jochem

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

Go download the freeware version of SQLite Expert. This is the best free SQLite manager I know of. You'll have good use of it anyway as it will enormously help you learn SQL and SQLite use.

Open your DB and hit the Schema tab. This displays the overall schema of the entire DB. For exact detail about each table, select the table in the left pane and hit the DDL tab. This displays the exact SQL statements used to create the table and indices, constraints, etc.

If your DB is that simple, you can copy and paste that. I'll then guide you towards a better design and SQL reporting.

Viewing your table is as simple as hitting the Data tab. You can edit it from there (but beware, databases don't have Undo!)

Edited by jchd

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

#9 ·  Posted (edited)

so the schema is :

CREATE TABLE [iNVENTORY] (

[computer] NOT NULL,

[user] ,

[servicetag] CHAR,

[mac_adres] CHAR,

[install_date] DATETIME,

[type] ,

[windows_version] ,

[windows_serial] ,

[office_version] ,

[office_serial] ,

[adobe_CS] ,

[acrobat_reader] ,

,

[java] ,

[norton] ,

[sketchup] ,

[autocad] ,

[profit] ,

[profit_web_wizard] );

as you see any constrain, more like a spreadsheet.

The problem with trimming is already solved actualy, I did that just before your post last time, with this code:

yours is more flexible, so I keep yours.

_SQLite_Startup()
_SQLite_Open(".\sql\KCAP_SYSTEEMBEHEER")
for $ii = 10 To 150 step 1
$cI_CompName = "WS-C" & $ii
ConsoleWrite($cI_CompName & @crlf)
For $i = 9 To UBound($aSoftwarecheck[0][0]) Step -1
$APP = $aSoftwarecheck[$i][0]
_SQlite_Query (-1, "SELECT " & $APP & " FROM " & $table & " WHERE computer='" & $cI_CompName & "';", $hQuery)
While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK ;1 row at a time
$sMsg = $aRow[0]
ConsoleWrite($sMsg & @CRLF)
if StringInStr($sMsg," ")then
$aupdatedversion = stringsplit($sMsg," ")
ConsoleWrite($aupdatedversion[2] & @CRLF)
_SQLite_Exec(-1, "update " & $table & " set " & $APP & "='" & $aupdatedversion[2] & "' where computer='" & $cI_CompName & "';")
endif
WEnd
next
next
_SQLite_Close()
_SQLite_Shutdown()
Edited by Jochem

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

Oke, I have setup some constraints, wasn`t that hard after all. but i have a question about setting up the database further:

I have now a table with computers (ID, NAME)

and a table with software (ID, TITLE, VERSION)

and after all a table inventory (COMPUTER_ID, SOFTWARE_ID)

CREATE TABLE [COMPUTERS] (

[iD] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

[NAME] CHAR NOT NULL UNIQUE COLLATE NOCASE);

CREATE TABLE [sOFTWARE] (

[iD] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

[TITLE] CHAR NOT NULL UNIQUE COLLATE NOCASE,

[VERSION] CHAR NOT NULL);

CREATE TABLE [iNVENTORY_TOTAL] (

[COMPUTER_ID] INTEGER NOT NULL CONSTRAINT [COMPUTERS] REFERENCES [COMPUTERS]([iD]) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY DEFERRED,

[sOFTWARE_ID] CHAR NOT NULL CONSTRAINT [sOFTWARE] REFERENCES [sOFTWARE]([iD]) ON DELETE CASCADE ON UPDATE CASCADE MATCH FULL NOT DEFERRABLE INITIALLY DEFERRED);

I am filling the table with:

_SQLite_Exec(-1,"INSERT OR IGNORE INTO INVENTORY_TOTAL (COMPUTER_ID,SOFTWARE_ID) VALUES((select ID from COMPUTERS where NAME = '" & $cI_CompName & "'),(select ID from SOFTWARE where TITLE = '" & $APP & "'));")

But what is the normal schema for such an table, do I need to make for all the softwares in the inventory table an collumn? or am I missing something?

Edited by Jochem

Share this post


Link to post
Share on other sites

For the record, this thread turned out into yet another SQL tutorial rallye. It has little AutoIt relevance and I switched to PMs with this member.


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

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