You may use the table method provided by the DB facade to begin a query. Therefore, you should never allow user input to dictate the column names referenced by your queries, including "order by" columns. PDO does not support binding column names. There is no need to clean or sanitize strings passed to the query builder as query bindings. The Laravel query builder uses PDO parameter binding to protect your application against SQL injection attacks. It can be used to perform most database operations in your application and works perfectly with all of Laravel's supported database systems. Where Clauses Or Where Clauses Where Not Clauses. I guess the only way I have to master this is by understanding how scriptcase’s grids works the SQL queries, what it really does when a query has a variable before being set (it’s set on execution, unless the system performs some kind of test run before compiling…).Laravel's database query builder provides a convenient, fluent interface to creating and running database queries. Select Statements Raw Expressions Joins Unions Basic Where Clauses. If I use the UNION statement with the INNER JOIN of a fixed database (writting the name of the database instead of using the variable), it works as expected, but if I use my variable in the query, it crashes Then Scriptcase had enough of my tweaking (and perhaps also of my twerking) and crashed like I explained. My previous working UNION statements took their data from the same database (so, there was no need of the variable), but this time, I needed to get in a GRID, the info of separate tables from separate databases, so I needed to use a INNER JOIN and my variable. Then again, It worked surprisingly good, until… This time, I created a workaround using the Scriptcase connection variables and a session variable like this (as a reminder, stores the database name of the connection) : = substr(, 0, strpos(,'_')) Īnd then, I use the session variable in any inner join query. This, again, works as intended: my client is happy and so am I… at least before deployment… That because my working evironment uses my databases named according to my domainname (and so I wrote it in the INNER JOINs), but on deployment they have another name. INNER JOIN mynewdom_warehouses.myOtherTable As T2 On T1.col3 = T2.col3 The only thing you might see is that, as I can’t use two databases for a scriptcase connection, in my queries I explicitly name the database of a table, like this: SELECT I managed to work around those issues by using inner joins, unions and the “connection” parameter of many scriptcase macros. The problem arises when my client’s requirements ask for this:ġ.- Get a report combining information of myclien_finance and myclien_warehouses together, for exampleĢ.- Don’t mix data from different databases at mysql serverīy requirement 1, I build some complex queries including INNER JOIN and UNION statementsīy requirement 2, on the other hand, I can’t create views or modify the databases so I have one single sc_connection (I know, I know, It’s kind of unefficient, but that’s what they want…)Īnyway, this was not a big deal before. This is not a problem as I can execute queries in a database different as the one used for the app connection (using the third parameter of sc_lookup “Connection”). The catch is this: My client’s requirements ask me to keep the data stored in 4 diferent schemas (say myclien_projects, myclien_finances, myclien_humanresources, myclien_warehouses). My client’s hosting handles the same way the database names. If my domain name was, then my databases would be something like mynewdom_database1. I work on a shared hosting service provider that asigns the database schemas’s name (MySQL DATABASE) tied to the hosting name. Finally I ended up with something odd, for what I have to elaborate about my query: Since I had no useful debugging information (as the error was at saving time, I wasn’t even able to build the app nor to execute it), I tried to simplify my query and “complex” it step by step. I practically don’t know a thing about PDO implementation and behaviour on PHP, but if the query works, how Scriptcase handles the queries? Didn’t it only take the result of the PDO execution of the SQL on the server? Why is Scriptcase crashing with a working query, even when it supports the UNION structure? I tested my query and it works, but when I use it to create a Grid app, scriptcase crashes even the saving process (a pop up shows up and It looses any field in the app) INNER JOIN myOtherTable AS T2 ON T1.col1 = T2.col1ĪFAIK, the first statement returns only 3 columns, as well as the second one. I want to create a grid with a MySQL UNION sentence of two SELECT statements, but one of them has some INNER JOIN statements and explicitly retrieves the same type and amount of columns something like this: (SELECT I have a running grid with a MySQL sentence that unites a single constant row with a regular select statement somehitng like this: (SELECT
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |