Basic SQL Commands
Basic SQL Commands
We have the Database connect module.
I'm not finding any examples of using INSERT INTO to send data to a SQL database from Switch (including the reference manual). Can anyone give me an example statement of inserting say 5 fields into a table (named Transactions)? The database name is defined in the SQL data source in the System DSN.
What I have:
INSERT INTO Transactions ('FileName', 'Salesperson', 'Email', 'SecondEmail') VALUES (`[Job.Name],[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML",Before="@"]`, `[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML"]`, `[Metadata.Text:Path="/field-list/field[2]/value",Dataset="Submit",Model="XML"]`)
The errors I'm getting seem to be format related - complains about "incorrect syntax near "`"" or "...`Transactions`".
I'm not finding any examples of using INSERT INTO to send data to a SQL database from Switch (including the reference manual). Can anyone give me an example statement of inserting say 5 fields into a table (named Transactions)? The database name is defined in the SQL data source in the System DSN.
What I have:
INSERT INTO Transactions ('FileName', 'Salesperson', 'Email', 'SecondEmail') VALUES (`[Job.Name],[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML",Before="@"]`, `[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML"]`, `[Metadata.Text:Path="/field-list/field[2]/value",Dataset="Submit",Model="XML"]`)
The errors I'm getting seem to be format related - complains about "incorrect syntax near "`"" or "...`Transactions`".
Re: Basic SQL Commands
It should be;
INSERT table_name SET key1 = 'value1' , key2 = 'value2' ... ...
See this; viewtopic.php?f=12&t=1405
INSERT table_name SET key1 = 'value1' , key2 = 'value2' ... ...
See this; viewtopic.php?f=12&t=1405
Between jobs!
Re: Basic SQL Commands
True. In addition to my other topic reply.
I'm heavily using this one, like a couple of 100 times a day when I create new carrier labels. Haven't failed once
I'm heavily using this one, like a couple of 100 times a day when I create new carrier labels. Haven't failed once
Code: Select all
update SwitchTransport
set TrackTraceURL='[Job.PrivateData:Key="TrackingURL"]', TrackTraceCode='[Job.PrivateData:Key="Barcode"]'
where ID=[Metadata.Text:Path="//ID",Dataset="Xml",Model="XML"]
Re: Basic SQL Commands
And this is strictly using the Database connect module, and a SQL statement - no scripting? I'm not having any success.
Re: Basic SQL Commands
Yes. Is the SQL on the local computer? Can you write the data to the database from any sql-program at the switch-computer?
At the switch-computer you need to have an 32bit connector SQL -> ODBC.
Then you need to use a "ODBC Administrator" there you setup the connection between the database and the switch-computer (even if there is on the same computer). (Can't connect to my Switch now, so I can't make any screenshots.)
Then you configure Switch to talk to the ODBC Administrator.
Then you should connect throw Switch to SQL with the command I and Sander wrote.
At the switch-computer you need to have an 32bit connector SQL -> ODBC.
Then you need to use a "ODBC Administrator" there you setup the connection between the database and the switch-computer (even if there is on the same computer). (Can't connect to my Switch now, so I can't make any screenshots.)
Then you configure Switch to talk to the ODBC Administrator.
Then you should connect throw Switch to SQL with the command I and Sander wrote.
Between jobs!
Re: Basic SQL Commands
Check on all of the above (well, the MS SQL server is external but I went through all the setup stuff and it configured just fine and I get a SQL error back as I mentioned in my original post), except I can't make the insert command work. Can someone please comment on my original query:
INSERT INTO Transactions ('FileName', 'Salesperson', 'Email', 'SecondEmail') VALUES (`[Job.Name],[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML",Before="@"]`, `[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML"]`, `[Metadata.Text:Path="/field-list/field[2]/value",Dataset="Submit",Model="XML"]`)
Transactions is the name of the table. All the data I'm working with is being pulled with the "Multi-line text with variables defined" dialog in Switch.
INSERT INTO Transactions ('FileName', 'Salesperson', 'Email', 'SecondEmail') VALUES (`[Job.Name],[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML",Before="@"]`, `[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML"]`, `[Metadata.Text:Path="/field-list/field[2]/value",Dataset="Submit",Model="XML"]`)
Transactions is the name of the table. All the data I'm working with is being pulled with the "Multi-line text with variables defined" dialog in Switch.
Re: Basic SQL Commands
Then is should look like this;
INSERT Transactions SET FileName = `[Job.Name]' , Salesperson = '[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML",Before="@"]`, Email = `[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML"]`, SecondEmail = `[Metadata.Text:Path="/field-list/field[2]/value",Dataset="Submit",Model="XML"]'
Everything as one line.
INSERT Transactions SET FileName = `[Job.Name]' , Salesperson = '[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML",Before="@"]`, Email = `[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML"]`, SecondEmail = `[Metadata.Text:Path="/field-list/field[2]/value",Dataset="Submit",Model="XML"]'
Everything as one line.
Between jobs!
Re: Basic SQL Commands
I copied and pasted your statement, nada. "Incorrect syntax near the keyword `SET`" and "unclosed quotation mark after the character string "`" "
Switch 13u1, SQL 12.
Switch 13u1, SQL 12.
Re: Basic SQL Commands
You need to change all " ' " , it looks like they are not the same..
Between jobs!
Re: Basic SQL Commands
Are they supposed to be single quotes (apostrophe's) or the acute character - same key as tilde?
Re: Basic SQL Commands
This worked:
INSERT INTO Transactions
VALUES ( '[Job.Name]' , '[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML",Before="@"]', '[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML"]', '[Metadata.Text:Path="/field-list/field[2]/value",Dataset="Submit",Model="XML"]');
INSERT INTO Transactions
VALUES ( '[Job.Name]' , '[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML",Before="@"]', '[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML"]', '[Metadata.Text:Path="/field-list/field[2]/value",Dataset="Submit",Model="XML"]');
Re: Basic SQL Commands
Good!
But strange. Mabe not the same if it is MySQL or MS-SQL? I have MySQL..
But strange. Mabe not the same if it is MySQL or MS-SQL? I have MySQL..
Between jobs!